Author Topic: VB.net Express 2010 - UPDATE RELATED TABLES (Is that a bug?!?!)  (Read 19826 times)

nangel

  • Guest
Hi guys
I am very new here...
Project: Update related tables...

Table1: States
StateID   (primary key, autoincrement)
State       (string)

Table2: Cities
CityID      (primary key, autoincrement)
City         (string)
idState    (integer)

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?



jsg

  • Guest
Re: VB.net Express 2010 - UPDATE RELATED TABLES (Is that a bug?!?!)
« Reply #1 on: July 29, 2013, 05:29:49 AM »
I face the same problem. But I am surprised no one has replied with a solution so far.

mkaatr

  • Administrator
  • Full Member
  • *****
  • Posts: 115
  • Karma: +1/-0
    • View Profile
Re: VB.net Express 2010 - UPDATE RELATED TABLES (Is that a bug?!?!)
« Reply #2 on: September 13, 2013, 01:23:01 PM »
Hi guys
I am very new here...
Project: Update related tables...

Table1: States
StateID   (primary key, autoincrement)
State       (string)

Table2: Cities
CityID      (primary key, autoincrement)
City         (string)
idState    (integer)

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?

Hi there...

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.

yours

mkaatr

  • Administrator
  • Full Member
  • *****
  • Posts: 115
  • Karma: +1/-0
    • View Profile
Re: VB.net Express 2010 - UPDATE RELATED TABLES (Is that a bug?!?!)
« Reply #3 on: October 09, 2013, 10:39:23 PM »
Hi guys...

I found a video i made two years ago about this problem and its solution. It can be found here:

http://mkasoft.com/index.php/how-to/117-how-to-solve-access-master-detail-key

hope it helps.