Can't get data table to stop constraining user input

[David]

Hi,

I have a data table that was generated using the dataset designer. It refers
to a table in a database that has a primary key. The primary key constraint
is captured by the designer and enforces a value/uniqueness in client
dataset.

I have added the select, delete, update, insert commands to the table
adapter. I have had to alter the 'default value' for the primary key column
to = 0 instead of allowing auto-increment. This works ok with the stored
procedures, however because the uniqueness contraint is being managed by the
dataset I can't enter more than one record at a time between saves to the
database (and retrieval of the new id). I would like to allow this column
to maintain the primary key in the dataset - to keep it related to other
tables - but to allow <DBNull> in this field. The designer won't let me do
anything other than 'throw exception' if there is a null value in this
column.

Note I can't generate the next free value (auto-increment) in the dataset as
the database has to do this; there are mapping tables that get appended to
when an insert is carried out and all this works within a stored procedure.

DB schema looks like:-

create table customer
(
cust_id numeric(8,0) identity not null,
cust_name nvarchar(100) not null,
cust_date_added datetime default getdate() not null
)
alter table customer add constraint customer_pkey primary key (cust_id)
go

As explained I would like to keep the primary key in the dataset, but allow
it to be null before committing to the database without throwing exceptions.

Thanks,

David

 

[Cor Ligthert [MVP]]

David,

That is a standard action. You can manage it a little bit by yourself by
setting the first one on zero or -2 and then set the seeds etc. for that.

http://msdn2.microsoft.com/zh-cn/li...r.management.smo.column.identity(SQL.90).aspx

Have a look as well to the identityseed and the identiyincrement

I hope this helps,

Cor