primary keys

[Mih_Flyer]

hello, i don't know how to explain that,, but i'll give it a go..

i have two tables :
1- items: which has these columns:
-------------itemid -->Priamry Key
-------------name
-------------price
-------------note
2- bookinfo: which has these columns:
-------------itemid -->Priamry Key
-------------author
-------------edition
-------------publisher

now,, in my application, if the item is not book, then i don't have to add any thing to the "bookinfo" table. but if the item is book then i have to add a row to the "bookinfo" table.. my problem now,, how do i relate to bookinfo table, i mean after i add the iteminfo i have to add the bookinfo in case the item is book. but how can i figure out the itemid so i put it the bookinfo table?

i use SQL Server 2000 prof. edition and VB6...

Please help

[mendhak]

I think your ITEMID is just a simple number there. THen how can you figure out what it is just by looking at the number. It's pretty obvious that you're going to have to put in some descriptive field there... describing it as a book..

OR

while the user is filling out the form, you can have something like a checkbox there... CHECK THIS BOX IF IT IS A BOOK...

you get my drift.



Once your app knows it's a book, it can do the adding there

HTH

[Mih_Flyer]

ok,,, thats what i said i can not explain what i want,,, bro..listen
the itemid in the items table is a primary key,, and Auto_Increment,, so when i add an item to the table and then want to add the book info in the bookinfo table i want to know the last itemid has been added to the items table...GOT IT??

thanx alot any way

[Ianpbaker]

how are you updating the records, through a recordset or through a SQL string. If you are using a recordset, once you have fired off the update line, the recordset will be at that record, so you can then do Myid = recordset("Idcolumn"). if you are updating using a a sql string you will need to fire off a

SELECT MAX(ITEMID) FROM items

after you do the update to get the last number that was entered

hope this helps
Ian

[Mih_Flyer]

thanx alot Ianpbaker... i did that,, but there is a little problem,,
if two persons using the program in a network,, so this way it could be dangerous,,,,, for example,,before i get the MAX id someone else adds a row so i might get the wrong id,,,got it?
is there any way around this?
Thanx alot

[mendhak]

transaction concurrency is NOT a problem with SQL Server.

[Mih_Flyer]

what do you mean ????

[mendhak]

Originally posted by Mih_Flyer
what do you mean ????

eh? By that I meant that.. several users are using it... ok.. and someone gets a MAX ID, the next person to get one will, well.. get the next one. It's not dangerous. You get?

basically, what you said wont happen unless you're using over 4000 users.

[Mih_Flyer]

eh? By that I meant that.. several users are using it... ok.. and someone gets a MAX ID, the next person to get one will, well.. get the next one. It's not dangerous. You get?

basically, what you said wont happen unless you're using over 4000 users.

ohh no,,,i have 4001 users,,,,,,,
thanx alot mate,,,i got it....