Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Shaun E on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

ADO stored procedure, return autonumber? 3

Status
Not open for further replies.

jenlion

IS-IT--Management
Nov 13, 2001
215
I have a new "data entry" mode to allow for heavy input in a shorter amount of time than is possible in a bound recordset. Users fill in "client" information, click "save", and the text fields are converted to parameters and a stored procedure is called to quickly add the record.

The record added will have an autonumber key. Problem is that there is a second set of data that will be entered immediately following "client" entry. This second set requires knowledge of the autonumber key that the client received upon entry; each entry here has a foreign key to the client table.

Sure, I can "select top 1 personid from clients where firstname = txtfname.text and lastname = txtlname.text order by personid desc", but it is entirely possible that another data entry person will enter a person with the same name (lots of "Joe Smith"s out there) at the same time, and name is the only data entry requirement, so I can't rely on SSN or something. I need to be sure that I've got the absolute correct person. Is there any way to have my procedure return the autonumber value assigned after the INSERT command?

Thanks...
 

If you're using SQL Server, @@IDENTITY returns the last-inserted identity value.


INSERT INTO jobs (job_desc,min_lvl,max_lvl)
VALUES ('Accountant',12,125)

SELECT @@IDENTITY AS 'Identity'

Mark
 
Mark,

Thanks, I forgot about @@identity. I now return the @@identity value as part of the procedure.

Do you suppose it's *possible* that two people might add a client at exactly the same time, and thus it might return the wrong number? Not likely, it happens so fast. Not sure how the locking would work. One person would run the procedure, another person would click the button that runs the procedure... can two people run it at the same time??

Thanks for your help!

Jennifer
 
@@identity is unique to the connection, so if two people run it, each @@identity belongs to its own connection.

You shouldn't have a problem with this. Let me know if this helps
________________________________________________________________
If you are worried about how to post, please check out FAQ222-2244 first

'There are 10 kinds of people in the world: those who understand binary, and those who don't.'
 
That's what I needed to know! Just trying to get a good clean program for these folks.

Thanks very much, Mark and John.

Jennifer
 
Huh. Tek tips seems to be logging me in as a different user every time I visit the page... at any rate, this "jthieded" should be jenlion. I've noticed quite a few changes here, hope they get things straightend out...
 
Is anyone else missing the "NEXT THREAD" link at the Bottom of the thread?

I used to find that really helpful, and I'm really missing it.

I've written to TEK-TIPS, so if you agree, pls write them also ('contact us' shortcut above sponsor msg) Let me know if this helps
________________________________________________________________
If you are worried about how to post, please check out FAQ222-2244 first

'There are 10 kinds of people in the world: those who understand binary, and those who don't.'
 
Can you give me the stored procedure for aut generating numbers with the full syntax for sql, and access.
Thanks
 
Can you give me the stored procedure for aut generating numbers with the full syntax for sql, and access.
Thanks
 
Can you give me the stored procedure for aut generating numbers with the full syntax for sql, and access.
Thanks
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top