Microsoft KB Archive Search

Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

  1. Home
  2. PRB: ADO Delete Method May Delete More Rows Than Expected

PRB: ADO Delete Method May Delete More Rows Than Expected

View products that this article applies to.

This article was previously published under Q294850

↑ Back to the top

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.

↑ Back to the top

Status

This behavior is by design.

↑ Back to the top

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.

↑ Back to the top

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

↑ Back to the top

Keywords: KB294850, kbprb

↑ Back to the top

Article Info
Article ID : 294850
Revision : 3
Created on : 5/12/2003
Published on : 5/12/2003
Exists online : False
Views : 371