Finding the autonumber field

[emozley]

Apologies I suspect this question has been asked before but I have yet to find a satisfactory answer.

With an MS Access/ASP combination what is the best way to insert a record and then get the autonumber value? The way I am doing it at the moment is to add the record then do a select all and order by ID DESC then assume the first record in the recordset is the last one added. As well as being a bit inefficient I am not sure if it is totally foolproof especially if the database is being used by a lot of people.

Would it be possible to create a recordset, then write that recordset then get the ID field before closing the recordset?

cheers!

Ed

<%

Set DB = Server.CreateObject("ADODB.Connection")
Set TBL = Server.CreateObject("ADODB.RecordSet")

DB.Mode = adModeReadWrite
DB.Open ("PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=" + Server.MapPath("../directory/userdb.mdb"))

Title=Request.QueryString("Title")
AuthorID=Request.QueryString("AuthorID")
DocsDate=Request.QueryString("DocsDate")
FilePath=Request.QueryString("FilePath")

TBL.Open "INSERT INTO DocsMaster (Title, AuthorID, DocsDate, FilePath) VALUES ('" & Title & "', '" & AuthorID & "', '" & DocsDate & "', '" & FilePath & "')", DB

TBL.Open "SELECT * FROM DocsMaster ORDER BY DocID DESC", DB
DocID=TBL("DocID")
TBL.Close

Set TBL=Nothing
Set DB=Nothing

URL="adddoc2.asp?DocID=" & DocID

Response.Redirect(URL)

%>

 

[Tarwn]

It is (much) less efficient, but you can open a recordset to your table and:

'add a new row
MyRecordset.AddNew

'set the values for the new row
MyRecordset("myfield") = "a"
MyRecordset("myotherfield") = "blah"

'force it to send the updates back
MyRecordset.Update

'read the value from the updated row
Response.Write "New Id Is: " & MyRecordset("idfield")

-T

 

[DotNetGnat]

see my reply in this thread thread333-1235077

-DNG

 

[theniteowl]

emozley, try it something like the code below. I modified my existing code to be more like yours. It may work but I have not tested it.
The approach is simply to set a new object and use the open DB connection to execute the SELECT @@Identity command which will return the newly created autonumber value.

<%
Title=Request.QueryString("Title")
AuthorID=Request.QueryString("AuthorID")
DocsDate=Request.QueryString("DocsDate")
FilePath=Request.QueryString("FilePath")

Set DB = Server.CreateObject("ADODB.Connection")
  DB.Open ("PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=" + Server.MapPath("../directory/userdb.mdb"))
    mySQL = "INSERT INTO DocsMaster (Title, AuthorID, DocsDate, FilePath) VALUES ('" & Title & "', '" & AuthorID & "', '" & DocsDate & "', '" & FilePath & "')"
    DB.Execute mySQL

    Set rsNewID = DB.Execute("SELECT @@IDENTITY") ' Create a recordset and SELECT the new Identity
      DocID = rsNewID(0) ' Store the value of the new identity in variable DocID
    rsNewID.Close
    Set rsNewID = Nothing
  DB.Close
  Set DB = Nothing

URL="adddoc2.asp?DocID=" & DocID
Response.Redirect(URL)
%>

It's hard to think outside the box when I'm trapped in a cubicle.