BUG: The DataAdapter Wizard generates an InsertCommand property that includes timestamp columns (815543)


The information in this article applies to:

  • Microsoft ADO.NET (included with the .NET Framework) 1.0
  • Microsoft ADO.NET (included with the Windows .NET Framework 1.1)
  • Microsoft Visual Basic .NET (2003)
  • Microsoft Visual Basic .NET (2002)
  • Microsoft Visual C# .NET (2003)
  • Microsoft Visual C# .NET (2002)

Beta Information

This article discusses a Beta release of a Microsoft product. The information in this article is provided as-is and is subject to change without notice.

No formal product support is available from Microsoft for this Beta product. For information about how to obtain support for a Beta release, see the documentation that is included with the Beta product files, or check the Web location where you downloaded the release.

SYMPTOMS

When you have a timestamp column in the query of the DataAdapter Wizard, the timestamp column is included in the InsertCommand property of the DataAdapter object, and you receive the following error message:
System.Data.SqlClient.SqlException: Cannot insert a non-null value into a timestamp column. Use INSERT with a column list or with a default of NULL for the timestamp column."
The DataAdapter Wizard uses OLE DB or Microsoft SQL Client .NET Data Providers. Although the timestamp columns are read-only columns in the table, you notice that the CommandText property in the InsertCommand property is the following:
INSERT INTO Tablename(column,
		  timestampcolumn) VALUES (@column, @timestampcolumn); SELECT column,
		  timestampcolumn FROM Tablename

CAUSE

timestamp columns are read-only columns of the table, and the values in each row of the timestamp column are unique. When the DataAdapter object connects to the Database through the Data Providers to run a set of commands, the columns in a table that cannot be updated or be inserted are marked as read-only by the Data Providers and are not included in the InsertCommand property or the UpdateCommand property of the DataAdapter object. But the timestamp columns are not marked as read-only by the .NET Data Providers in the DataAdapter object. Therefore, the InsertCommand property includes the timestamp columns.

RESOLUTION

To work around this problem, remove the timestamp column from the CommandText property of the InsertCommand property of the DataAdapter object. To do this, follow these steps:
  1. In Design View of the Form1 form in a Microsoft Visual Studio .NET project, right-click the SqlDataAdapter1 control, and then click Properties.
  2. In the Properties window, locate the InsertCommand property.
  3. Select CommandText in the InsertCommand property, and remove the tstamp column from the insertSql query
  4. In the Do you want to regenerate the Parameters collection for this command? dialog box, click Yes.
  5. In the Source column information for some parameters may be lost. Do you want to apply new parameter configuration? dialog box, click Yes.

STATUS

Microsoft has confirmed that this is a bug in the Microsoft products that are listed in the "Applies to" section of this article.

MORE INFORMATION

Steps to reproduce the behavior

  1. Run the following commands in SQL Query Analyzer to create a table with a timestamp column in the NorthWind database:
    create table student
( name char(10) primary key,
tstamp timestamp)
  2. Run the following commands in SQL Query Analyzer to insert data in the student table:
    insert into student(name)
values('Rusko')
insert into student(name)
values('Amy')
  3. In Microsoft Visual Studio .NET, create a new Windows Application project by using Microsoft Visual Basic .NET or Microsoft Visual C# .NET. By default, Form1 is created.
  4. On the View menu, click Server Explorer.
  5. Right-click Data Connections, and then click Add Connection.
  6. Type the name of your local computer that is running SQL Server, and then click to select the NorthWind database.
  7. In Server Explorer, expand Data Connections, expand your SQL Server Connection, and then expand Tables.
  8. In the Tables node, add the Student table to Form1. By default, the SqlConnection1 and the SqlDataAdapter1 controls are created.
  9. Right-click SqlDataAdapter1, and then click Properties.
  10. In the Properties window, locate the InsertCommand property.

    You notice the CommandText property of the InsertCommand property that is mentioned in the "Symptoms" section of this article.

REFERENCES

For more information, visit the following Microsoft Developer Network (MSDN) Web sites:

http://msdn.microsoft.com/library/en-us/cpguide/html/cpconadonetproviders.asp

http://msdn.microsoft.com/library/en-us/cpref/html/frlrfSystemDataSqlClientSqlDataAdapterClassTopic.asp

Modification Type:MinorLast Reviewed:3/9/2006
Keywords:kbvs2002sp1sweep kbpending kbWindowsForms kbSSExplorer kbSqlClient kbProvider kbDataObject kbDataLoader kbDataBinding kbDatabase kbDataAdapter kbbug KB815543 kbAudDeveloper
©2004 Microsoft Corporation. All rights reserved.