asked on

VB6 calling SQL Stored Procedure - no recordset returned (SELECT not first statement!)

I'm using Visual Basic 6, SQL Server 2005 and I'm trying to call a Stored Procedure from VB6 which; 1) Creates some temporary tables, 2) Gathers a whole host of data and 3) Returns it via a SELECT command. The problem is that if "SELECT" is not the first statement in the Stored Procedure then a recordset is not returned. Why? How do I get the resulting recordset returned?

ASKER CERTIFIED SOLUTION

amit_g

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

wsh2

Your code should look somehting like this..
---------------------------------------------------------------------------------------------------
Set adoCommand = New ADODB.Command
With adoCommand
.ActiveConnection = adoConnectionObject
.CommandType = adCmdStoredProc ' <- I think you are missing this
.CommandText = "MyStoredProcedureName"
.Parameters.Append _
.CreateParameter("MyParameter", adVarChar, adParamInput, 6, strMyParameter)
Set adoRS = .Execute
End With
---------------------------------------------------------------------------------------------------

ManSys UK

ASKER

Definitely SET NOCOUNT ON - works an absolute treat. The special thing about doing it this way is that if you have a user defined query, if it returns a recordset you display it in a grid, otherwise tell them it's run and let them know how long it took - perfect for what I was doing.