|
|
Posted by Bernard Peek on 11/05/05 20:50
In message <436cefc6$1_3@mk-nntp-2.news.uk.tiscali.com>, Mary Walker
<123@123.com> writes
>Hi,
>
>I'm enclosed a snippet of test code which highlights my problem. The Stored
>procedure insertValue should insert text into the parent, then insert other
>text into the child table but the 2 tables should auto increment in sync
>(i.e. so that they both end up with the same id numbers). I've tried taking
>the auto increment out of the child table but then I don't know how to get
>the right parent id into the child table.
>
>Any advice appreciated - this is my first database, so I'm just in the
>learning process really. Code follows:
OK. The first piece of advice is don't rely on the autonumber to return
any particular value. There are ways that the values in the two tables
could get out of sync and it would be a PITA to fix.
There are two options that I could suggest.
One is that you create the parent record and then take the autonumber
value from there and explicitly insert it into the child table. Don't
use an autonumber in child table, use a simple integer instead. This is
the way I recommend that you do it.
The second method has limited applicability but can make things easier.
First redesign your child table with an integer key. Then create a query
that links the two tables. You can now update the query instead of
updating the two tables separately.
--
Bernard Peek
London, UK. DBA, Manager, Trainer & Author.
Navigation:
[Reply to this message]
|