Retrieving db Primary Key after update ?

Results 1 to 3 of 3

Thread: Retrieving db Primary Key after update ?

  1. #1
    Glenn Wilson Guest

    Default Retrieving db Primary Key after update ?

    If I have an MS Access database with the following fields:<BR><BR>ID (the primary key, an integer, automatically incremented)<BR>field1 (text)<BR>field2 (text)<BR><BR>and I add a new field as follows<BR><BR>&#060;%<BR>rs.addnew<BR>rs("field1" ) = "whatever"<BR>rs("field2") = "whatever"<BR>rs.update<BR>%&#062;<BR><BR>what is the best way to obtain the value of rs("ID") generated by the database upon record creation ?<BR><BR>on one version of IIS 4.0, rs("ID") contained the correct value immediately after the update, i.e.<BR><BR>&#060;%<BR>rs.addnew<BR>rs("field1") = "whatever"<BR>rs("field2") = "whatever"<BR>rs.update<BR>response.write rs("ID")<BR>%&#062;<BR><BR>displayed the primary key integer of the record just added. Oddly on two other IIS 4.0 no value was displayed ... i.e. rs("ID") was empty.<BR><BR>to solve the problem I have used the following code<BR><BR>&#060;%<BR>rs.addnew<BR>rs("field1") = "whatever"<BR>rs("field2") = "whatever"<BR>rs.update<BR>rs.movelast<BR>%&#062;< BR><BR>which works on all IIS&#039s I&#039ve tried.<BR><BR>Is this the BEST way to do it ?<BR>Will it ALWAYS work ?<BR><BR>Regards,<BR>Glenn<BR>

  2. #2
    Join Date
    Dec 1969

    Default RE: Retrieving db Primary Key after update ?

    I was playing around with this issue one day and found an interesting way of getting the Autonumber field. Try this out:<BR>&#060;%<BR>rs.addnew<BR>rs("field1") = "whatever"<BR>primarykey = rs("ID")<BR>rs("field2") = "whatever"<BR>rs.update<BR>response.write(primaryk ey) <BR>%&#062;<BR><BR>I have only used this in testing and it seams to work ok. I noticed that when i was in access adding records to the table directly that right after entering a single character that the autonumber field showed up. so i figured that if i do a AddNew then put in atleast one field into the DB I could get the Autonumber field right away. then go ahead and add all of the other fields to the db then do an update like the code above.<BR>Try it out. Thanks Jefferys<BR><BR>

  3. #3
    Join Date
    Dec 1969

    Default RE: Retrieving db Primary Key after update ?

    It&#039s safer to wait until after the RS.Update to go get the key (for example, if the DB engine is on a remote machine from the ASP engine and/or if you are using SQL Server), but the idea is right.<BR><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