Problem solve Get help with specific problems with your technologies, process and projects.

VB.NET and SQL Server primary key newid() default value

When a primary key is a unique ID datatype with the newid() default value, adding records in a grid in VB.NET will cause an error disallowing nulls.

This tip was submitted to the VS.NET Info Center by member Janet Landis. Please let other users know how useful it is by rating it below. Do you have a tip or code of your own you'd like to share? Submit it here.


When the underlying datasource of a dataset table in VB.NET is a SQL Server table with a primary key that is a 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 does not allow nulls.

We know that the key field will be defaulted on the SQL Server side, but VB.NET enforces the null before the data is pushed to SQL Server. Luckily, the solution is easy, since we have access to the dataset XML.

First we can delete the key from the dataset table by right clicking on the table and selecting Delete Key. The field is not deleted, just the key indicator for the field. Now the field will not require a unique value.

But we still need to allow the value to be null. This is done by adding minOccurs="0" to the <xs:element> 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 with no error and SQL Server will default the key field values with newid() once the table is updated.

Dig Deeper on Win Development Resources

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchCloudComputing

SearchSoftwareQuality

TheServerSide.com

Close