The CommandBuilder object may rebuild a command that you try to modify during the next call to the DataAdapter.Update method and your changes to the command may be lost

Article translations Article translations
Article ID: 310366 - View products that this article applies to.
This article was previously published under Q310366
This article has been archived. It is offered "as is" and will no longer be updated.
This article refers to the following Microsoft .NET Framework Class Library namespace:
  • System.Data.SqlClient
Expand all | Collapse all

On This Page

SYMPTOMS

The CommandBuilder object may rebuild a command that you try to modify during the next call to the DataAdapter.Update method, and your changes to the command may be lost. This problem occurs under the following circumstances:
  • If you associate a CommandBuilder object with a DataAdapter object.
  • If you use the GetInsertCommand, GetUpdateCommand, or GetDeleteCommand method of the CommandBuilder object to explicitly assign commands to the DataAdapter.
  • If you modify one of the commands that CommandBuilder generates.
When you try to call the Update method of the DataAdapter, you may receive the following error message:
An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in system.data.dll

CAUSE

This problem occurs because CommandBuilder dynamically alters the commands that it generates back to the original command.

RESOLUTION

Use one of the following methods to resolve this problem:
  • Do not modify commands that CommandBuilder generates. CommandBuilder does not alter Command objects that you build yourself.
  • Copy the InsertCommand, DeleteCommand, and UpdateCommand objects to a new DataAdapter (see the "More Information" section for an example). The new DataAdapter variable must have the same scope or narrower as the old DataAdapter variable.
  • Do not use CommandBuilder at all. Write your own Command objects, or use Visual Data Tools to write them.

STATUS

This behavior is by design.

MORE INFORMATION

Steps to Reproduce the Behavior

  1. Create a new Visual Basic Windows Application project. Form1 is added to the project by default.
  2. Double-click the form, and add the following code at the top of the Code window:
    Imports System.Data.SqlClient
    					
  3. Add a Button control to Form1.
  4. Double-click the button, and add the following code to the Click event:
    Dim cn As New SqlConnection()
            Dim custDS As New DataSet()
            Dim da1 As New SqlDataAdapter()
            Dim da2 As New SqlDataAdapter()
            Dim dr As DataRow
            Dim cb As SqlCommandBuilder
    
            cn.ConnectionString = "server=servername;database=northwind;uid=sa;pwd=password;"
            cn.Open()
            da1 = New SqlDataAdapter("select * from Customers", cn)
            cb = New SqlCommandBuilder(da1)
    
            da1.Fill(custDS, "Customers")
    
            'Get the original commands.
    
            da1.InsertCommand = cb.GetInsertCommand
            da1.DeleteCommand = cb.GetDeleteCommand
            da1.UpdateCommand = cb.GetUpdateCommand
    
            Debug.WriteLine("Original command length: " & da1.InsertCommand.CommandText.Length)
    
            'Modify the Insert command.
    
            da1.InsertCommand.CommandText = "select * from customers where customerid=@@identity"
    
            da1.InsertCommand.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord
    
            da2.InsertCommand = da1.InsertCommand
            da2.DeleteCommand = da1.DeleteCommand
            da2.UpdateCommand = da1.UpdateCommand
    
            'Add a record to the table.
    
            Dim tblCust As DataTable
            tblCust = custDS.Tables("Customers")
    
            Dim drCust As DataRow
    
            drCust = tblCust.NewRow()
            drCust("CustomerID") = "ZYYYY"
            drCust("CompanyName") = "Zora's Yummies"
            drCust("ContactName") = "Christophe Namby"
            drCust("ContactTitle") = "Assistant Manager"
    
            tblCust.Rows.Add(drCust)
    
            'Update the backend.
    
            Debug.WriteLine("Length da1 before insert: " & da1.InsertCommand.CommandText.Length)
    
            da1.Update(custDS, "Customers")
    
            Debug.WriteLine("Length da1 after insert: " & da1.InsertCommand.CommandText.Length)
            Debug.WriteLine("length da2 before insert: " & da2.InsertCommand.CommandText.Length)
    
            da2.Update(custDS, "Customers")
    
            Debug.WriteLine("Length da2 after insert: " & da2.InsertCommand.CommandText.Length)
    					
  5. Modify the connection string to connect to your Microsoft SQL Server computer.
  6. Press the F5 key to run the code.
  7. Comment out the following line to resolve this problem:
            da1.Update(custDS, "Customers")
    					
This resolution works because CommandBuilder hooks the RowUpdating event on the DataAdapter ("da1") so that it knows when to generate the commands. When you copy the commands to a different DataAdapter ("da2"), CommandBuilder is no longer hooked into its events and does not alter them.

Variable scope is important because if "da1" and SqlCommandBuilder ("cb") go out of scope, and if garbage collection occurs, CommandBuilder sets its commands to Nothing (or null in Visual C#). Thus, you cannot use these commands in "da2." Because garbage collection may occur at random times, the da2.Update method call may raise intermittent NullReferenceException errors.

Properties

Article ID: 310366 - Last Review: February 28, 2014 - Revision: 1.7
APPLIES TO
  • Microsoft ADO.NET 2.0
  • Microsoft ADO.NET (included with the .NET Framework)
  • Microsoft Visual Basic .NET 2002 Standard Edition
  • Microsoft Visual Basic .NET 2003 Standard Edition
  • Microsoft Visual Basic 2005
Keywords: 
kbnosurvey kbarchive kbtshoot kberrmsg kbprb kbsqlclient kbsystemdata KB310366

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com