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

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...

Digg This!     StumbleUpon     Del.icio.us

RELATED CONTENT Visual Basic and Visual Basic .NET Compose XML more quickly using Visual Basic 9 Virtualization keeps Legacy Apps alive VB 9 Anonymous Types help create flexible objects Visual Studio Team System Add-ins: Conchango Scrum for Team System and Scrum Dashboard Book Excerpt: Sams Teach Yourself Visual Basic 2008 in 24 Hours -- Complete Starter Kit Check out CodePlex for a ton of interesting .NET projects Book excerpt: Murach's VB 2008 Book excerpt: Printing in Visual Basic 2005 Visual Basic 2008 and closures WinForms development using SQL Server 2005 and Visual Basic 2005 Microsoft SQL Server Using the Visual Studio 2005 DataSet Designer to build a data access layer The CTE, the hierarchical query and SQL Server 2005 SQL Server 2005 recursive functions and the CTE Choose the right .NET data provider, optimize application performance The fallacy of the data layer -- or, a new architectural model for software designs Book excerpt: ADO.NET and SQL Server 2005 Addressing common SQL Server questions Book Excerpt: The .NET Framework and SQL Server 2005 WinForms development using SQL Server 2005 and Visual Basic 2005 Configuring ASP.NET 2.0 apps to SQL Server 2005 databases

RELATED RESOURCES 2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems Search Bitpipe.com for the latest white papers and business webcasts Whatis.com, the online computer dictionary

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.

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.

Rate this Tip To rate tips, you must be a member of SearchWinDevelopment.com. Register now to start rating these tips. Log in if you are already a member. DISCLAIMER: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.