best way to return inserted record ID

[gagz]

Hi-

I have an ASP.NET form (VB) that on submittal does a 3 part insert. It runs 3 stored procedures, the first of which creates a record of the majority of the data. What I want is the simplest, most accurate way to return the ID (incremented primary key from the first insert), so that I can use it in the next 2 inserts. All I can think of doing is
'call sproc1
'do a select max(ID) from table1
'call sproc2 and 3 with that ID as an added arg


i know the chance is slim that i would get the wrong ID back, but its possible, and there just seems like there should be a better way.

any ideas?

THanks!

 

[Zarcom]

Have sproc1 return the identity of the record it just added.

return @@identity That'l do donkey, that'l do
Mark
If you are unsure of forum etiquette check here faq796-2540

 

[jfrost10]

Just a note on Mark's post (and I could be wrong on this, but

The Identity trick will work only if you have your primary key field set to BE an identiy. If you have code that grabs the max value and saves that with your record, I'm not sure if the identity thing will work.

That said, getting hte next ID by using Max is a TERRIBLE idea, and Identity is definately the way to go.

D

 

[Alcar]

The way I found to get the last ID when the ID is not an Identity, I have a separate table where I create these IDs and I then have a way to determine the last one inserted.
Daren J. Lahey
Just another computer guy...
FAQ183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.
Support your forums TODAY!

http://www.tek-tips.com/supportus.cfm

 

[TonyU]

[tt]

I don't know much about ASP.Net yet but wouldn't this .ASP code work?


<%
If (CStr(Request(&quot;insert&quot) <> &quot;&quot Then
set TonyRS = Server.CreateObject(&quot;ADODB.Recordset&quot
TonyRS.ActiveConnection = editCmd.ActiveConnection
TonyRS.Source = &quot;SELECT max(ID) as MaxID FROM table&quot;
TonyRS.CursorType = 0
TonyRS.CursorLocation = 2
TonyRS.LockType = 3
TonyRS.Open()
Session(&quot;NewID&quot=TonyRS(&quot;MaxID&quot
end if
%>

Got ASP Error(s)? =

http://www.aspfaq.com

​

 

[gagz]

Thanks (again) Mark, for putting me on the right path!

 

[Zarcom]

umm hmm That'l do donkey, that'l do
Mark
If you are unsure of forum etiquette check here faq796-2540