Professional Web Applications Themes

Getting the new record's AutoNumber - ASP Database

Damn! Again a piece of working code from another page of mine is not working when I use it elsewhere... The code below is not giving me the new records Autonumber. When I look at the raw data in Access the Autonumber is from the first record! I'm using recSet2.CursorLocation = adUseClient to make this all possible and Query2 = "Select custID, firstname, lastName, email, bestPhone FROM tblCustomers ORDER by lastName" recSet2.open Query2,Cn,adLockOptimistic,adOpenStatic recSet2.AddNew recSet2("firstName") = Request("firstName") recSet2("lastName") = Request("lastName") recSet2("email") = Request("email") recSet2("bestPhone") = Request("bestPhone") recSet2.Update bookmark = recSet2.absolutePosition ' First, store the location of you cursor recSet2.Requery recSet2.absolutePosition ...

  1. #1

    Default Getting the new record's AutoNumber

    Damn!
    Again a piece of working code from another page of mine is not working when
    I use it elsewhere...
    The code below is not giving me the new records Autonumber. When I look at
    the raw data in Access the Autonumber is from the first record!

    I'm using recSet2.CursorLocation = adUseClient to make this all possible and
    Query2 = "Select custID, firstname, lastName, email, bestPhone FROM
    tblCustomers ORDER by lastName"
    recSet2.open Query2,Cn,adLockOptimistic,adOpenStatic

    recSet2.AddNew
    recSet2("firstName") = Request("firstName")
    recSet2("lastName") = Request("lastName")
    recSet2("email") = Request("email")
    recSet2("bestPhone") = Request("bestPhone")
    recSet2.Update
    bookmark = recSet2.absolutePosition ' First, store the location of you
    cursor
    recSet2.Requery
    recSet2.absolutePosition = bookmark

    I then do....

    recSet1.AddNew
    recSet1("custID") = recSet2("custID")
    recSet1.Update


    Darren Guest

  2. #2

    Default Re: Getting the new record's AutoNumber

    Darren,

    In looking at your code there are a couple of things i would change.
    For performance reasons use a sql state to insert your record rather than
    "add new"
    Using a sql "insert into" statement would probably simplify things for you
    also.

    example:

    sql = "insert into mytable (mycolumn) Values (myvalue)
    cn.execute(sql)

    You then can then run another sql statement straight up the record is saved.

    example:
    //my column is the auto number column
    sql = "select max(mycolumn) from mytable"
    set rs = cn.execute(sql)

    http://www.aspfaq.com/show.asp?id=2174

    The above link will give you more details:

    AJ


    Anthony Guest

  3. #3

    Default Re: Getting the new record's AutoNumber

    Anthony Judd wrote: 
    Good advice, but you can improve performance and reduce the potential for
    error by specifying the command type (adCmdText) and exection option
    (adExecuteNoRecords), like this:

    cn.execute sql,,129

     
    Which will not include depending on max to get the newly inserted id. In a
    multi-user environment, that technique is not reliable. Use IDENTITY
    instead (again, specifying the command type to reduce the potential for
    error):

    set rs=cn.execute("SELECT IDENTITY",,1)

    Bob Barrows

    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"


    Bob Guest

  4. #4

    Default Re: Getting the new record's AutoNumber

    Darren Heinrich wrote: 

    Haven't you got the CursorType and Locktype parms swapped here? That is,
    shouldn't it be:
    recSet2.open Query2,Cn,adOpenStatic, adLockOptimistic
     
    Michael Guest

Similar Threads

  1. autonumber troubles
    By JMosey@gmail.com in forum MySQL
    Replies: 4
    Last Post: May 23rd, 01:46 PM
  2. Insert Record and Retrieve autonumber extension
    By galeemma in forum Macromedia Exchange Dreamweaver Extensions
    Replies: 2
    Last Post: April 21st, 05:14 PM
  3. autonumber question
    By dthatsme in forum Coldfusion - Getting Started
    Replies: 4
    Last Post: March 11th, 10:39 AM
  4. ASP Access Autonumber property
    By mike in forum ASP Database
    Replies: 1
    Last Post: August 11th, 05:44 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139