Get autonumber after Insert with MS Access

[TheCandyman]

After i do an insert to my MS Access DB, i am trying to find a way to get the UserID (autonumber) it just created. I have look high and low and see a few ways, but nothing seems to work. There are quite a few that suggest using the @@Identity, but i can't seems to get that working either. (The insert works great)

Error

Microsoft OLE DB Provider for ODBC Drivers error '80040e51'
Provider cannot derive parameter information and SetParameterInfo has not been called.

/convention/reg/stage2.asp, line 61

Code

        Set addNew = Server.CreateObject("ADODB.Command")
	    addNew.ActiveConnection = MM_data_STRING
	    addNew.CommandText = "INSERT INTO tbl_Mailing (Address,City,State,Zip,Phone,Fax,EMail) "&_
	    				"VALUES('"&Request("Address")&"','"&Request("City")&"','"&Request("State")
&"','"&Request("Zip")&"','"&Request("Phone")&"','"&Request("Fax")&"',
'"&Request("EMail")&"');"
		addNew.Execute()
		
		addNew.CommandText="SELECT @@Identity as GPID;"
		addNew.Execute()
		GPID=addNew("GPID")

		response.write "DONE " & GPID

 

[DotNetGnat]

try something like this

<%
Set RS = Server.CreateObject("ADODB.RecordSet")
' Open the table
RS.Open "table_name", connection_name, adOpenKeySet, adLockPessimistic, adCmdTable
' Add a new record
RS.AddNew
RS("field_name") = request.form("field_name")

' Update the record
RS.update
' Retrive the ID
unique_ID=RS("ID")
' Close the RecordSet
RS.Close
Set RS = Nothing
%>

-DNG

 

[TheCandyman]

Well I tried your suggestion and had to tweak it a bit to work. It runs through and does the insert, but won't pull the GPID(autonumber) still. suggestions?



code

		Set RS= Server.CreateObject("ADODB.Recordset")
		RS.ActiveConnection = MM_data_STRING
		RS.Source = "SELECT *  FROM tbl_Mailing;"
		RS.CursorType = 0
		RS.CursorLocation = 2
		RS.LockType = 3
		RS.Open()
		RS.AddNew
		RS("Address") = Request("Address")
		RS("City") =Request("City")
		RS("State") =Request("State")
		RS("Zip") =Request("Zip")
		RS("Country") =Request("Country")
		RS("Phone") =Request("Phone")
		RS("Fax") =Request("Fax")
		RS("EMail") =Request("EMail")
		RS.update

		GPID=RS("GPID")
		RS.Close
		Set RS = Nothing
		response.write "DONE " & GPID

 

[damber]

Try this:

Set addNew = Server.CreateObject("ADODB.Command")
        addNew.ActiveConnection = MM_data_STRING
        addNew.CommandText = "INSERT INTO tbl_Mailing (Address,City,State,Zip,Phone,Fax,EMail) "&_
                        "VALUES('"&Request("Address")&"','"&Request("City")&"','"&Request("State")
&"','"&Request("Zip")&"','"&Request("Phone")&"','"&Request("Fax")&"',
'"&Request("EMail")&"');"
        addNew.Execute()
        
        addNew.CommandText="SELECT @@Identity as GPID;"
        [COLOR=red]set oRS = [/color]addNew.Execute()
        GPID= [COLOR=red]oRS.Fields[/color]("GPID")

        response.write "DONE " & GPID

A smile is worth a thousand kind words. So smile, it's easy!

 

[TheCandyman]

sweet, it finally worked, thanks damber!

Can't believe something so simple has taken so much time to get working

 

[damber]

no problem glad to help - it's easy to miss the simple things sometimes, even for exeperienced and skilled developers.

Also, the concept that DNG suggested will also work, though your code needs a few tweaks:

Change the CursorType to 1 (Keyset) and also for efficiency, change the initial SELECT statement to return zero rows (e.g.: SELECT fld1,fld2,fld3,etc FROM MyTable WHERE 0=1) - this wont return any records, but you can still add to it, which makes it more efficient.

The first @@IDentity method is a little more efficient, however is less reliable due to the concurrency of insert requests that might be made (potentially a second insert may be done before it retrieves the @@Identity - which is the last identity to be added) - though this is unlikely.

The second method is less efficient, but guarantees the correct ID.

A smile is worth a thousand kind words. So smile, it's easy!