Solved

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

Posted on 2008-06-23
18
881 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
  • 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
 
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
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.

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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

760 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

19 Experts available now in Live!

Get 1:1 Help Now