Prevent negative id's from being inserted into Database from .NET DataSet

Question

I have my data set filled and I allow a user to add a row to the data table. Once they confirm they want to add the row I add the row to the DataSet and proceed to update the Database DataTable.

MyTypedDataSet.MyDataTable newRow = dataSetObject.MyDataTable.NewMyDataTableRow();
newRow.Description = "New Row";

dataSetObject.MyDataTable.AddMyDataTableRow( newRow );

I can update the data table by using an SQLConnection and calling SQLDataAdapter.Update(), but the problem is, the ID value from the data set gets carried over into the database, which is still negative.

How do i update my DataTable so that newly added rows have the correctly incremented ID?

Answer 1

You didn't indicate the RDBMS you're using, but given your comments and my assumption, you're using SQL Server. And, you're using the IDENTITY property to increment the key value.

If new rows are not getting the correct next identity value, you can run the following to reset the value.

DBCC CHECKIDENT('table-name', RESEED)

This will reseed the identity value using the max value in the table.

You can view the current information about the identity using the IDENT_CURRENT, IDENT_INCR and IDENT_SEED functions.

Answer 2

Do not take the ID data from your client. Rather use a generated ID for your table.

May be you could use a stored procedure(CRUD) which captures the MAX(col) and puts the nextid in the new row created.