[AccessD] Making SQL Server NewID() work with a dataset in .net

Hi Gustav:

How did you anticipate the error I was having before I mentioned it?

TIA
Jim

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
Sent: Wednesday, February 23, 2005 1:09 AM
To: accessd at databaseadvisors.com
Subject: [AccessD] Making SQL Server NewID() work with a dataset in .net

Hi all using dotnet.

Snipped this from the ElementK Tips. 
I have not used the datagrid from dotnet and have to ask if this really
is the way to handle this task? From an Access view it seems like moving
to the stone age.

Or would I just use another more clever grid?

/gustav


Making SQL Server primary key NEWID() default values work with a
Visual Basic .NET DataSet (Visual Basic .NET/SQL 2000)

Designing a SQL Server table to use the uniqueidentifier
datatype for its primary key column and then giving it a default
with the NEWID() function is a great way to manage your data;
however, there can be a slight snag if you use ADO.NET's DataSet
in Visual Basic .NET with such a table. In this scenario, adding
records in a DataGrid in Visual Basic .NET will cause an error.
The primary key field doesn't allow NULL values. We know that the
key field will be defaulted on the SQL Server side, but Visual
Basic .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 from the dataset table by
right-clicking on the table and selecting the 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. 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 without raising any errors,
and SQL Server will use the NEWID() default for primary key
column values once the table is updated.

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com