Deleting records in a Disconnected Recordset 
Author Message
 Deleting records in a Disconnected Recordset

Can anyone help with this one:

I have a recordset which is opened and then disconnected from the database
like this:

m_rec.Open strSQL, m_cnnADO, adOpenKeyset, adLockBatchOptimistic
m_rec.ActiveConnection = Nothing

The user is then able to add or delete records from this recordset. When the
data needs to be saved I reconnect the recordset and call the UpdateBatch
method on the recordset. This works fine if the user adds records but it
fails if the user deletes any records. Here is how the add and delete are
performed:

Add:
    With m_rec
          .AddNew
          !field1 = somevalue
          !field2 = somevalue
          .Update
    End with

Delete:
    With m_rec
        .MoveFirst
        .Find "field1 = '" & strValue & "'"
        If Not .EOF Then
          .Delete adAffectCurrent
        End If
    End with

Save Recordset:
    With m_rec
        .ActiveConnection = m_cnnADO
        .UpdateBatch
    End With

There error that is thrown is "Key column information is insufficient or
incorrect. Too many rows were affected by update.". I've done this with many
combinations of cursor types and lock types.

Does anyone have any idea how to make the update work for deleted
records?????

Thanks,

Brent Smith
Senior Software Engineer
Panther Systems, Inc.
(360) 993-0369 x126 (direct)
(360) 750-9783 (office)
(360) 694-0866 (fax)
Web: www.panthersys.com



Sat, 17 Apr 2004 01:18:15 GMT  
 Deleting records in a Disconnected Recordset

Quote:
> I have a recordset which is opened and then disconnected from the database
> like this:
> m_rec.Open strSQL, m_cnnADO, adOpenKeyset, adLockBatchOptimistic
> m_rec.ActiveConnection = Nothing

> The user is then able to add or delete records from this recordset. When the
> data needs to be saved I reconnect the recordset and call the UpdateBatch
> method on the recordset. This works fine if the user adds records but it
> fails if the user deletes any records.

Brent,
If there is a SQL Join in strSQL var, then make sure to set the "UNIQUE TABLE property"
http://support.microsoft.com/support/kb/articles/Q251/0/21.ASP

Also I'm assuming you have a Primary Key on your table(s).

--

Thanks,
Carl Prothman
Microsoft Visual Basic MVP
http://www.able-consulting.com



Sat, 17 Apr 2004 09:34:42 GMT  
 Deleting records in a Disconnected Recordset
There is a primary key on one of the two fields in the table. The recordset
is opened on just the table with no joins to any other table. I can insert
and update just fine. It's when I try to delete that it fails on me.

Thanks,

Brent Smith


Quote:

> > I have a recordset which is opened and then disconnected from the
database
> > like this:
> > m_rec.Open strSQL, m_cnnADO, adOpenKeyset, adLockBatchOptimistic
> > m_rec.ActiveConnection = Nothing

> > The user is then able to add or delete records from this recordset. When
the
> > data needs to be saved I reconnect the recordset and call the
UpdateBatch
> > method on the recordset. This works fine if the user adds records but it
> > fails if the user deletes any records.

> Brent,
> If there is a SQL Join in strSQL var, then make sure to set the "UNIQUE
TABLE property"
> http://support.microsoft.com/support/kb/articles/Q251/0/21.ASP

> Also I'm assuming you have a Primary Key on your table(s).

> --

> Thanks,
> Carl Prothman
> Microsoft Visual Basic MVP
> http://www.able-consulting.com



Sun, 18 Apr 2004 01:07:21 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. Delete multiple records from disconnected recordset

2. Delete multiple records from disconnected recordset

3. Insert or Delete Records in a Disconnected Hierarchical Recordset

4. Deleting records from a recordset without deleting from the database

5. Disconnected recordsets deleting data from joined tables

6. Deleting Disconnected Shaped Recordset Problem

7. Deleting Disconnected Shaped Recordset Problem

8. Deleting a Disconnected Recordset

9. deleting from disconnected recordsets

10. sub records in ADO disconnected recordset

11. Disconnected Recordset Moving through records

12. Disconnected Recordset will not move to the next record

 

 
Powered by phpBB® Forum Software