get the last row of record from a table

[reigalz]

hi, there's a field SalesOrderNumber in my database...how do i get the value of the last row of the SalesOrderNumber?

[raf]

What do you mean? The id of that row or the value of that variable?

[reigalz]

the value of the last row...means the data entered in the last row of the field SalesOrderNumber

[raf]

Since it’s a sales order number, i can immagen it’s a unique value that get incremented with each time a new record.So you could use

Sql=”SELECT Max(SalesOrderNumber) from table”

But it really depends on what you wanna do with it. If you create a new salesorder online and write it to the table, and you then want to retrieve the salesOrerNumber that was generated in the db, then you need to take some extra precautions (cause another user might have entered another record between your insert en select statements). I then always insert a date/time into the db (time gets stored in a variable on top of the page), so that i can retrieve the record by adding the date/time and the userID into the condition (the WERE clause)
(for Access that is, other DB’s have build in functionalitys for that). More info
http://www.codingforums.com/showthread.php?s=&threadid=17422&highlight=%40%40IDENTITY

If this field isn’t an autonumber, then you need to get the id of the last record and then select the value.

Sql=”SELECT SalesOrderNumber from table where ID IN (SELECT Max(ID) from table)”

Here you can also add the datetimeand user in the condition


datetime=Now()
‘first insert
sql=”Insert into table (variables,regdate,insuser) values(other values,#thetime#,theid)”
sql=replace(sql,”thetime”,datetime)
sql=replace(sql,”theid”,request.form(“user”))

‘then retrieve value
Sql=”SELECT SalesOrderNumber from table where ID IN (SELECT Max(ID) from table WHERE regdate=#thedate# AND userID=theid)”

sql=replace(sql,”thetime”,datetime)
sql=replace(sql,”theid”,request.form(“user”))