none
Problem with an AutoNumber column in Dataset RRS feed

  • Question

  • I have a table in my SQL Server 2005 database named TestTable. This table contains two columns viz. UID and Description. The UID column is of the data type INT and has been set as an Identity column. Description column is of data type NVARCHAR(50).

    In one of my Projects inside Visual Studio 2005, I have created a dataset and have dragged and dropped this table (TestTable) using the Server Explorer fly-out window. Then, I changed the AutoIncrement Seed property for the UID column in the TestTable in the dataset from 0 to 1. I have also created a simple windows forms-based application only to insert data into the database table using this dataset. The important point worth noting here is that the dataset WILL NOT ALWAYS CONTAIN ALL THE DATA BEING STORED IN THE DATABASE.

    As a first step, I ran the windows forms application I had created and inserted 3 new rows into the dataset's data table and then called Dataset.Update() method to save the rows to the database. Then, I executed a SELECT statement on the TestTable in the database to find out whether any rows have been inserted. As per my expectation, three rows had been inserted as shown below:

     

    UID          Description

    -----          ----------------

    1               Desc1

    2               Desc2

    3               Desc3

     

    Having done that, I stopped my windows application and executed it again. I did not retrieve any rows from the database into the dataset's data table. Then, I added four new rows to the TestTable within the dataset. Using a Watch window, I saw that the TestTable within the dataset contained the following four rows:

     

    UID          Description

    -----          ----------------

    1               Desc4

    2               Desc5

    3               Desc6

    4               Desc7

     

    Then, I called the Dataset.Update() method to save the new rows to the database. This time, I got the following error:

    Column UID is constrained to be Unique. Value '4' is already present.

     

    The best way of reproducing this error is by trying to insert more records into the dataset's datatable than what are already present in the database's datatable, and then calling the Dataset.Update() method. 

     

    We have several such datasets in our application which contain autonumber columns. At all such places, we have been getting these errors. Does anyone have a solution to this problem?

    Friday, August 17, 2007 3:56 PM

Answers

  • In applications I've done that operate with the same model as you are describing, I set my auto-incrementing fields in my dataset so that they go negative (-1, -2, -3...), this prevents new records from utilizing an existing record's value (because in most cases your database will be generating positive values).  When issuing an update from your dataset to your database, you'll need to have your database supply you with the updated identifier, because you will more than likely want your database to be in charge of the actual [stored] auto-incrementing value... not your application.  How you have your database supply your application with the database's auto-incremented value depends on how you are coding your SQL updates (stored procedure vs. adhoc SQL statements), so you may need to share which method you are using in order for us to help you in that regard.  In the case of the applications I've worked on, I normally use stored procedures and have my unique ID parameter setup as an input/output so that my data adapter can map it and automatically update my dataset.

     

    I think there is a Microsoft article about this (maybe in the knowledge base somewhere), but it was so long ago when I read it that I can't specifically direct/link you to it.  Maybe if you search the internet or microsoft's site you can dig it up.

     

    Mabye someone else has a different/better way of doing this.  If you need more information about this, please let us know.

    Friday, August 17, 2007 9:50 PM

All replies

  • In applications I've done that operate with the same model as you are describing, I set my auto-incrementing fields in my dataset so that they go negative (-1, -2, -3...), this prevents new records from utilizing an existing record's value (because in most cases your database will be generating positive values).  When issuing an update from your dataset to your database, you'll need to have your database supply you with the updated identifier, because you will more than likely want your database to be in charge of the actual [stored] auto-incrementing value... not your application.  How you have your database supply your application with the database's auto-incremented value depends on how you are coding your SQL updates (stored procedure vs. adhoc SQL statements), so you may need to share which method you are using in order for us to help you in that regard.  In the case of the applications I've worked on, I normally use stored procedures and have my unique ID parameter setup as an input/output so that my data adapter can map it and automatically update my dataset.

     

    I think there is a Microsoft article about this (maybe in the knowledge base somewhere), but it was so long ago when I read it that I can't specifically direct/link you to it.  Maybe if you search the internet or microsoft's site you can dig it up.

     

    Mabye someone else has a different/better way of doing this.  If you need more information about this, please let us know.

    Friday, August 17, 2007 9:50 PM
  • Thanks a lot for the solution. It will definitely take care of my problem.

    Sunday, August 19, 2007 7:38 PM