get value of autonumber-field

Discussion in 'ASP General' started by Jan Hendrickx, Sep 20, 2005.

  1. Hi all,

    I use (something like) following code to add new records to a database,
    but it doesn't work!!!

    I need to know the value of an AutoNumber-field when I add the record.

    Why doesn't it work and how can I make it work???
    I think it should be easy, but I can't figure it out!

    Thanks in advance...
    Jan H.

    <%option explicit
    dim cn, rs, v1, v2, v3
    set cn=server.createobject("ADODB.Connection")
    set rs=server.createobject("ADODB.Recordset")
    cn.open "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" &
    server.mappath("./mdb/MyMDB.mdb")
    rs.open "SELECT * FROM tblTest",cn,3,3
    rs.addnew
    rs("Naam")="Some new value"
    v1=rs("ID")
    rs.update
    v2=rs("ID")
    rs.moveprevious 'this works,
    rs.movenext 'but only when
    v3=rs("ID") 'not first record!!!!
    rs.close
    cn.close
    set rs=nothing
    set cn=nothing%>
    <html>
    </head>
    <body>
    *** <%=v1%> *** <%=v2%> *** <%=v3%> ***
    </body>
    </html>
     
    Jan Hendrickx, Sep 20, 2005
    #1
    1. Advertising

  2. Jan Hendrickx wrote:
    > Hi all,
    >
    > I use (something like) following code to add new records to a
    > database, but it doesn't work!!!
    >
    > I need to know the value of an AutoNumber-field when I add the record.
    >
    > Why doesn't it work and how can I make it work???
    > I think it should be easy, but I can't figure it out!
    >
    > Thanks in advance...
    > Jan H.
    >

    http://www.aspfaq.com/show.asp?id=2174
    --
    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 Barrows [MVP], Sep 20, 2005
    #2
    1. Advertising

  3. Jan Hendrickx

    Bullschmidt Guest

    Perhaps change this:

    rs.open "SELECT * FROM tblTest",cn,3,3

    To be more like this instead:

    rs.open "SELECT * FROM tblTest",cn,1,3

    And here is a memo I previously wrote to myself about the issue:

    Use an autonumber in a table whenever can't get a unique record from
    just one field. This is needed in subquery on "many" pages and will
    make things easier if just have to check one field for the cur rec etc.

    And's some sample code I use WHEN FIRST OPENING the recordset (notice
    the CursorType) so that later I can easily get the new Autonumber:

    ' Open rs.
    Set objRS = Server.CreateObject("ADODB.Recordset")
    ' (1=CursorType of adOpenKeyset in case ever want to get an autonumber
    of new rec,
    ' 3=LockType of adLockOptimistic because updating.)
    objRS.Open strSQL, objConn, 1, 3

    Best regards,
    J. Paul Schmidt, Freelance ASP Web Developer
    http://www.Bullschmidt.com
    ASP Design Tips, ASP Web Database Demo, Free ASP Bar Chart Tool...

    <<
    Hi all,

    I use (something like) following code to add new records to a database,
    but it doesn't work!!!

    I need to know the value of an AutoNumber-field when I add the record.

    Why doesn't it work and how can I make it work???
    I think it should be easy, but I can't figure it out!

    Thanks in advance...
    Jan H.

    <%option explicit
    dim cn, rs, v1, v2, v3
    set cn=server.createobject("ADODB.Connection")
    set rs=server.createobject("ADODB.Recordset")
    cn.open "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" &
    server.mappath("./mdb/MyMDB.mdb")
    rs.open "SELECT * FROM tblTest",cn,3,3
    rs.addnew
    rs("Naam")="Some new value"
    v1=rs("ID")
    rs.update
    v2=rs("ID")
    rs.moveprevious 'this works,
    rs.movenext 'but only when
    v3=rs("ID") 'not first record!!!!
    rs.close
    cn.close
    set rs=nothing
    set cn=nothing%>
    <html>
    </head>
    <body>
    *** <%=v1%> *** <%=v2%> *** <%=v3%> ***
    </body>
    </html>
    >>


    *** Sent via Developersdex http://www.developersdex.com ***
     
    Bullschmidt, Sep 21, 2005
    #3
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Hai Nguyen
    Replies:
    3
    Views:
    1,510
    Hai Nguyen
    Jan 10, 2004
  2. Nathan Sokalski

    Getting the value of the next Autonumber

    Nathan Sokalski, Jun 30, 2005, in forum: ASP .Net
    Replies:
    4
    Views:
    11,353
    William \(Bill\) Vaughn
    Jun 30, 2005
  3. Roger Withnell
    Replies:
    9
    Views:
    333
    Aaron Bertrand - MVP
    Dec 2, 2003
  4. Sound
    Replies:
    2
    Views:
    454
    Randy Webb
    Sep 28, 2006
  5. jr
    Replies:
    3
    Views:
    426
Loading...

Share This Page