I am very new here...
Project: Update related tables...
StateID (primary key, autoincrement)
CityID (primary key, autoincrement)
I built a 1 to many relationship between the States.StateID and Cities.idState
When I built the form by dragging the two tables on it, it seems like when you add a new state and then for that new state you add new cities, when you press the save button it fails when
ithe AutoIncrementStep is -1 (which is the default) and the AutoIncrementSeed=-1(which is the default also)!!!
it only succeds if I change the AutoIncrementStep to 1 and the AutoIncrementSeed to 1 as well....
Any idea if that is a bug or any idea of what I am doing wrong and how could I resolve this issue?
I remember facing a similar problem, this is what is happening:
1- when using auto increment key values, vb.net assigns a fake key to be used in order to link the data in the buffers. So you add a record in table A, it gets the key -1, and the child records get the same fake key -1.
2- when you press save first thing happens is that the master table records get inserted to db. Now the fake key will not be used in the db, so you get another key (generated by dbms not vb.net ) such as 10, not -1.
3- when the details table records are being inserted, the data in the buffer has no way to determine the value of the new key. So, there is no way vb.net can figure out how to change -1 to 10 in the child records. So, it will attempt inserting the child record with the foreign key values of -1 which does not exist in the db. So you get referential integrity error.
The way i solved this problem - as far as I remember - was to avoid using auto increment fields in the db. When inserting a new record, vb.net will call the database and get a sequence number, and that number is then assigned to the key. This way you will avoid this error because the values in the buffer will be same in the db.
I remember doing a video about that, but I can't figure out what the title of the video is. Try this out, if you still have a problem with it let me know, so I could make another video.