Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2002
    Posts
    14

    Unanswered: Problem with MAX()

    I need to get the Autonumber of the newly inserted data. I am using second SQL to get the max(Autonumber)
    SQL="SELECT (MAX(T_ID) as ID from TradeTbl)"

    Set rs = Server.CreateObject("ADODB.Recordset")
    rs.Open SQL, MyConn, 3

    Reponse.write returns me empty data (not null).

    What is going on? Do I need to set up a new connection (tried that) or what can I do?
    I would really appreciate your help

  2. #2
    Join Date
    Mar 2002
    Posts
    14
    I know I could use rs.addnew and rs.update to get the Autonumber.
    However, I am using a huge (and I mean it, HUGE SQL statement with if else statements inside), and I wouldn't want to build a whole new procedure using addnew and update. :-(
    Help!!!

  3. #3
    Join Date
    Feb 2002
    Posts
    2,232
    Which database are you using ?

  4. #4
    Join Date
    Mar 2002
    Posts
    14
    MS ACCESS
    the addnew/update is working
    but i still want to know how to solve the prob with plain sql statements

  5. #5
    Join Date
    Feb 2002
    Posts
    2,232
    Run the "select @@identity" statement after the record has been inserted. You can then reference it using rs(0).

  6. #6
    Join Date
    Mar 2002
    Posts
    14

    Thumbs up

    Originally posted by rnealejr
    Run the "select @@identity" statement after the record has been inserted. You can then reference it using rs(0).
    Dear rnealejr!
    Thank you so much for the help!!! I think you DO deserve that fifth star! "Select @@Identity" worked!
    I will come back again if I have some other issues.
    Thank you

  7. #7
    Join Date
    Mar 2002
    Posts
    14

    Thumbs up

    Originally posted by rnealejr
    Run the "select @@identity" statement after the record has been inserted. You can then reference it using rs(0).
    Dear rnealejr!
    Thank you so much for the help!!! I think you DO deserve that fifth star! "Select @@Identity" worked!
    I will come back again if I have some other issues.
    Thank you

Posting Permissions

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