Added: January/31/2006 at 10:17am | IP Logged

Hi,

I am new here but I was wondering if anyone could answer a question for me as I am in need of urgent help.

I am writting an application in classic ASP (well its un upgrade to an application already created in ASP and MS SQL Server 2000).

Anyway I am trying to get the ID of a row I just inserted into a database table, but when I run my code my SQL code runs and does the insert (I ran a test using MS SQL profiler) and the ID is returned but my recordset seems to run into errors after that.

I get an error saying that the recordset is already closed

Here is my code

 set objConn = Server.CreateObject("ADODB.Connection")
 set objRs = Server.CreateObject("ADODB.Recordset")

 objConn.Open strConnect

 strSQL = "DECLARE @test_ID INT; INSERT INTO myTable VALUES ('myCol', 'myCol'); SELECT @test_ID = @@IDENTITY; SELECT @test_ID AS test_ID"
 
 objRs.open strSQL, objConn
 
 if NOT (objRs.EOF AND objRs.BOF) then
  intID = objRs("test_ID")

The error occours on the if statment (if I get rid of the if statement I get an error saying the operation does not exist).

Anyone got any idea's?

Added: May/27/2006 at 8:04am | IP Logged

i dont recognize ur SQL Statment

but are you sure its correct

i have another way doing this and always works fine

Dim test_ID
objrs.open myTable, objconn,2,3
objrs.addnew
objrs.fields("Col1")="myCol"
objrs.fields("Col2")="myCol"
objrs.update

test_ID=objrs.fields("ID")

hope it works

tell me what happens with u

Best Regards