How can I retrieve AutoNumber after INSERT?

[mavalon]

When I add a record to the table in an Access Database, I need to return the value of the AutoNumber (Unique ID) of the record (so that I can use the number to create a folder with the unique ID appended to the end of the folder name.

I'm using the following code to add records to the database. How can I retrieve the Unique ID after I have inserted a new record?


**********************************************************

Dim adoCon 'Holds the Database Connection Object
Dim rsAddEntry 'Holds the recordset for the record to be added
Dim strSQL 'Holds the SQL query to query the database

Set adoCon = Server.CreateObject("ADODB.Connection&quot
adoCon.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
Server.MapPath ("data/Licensee" & Session("cid&quot & "/companies.mdb&quot

Set rsAddEntry = Server.CreateObject("ADODB.Recordset&quot
strSQL = "SELECT * FROM tblCompanies;"
rsAddEntry.CursorType = 2
rsAddEntry.LockType = 3
rsAddEntry.Open strSQL, adoCon

rsAddEntry.AddNew

rsAddEntry.Fields("longName&quot = Request.Form("txtFull&quot
rsAddEntry.Fields("shortName&quot = Request.Form("txtShort&quot
rsAddEntry.Fields("addr1&quot = Request.Form("addrLn1&quot
rsAddEntry.Fields("addr2&quot = Request.Form("addrLn2&quot
rsAddEntry.Fields("addr3&quot = Request.Form("addrLn3&quot
rsAddEntry.Fields("city&quot = Request.Form("city&quot
rsAddEntry.Fields("state&quot = Request.Form("state&quot
rsAddEntry.Fields("zip&quot = Request.Form("zip&quot
rsAddEntry.Fields("alias1&quot = Request.Form("alt1&quot
rsAddEntry.Fields("alias2&quot = Request.Form("alt2&quot
rsAddEntry.Fields("alias3&quot = Request.Form("alt3&quot
rsAddEntry.Fields("alias4&quot = Request.Form("alt4&quot
rsAddEntry.Fields("alias5&quot = Request.Form("alt5&quot
rsUpdateEntry.Fields("coURL&quot = Request.Form("txtURL&quot

rsAddEntry.Update
rsAddEntry.Close
Set rsAddEntry = Nothing
Set adoCon = Nothing

' Copy folder with database to new folder for new company
folderPath = Server.MapPath("data/Licensee" & Session("cid&quot & "/company" & AUTONUM & "/&quot
fromPath = Server.MapPath("Tmp/&quot
Set fsObj = CreateObject("Scripting.FileSystemObject&quot
If fsObj.FolderExists(fromPath) Then
set fo = fsObj.GetFolder(fromPath)
' The following line is where it dies
fo.Copy folderPath, false
set fo=nothing
set fsObj=nothing
msg = "Copied folder"
End If

 

[lesleint]

Try this

http://www.aspfree.com/authors/briang/identitywithsql.asp

 

[mavalon]

I'm new to ASP. So, forgive my ignorance. Would that code work with what I've got?

 

[mavalon]

I found my solution:

After the insert i am able to retrieve the value of the Autonumber field of the new entry simply by calling the Autonumber field (in this case "cid&quot of the recordset.

rsAddEntry("cid&quot

That was a lot easier than I thought it would be.

 

[ahmun]

I'm running into the problem that retreiving this value with that call var = rsEmployee("ID&quot returns a null value...

the funny thing is it worked before.. here are some details:

myRecordSet.open sSQL, myConnection, 2, 3
All the variables (sSQL and myConnection) are valid, and the 2 and three are adDynamicKeyset, adLockOptimistic

myRecordSet.addnew
some other code to add values to the fields
myRecordSet.update

now I try to retrive the recordset's autonumbered ID field with a call like you mentioned above:
currentID = myRecordSet.Fields("empID&quot

I did a test run of this case with a response.write:
response.write "current id is: " & currentID

but, alas, I get no value... so the html page just returns an empty string. This worked at first, when I had my recordset at LockPessimistic... then we encouraged a lot of users to log on and add their info. The sytem crashed with lots of access errors cuz of the fact that Access (my backend) probably can't handle multi-user very well.

see thread329-563592 for more info related to my problem... I respond to this post cuz it seems closer to solving my problem than that thread...

Earnie Eng
If you are born once, you will die twice
If you are born twice, you will die once

 

[ahmun]

Well.... it's a friday evening.... who is crazy enough to be working at a time like this.. me...

but anyways... I think I found a solution to my problem. It seems that by setting my recordset's cursorlocation to the client side, I am able to retrieve the ID field after using the .addnew command.

you can find the full explanation here:

http://www.aspalliance.com/remas/VFAQ/IdentityColumn/

Earnie Eng
If you are born once, you will die twice
If you are born twice, you will die once

 

[CDN]

you're going to have a problem with simply pulling the last Id unless you're the only person inserting into the database at once.

Do a search on how to retrieve the @@Identity which is what you want

 

[TonyU]

[tt][COLOR=navy}Here you go:

[URL unfurl="true"]http://aspfaq.com/show.asp?id=2174[/URL]

[sup]

Delete * from brain Where MaxLevel = "Full" and reaction = "Slow" order by StartOver
​

[/sup]

 

[mavalon]

I thought @@Identity works for SQL Server, but not for Access. Am I wrong?

 

[TonyU]

[tt]It works with access 2000

[sup]

Delete * from brain Where MaxLevel = "Full" and reaction = "Slow" order by StartOver
​

[/sup]

 

[TonyU]

[tt] I'm pretty sure it works with 2000. In case it doesn't, and you're using dw to build your logic, you can add this code to create a session of the newly inserted ID

faq770-3089

[sup]

Delete * from brain Where MaxLevel = "Full" and reaction = "Slow" order by StartOver
​

[/sup]

 

[Tarwn]

I thought @@Identity pulled the last id that was added, or is it connection specific?

The method he is doing should work since he is using a dynamic keyset. What happens is he adds a new record, which moves the recordset pointer to the record he's adding. Once he updates it generates the id needed for that record, since his recordset is dynamic and still poinbted to the record he just added, than pulling out the id pulls out the id for that new record, even if someone else just added another one to the table.

-Tarwn

[sub]01010100 01101001 01100101 01110010 01101110 01101111 01101011 00101110 01100011 01101111 01101101 [/sub]
[sup]29 3K 10 3D 3L 3J 3K 10 32 35 10 3E 39 33 35 10 3K 3F 10 38 31 3M 35 10 36 3I 35 35 10 3K 39 3D 35 10 1Q 19[/sup]
Get better results for your questions: faq333-2924
Frequently Asked ASP Questions: faq333-3048

 

[TonyU]

[tt]If Tarwn, says it. It's must be 99.9% true.

[sup]

Delete * from brain Where MaxLevel = "Full" and reaction = "Slow" order by StartOver
​

[/sup]

 

[onpnt]

I'll never let him ove that one down

_________________________________________________________
[sub]$str = "sleep is good for you. sleep gives you the energy you need to function";
$Nstr = ereg_replace("sleep","coffee",$str); echo $Nstr;[/sub]

[sup]

http://www.onpntwebdesigns.com

[/sub]

 

[onpnt]

ove = live

and on that -->sleep needed <---coffee not working any, any more

_________________________________________________________
[sub]$str = &quot;sleep is good for you. sleep gives you the energy you need to function&quot;;
$Nstr = ereg_replace(&quot;sleep&quot;,&quot;coffee&quot;,$str); echo $Nstr;[/sub]

[sup]

http://www.onpntwebdesigns.com

[/sub]