PRB: ADO Delete Method May Delete More Rows Than Expected (294850)


The information in this article applies to:

  • ActiveX Data Objects (ADO) 2.0
  • ActiveX Data Objects (ADO) 2.1
  • ActiveX Data Objects (ADO) 2.5
  • ActiveX Data Objects (ADO) 2.6
  • Microsoft Data Access Components 2.0
  • Microsoft Data Access Components 2.1
  • Microsoft Data Access Components 2.5
  • Microsoft Data Access Components 2.6
  • Microsoft Data Access Components 2.7
This article was previously published under Q294850

SYMPTOMS

The Delete method of the ActiveX Data Objects (ADO) Recordset object may delete more than the rows identified for deletion if the following conditions are true:
  • You are connecting to SQL Server as a data source.
  • You are using the MSDASQL OLE DB provider.
  • The value of CursorType is not adOpenDynamic.
  • Columns specified in the resultset do not contain a unique field.
  • The table against which you are querying has no primary key.
If all of these conditions are met and a Delete method is called on the current record in the recordset, all matching records returned by the query (instead of just the current record) will be marked for deletion.

STATUS

This behavior is by design.

MORE INFORMATION

The following Microsoft Visual Basic sample code demonstrates this behavior: 
Private Sub Command1_Click()
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset

cn.Open "Provider=MSDASQL.1;DSN=YourDSN;UID=YourUserId;PWD=YourPassword;"

On Error Resume Next
cn.Execute "drop table RSDelete"
On Error GoTo 0
  
cn.Execute "CREATE TABLE RSDelete (ID int NOT NULL IDENTITY (1, 1),  TextField char(10) NOT NULL, OtherField char(10) )"

rs.Open "SELECT * FROM RSDelete", cn, adOpenDynamic, adLockPessimistic
rs.AddNew
rs("TextField") = "Value1"
rs("OtherField") = "Dummy A"
rs.Update
rs.AddNew
rs("TextField") = "Value1"
rs("OtherField") = "Dummy B"
rs.Update
rs.AddNew
rs("TextField") = "Value2"
rs("OtherField") = "Dummy C"
rs.Update
rs.AddNew
rs("TextField") = "Value2"
rs("OtherField") = "Dummy D"
rs.Update
rs.Close

Debug.Print "Before delete"
Debug.Print "============="
Call show_rows(rs, cn)

rs.Open "SELECT TextField FROM RSDelete where ID = 1", cn, adOpenStatic, adLockPessimistic
rs.MoveFirst
rs.Delete
rs.Close

Debug.Print "After delete"
Debug.Print "============"
Call show_rows(rs, cn)

cn.Close

End Sub

Private Sub show_rows(rs As ADODB.Recordset, cn As ADODB.Connection)
rs.Open "SELECT * FROM RSDelete", cn, adOpenStatic, adLockPessimistic
Do While Not rs.EOF
    For i = 0 To rs.Fields.Count - 1
        Debug.Print rs(i)
    Next
    Debug.Print " "
    rs.MoveNext
Loop
rs.Close
End Sub
If you capture an ODBC Trace while the above code is running, you can see the following DELETE statement:

"DELETE FROM RSDelete WHERE (TextField=?)"

If you modify the query in the above code as follows: 
rs.Open "SELECT * FROM RSDelete where ID = 1", cn, adOpenStatic, adLockPessimistic
rs.MoveFirst
rs.Delete
rs.Close
you will see the following DELETE statement in the ODBC Trace:

"DELETE FROM RSDelete WHERE (ID=? AND TextField=? AND OtherField=?)"

In the latter case, the parameters are bound to the respective columns in each row.

REFERENCES

For additional information, click the article number below to view the article in the Microsoft Knowledge Base:

274551 HOWTO: Generate an ODBC Trace w/ ODBC Data Source Administrator

193946 HOWTO: Demo of ADO AddNew, Update, Delete, Find and Filter

Modification Type:MajorLast Reviewed:5/12/2003
Keywords:kbprb KB294850
©2002 Microsoft Corporation. All rights reserved.