Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941

    Unanswered: Returning Stored Procedure Results to ADO

    I'm trying to get the results from a SQL Server SPROC into a ADO recordset in Access. I don't have any problem, until I try to use a sproc that has input parameters. When I try that, no recordset is returned. I've tested my sproc with the same parameters in SQL Query Analyzer, and get the correct number of records. I tried eliminating one of my parameters from my open statement, and got 'Procedure accProdFcstAnalysis expects parameter xxx...', which tells me it is calling the right stored procedure. Then I tried changing the procedure name to one that returns a recordset, but does not have input parameters and it works fine.

    Anybody else have an idea?

    Code:
    Private Sub Form_Current()
      Dim rstValues As New Adodb.Recordset, strExec As String
      SetDBCNXN
      rstValues.Open "exec accProdFcstAnalysis 'APT194DM5.2', '11', '4'", DBCNXN
      rstValues.MoveFirst
      
    End Sub
    If I run this as is, it will stop on rstValues.MoveFirst, with:
    "Operation is not allowed when the object is closed"
    telling me it's executing the sproc, but not returning records.
    Inspiration Through Fermentation

  2. #2
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941

    Different Method - Same Result

    I also tried it this way:

    Code:
    Dim cmd As New ADODB.Command, prm As ADODB.Parameter
      Dim rstValues As New ADODB.Recordset, strExec As String
      SetDBCNXN
      With cmd
        .ActiveConnection = DBCNXN
        .CommandText = "accProdFcstAnalysis"
        .CommandType = adCmdStoredProc
        .Parameters.Refresh
      End With
      For Each prm In cmd.Parameters
        Select Case prm.Name
        Case "@strProduct"
          prm.Value = "APT194DM5.2"
        Case "@strMnth_beg"
          prm.Value = "11"
        Case "@strMnth_end"
          prm.Value = "4"
        End Select
      Next
      Set rstValues = cmd.Execute
      
      rstValues.MoveFirst
    ...but still didn't get any records
    Inspiration Through Fermentation

  3. #3
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941

    ...and the answer is

    For anyone that's interested, the problem was in the SPROC.
    You must have

    SET NOCOUNT ON

    in the sproc in order for it to return a recordset using the method(s) above.
    Inspiration Through Fermentation

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •