VS2005/VS2008 DataSet designer, insert a row into a table that has an autogenerated guid column

Question

Hello all,

I have a strongly typed DataTable created with the VS2005/VS2008 DataSet designer.

The table has a Primary Key column that is a guid, which gets populated by SQL server. The problem is when I want add a row (or multiple rows) to my DataTable and then call the DataAdapter.Update method (passing in the DataTable). When DataAdapter.Update is called I get a SQL exception saying that I cannot insert NULL into the primary key column.

How do I tell the designer that this is an autogenerated column and I do not want to provide a value for new rows? I just want the value generated by SQL.

Am I missing something here, or is this a limitation of the DataSet designer?

I know how achieve this using LINQ to SQL, but unfortunatley I do not have it at my disposal for this project.

Answer 1

You're problably using DEFAULT NEWID() on your SQL Server table definition so the problem may be that the Dataset designer doesn't see this column as auto-generated.

Maybe generating guid in your application code could be a solution? If not, then you could set default value in you Dataset but then you're probably have to also change DataAdapter Insert/Update statements so that this default value doesn't get inserted into Sql Server table.

There could also be some other solution that I'm not aware of...

Answer 2

Possibly one of these:

• If you don't need the column in your DataSet for your app, then remove it.
• If you want the column but don't care to give it a value, then change it to allow DBNull.
• You can always turn off constraint enforcement (probably a bad idea): DataSet.EnforceConstraints = false
• You could fill the column with a surrogate key that does not get sent to the DB.

For the first two options, if you want the convenience of letting the designer keep your structure in sync with your database, then you could remove the column or allow null programmatically, perhaps right next to a "// HACK: " comment explaining why.