Check for records

[discusmania]

hi guys.....
I want to get the maximum value of item_id and add 1 to it to get the new request item id. i use

sql=select max("item_id) as item_id from act_item.

it works fine if there's records in that table. but if there's no records i want the item_id to be initiate to 0

how to do that.

Thanks a lot

RON

 

[TimLarkin]

What's being returned if there are no records? - check for that.(I'd check it out for you, but this is a new machine and I haven't installed SQL yet and the Intranet at work is down for upgrades).
However, what I usualy do is make the item_id field an identity (auto-incrementing) field, and have another field as a (nearly)unique field, such as Date_Created. This way more than one user can add a record, and get the new item_id:
MyTime = Now
sql="Insert Into MyTable (Date_Created) Values ('" & MyTime & "')"
adoconConnection.Execute sql
sql="Select * From MyTable Where Date_Created = '" & MyTime & "'"
Set MyRS = adoconConnection.Execute(sql)

NewItem_ID = MyRS.Item_ID

 

[Geee]

This is the code I use to connect to an Oracle DB Backend and select the new Item_ID. Hope it helps...

<%

ITEM_NOsql = &quot;Select Max(JOB_ID) as Result from ENGENV_OPS_PLAN&quot;
'SQL String returns Last JOB_ID as Result.

adoRS_Max.Open ITEM_NOsql,adoConn
'Execute the SQL command.

If ISNull(ADORS_Max(&quot;Result&quot) Then
ItemNo = 1
Else
ItemNo = ADORS_Max(&quot;Result&quot+1
End If
'Set the ItemNo. If Result is Null set ItemNo to 1 else set ItemNo to Result +1

%>


Hope this helps!