VB Forum / Database Access / August 2004

After upgrading to SQL Server 2000 SP3, our VB app is encountering a really
nasty bug.

The error is:
"Key column information is insufficient or incorrect. Too many rows were
affected by update."

I use VB6 (SP5) and ADO 2.7

1) First I open a disconnected recordset.  (RecID is a Primary key,
CustomerID is a Foreign key)
   Dim Rs As New ADODB.Recordset
   Rs.CursorLocation = adUseClient
   Rs.Open "SELECT RecID, CustomerID, Street, CurrentFlag FROM Address
WHERE OldFlag = 'Y' AND CustomerID = 10", Cn, adOpenStatic,
adLockBatchOptimistic
   Set Rs.ActiveConnection = Nothing   ' Disconnected recordset

2) If a customer has 3 addresses with OldFlag = 'Y', Rs will have 3 records.
Let the user scroll to ONE record and click chkDelete Checkboxes.

3) When user clicks chkDelete in the interface, scroll to the record and
delete.
    .MoveFirst
    Do While Not .EOF
         If !RecID= SelectedID Then
            blnFound = True
            Exit Do
       End If
         .MoveNext
    Loop
    if blnFound then Rs.Delete

4) When user hits Save button, do
    Set recAddress.ActiveConnection = Cn
    recAddress.UpdateBatch     ' Results in ERROR

UpdateBatch will result in Err.Number = -2147467259
"Key column information is insufficient or incorrect. Too many rows were
affected by update."

If I query the table at this point, ALL the addresses that belonged to
CustomerID = 10 are deleted, even the ones with OldFlag = 'N' !!

The above code used to work fine when we had SQL Server 7.0.

Has anyone encountered this problem?
Does anybody know a solution/work around?
I'd really appreciate it.

Ktaro