Microsoft KB Archive/290594

= PRB: ADO Recordset Delete Method Deletes More Than a Single Record =

Article ID: 290594

Article Last Modified on 8/23/2001

-

APPLIES TO


 * Microsoft ActiveX Data Objects 2.5
 * Microsoft ActiveX Data Objects 2.6
 * Microsoft ActiveX Data Objects 2.7

-



This article was previously published under Q290594



SYMPTOMS
Sometimes the Delete method of the ADO Recordset object may delete more than the row that you identify for deletion. This occurs only if the following items are true:
 * The data source is SQL Server.
 * The OLE DB provider is Microsoft OLE DB Provider for ODBC Drivers.
 * The value of the CursorType property is anything other than adOpenDynamic.
 * The columns that are specified in the resultset do not contain a unique field.
 * The queried table does not contain a primary key.

If all of these conditions are true, and the Delete method is called on a record in the Recordset, all the records that match the values of the fields that are returned in the query are deleted instead of that single record being deleted.



CAUSE
When the OLE DB Provider for ODBC Drivers creates a Delete statement to send back to the backend datasource, it only includes the columns that are selected. Thus, if you select only a text field, this text field is used in the where condition for the Delete method when you call a Delete.

For example, suppose that you have a table with two fields: ID (an identity field) and Owner (a text field). Suppose that a Recordset is created from the following query: SELECT Owner FROM Table WHERE ID = n If you call the Delete method on the single record that is returned (because ID is an identity field), all records that match the value for Owner are deleted. An ODBC trace shows that the following DELETE statement is executed: DELETE FROM Table WHERE Owner = ?

The OLE DB Provider for ODBC drivers is including all equally comparable columns in the rowset in the where clause. In the above example, because the original select only contains one text field, this is all that the OLE DB Provider for ODBC includes in the where clause.



RESOLUTION
There are two ways to work around this problem:
 * Use the OLE DB Provider for SQL Server instead of the Microsoft OLE DB Provider for ODBC Driver.
 * Select a unique field in addition to the text field.

For a code sample that demonstrates this, see the &quot;More Information&quot; section.



STATUS
This behavior is by design.



Create Table in SQL Server
 Open SQL Server Query Analyzer. In the Database drop-down list box on the toolbar, select the Northwind database.  Copy and paste the following SQL script to the Query Analyzer window, and click Run. This creates a table named Table1 with 6 records. if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Table1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[Table1] GO

CREATE TABLE [dbo].[Table1] (   [ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL,    [Name] [char] (10) NULL ) ON [PRIMARY] GO

Insert into table1 (name) values ('abc'); Insert into table1 (name) values ('abc'); Insert into table1 (name) values ('abc'); Insert into table1 (name) values ('def'); Insert into table1 (name) values ('def'); Insert into table1 (name) values ('def'); GO 

Create the Visual Basic Project
 Open a new Standard EXE project in Visual Basic. Form1 is created by default.</li> Set a reference to ActiveX Data Objects 2.x library.</li>  Copy and paste the following code into the code window: Option Explicit

Dim cn As ADODB.Connection Dim rs As ADODB.Recordset Dim rs2 As ADODB.Recordset

Private Sub Form_Load Set cn = New ADODB.Connection Set rs = New ADODB.Recordset Set rs2 = New ADODB.Recordset cn.ConnectionString = &quot;Provider=MSDASQL;&quot; & _ &quot;Extended Properties=DSN=SQLNwind;DATABASE=Northwind;Initial Catalog=northwind&quot; cn.Open Set rs.ActiveConnection = cn   rs.Source = &quot;select Name from table1 where id=1&quot; 'rs.Source = &quot;Select ID, Name from Table1 where ID=4&quot; rs.CursorLocation = adUseServer rs.LockType = adLockOptimistic rs.CursorType = adOpenStatic rs.Open rs.Delete rs2.ActiveConnection = cn   rs2.Source = &quot;select * from table1&quot; rs2.Open Do While Not rs2.EOF Debug.Print rs2.Fields(0).Value & &quot; &quot; & rs2.Fields(1).Value rs2.MoveNext Loop rs.Close rs2.Close cn.Close Set rs = Nothing Set rs2 = Nothing Set cn = Nothing End Sub </li> Create a DSN on your system named SQLNwind that points to the Northwind database on your SQL Server.</li> Run the code. Notice that the Debug window only displays records with the ID of 4, 5, and 6. These three records contain 'def' in the Name field. This indicates that records with the ID of 1, 2, and 3 that had the 'abc' value in the Name field were deleted.</li>  To work around this problem, uncomment the following line: 'rs.Source = &quot;Select ID, Name from Table1 where ID=4&quot; </li>  Comment the following line of code: rs.Source = &quot;select Name from table1 where id=1&quot; </li> Run the project. Notice that the Debug window displays records with the ID of 5 and 6. This indicates that only the record that is selected in the Recordset was deleted as expected and not all the records that have a matching Name field.</li></ol>

Keywords: kbdatabase kbprb KB290594

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.