asked on

ado command object to sql server stored procedure issue for output variable

I'm trying to use a stored procedure in sql server 2000 that returns one record and get it in vba in Excel. I am using ADO in Excel VBA.

I've gotten several errors as I play around with it, thinking it is the order of the parameters. Now it says the procedure has "too many arguments specified". I've never tried an output variable character based, which may be an issue.

The usp is as follows:
ALTER Procedure dbo.usp_getDescFromSurvW2LU (@fieldnameInput varchar(50), @codeInput int,
@descriptionOutput varchar(100) output)
AS
SELECT @descriptionOutput = description
FROM dbo.SurvW2_LU
WHERE (FieldName = @fieldnameInput) AND (code = @codeInput)

the relavent VBA code is:
Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
cmd.ActiveConnection = goConn
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "usp_getDescFromSurvW2LU"

cmd.Parameters.Append cmd.CreateParameter("fieldNameInput", adVarChar, adParamInput, 50, .Cells(1, i).Value)
cmd.Parameters.Append cmd.CreateParameter("codeInput", adVarChar, adParamInput, 6, .Cells(iRow, i).Value)
cmd.Parameters.Append cmd.CreateParameter("descriptionOutput", adVarChar, adParamReturnValue, 100)

cmd.Execute

oWS.Cells(iRow, i).Value = cmd.Parameters("descriptionOutput")

ydramu

Could you try with this once

Dim cmd As ADODB.Command
    Set cmd = New ADODB.Command
        cmd.ActiveConnection = goConn
        cmd.CommandType = adCmdStoredProc
        cmd.CommandText = "usp_getDescFromSurvW2LU"
 
       
                    cmd.Parameters.Append cmd.CreateParameter("@fieldNameInput", adVarChar, adParamInput, 50, .Cells(1, i).Value)
                    cmd.Parameters.Append cmd.CreateParameter("@codeInput", adVarChar, adParamInput, 6, .Cells(iRow, i).Value)
                    cmd.Parameters.Append cmd.CreateParameter("@descriptionOutput", adVarChar, adParamReturnValue, 100)
                   
                    cmd.Execute
                   
                    oWS.Cells(iRow, i).Value = cmd.Parameters("@descriptionOutput")

Open in new window

ydramu

Sorry I missed changing this line.

cmd.Parameters.Append cmd.CreateParameter("@descriptionOutput", adVarChar, adParamOutput, 100)

In your stored procedure, you are returning value as output parameter, not as return value from funciton. so use adParamOutput instead of adParamReturnValue

Doug Foster

ASKER

Thanks ydramu. That helped, but unfortunately didn't solve my other problem, which I'm still trying to figure out what it is. My original code actually worked once, but then gave an error on the second time in the loop because I was appending the parameter, which I should only do once.

I revamped my code so it is more correct, where I append it, but still am getting the "procedure has too many arguments specified" error.

My code is attached. I'm not sure how to handle the output variable, which I believe is the problem. The sp is in my first posting and hasn't changed.

        With cmd
            .ActiveConnection = goConn
            .CommandType = adCmdStoredProc
            .CommandText = "usp_getDescFromSurvW2LU"
            Set oParam1 = New ADODB.Parameter
            oParam1.Direction = adParamInput
            oParam1.Type = adVarChar
            oParam1.Size = 50
            Set oParam2 = New ADODB.Parameter
            oParam2.Direction = adParamInput
            oParam2.Type = adInteger
            Set oParam3 = New ADODB.Parameter
            oParam3.Direction = adParamOutput
            oParam3.Type = adVarChar
            oParam3.Size = 100
            
            .Parameters.Append oParam1
            .Parameters.Append oParam2
            .Parameters.Append oParam3
        End With
        
        For iRow = 4 To iLastRow
            If IsNumeric(.Cells(iRow, i).Value) Then
                If isSurvField(.Cells(1, i).Value) Then
                    oParam1.Value = .Cells(1, i).Value
                    oParam2.Value = .Cells(iRow, i).Value
                    'cmd.Parameters.Append cmd.CreateParameter("descriptionOutput", adVarChar, adParamOutput, 100)
                    
                    cmd.Execute
                    
                    'get output from USP
                    .Cells(iRow, i).Value = oParam3.Value
                
                    '.Cells(iRow, i).Value = getSurvCode(.Cells(1, i).Value, .Cells(iRow, i).Value)
                End If
            End If
        Next iRow

Open in new window

ydramu

I feel, you just commented your Output parameter in this code for the post purpose here.

And also, this output parameter adding line also should be with the parameter append statements, which you did with using "With cmd" code block.

We just need to add this as Output parameter to the command once, no need to add that every time we execute it in the loop.

Doug Foster

ASKER

I have that output variable commented out, so that's not an issue. But I'm still confused on how the sp variables work.

I have two input variables and an output variable in the sp. I think the problem has to do with how the param objects are connected with the sp.

I am creating 3 parameter objects and appending them. The error says that the sp has "too many arguments specified" when I execute the command object.

One question: I have to append the parameters in the same order as the sp, but is that in the order the sp dimensions the variables or that they are in the statement? The output variable is dimensioned last but the first in the sp statement. Maybe that is the problem...

ydramu

Could you try this once.

With cmd
            .ActiveConnection = goConn
            .CommandType = adCmdStoredProc
            .CommandText = "usp_getDescFromSurvW2LU"
	    Set oParam1 = .CreateParameter("@fieldnameInput", _
      			adVarChar, adParamInput, 50)
            
            Set oParam2 = .CreateParameter("@codeInput", _
      			adInteger, adParamInput)
 
            Set oParam3 = .CreateParameter("@descriptionOutput", _
      			adVarChar, adParamOutput, 100)
 
              
            .Parameters.Append oParam1
            .Parameters.Append oParam2
            .Parameters.Append oParam3
 
End With

Open in new window

Doug Foster

ASKER

I basically set all those properties for the parameter objects, except for the name, which i don't think is necessary. I set a name for the objects and that didn't make a difference.

Once again, the error is saying that the stored procedure has too many arguments specified. What does that mean and what could be wrong?

ydramu

I have a quick question, is this error you are getting at the first iteration of the loop itself or after completion of executing the stored procedure successfully, is the second iteration getting this error?

ydramu

The error statement says, "that you have more parameters than are defined in the stored procedure"

ydramu

This is the issue you are facing.

http://support.microsoft.com/kb/178038

Based on this, I would say we may have to write this way our code as below.

      With cmd
            .ActiveConnection = goConn
            .CommandType = adCmdStoredProc
            .CommandText = "usp_getDescFromSurvW2LU"
      End With
        
        For iRow = 4 To iLastRow
            If IsNumeric(.Cells(iRow, i).Value) Then
                If isSurvField(.Cells(1, i).Value) Then
                   Set oParam1 = New ADODB.Parameter
                   oParam1.Direction = adParamInput
                   oParam1.Type = adVarChar
                   oParam1.Size = 50
                   Set oParam2 = New ADODB.Parameter
                   oParam2.Direction = adParamInput
                   oParam2.Type = adInteger
                   Set oParam3 = New ADODB.Parameter
                   oParam3.Direction = adParamOutput
                   oParam3.Type = adVarChar
                   oParam3.Size = 100
            
                    cmd.Parameters.Append oParam1
                    cmd.Parameters.Append oParam2
                    cmd.Parameters.Append oParam3
                   
                    oParam1.Value = .Cells(1, i).Value
                    oParam2.Value = .Cells(iRow, i).Value
                    'cmd.Parameters.Append cmd.CreateParameter("descriptionOutput", adVarChar, adParamOutput, 100)
                    
                    cmd.Execute
                 
                     cmd.Parameters[0].Delete
                     cmd.Parameters[1].Delete
                     cmd.Parameters[2].Delete
                    
                    'get output from USP
                    .Cells(iRow, i).Value = oParam3.Value
                
                    '.Cells(iRow, i).Value = getSurvCode(.Cells(1, i).Value, .Cells(iRow, i).Value)
                End If
            End If
        Next iRow

Open in new window

ASKER CERTIFIED SOLUTION

ydramu

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Doug Foster

ASKER

ydramu, thanks for haning in there.

I think we're almost there. it works once now, but after I run the usp, and goes into delete, it can't delete the last parameter, the output one, #2. It says "item cannot be foudn in the collection corresponding ot the requested name or ordinal". I also try it with its name but it gives me the same error. It's weird, I have created it and it runs once, how come I cant delete it?

ydramu

It may be just we need to delete only the input parameters. Just only delete input parameters and try to continue with rest of other stuff.

Doug Foster

ASKER

I should have mentioned that I tried that, and when i rem that out and don't delete it, it says that the sp has too many arguments specified again....

It is something small that I'm missing but it's driving me crazy....

Doug Foster

ASKER

I figured it out!!! Thanks GOD.

Ok, I don't know what the deal is, but there seems to be another parameter in there, so what I did was add the loop below to delete the parameters (and there is a parameter count of 4). THen it clears all the parameters and starts over.

Thank you ydramu for getting me here.....

For j = 1 To cmd.Parameters.Count
cmd.Parameters.Delete (0)
Next j

Doug Foster

ASKER

I am thankful for his quick responses and hanging in through the process.

ydramu

Nice to hear that we solved the issue. But I wonder why we got parameter count of 4. Even we added only 3.

Doug Foster

ASKER

No Idea. If you have an idea on figuring it out, that would be great, but now that I got it working, I'm willing to let that dog sleep.