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

Posted on 2007-07-20
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

    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

    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
    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

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Join & Write a Comment

    Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
    You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
    Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
    Viewers will learn how the fundamental information of how to create a table.

    734 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

    Need Help in Real-Time?

    Connect with top rated Experts

    24 Experts available now in Live!

    Get 1:1 Help Now