[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

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

Posted on 2008-06-23
18
Medium Priority
?
899 Views
Last Modified: 2013-12-25
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")




0
Comment
Question by:dougfosterNYC
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 8
18 Comments
 
LVL 7

Expert Comment

by:ydramu
ID: 21852059
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

0
 
LVL 7

Expert Comment

by:ydramu
ID: 21852070
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
0
 

Author Comment

by:dougfosterNYC
ID: 21855804
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

0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 7

Expert Comment

by:ydramu
ID: 21855868
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.
0
 

Author Comment

by:dougfosterNYC
ID: 21856473
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...
0
 
LVL 7

Expert Comment

by:ydramu
ID: 21856687
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

0
 

Author Comment

by:dougfosterNYC
ID: 21856958
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?
0
 
LVL 7

Expert Comment

by:ydramu
ID: 21857057
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?
0
 
LVL 7

Expert Comment

by:ydramu
ID: 21857139
The error statement says, "that you have more parameters than are defined in the stored procedure"
0
 
LVL 7

Expert Comment

by:ydramu
ID: 21857315
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

0
 
LVL 7

Accepted Solution

by:
ydramu earned 2000 total points
ID: 21857341
Replace these
                     cmd.Parameters[0].Delete
                     cmd.Parameters[1].Delete
                     cmd.Parameters[2].Delete

as below

                     cmd.Parameters.Delete 0
                     cmd.Parameters.Delete 1
                     cmd.Parameters.Delete 2
0
 

Author Comment

by:dougfosterNYC
ID: 21857881
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?
0
 
LVL 7

Expert Comment

by:ydramu
ID: 21857915
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.
0
 

Author Comment

by:dougfosterNYC
ID: 21858211
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....
0
 

Author Comment

by:dougfosterNYC
ID: 21858326
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
0
 

Author Closing Comment

by:dougfosterNYC
ID: 31470030
I am thankful for his quick responses and hanging in through the process.
0
 
LVL 7

Expert Comment

by:ydramu
ID: 21858476
Nice to hear that we solved the issue. But I wonder why we got parameter count of 4. Even we added only 3.
0
 

Author Comment

by:dougfosterNYC
ID: 21858504
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.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Ready to get certified? Check out some courses that help you prepare for third-party exams.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Suggested Courses

656 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