addnew() problem with auto increment id
Hi,
I am writting a program using VB.Net to connect to a MSSQL Server 2000 database by ADO.Net technology. My database has four tables, namely "Patients", "Lesions", "Measurements" and "Files". They are all in a series of "one to many" relations.For example, one patient can have multiple lesions and one lesion can have multiple measurements and one measurement
can have multiple files. I use auto increment integer number as the "id"s of table "Lesions", Measurements", "files", and set their seeds and as "identical".
I have a problem to add a new record for the tables with auto increment numbers as their "id"s. Whenever I called the addNew() meathod to add a new record to, for exampel, the "Lesions" table, the number of "id" shown is always a number larger than it should be. In my case, it's always two more than it should be in the "Lesions" table, for example, I already have 3 records with "id" 0, 1, 2, in the table, the next (new) one I add should be with "id" 3, but it actually comes up with "id" 5. Then this conflicts with the table so if I update the data adaptor, there are two new records shown up, one with "id" 3, the other with "id" 5. Of course the one with "id" 5 is invalid, so any work with it will end up with an error. I have to close the form and restart it, then the devil record with "id" 5 disappear, and everything now is OK.
Can somebody please give me some hint to solve this problem? It's a real time killer for me.
And I also noticed there is a post with same problem marked with solved, but there was simply no reply to the thread.
Thank you,
adHeadache
Re: addnew() problem with auto increment id
Re: addnew() problem with auto increment id
Within you dataset or datatable on the form, the Identity values created there are solely for the purpose of maintaining the linkage until it is posted to the database. For your insert statements into the database, you should not be inserting the Identity values. The database id columns should be setup as Identity columns. Then, after your insert statement, you just return the row where id column = @@Identity. This should repopulate your datatable with the values from the database.
If that is what you are doing, then it sounds like you have some fairly new tables. It is recommended to set the datatable objects to have an Identity Seed of -1 and an Increment of -1. This will keep you IDs in your datatables from getting confused with the IDs in SQL Server.
Re: addnew() problem with auto increment id
Quote:
Originally Posted by Aspnot
Within you dataset or datatable on the form, the Identity values created there are solely for the purpose of maintaining the linkage until it is posted to the database. For your insert statements into the database, you should not be inserting the Identity values. The database id columns should be setup as Identity columns. Then, after your insert statement, you just return the row where id column = @@Identity. This should repopulate your datatable with the values from the database.
I Checked the IDE automatically generated code, that's what it did.
Quote:
Originally Posted by Aspnot
If that is what you are doing, then it sounds like you have some fairly new tables. It is recommended to set the datatable objects to have an Identity Seed of -1 and an Increment of -1. This will keep you IDs in your datatables from getting confused with the IDs in SQL Server.
Can you clarify this a bit more? I don't know I have any new tables. All my tables are in the SQL server, but I set the Identity Seed of 1 and Increment of 1. Should I change all there to -1? Or you are saying I should set the "fairly new tables Identity seed to -1 and increment of -1?
Thank you very much,
anheadache
Re: addnew() problem with auto increment id
Quote:
Originally Posted by Aspnot
Within you dataset or datatable on the form, the Identity values created there are solely for the purpose of maintaining the linkage until it is posted to the database. Then, after your insert statement, you just return the row where id column = @@Identity. This should repopulate your datatable with the values from the database.
But how about if the Identity values it created there are different from the one generated from database? My problem is that the one generated by my form and the one from database are coexist in my dataset if they are different. How can I remove the one generated from my form?
Actually, I played around setting the seed a bit. I set two of my tables, which are in the middle of the master-slave chain, with Identity seed of -1 and increment of -1 then the top master table are working fine, because now the Identity value my form generated matches the one from database. But because I have already have positive values in the two middle tables, so they are still not working well.
Any more suggestions,
Thanks,
anheadache
