Empty Recordset Problem

[mrDrive]

Hi,

I'm creating a new record for my db. Before I run my insert statement, I have to create a new ID value (primary key) for the new record. I'm trying to make this application ready for an empty db table.

dim objRS, strSQL, intNewID
strSQL = "select max(ID) + 1 as NEXTIDVAL " & _
         "from MyTable"
set objRS = Server.CreateObject("ADODB.Recordset")
objRS.Open strSQL, objConn
if objRS.eof then
	intNewID = 1
else
	intNewID = objRS("NEXTIDVAL")
end if
response.Write("intNewID = " & intNewID)
objRS.close
set objRS = nothing
set strSQL = nothing

For some reason, intNewID comes back empty even though I look for an empty recordset.

Any help would be greatly appreciated!

mD

 

[btgroup]

When you select a Max on an empty table, a NULL will be returned. You will never receive an EOF on that query.

You might try doing something as follows:

If isnull(objRS("NEXTIDVAL") Then
intNewID = 1
Else
intNewID = objRS("NEXTIDVAL")
End If

 

[FesterSXS]

Can't you setup the ID field in the table as an identity field which auto-increments whenever a new record is added?

Tony
________________________________________________________________________________

http://www.SpiraxSarco.com

 

[JamesLean]

Just change this line of code:

strSQL = "select ISNULL(max(ID), 0) + 1 as NEXTIDVAL from MyTable"

You actually don't then need to test for EOF as you will never get an empty recordset, as wrentmore pointed out.

--James

 

[mrDrive]

Thanks guyZ!

I like James' technique.