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

Posted on 2007-07-20
Medium Priority
Last Modified: 2013-12-25
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?
Question by:ManSys UK
LVL 58

Accepted Solution

amit_g earned 2000 total points
ID: 19535296
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.
LVL 14

Expert Comment

ID: 19536140
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

Author Comment

by:ManSys UK
ID: 19537987
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.

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question