Return autonumber key field after insert!

Results 1 to 5 of 5

Thread: Return autonumber key field after insert!

  1. #1
    Mark Guest

    Default Return autonumber key field after insert!

    I am trying to find a way to create a new record... do the INSERT and return the dynamic (autonumber) ID field back to the page via ASP... I need the ID back so we can use it on the following form in a WHERE ID=???? <BR><BR>Someone do this before? Going against a DB2 database.

  2. #2
    Huw Guest

    Default RE: Return autonumber key field after insert!

    I haven&#039t done it for DB2 but for access, if you use sequential autonumber, write the record, update the recordset, move to the last record - i.e. the record you&#039ve just added and read the ID<BR><BR>Hope this helps<BR><BR>Huw

  3. #3
    Join Date
    Dec 1969

    Default RE: Return autonumber key field after insert!

    First: This solution is pretty dangerous to use since there is a possibility that another process adds yet another record to the db in the timeslot between the update and the movelast call. <BR>Then you would ofcourse move to the wrong record with your movelast call. This type of problem is known as a "Racing condition problem". Although the probability for this to happen on a site with not that many simultanious users isn&#039t very high it still causes some potential unpredictable results, and on a heavily used site the probability increases ofcourse.<BR><BR>Second: As mentioned this "unsafe" method only works for Access. I have had the same problem but with SQLServer7 and here this method doesn&#039t work at all. I was forced to generate a uniqueid in an own external routine and set the idfield by my self, then using the now "known" uniqueid in the following code. Still I am wondering if anyone out there knows a "real" solution to get the autogenerated field from the db during an insert that works even with SQLServer7 and other databases and doesn&#039t have racing condition problems?

  4. #4
    Matthew Deeks Guest

    Default RE: Return autonumber key field after insert!

    This Works for SQL Server:<BR>In SQL then "autonumbering" column is called an Identity column.<BR>@@IDENTITY is a variable in SQL that will give the value of the last unique identity assigned by the connection.<BR><BR>Set objConn = Server.CreateObject("ADODB.Connection")<BR>Set Rs = Server.CreateObject("ADODB.Recordset")<BR>objConn. Open ("DSN=TestSQLODBCSource;")<BR><BR>SQLStr = "Insert Member(MemberName) Values (&#039Matthew Deeks&#039)"<BR>objConn.Execute SQLStr<BR><BR>SQLStr = "Select @@IDENTITY"<BR>Rs.Open SQLStr, objConn, 3, 1<BR><BR>Response.Write Rs.Fields(0).Value<BR>

  5. #5
    Mark Guest

    Default RE: Return autonumber key field after insert!

    &nbsp;<BR>&#060;!-- #include file="" --&#062;<BR>Set Rs = Server.CreateObject("ADODB.RecordSet")<BR><BR>Rs.O pen "tUsers", "AutoExample", adOpenKeySet, adLockPessimistic, adCmdTable<BR><BR>Rs.AddNew<BR><BR>Rs("UserName") = Request.Form("USERNAME")<BR>Rs("UserEmail") = Request.Form("USEREMAIL")<BR><BR>lUserID = Rs("UserID")<BR><BR>Rs.Close<BR>Set Rs = Nothing<BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts