• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 544
  • Last Modified:

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



0
qwynia
Asked:
qwynia
  • 2
  • 2
1 Solution
 
fritz_the_blankCommented:
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

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now