Muhahahahah! The more you tighten your grip the more star systems will slip through your fingers!
Ben
· 4 years ago
I feel I must add context to the above comment. You see Darth Vadar is a Java programmer, and we all know that Java is the evil all powerful empire of programming languages.
Hence us poorly .NET programmers must unite to form a Rebel Alliance against Darth Vadar and his evil cohorts, lest they bring their space station up to full operating capacity.
Liam
· 4 years ago
If I understood correctly, the issue is reflecting the SQL identity value back in the Dataset (ie they could be out of synch). From what I've seen, the Dataset identity value is replaced by the SQL version when:
1. you use a typed dataset 2. you use update\fill on a SqlDataAdapter
I've used this mechanism together with the SetParentRow methods to enable related rows to be created in memory during a users session with my web app, and then splat the lot into the database and ensure all the relationships are maintained without having to right a single line of custom code (other than to make sure i do the update on each table in the dataset in a logical order)...a by-product of these observations was that the sql commands generated with the SqlDataAdapter seemed to magically synch up the identit column values.....man I love Typed DataSets.....
Rimu
· 4 years ago
ha
that'll teach you to use datasets.
ffs hand-code everything, using stored procedures, like a real man
;-)
Ben
· 4 years ago
Heh. We only use Datasets as a storage place for data really. Saves coding or generating objects, and you get the benefit of the whole difgram thing.
We definitely use stored procs to populate them. I can't stand the generated SqlCommand object thingies.
Tex
· 3 years ago
Don't know if someone still reads this ...
I had the problem dicussed in the posting here. So I tried the hacky workaround and it worked well - at first. The next time I added lots of rows, die DataSet comlained again about an already taken id.
So what happened? I populated the DataSet with the data from the database, using the Fill method. Before adding new rows I changed AutoIncrementSeed and Step to -1. However at this point the Seed cannot be changed anymore, because the DataSet already knows the correct Seed of the database - remeber I used the Fill method earlier. Although the Seed property is set to -1, the DataSet will use the real Seed. As you can guess the DataSet now generates duplicate ids, because the Step is still set to -1.
I actually don't know how a DataSet behaves if there are already rows in the table AND there are added new rows by another application. This would result in a seed set to a specific value which means that the seed = -1 solution doesn't work. I think this should result in an exception, because the databases id seed has been changed by another application.
Ben
· 3 years ago
Interesting point Tex. I guess Microsoft didn't consider the fill scenario with -1 increment when they suggested it.
That probably means the only solution is to either manually or programatically set your seed to something much higher than your highest existing ID, and leave increment at +1?
Dan Bailiff
· 3 years ago
I ran into this very problem and was hoping someone like you would have the answer. However, I did discover the solution, although it is only mentioned in passing and I could find no code examples.
The method goes like this:
1) call the FillSchema method of the table 2) set the auto-increment values (true, -1, -1) 3) fill the table
Getting the schema before filling the table sets the correct seed value and prevents duplicate IDs.
Mick
· 2 years ago
Found this post as the _only_ result when googling:
All Comments
The more you tighten your grip the more star systems will slip through your fingers!
Hence us poorly .NET programmers must unite to form a Rebel Alliance against Darth Vadar and his evil cohorts, lest they bring their space station up to full operating capacity.
1. you use a typed dataset
2. you use update\fill on a SqlDataAdapter
I've used this mechanism together with the SetParentRow methods to enable related rows to be created in memory during a users session with my web app, and then splat the lot into the database and ensure all the relationships are maintained without having to right a single line of custom code (other than to make sure i do the update on each table in the dataset in a logical order)...a by-product of these observations was that the sql commands generated with the SqlDataAdapter seemed to magically synch up the identit column values.....man I love Typed DataSets.....
that'll teach you to use datasets.
ffs hand-code everything, using stored procedures, like a real man
;-)
We definitely use stored procs to populate them. I can't stand the generated SqlCommand object thingies.
I had the problem dicussed in the posting here. So I tried the hacky workaround and it worked well - at first. The next time I added lots of rows, die DataSet comlained again about an already taken id.
So what happened? I populated the DataSet with the data from the database, using the Fill method. Before adding new rows I changed AutoIncrementSeed and Step to -1. However at this point the Seed cannot be changed anymore, because the DataSet already knows the correct Seed of the database - remeber I used the Fill method earlier. Although the Seed property is set to -1, the DataSet will use the real Seed. As you can guess the DataSet now generates duplicate ids, because the Step is still set to -1.
I actually don't know how a DataSet behaves if there are already rows in the table AND there are added new rows by another application. This would result in a seed set to a specific value which means that the seed = -1 solution doesn't work. I think this should result in an exception, because the databases id seed has been changed by another application.
That probably means the only solution is to either manually or programatically set your seed to something much higher than your highest existing ID, and leave increment at +1?
The method goes like this:
1) call the FillSchema method of the table
2) set the auto-increment values (true, -1, -1)
3) fill the table
Getting the schema before filling the table sets the correct seed value and prevents duplicate IDs.
"is constrained to be unique" UpdateDataSet
Perfect. Thank you