Updating 2 Tables at once

[MRW]

Quick question for anybody who can help:

I have two tables:

tblUsers
--------
UserID
Name

tblPhones
---------
PhoneID
UserID
PhoneNumber

If I'm collecting the name and the phone numbers (can be multiple
numbers) in one form, what's the best way of placing it in the
database? The question revolves around the UserID and making sure the
tblPhones.UserID = tblUsers.UserID

Thanks for any help!

 

[MRW]

Oh yes... tblUsers.UserID is automatically incremented in the
database...

 

[Mark Rae]

Quick question for anybody who can help:

I have two tables:

tblUsers
--------
UserID
Name

tblPhones
---------
PhoneID
UserID
PhoneNumber

If I'm collecting the name and the phone numbers (can be multiple
numbers) in one form, what's the best way of placing it in the
database? The question revolves around the UserID and making sure the
tblPhones.UserID = tblUsers.UserID

Thanks for any help!

Is the UserID already known, or are you adding one record to tblUsers and
one or more to tblPhones? If the former, is tblUsers.UserID an identity
field? If so, you can retrieve the identity by SELECT @@IDENTIY or SELECT
SCOPE_IDENTITY. Then you can use that to pump the records into tblPhones.

 

[Sarat Pediredla]

Based on that fact that you mention that the UserID is an auto
incremented field, I am assuming this is an IDENTITY and the SELECT
@@IDENTITY in your SQL should do it for you.

 

[MRW]

Thanks for the feedback!

I'll be honest. I'm not familiar with the @@IDENTITY. Can you give a
quick example on how it is used (VB)?

Thank you for the help!

 

[Sarat Pediredla]

I feel generous today, so here you go. 2 mins on Google could have got
you that.

http://support.microsoft.com/default.aspx?scid=kb;en-us;815629

 

[Sarat Pediredla]

Ok, looks like I pasted a dummy link there. Here is another go.

http://www.informit.com/articles/article.asp?p=382852&seqNum=4&rl=1

 

[MRW]

Great! Thanks a lot!

Ok, looks like I pasted a dummy link there. Here is another go.

http://www.informit.com/articles/article.asp?p=382852&seqNum=4&rl=1

 

[Mark Rae]

Great! Thanks a lot!

Sarat's comment about Google is very relevant, though... you really could
have found this out for yourself in a matter of seconds...

 

[MRW]

I've been on Google searching for things for hours. I did not find
anything that would have led me to @@IDENTITY. So when he told me, I
simply asked if he had a quick example on how to use it while I've got
him.

 

[JT]

To Mark & Sarat:

We all appreciate your help. And I try to help when I can. Isn't that
why this group is here? If we are just going to tell people to go look
on Google, why does this group exist? I'm sure you're both very
helpful and generous in most cases. If you were a guru sitting on a
mountaintop and someone climbed up to ask a question, wouldn't you
think they were an idiot if they climbed back down and then had to
climb back up to ask a follow-up question instead of asking while they
had your attention?

Now blithering idiots should go study before peppering you with inane
questions, but that's a different situation (IMHO).

We all have bad days...

BTW, I benefitted from your answers, too.

Thanks.