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?
ManSys UKAsked:
Who is Participating?
amit_gConnect With a Mentor Commented:
You have 2 options...

1) Change the stored procedure to have

set nocount on

as the first line.

2) Do oRs.NextRecordset in VB until you find your recordset.

I prefer first options.
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 UKAuthor Commented:
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.