Hi, This question probably may be 1

[Deadline]

Hi,
This question probably may be asked already but couldn't find it elsewhere!!!!

This table QM contains an ID field, which is an AutoNumber field.

rs.Open "QM",objConn,2,3
rs.AddNew
	rs("A1") = Request.Form("cafewait")
	rs("A2")= Request.Form("overall")
	rs("A3A") = Request.Form("s3a")	
	rs("A3B") = Request.Form("s3b")
	rs("A3C") = Request.Form("s3c")
	rs("A3D") = Request.Form("s3d")
	rs("A3E") = Request.Form("s3e")
	rs("A3F") = Request.Form("s3f")
	rs("A3G") = Request.Form("s3g")
	rs("A3H") = Request.Form("s3h")
	rs("A3I") = Request.Form("s3i")
	rs("A3J") = Request.Form("s3j")
	rs("A3K") = Request.Form("s3k")
	rs("A3L") = Request.Form("s3l")
	rs("A4A") = Request.Form("s4a")
	rs("A4B") = Request.Form("s4b")
	rs("A6") = Request.Form("s6")
        rs.Update
        rs.Close

After performing the above insert, I am trying to fetch the MAX(ID) like

SQL = "SELECT Max(Q5.QMID) AS MaxOfQMID FROM Q5"

rs.Open	SQL, objConn

Response.Write &quot;Max of QMID=&quot; & rs(&quot;MaxOfQMID&quot;)	& &quot;<HR>&quot;

It never gives me a value.. The

rs("MaxOfQMID")

is always blank.. What could be the reason ?
Thank you very much.

RR

 

[Deadline]

And I forgot to mention.. that I am running this code on a fresh database with no records initially..

Thank you very much.

RR

 

[foxbox]

When you use SQL Server you can retrieve the id of the new record by using @@identity.

EG


Dim db,rcs,new_identity

'Create a database connection
Set db = Server.CreateObject("adodb.connection&quot
db.Open "DSN=MyDSN"

'Execute the INSERT statement and the SELECT @@IDENTITY
Set rcs = db.execute("insert into tablename (fields,..) "&_
"values (values,...);" &_
"select @@identity&quot.nextrecordset
'Retrieve the @@IDENTITY value
new_identity = rcs(0)


br
Gerard

 

[Deadline]

Thanks Gerad..
But the database in my case is Access 2000. It works if I convert the above into INSERT statement.


Thank you very much.

RR

 

[vasah20]

festivista97 -
by using the RS.AddNew as you are, you don't need to issue another query. Since QM is returning all the fields, all you need to do is RS.Update. After you RS.Update, the new ID will be in RS("QMID&quot.

The main reason you want to do it this way is because of concurrency problems that may arise if two people attempt to update the table at the same time.

For a good discussion of this check out this link:

http://www.aspfaqs.com/aspfaqs/ShowFAQ.asp?FAQID=79

It should answer your problems about getting the just added ID number in an Access table.

good luck -
leo