access stored query retrieve PK (Autonumber) after INSERT

Hello,
i have a Access DB with stored query to insert data via asp page.
I would like to be able to retrieve the Primary Key (Autonumber) of the inserted data right after the insertation.

For Insert i have on my asp page:
newvalue=Request.Form("mytext")

    execTimeout=60
    execCount=1
    Server.ScriptTimeout=execCount*execTimeout+5

    Set commandObj=Server.CreateObject("ADODB.Command")
    commandObj.ActiveConnection=adoCon
    commandObj.CommandTimeout=execTimeout
    commandObj.CommandType=adCmdStoredProc
      
      commandObj.CommandText="test_01"
        commandObj.Parameters.Append commandObj.CreateParameter(":param1", _
    adVarchar, _
    adParamInput, _
    150, _
    newvalue)

    commandObj.Execute


wheras "test_01" is the stored query name (SQL:INSERT INTO TEST ( [Text] )SELECT [@newvalue] AS Ausdr1;)
and the name of the PK field is THE_ID.

This is for a multi user input, so i need to retrieve the PK of the record the one particular user added.(and not for example the Max(THE_ID) ).

Thank you in advance for your help.
qwynia



LVL 3
qwyniaAsked:
Who is Participating?
 
fritz_the_blankConnect With a Mentor Commented:
The way that I usually do it is to use the recordset object:

strSQL = "SELECT * FROM tblYourTable WHERE 1=0"
objRS.Open strSQL,objConnection
objRS.AddNew()
  objRS("fldSomeField") = "some value"
objRS.Update()
intID = objRS("ID")
objRS.Close()
set objRS = Nothing

0
 
qwyniaAuthor Commented:
This way it doesnt seam to work though together with my stored queries.

Any suggestion how to get this work with stored queries?

Thanks again
qwynia
0
 
qwyniaAuthor Commented:
....ok, thanks got it working (forgot to set the LockType right).

Thank you for your help fritz_the_blank .

qwynia
0
 
fritz_the_blankCommented:
Glad to have helped,

FtB
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.