Active Server - ASP.NET And SQL Server Primary Key Newid() Default Values (ASP.NET 1.0+, SQL 7.0/2000)

When the underlying data source of a DataSet table in VB.NET is a SQL Server table with a primary key that's an unique identifier datatype with the NEWID() default value, adding records in a grid in VB.NET will cause an error that the primary key field doesn't allow NULLs. We know that the key field will be defaulted on SQL Server side, but VB.NET enforces the NULL before the data is pushed to SQL Server. The solution is easy, since we have access to the dataset XML. First, we can delete the key form the dataset table by right-clicking on the table and selecting Delete Key. The field isn't deleted, just the key indicator for the field. Now the field won't require a unique value, but we still need to allow the value to be NULL. You can do this by adding minOccurs="0" to the code for this field as in the example below:

  • < xs:element
  • name="KeyFieldName"
  • msdata:DataType="System.Guid, mscorlib, version=1.0.3300.0,Culture=neutral, PublicKeyToken=b77a5c561934e089"
  • type="xs:string"
  • minOccurs="0" />

Now records can be added to the grid without raising any errors, and SQL Server will default the key field values with NEWID() once the table is updated.

Go back