DataSet auto increment problem
-
Hi Everyone: I have set up a dataset that uses an auto increment field that is set to start at minus one and step by minus one when I add a new record to the dataset. Example dataset: 1. Existing Record 1 2. Existing Record 2 3. Existing Record 3 4. Existing Record 4 5. Existing Record 5 The ID field is an auto increment field in SQL Server. To prevent accidental data corruption, whenever I add a new record to the dataset I start with -1. So the dataset will look like this when I add new records: 1. Existing Record 1 2. Existing Record 2 3. Existing Record 3 4. Existing Record 4 5. Existing Record 5 -1. 1st New Record -2. 2nd New Record -3. 3rd New Record Here is my problem when I try to save the new record into the dataset I get the following error: “Constraint Exception was unhandled. Column ‘ID’ is constrained to be unique. Value ‘4’ is already present.” I don’t understand why this is happening. The dataset’s auto increment field should put a -1 on the new record I’m adding. Instead it looks like it is starting at the last record ‘5’, subtracts one to get four and is trying to place the new information in record four instead of creating a new record in the dataset and starting the ID number at -1. What is going on? Thank you, Quecumber256
-
Hi Everyone: I have set up a dataset that uses an auto increment field that is set to start at minus one and step by minus one when I add a new record to the dataset. Example dataset: 1. Existing Record 1 2. Existing Record 2 3. Existing Record 3 4. Existing Record 4 5. Existing Record 5 The ID field is an auto increment field in SQL Server. To prevent accidental data corruption, whenever I add a new record to the dataset I start with -1. So the dataset will look like this when I add new records: 1. Existing Record 1 2. Existing Record 2 3. Existing Record 3 4. Existing Record 4 5. Existing Record 5 -1. 1st New Record -2. 2nd New Record -3. 3rd New Record Here is my problem when I try to save the new record into the dataset I get the following error: “Constraint Exception was unhandled. Column ‘ID’ is constrained to be unique. Value ‘4’ is already present.” I don’t understand why this is happening. The dataset’s auto increment field should put a -1 on the new record I’m adding. Instead it looks like it is starting at the last record ‘5’, subtracts one to get four and is trying to place the new information in record four instead of creating a new record in the dataset and starting the ID number at -1. What is going on? Thank you, Quecumber256
Actually, it should DBNull. The new record doesn't get an ID until it's written to the database and refetched. You've been hammering away at this for so long, I have no idea why the ID is so important that it has to remain in order. Databases don't care about the order of records so why are you trying to get these in order?? Only when the records are displayed in some UI do they get any kind of ordering.
A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
2006, 2007 -
Hi Everyone: I have set up a dataset that uses an auto increment field that is set to start at minus one and step by minus one when I add a new record to the dataset. Example dataset: 1. Existing Record 1 2. Existing Record 2 3. Existing Record 3 4. Existing Record 4 5. Existing Record 5 The ID field is an auto increment field in SQL Server. To prevent accidental data corruption, whenever I add a new record to the dataset I start with -1. So the dataset will look like this when I add new records: 1. Existing Record 1 2. Existing Record 2 3. Existing Record 3 4. Existing Record 4 5. Existing Record 5 -1. 1st New Record -2. 2nd New Record -3. 3rd New Record Here is my problem when I try to save the new record into the dataset I get the following error: “Constraint Exception was unhandled. Column ‘ID’ is constrained to be unique. Value ‘4’ is already present.” I don’t understand why this is happening. The dataset’s auto increment field should put a -1 on the new record I’m adding. Instead it looks like it is starting at the last record ‘5’, subtracts one to get four and is trying to place the new information in record four instead of creating a new record in the dataset and starting the ID number at -1. What is going on? Thank you, Quecumber256
Honestly unless this app is going to be run disconnected to the sql server you should probably take a different approach. If you will always be connected to the sql server. I would pass the new record values into a stored procedure that will do the insert. You can have the stored procedure pass back the indentity that it just inserted, or you can just refresh the select query. If your app can be run dissconnected, then perhaps you should look at adding a large number to your internal dataset, just make sure when the insert happens that it is null so that you get the correct auto increment id from sql server. Hope that helps. Ben
-
Actually, it should DBNull. The new record doesn't get an ID until it's written to the database and refetched. You've been hammering away at this for so long, I have no idea why the ID is so important that it has to remain in order. Databases don't care about the order of records so why are you trying to get these in order?? Only when the records are displayed in some UI do they get any kind of ordering.
A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
2006, 2007Dave, I sincerely thank you for your help. I hope I can explain my problem accurately enough so you can at least get an understanding of what I'm trying to do. I think I mentioned in a previous post that I have a data entry form that has four text boxes data bound to the BindingSource control. The BingingNavigator control is bound to the BindingSource control to allow the user the ability to navigate through the dataset while it is stored in memory. I designed the form so I can edit existing records as well as add new ones during a session. The dataset's schema is derived from the table the Dataset is recovering the information from. So in theory I have all four columns ID, Ordinal, Code and name in the dataset. I'm using the SqlCommandBuilder to automatically generate the add and update queries to update the Database table. Edited records need the ID assigned from the database table before any changes made to them can be written to the database. The new records need to be distingushed from the existing records by setting the ID column in the dataset as a negitively incrementing number. I got this idea from Microsoft MSDN. So in theory if I edit an existing record and then add a new one. The DataSet knows which record was edited by its ID number while the new record is seperately distinguished by its negitive increment. Unless you set the dataset's autoincrement column to start a -1 and step by -1 whenever you add a new record to the dataset it will start a 1 and if you already have a record with the ID of 1 that record will be updated with the new record's data. It thinks you are editing record number one, not adding a new one. Does this help? Quecumber256
-
Honestly unless this app is going to be run disconnected to the sql server you should probably take a different approach. If you will always be connected to the sql server. I would pass the new record values into a stored procedure that will do the insert. You can have the stored procedure pass back the indentity that it just inserted, or you can just refresh the select query. If your app can be run dissconnected, then perhaps you should look at adding a large number to your internal dataset, just make sure when the insert happens that it is null so that you get the correct auto increment id from sql server. Hope that helps. Ben
I want the app to run disconnected from the SQL Server database; thus the reason I want to use a DataSet. I just responded to Dave, I hope the response I gave to him explains what I'm trying to accomplish. I don't see a reson why I can't distingush a new record added to the dataset by assigning a negitive number to it in the auto increment column while the existing records in the dataset as positive. Do you have any suggestions on how to approach this problem? Thanks for you input, Quecumber256
-
Dave, I sincerely thank you for your help. I hope I can explain my problem accurately enough so you can at least get an understanding of what I'm trying to do. I think I mentioned in a previous post that I have a data entry form that has four text boxes data bound to the BindingSource control. The BingingNavigator control is bound to the BindingSource control to allow the user the ability to navigate through the dataset while it is stored in memory. I designed the form so I can edit existing records as well as add new ones during a session. The dataset's schema is derived from the table the Dataset is recovering the information from. So in theory I have all four columns ID, Ordinal, Code and name in the dataset. I'm using the SqlCommandBuilder to automatically generate the add and update queries to update the Database table. Edited records need the ID assigned from the database table before any changes made to them can be written to the database. The new records need to be distingushed from the existing records by setting the ID column in the dataset as a negitively incrementing number. I got this idea from Microsoft MSDN. So in theory if I edit an existing record and then add a new one. The DataSet knows which record was edited by its ID number while the new record is seperately distinguished by its negitive increment. Unless you set the dataset's autoincrement column to start a -1 and step by -1 whenever you add a new record to the dataset it will start a 1 and if you already have a record with the ID of 1 that record will be updated with the new record's data. It thinks you are editing record number one, not adding a new one. Does this help? Quecumber256
Quecumber256 wrote:
The new records need to be distingushed from the existing records by setting the ID column in the dataset as a negitively incrementing number. I got this idea from Microsoft MSDN.
No they don't. The records don't exist until they are in the database. Until then, they are potentially records.
Quecumber256 wrote:
So in theory if I edit an existing record and then add a new one. The DataSet knows which record was edited by its ID number while the new record is seperately distinguished by its negitive increment.
The DataAdapter, not the DataSet, knows which record was added as opposed to edited by each DataRow's RowState property in each table. The adapter then uses the correct SqlCommand (SQL UPDATE, INSERT, DELETE) to update the database with the new data. It has nothing to do with the ID number you give the record.
Quecumber256 wrote:
Unless you set the dataset's autoincrement column to start a -1 and step by -1 whenever you add a new record to the dataset it will start a 1
No, it won't. Any additions to the database are written without an ID number. The database assigns auto-incrementing ID's when the record is commited to the database. You really shouldn't be editing the ID number anyway unless you want to ADD the possibility of duplicate, or incorrect ID's.
A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
2006, 2007 -
Quecumber256 wrote:
The new records need to be distingushed from the existing records by setting the ID column in the dataset as a negitively incrementing number. I got this idea from Microsoft MSDN.
No they don't. The records don't exist until they are in the database. Until then, they are potentially records.
Quecumber256 wrote:
So in theory if I edit an existing record and then add a new one. The DataSet knows which record was edited by its ID number while the new record is seperately distinguished by its negitive increment.
The DataAdapter, not the DataSet, knows which record was added as opposed to edited by each DataRow's RowState property in each table. The adapter then uses the correct SqlCommand (SQL UPDATE, INSERT, DELETE) to update the database with the new data. It has nothing to do with the ID number you give the record.
Quecumber256 wrote:
Unless you set the dataset's autoincrement column to start a -1 and step by -1 whenever you add a new record to the dataset it will start a 1
No, it won't. Any additions to the database are written without an ID number. The database assigns auto-incrementing ID's when the record is commited to the database. You really shouldn't be editing the ID number anyway unless you want to ADD the possibility of duplicate, or incorrect ID's.
A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
2006, 2007Dave, I know the DataSet is an Array type object. Whenever data is added to an empty array it starts at 0 and increments by one. So information in a DataSet before it is appended to the database looks like this: 0 Record 1 1 Record 2 2 Record 3 And so on. The id column in the database will store it in this manner: 1 Record 1 2 Record 2 3 Record 3 and so on. I must have missed something with the DataAdapter because when I tried to add a new record to the dataset after populating it with data returned from the database I got something like this: 1 2 Record 2 3 Record 3 4 Record 4 Can you tell me what I did wrong? Quecumber256
-
Dave, I know the DataSet is an Array type object. Whenever data is added to an empty array it starts at 0 and increments by one. So information in a DataSet before it is appended to the database looks like this: 0 Record 1 1 Record 2 2 Record 3 And so on. The id column in the database will store it in this manner: 1 Record 1 2 Record 2 3 Record 3 and so on. I must have missed something with the DataAdapter because when I tried to add a new record to the dataset after populating it with data returned from the database I got something like this: 1 2 Record 2 3 Record 3 4 Record 4 Can you tell me what I did wrong? Quecumber256
Quecumber256 wrote:
I know the DataSet is an Array type object.
Uhh, where did you see that?! DataSet implements a collection of DataTable objects as an implementation of the IListSource interface (among others), VERY different from an Array. Have you just NOT put the ID numbers in?? BTW: A DataSet can't hold DataRows, only a DataTable can. DataSet is a collection of DataTables, which are collections of DataRows.
A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
2006, 2007 -
I want the app to run disconnected from the SQL Server database; thus the reason I want to use a DataSet. I just responded to Dave, I hope the response I gave to him explains what I'm trying to accomplish. I don't see a reson why I can't distingush a new record added to the dataset by assigning a negitive number to it in the auto increment column while the existing records in the dataset as positive. Do you have any suggestions on how to approach this problem? Thanks for you input, Quecumber256
Ok now knowing that you want to run this app disconnected. Your idea of the negative auto increment doesn't solve all your problems since I would assume you would have more then one person that would want to run the app disconnect. Now you would have two applications that both assigned -1 to a new row, you would still have the same problem. If you truely want to run the app disconnected, it doesn't matter what the id is in your app when it is disconnected, what matter is when the app syncs with the live database what do you do to get the correct ids. Again I think you are approaching this problem the wrong way. Focus on writing a stored procedure that inserts a new row in to the sql server. Then write your routine to use it when connected and to use some other stored procedure that syncs up the changes that would also use this insert stored procedure. Ben