select max problem

[daveigh]

hey guys.

got some prob here. i'd like to select the max value of field named oid. the problem is when the oid has no value initially, what do i have to do so that i could trap it and create an initial value?

here is my current code which wont work, coz the oid field has no value.

set toprs = server.createobject("ADODB.Recordset")
topsql = "select max(oid) as temp from tablename"
toprs.open topsql,dataconnection
if toprs.eof then
tempnum = 10000
else
tempnum = toprs("temp") + 1
end if


______________CRYOcoustic_____________

 

[DotNetGnat]

did you mean this:

if toprs("temp")<>"" then
tempnum=toprs("temp")+1
else
tempnum=10000
end if

didnt understand your logic though...

can you please explain what you are actually trying to do..

-DNG

 

[Sheco]

You can get the value NULL with Max(), Count() and the other aggregate SQL functions.

Set toprs = Server.CreateObject("ADODB.Recordset")
topsql = "select max(oid) as temp from tablename and oid is not null"
toprs.Open topsql, DataConnection
If toprs.EOF Then
  tempnum = 10000
Else
  [highlight]If IsNull(toprs("temp")) Then[/highlight]
    tempnum = 10000
  Else
    tempnum = toprs("temp") + 1
  End If
End If