Using Access VBA to get varchar OUTPUT parameter from SQL Server stored procedure

Question

I have the following stored procedure in SQL server and can call it just fine from within SQL Server:

CREATE PROCEDURE uspRecipeNote
    @IngString varchar(255) OUTPUT,
    @MixID int = NULL
AS 
    SET NOCOUNT ON;
    SET @IngString = dbo.ufnRecipeNote(@MixID);

I have been using VBA in Access to call stored procedures for months and have never had a problem, but have tried several possibilities and am stumped, getting either of the following errors, depending upon what I have tried:

"Parameter object is improperly defined. Inconsistent or incomplete information was provided." "Procedure or function uspRecipeNote has too many arguments specified."

Here is what I have at this point in VBA:

Set cmd.ActiveConnection = cn
cmd.CommandTimeout = 0
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "uspRecipeNote"

'add parameters
cmd.Parameters.Append cmd.CreateParameter("@IngString", adVarChar, adParamOutput)
cmd.Parameters.Append cmd.CreateParameter("@MixID", adInteger, adParamInput, , intMixID)

cmd.Execute

I get an error on the first parameter no matter what I have tried. I suspect it is something simple. Thanks in advance for any help!

Have you tried putting the output parameter last instead of first in the SP? — alas
– alas, Commented Apr 1, 2016 at 16:45
Yes, that is my typical practice, but I switched them as one of my attempts to make it work. — Toni
– Toni, Commented Apr 1, 2016 at 16:51
When I type in what you have above, VBA automatically inserts a ";" as follows: Debug.Print cn.Execute; "uspRecipeNote " & intMixID — Toni
– Toni, Commented Apr 1, 2016 at 17:02
No problem. I have tried several options using parentheses, to no avail. When I try what you have listed above, I get "Procedure or function 'uspRecipeNote' expects parameter '@MixID', which was not supplied." — Toni
– Toni, Commented Apr 1, 2016 at 17:27
Also you can use Execute instead ADODB.Commands and put into record set Dim rs as New ADODB.Recordset and then rs = connection_name.Execute ("EXEC uspRecipeNote @IngString OUTPUT, " & intMixID & ";"). — gofr1
– gofr1, Commented Apr 1, 2016 at 17:53

Gord Thompson · Accepted Answer · 2016-04-01 17:00:23Z

5

String parameters need to be defined with a (maximum) length, e.g.,

cmd.Parameters.Append cmd.CreateParameter("@IngString", adVarChar, adParamOutput, 255)

answered Apr 1, 2016 at 17:00

Gord Thompson

125k38 gold badges251 silver badges458 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Toni Over a year ago

Thank you, but when I have tried that, I get the error: "Procedure or function uspRecipeNote has too many arguments specified." Does this provide a clue?

Toni Over a year ago

It did get past adding the parameter, but now fails at "cmd.Execute" with the error just above.

Gord Thompson Over a year ago

I dunno, I just tried it (code here) and it works for me. Is it possible that somewhere along the line @IngString got mixed up with @lngString? Or maybe there are multiple versions of uspRecipeNote under different schemas?

Toni Over a year ago

Thank you so much. It was a problem with my command object, which I was reusing, as I have many times before. This time I failed to clear it properly, which explains some of the odd behavior, like "too many arguments." Oh, the joys...

Gord Thompson Over a year ago

Ah, okay. Reusing ADODB.Command objects is not a recommended practice as strangeness may ensue, and not just for that reason (as I recall).

Toni · Accepted Answer · 2016-04-01 19:36:19Z

0

Thanks to all who helped try to solve my problem. It turns out that it was a problem with my command object, not the SP or the VBA (except for needing the length for the varchar parameter - thanks, Gord). Everything works just fine now!

answered Apr 1, 2016 at 19:36

Toni

435 bronze badges

Collectives™ on Stack Overflow

Using Access VBA to get varchar OUTPUT parameter from SQL Server stored procedure

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related