Set rsObj=cmdObj.Execute error

[behbeh]

Hi!

I hope some one can tell me what I am doing wrong with the following code:

Dim cmdObj As ADODB.Command
Dim rsObj As ADODB.Recordset

Set cmdObj = New ADODB.Command
With cmdObj
.ActiveConnection = g_objConn
.CommandText = "dbo.usp_SELECT_Info"
.CommandType = adCmdStoredProc
.Parameters("@intID&quot = g_lngCustomerID
End With

Set rsObj = cmdObj.Execute
Do While Not rsObj.EOF
'----
Loop

I receive a Runtime error #3704 on the "Do While.." line(it thinks the rsObj is closed).
Any suggestions would be greatly appreciated!
Thanks!

 

[woyler]

The recordset is not getting any value, so you can not reference it by .eof.

 

[edmundo]

I'm having the same problem.

I have the cause of it in my case but not the reason why nor how to solve the problem.

In my case it is due to the fact that in the stored procedure I am populating a #temp table and selecting from it. I have found that if I do something like

select * from #temp

I receive the error message, mentioned above. If I was to run the same code in the procedure but populate an actual table in the database, the query works. I obviously can't create a physical table in the database to run the query but it appears that using temp tables at any point in the procedure causes this error message.

I have run the procedure from query analyzer and the procedure returns data. Any help greatly appreciated...

======
Just to assist, here's the code I use


==== In VB

With spCommandClosed
.ActiveConnection = cnNetSup
.CommandText = "sp_select_jobs_closed"
.CommandType = adCmdStoredProc
Set rsClosedJobs = .Execute
End With


Do While Not rsClosedJobs.EOF
'----
Loop

====in Sql (sp_slect_job_closed)

create table #temp
(f1 as char(1),f2 as char(1))

insert into #temp
select....

insert into #temp
select....

insert into #temp
select....

select * from #temp

 

[edmundo]

Me again...

found what I wanted. Behbeh, It was actually a response to another one of your threads in SQL Server

but for anyone else interested

thread183-110119

bascially

set nocount on

at the beginning of the stored procedure... simple when you know how!!!