Microsoft KB Archive/294850

= PRB: ADO Delete Method May Delete More Rows Than Expected =

Article ID: 294850

Article Last Modified on 5/12/2003

-

APPLIES TO


 * Microsoft ActiveX Data Objects 2.0
 * Microsoft ActiveX Data Objects 2.1
 * Microsoft ActiveX Data Objects 2.5
 * Microsoft ActiveX Data Objects 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 &quot;Provider=MSDASQL.1;DSN=YourDSN;UID=YourUserId;PWD=YourPassword;&quot;

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

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

Debug.Print &quot;Before delete&quot; Debug.Print &quot;=============&quot; Call show_rows(rs, cn)

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

Debug.Print &quot;After delete&quot; Debug.Print &quot;============&quot; Call show_rows(rs, cn)

cn.Close

End Sub

Private Sub show_rows(rs As ADODB.Recordset, cn As ADODB.Connection) rs.Open &quot;SELECT * FROM RSDelete&quot;, cn, adOpenStatic, adLockPessimistic Do While Not rs.EOF For i = 0 To rs.Fields.Count - 1 Debug.Print rs(i) Next Debug.Print &quot; &quot; 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:

&quot;DELETE FROM RSDelete WHERE (TextField=?)&quot;

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

&quot;DELETE FROM RSDelete WHERE (ID=? AND TextField=? AND OtherField=?)&quot;

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

