Retrieve id from record just inserted

[camillaj]

Is there a way to retrieve the id from the record that was just inserted to DB? That is, I insert some values into an access DB and the record automatically creates an ID.

The ID that is created (autonumber) is needed when I add new records to other tables with this ID as foreign key.

I doesn't seem like a sql INSERT Query returns something, so just after Inserting the record, I try to do a SELECT query with the same conditions. Among others I insert the current date:

INSERT INTO Responses(Customer, FormId, DateSubmitted) VALUES ('" & Session("Customer&quot & "'," & Session("FormId&quot & ",'" & today & "')"

I then tried to SELECT

"SELECT ResponseId FROM Responses WHERE DateSubmitted='" & today "'"

But here I get a datatype missmatch!!

So, is there another way to retrieve the ID that was just generated from the INSERT statement or do I have to convert the 'today' to another format when using it as condition in a select?

Hope this wasn't too complicated.

Thanks for your help guys

 

[GaryC123]

What DB is it, they all have different methods.
e.g.
rs.open "Select @@IDENTITY From

",conn
newID=rs(0)

or use a recordset to do the update..

rs.open "Responses",conn,2,2
rs.addnew
rs("customer&quot=session("customer&quot
rs("formid&quot=session("formid&quot
rs("datesubmitted&quot=Date
newID=rs("ID_Field&quot
rs.update

Using a select like you have above is not foolproof since another record could have been added in between your adding and selecting the last record.

http://www.intouchmedialtd.com

 

[Veep]

These will work for Access:

strSql="INSERT INTO Responses(Customer, FormId, DateSubmitted) VALUES ('" & Session("Customer&quot & "'," & Session("FormId&quot & ",'" & today & "')"
conn.execute(strSql)
strSql="SELECT @@Identity FROM Responses"
set rs=conn.execute(strSql)
NewID=rs(0)
set rs=nothing
response.write(NewID)

OR:

strSql="INSERT INTO Responses(Customer, FormId, DateSubmitted) VALUES ('" & Session("Customer&quot & "'," & Session("FormId&quot & ",'" & today & "')"
conn.execute(strSql)
strSql="SELECT MAX(ResponseID) as NewID FROM Responses"
set rs=conn.execute(strSql)
NewID=rs("NewID&quot
set rs=nothing
response.write(NewID)