Output Parameter returning empty string

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • jpatchak
    New Member
    • Oct 2006
    • 76

    Output Parameter returning empty string

    OK, not sure if there is a way around this. I have some relatively complicated SQL code in a stored proc. The proc is working fine. When I execute this code in SSMS, it returns a record set and an output parameter (varchar(50):
    Code:
    Declare @name varchar(50)
    exec pra_StoredProc
    	@id=12,
    	@name=@name OUTPUT
    select @name
    So, I need to access this data from Microsoft Access. I need the proc to return a recordset that I can work with and I also need the output parameter value. However, when I do this in VBA:
    Code:
     
    Dim myrst As New ADODB.Recordset
    Dim mycmd As New ADODB.Command
    Dim mycon As New ADODB.Connection
    
    mycon.open connectionString
    
    With mycmd
        .ActiveConnection = mycon
        .CommandText = "pra_StoredProc"
        .CommandType = adCmdStoredProc
        .Parameters.Append .CreateParameter("@id", adInteger, adParamInput, , 12)
        .Parameters.Append .CreateParameter("@name", adVarChar, adParamOutput, 50)
        Set myrst = .Execute
        MsgBox .Parameters("@name")
    End With
    The msgbox displays an empty string. The problem I know is the line that says:
    Code:
    Set myrst = .Execute
    If I change it to just:
    Code:
    .Execute
    it returns the right value for the output parameter. However, I also need to work with the recordset returned by the proc, which is why I was setting the myrst object reference to mycmd.execute. Is there any way to get both the data in the recordset returned by the stored proc into an ADODB Recordset AND the output parameter's value into a string variable (besides executing the proc twice)?
  • jpatchak
    New Member
    • Oct 2006
    • 76

    #2
    Hmmm, no takers huh? I am just going to execute the proc twice, but this makes me a little worried, as I am trying to write a procedure that can handle all kinds of these procedures and some of them are going to take 15+ seconds to execute (per execution). Not crazy about that.

    Comment

    • MMcCarthy
      Recognized Expert MVP
      • Aug 2006
      • 14387

      #3
      Can you explain more about what value is being returned by the stored proc. Is it just one record and one field and if so why are you putting it in a recordset variable.

      Comment

      • jpatchak
        New Member
        • Oct 2006
        • 76

        #4
        The proc constructs a select statement and executes it. It returns the results of the select along with the scalar value in the output parameter. The last 2 statements would be something like this (the @sql variable contains the text of the select statement):
        Code:
        @name = 'The title of a graph'
        exec(@sql)
        The purpose of the vb code is to take the dataset returned by the proc and dump it into an excel sheet (nicely formatted) and automatically generate a corresponding chart. I want to be able to use this same code for all sorts of these same kind of procs - the dataset becoming the data for the excel range and the scalar value from the output parameter becoming the title of the graph.

        Comment

        • MMcCarthy
          Recognized Expert MVP
          • Aug 2006
          • 14387

          #5
          OK this is not the way I would have done it but I've done some research and this appears to be how it works.

          Code:
          Dim myCon As ADODB.Connection  
          Dim myCmd As ADODB.Command 
          Dim myPara1 As ADODB.Parameter
          Dim myPara2 As ADODB.Parameter
          Dim myRst As ADODB.Recordset 
          
              Set myCon = New ADODB.Connection
              Set myCmd = New ADODB.Command 
           
              myCon.ConnectionString = "connectionString"
              myCon.Open myCon.ConnectionString
          
              With mycmd
                  .ActiveConnection = myCon.ConnectionString
                  .CommandText = "pra_StoredProc" 
                  .CommandType = adCmdStoredProc 
              End With
          
              'Create 2 output parameters
              Set myPara1 = myCmd.CreateParameter("id", adInteger, adParamOutput, , 12)
              Set mypara2 = myCmd.CreateParameter("name", adVarChar, adParamOutput, 50)
          
              'Append the output parameters to command object
              myCmd.Parameters.Append myPara1
              myCmd.Parameters.Append mypara2
          
              Set myRst = myCmd.Execute 
          
              'Add your code here
          
              'close the objects
              myRst.Close
              myCon.Close
            
              'reset the objects
              Set myCmd = Nothing
              Set myCon = Nothing
              Set myPara1 = Nothing
              Set mypara2 = Nothing
              Set myRst = Nothing
          Last edited by MMcCarthy; Oct 13 '10, 01:52 AM.

          Comment

          Working...