Insert in a DB and retrieve new info

[PhilippeL]

Hi everyone,

Let's say I insert a new entry in a table with a query such as:

INSERT INTO tblUsers ( userName ) VALUES ('" & strUserName & "')

The table has a field userId which is an autonumber. My question is, is there a way to retrieve the userId generated by the INSERT, so I can then use this userId right away for other purposes in the following lines of code?

Btw, i'm using ASP/VBscript with an Access DB.

Thanks for any assistance.

[txbakers]

Right after you make the insert, run another query pulling the ID number.

[PhilippeL]

Hum... yes that's what i'm trying to do... but can you be more specific?

If my table has 2 fields, userId and userName, and the userName is not unique, I can't get the userId with a "SELECT userId FROM tblUsers WHERE userName=someusername".

[txbakers]

You can also try

select max(id) from table

[PhilippeL]

That's what i was thinking about, i guess it's the way to do it that makes most sense. I was wondering if there was some other way i wasn't aware of...

Thanks for the help!

[mattglet]

select max(id) is the only way to do it in an Access DB. Theoretically, you COULD end up pulling the wrong ID from your max(id) statement. If you have 2 users doing the insert milliseconds apart, and for whatever reason one takes longer than the other, they could swap id's.

If you were using SQL, you could pull the IDENTITY like so:

SQL = "INSERT INTO tblTemp (name) VALUES('" & yourname & "'); SELECT idNew = @@IDENTITY FROM tblTemp"
set rs = conn.execute(SQL)
set rsNew = rs.nextrecordset

intNewID = rsNew("idNew")

-Matt

[txbakers]

matt - does that also work in mySQL do you know?

[mattglet]

@@Identity is not available in mySQL.

With mySQL I use:

SQL = "SELECT LAST_INSERT_ID() as idNew FROM tblTemp;"
set rs = conn.execute(SQL)

intNewID = rs("idNew")

This should be performed directly after your INSERT statement. I can't remember the full wording, but with this you can't get a wrong ID. It has something to do with mySQL assigning you some type of session to keep the data going where it's supposed to go. I should probably look it up again. I haven't used mySQL in a few months.

-Matt

[Easy_Coder]

handle it all in your sproc...

create procedure cspInsertStuff
@i_vValue as varchar(10)
@o_iID AS int output
as

insert into tblUsers(username)
values(@i_vValue)

set @o_iID = @@IDENTITY
go

then call it from a command

set oCmd = server.createobject("adodb.command")
With oCmd
 .CommandType = adCmdStoredProc
 .CommandText = "cspInsertStuff"
 .Parameters.Append .CreateParameter("@i_vValue", advarchar, adparaminput, 10, "your value")
 .Parameters.Append .CreateParameter("@o_iID", adInteger, adParamOutput, , returnSprocOutput)
 .ActiveConnection = yourConnObjHere
 .Execute
 returnSprocOutput = .Parameters("@o_iID").Value
End With
Set oCmd = Nothing

[PhilippeL]

Thanks Matt for the confirmation. In my case, there should very rarely be more than 1 user connected at once, so the possibility for MAX(id) to select the wrong id is negligible.

I find it strange that there's no fool-proof (and easy) way to do this (in Access), since this is something that can be needed quite often.

[txbakers]

no fool-proof (and easy) way to do this (in Access)

Unfortunately, there is nothing easy or fool-proof about Access.

It's a good little database for desktop applications, but it's not very standard and there are way too many quirks to work around.

But it beats the alternatives - FileMaker Pro? Panorama? Microsoft Works? Etch-a-sketch?

[mattglet]

HAHA... Etch-a-sketch. I needed that.

-Matt

[Spooky]

Jet OLE DB Provider Version 4.0 Supports SELECT @@Identity, so use something like this :

set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open sDSN
objConn.Execute(sSQL)
Set objrs = objConn.Execute("select @@Identity")
response.write objrs(0)
objRS.close
Set objRS = nothing
objConn.close
set objConn = nothing