SP with Results

sidneyfuerte · September 8th, 2003, 02:09 PM

Hi everyone,

I'm getting an error "run-time error '3704' operation is not allowed when object is closed." when i tried to access the content of my recordset, I am calling a stored procedure with the following sql statement:

sSq = "DECLARE @iIntId INT " & _
      "SET @iIntId = null " & _
      "exec GetID 1,@iIntId OUTPUT " & _
      "SELECT @iIntId " & _
rs.Open sSQl, gDbConn, adOpenDynamic, adLockOptimistic
if Not rs.Eof then
   MyId = rs("iIntId")
End if

The error occur in the "if Not rs.eof..." line co'z i debug the program and i'm sure the recordset has one row in it... the ID.

Can somebody explain to me why this is happening? I really appreciate any comment or help.

Thanks/Regards
Sidney

jlick · September 8th, 2003, 02:18 PM

Ok, the code that you posted seems to have errors in it, So first lets assume that you didn't just mis-type in the post...

sSq = "DECLARE @iIntId INT " & _
      "SET @iIntId = null " & _
      "exec GetID 1,@iIntId OUTPUT " & _
      "SELECT @iIntId " & _
rs.Open sSQl, gDbConn, adOpenDynamic, adLockOptimistic
if Not rs.Eof then
   MyId = rs("iIntId")
End if

This looks like you are trying to add your rs.Open to the SQL string.

If your code is right, and this is just a typo, then I am wondering if you are getting enbedded recordsets back. The first recordset being the empty results from the exec GetID, and the ".NextRecordset" holding your id.

I didn't test this, but it is a thought.

John R Lick
[email protected]

sidneyfuerte · September 8th, 2003, 05:09 PM

Yes it was just a typo, and your correct i want to have get the id as an output, if i execute this sql command in the querry analyzer i get 1 row back... which is the id. but when i execute it in vb where should i get the row? is it not in my current opened recordset?

I really appreciate your response to my question :)

Thanks/Regards
Sidney

jlick · September 9th, 2003, 08:57 AM

I cannot recreate the error you got. What I do get is that the field name that you are assuming to get back does not exist. This is due to the fact that you are selecting a varaible, and not giving it a name.

You should use:
SELECT iIntId = @iIntId

As far as the error that you got, I need more information since I can't recreate it.

John R Lick
[email protected]

Yehuda · September 9th, 2003, 01:48 PM

My initial hunch would be to check the connection object and make sure it is open. Just a couple of suggestions:

1. I don't know what your error handling looks like, but if you are not doing so use error lines to get which line it fails on. If it fails on the rs.Open, the connection is bad. If it fails on rs.EOF the recordset failed to execute properly.

2. Try to run the stored proc through a command object rather than a recordset (a technique, I happen to favor as a whole). See what happens.

Yehuda