PDA

View Full Version : Problem with MAX()

Shurik
08-28-02, 15:50
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
Shurik
08-28-02, 16:11
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. :-(
:confused: Help!!!
rnealejr
08-28-02, 16:59
Which database are you using ?
Shurik
08-28-02, 17:17
MS ACCESS
the addnew/update is working
but i still want to know how to solve the prob with plain sql statements
rnealejr
08-28-02, 18:10
Run the "select @@identity" statement after the record has been inserted. You can then reference it using rs(0).
Shurik
08-29-02, 12:19
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
Shurik
08-29-02, 12:19
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