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

It isn't a grid issue, it's a SQL Server issue.  I don't know whether
MySQL or Oracle would have the same issues or not.  We do use a third
party grid because we like what it can do, but the identity key issue
applies anyhow.  We use exactly the same controls with Access and SQL
Server and will be using them with Oracle if our clients demand it.  The
magic happens in the data provider.  The UI doesn't have to know how to
deal with it.

Charlotte Foust


-----Original Message-----
From: Gustav Brock [mailto:Gustav at cactus.dk] 
Sent: Wednesday, February 23, 2005 10:57 AM
To: accessd at databaseadvisors.com
Subject: RE: [AccessD] Making SQL Server NewID() work with a dataset
in.net


Hi Charlotte

I would be both.
Is this a SQL Server issue only or would I face it with other SQL server
engines too?

Is this a limitation of all datagrids or do third-party grids manage to
deal with it?

/gustav

>>> cfoust at infostatsystems.com 23-02-2005 17:30:25 >>>
Yes, it is.  The problem only occurs with a SQL Server backend.  An
Access backend doesn't experience the problem.  One of the nice things
about .Net is that you can create separate data providers to handle SQL
Server and Access, so the details are not dealt with by the UI
programmer, only by the data tier programmer.  Of course, if you happen
to be both ... <g>

Charlotte Foust


-----Original Message-----
From: Gustav Brock [mailto:Gustav at cactus.dk] 
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