PRB: ADO Recordset Delete Method Deletes More Than a Single Record (290594)

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.

MORE INFORMATION

Steps to Reproduce Behavior

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.
Set a reference to ActiveX Data Objects 2.x library.

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 = "Provider=MSDASQL;" & _
    "Extended Properties=DSN=SQLNwind;DATABASE=Northwind;Initial Catalog=northwind"
    cn.Open
    
    Set rs.ActiveConnection = cn
    rs.Source = "select Name from table1 where id=1"
    'rs.Source = "Select ID, Name from Table1 where ID=4"
    rs.CursorLocation = adUseServer
    rs.LockType = adLockOptimistic
    rs.CursorType = adOpenStatic
    rs.Open
    rs.Delete
    
    rs2.ActiveConnection = cn
    rs2.Source = "select * from table1"
    rs2.Open
    Do While Not rs2.EOF
        Debug.Print rs2.Fields(0).Value & " " & rs2.Fields(1).Value
        rs2.MoveNext
    Loop
      
    rs.Close
    rs2.Close
    cn.Close
    Set rs = Nothing
    Set rs2 = Nothing
    Set cn = Nothing
End Sub

Create a DSN on your system named SQLNwind that points to the Northwind database on your SQL Server.
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.

To work around this problem, uncomment the following line:

  'rs.Source = "Select ID, Name from Table1 where ID=4"

Comment the following line of code:

    rs.Source = "select Name from table1 where id=1"

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.