SCOPE_IDENTITY() returning NULL - URGENT

[ranta]

I have a function which is insterting a record into a table and then using SCOPE_IDENTITY() to return the newly created ID for that table.

This all works fine untill I call the function within a loop, then the SCOPE_IDENTITY() returns a NULL.

Does anyone have any idea why this is happening, is it even possible to insert statements in a loop retrieveing the IDENTITY each time????

 

[hmckillop]

Are you executing SCOPE_IDENTITY() directly after the INSERT statement. If so this will be fine, the problem is SCOPE_IDENTITY() function will return the NULL value if the function is invoked before any insert statements into an identity column occur in the scope.


"I'm living so far beyond my income that we may almost be said to be living apart

 

[ranta]

The SCOPE_IDENTITY() function is directly after the INSERT statement, let me give you cut down version of the code...

Function copyRecord(recordID)
SQL="INSERT INTO tableName (name,desc) SELECT name,desc FROM originalTable WHERE recordID="&recordID&""
set rs=objConn.execute(SQL)
set rs=nothing

SQL="SELECT SCOPE_IDENTITY() AS SCOPE_IDENTITY"
set rs=objConn.execute(SQL)
newRecordID=rs("SCOPE_IDENTITY")
set rs=nothing


End Function


'Call to copyRecord function...
SQL="SELECT recordID FROM originalTable WHERE active=1"
set cat=objConn.execute(SQL)
if not cat.eof then

call copyRecord("&cat("recordID")&")

end if
cat.close


As I said, if I take out the loop and just call it once it works fine, if I put the loop back in then is just returns NULL... confused!