BUG: Inserting a New Record With addImmediate May Fail (190591)

SYMPTOMS

If you bind a server-side Recordset DTC to a table or query that has an ID (AutoNumber) field as part of the primary key, an attempt to insert a new record with addImmediate() after round-tripping the page may fail. This typically happens when inserting records with some type of Active Server Pages (ASP) data form. Similarly, if you are using addRecord() and updateRecord() to insert new records, you could run into this bug if you call updateRecord() and then addRecord() on the same round-trip.

CAUSE

When you insert a new record into a table or query that has an ID (AutoNumber) field as part of the primary key, the value of the primary key for the new record is unknown at the time of insertion. Therefore, the Recordset cannot be returned to the current record when you round-trip the page, and the Recordset is flagged as "non-updateable" to safeguard against modifying an unknown record. If you then try to update the recordset or add a new record, the operation will fail.

RESOLUTION

One simple workaround for this problem is to call the requery() method of the Recordset DTC after you insert a new record. This will ensure the Recordset is updateable when you round-trip the page. In the case where you are using addRecord() and updateRecord() to insert new records, you should call the requery() method after calling updateRecord().

STATUS

Microsoft has confirmed this to be a bug in the Microsoft products listed at the beginning of this article. We are researching this bug and will post new information here in the Microsoft Knowledge Base as it becomes available.

MORE INFORMATION

Steps to Reproduce Behavior

Create a new ASP page.
Enable the scripting object model.
Insert a Recordset DTC called Recordset1, and bind it to a table with an ID (AutoNumber) field as a primary key, for example, the Shippers table in Northwind)
Insert a Button DTC called Button1.

Create a server-side onclick handler for the Button DTC that calls Recordset1.addImmediate():

      <SCRIPT LANGUAGE=VBScript RUNAT=Server>
      Sub Button1_onclick()
         Dim arrFields(1)
         Dim arrValues(1)
         arrFields(0) = "CompanyName"
         arrFields(1) = "Phone"
         arrValues(0) = "MyShipper"
         arrValues(1) = "(503) 555-9931"
         Recordset1.addImmediate arrFields, arrValues
      End Sub
      </SCRIPT>

View the page in your browser, and click Button1.
Click Button1 again.