Can't pass a string to a stored procedure

Question

Here's my stored procedure...

alter PROCEDURE ReplyToEmailConfirmation 
    @uniqueKey varchar(36)
AS
BEGIN

Print 'Hello World!'
END

Here's the code...

Set cmd = Server.CreateObject("ADODB.Command")
With cmd
    .ActiveConnection = getConfigValue("ASPClassicConnectionString")
    .CommandType = adCmdStoredProc
    .CommandText = "[ReplyToEmailConfirmation]"

    .Parameters.Append .CreateParameter("@uniqueKey", adVarChar, adParamInput, 36, "dc8d8bfd-ea3a-4ad9-9f2d-92831eb2655a")

End With

cmd.Execute

Here's the error...

ADODB.Command error '800a0bb9'

Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.

How do I get this to work? The intention is to use adGUID, but I figured I'd try adVarChar to narrow down the error.

seems like you can automatically fill in the parameter info from the stored procedure with `.Parameters.Refresh` https://msdn.microsoft.com/en-us/library/ms676516 — Slai, Nov 08 '16 at 03:05
@Slai don't do that it requires an extra trip to the server to pull the parameter information, not recommended. — user692942, Nov 08 '16 at 09:13
You are missing the `Size` parameter from your `CreateParameter()` method try `.Parameters.Append .CreateParameter("@uniqueKey", adVarChar, adParamInput, 100)`. Also try telling ADODB that you are executing a Stored Procedure by setting `.CommandType = adCmdStoredProc`. — user692942, Nov 08 '16 at 09:15

score 4 · Answer 1 · edited Jun 20 '20 at 09:12

If you read the documentation for CreateParameter() all becomes clear;

If you specify a variable-length data type in the Type argument, you must either pass a Size argument or set the Size property of the Parameter object before appending it to the Parameters collection; otherwise, an error occurs.

As you are passing a VARCHAR which is a "variable-length" data type you have to specify Size when calling CreateParameter().

Dim cmd

Set cmd = Server.CreateObject("ADODB.Command")
With cmd
    'Let the cmd deal with the connection.
    .ActiveConnection = getConfigValue("ASPClassicConnectionString")
    .CommandText = "[ReplyToEmailConfirmation]"
    .CommandType = adCmdStoredProc
    Call .Parameters.Append(.CreateParameter("@uniqueKey", adVarChar, adParamInput, 100))
    .Parameters("@uniqueKey") = "dc8d8bfd-ea3a-4ad9-9f2d-92831eb2655a"
End With
Call cmd.Execute()

'Tidy up memory
Set cmd = Nothing

Also included CommandType of adCmdStoredProc which tells ADO to interpret this command as a Stored Procedure call, without it the default is adCmdUnknown which means ADO has to attempt to workout what the command is, which however small adds an unnecessary overhead.

Also not a big fan of instantiating the ADODB.Connection object just to execute a ADO.Command object which then means you have to manage closing the ADODB.Connection yourself. Instead let the ADODB.Command do it for you, by passing a connection string letting it create the connection and destroy it itself. Assuming getConfigValue("ASPClassicConnectionString") returns a connection string you can pass it directly to ActiveConnection and the ADODB.Command will instantiate the connection and dispose of it.

Useful Links

A: Passing Parameters to a Stored Procedure using ASP (Explains how to use METADATA to include Named Constants)

I'd already tried the size parameter and it didn't work either. I've updated the question. — Ian Warburton, Nov 08 '16 at 12:43
Also, I removed `.CommandType = adCmdStoredProc` because it seemed to be causing the same error. — Ian Warburton, Nov 08 '16 at 13:03
In fact, if I remove the parameter from both ends, setting the command type results in this error. — Ian Warburton, Nov 08 '16 at 13:14
@IanWarburton Yes as you have surmised you need the ADODB Named Constants defining either manually using `Const name = value` or using `METADATA` *(see link in answer)* to import the Type Library for your entire Web Application and never have to define them again. — user692942, Nov 08 '16 at 14:03

Ian Warburton · Accepted Answer · 2016-11-08T13:51:51.213

0

I didn't include adovbs.inc for the adCmdStoredProc, adVarChar and adGUID constants. Doh.

edited Nov 08 '16 at 13:51

answered Nov 08 '16 at 13:29

Ian Warburton

15,170
23
107
189

1

Are you using this in Classic ASP? VBScript doesn't support includes, if you are consider using [METADATA](http://stackoverflow.com/questions/26765838/passing-parameters-to-a-stored-procedure-using-asp/26776169#26776169) approach. – user692942 Nov 08 '16 at 13:46
Yes, Classic ASP. By include, I mean... ``. – Ian Warburton Nov 08 '16 at 13:48
Would be helpful to have the [tag:asp-classic] tag in the question so we understand the context. If I'd realised would have included information about using `METADATA` in my answer. – user692942 Nov 08 '16 at 13:58
That tag is on the question! Or did you add it? – Ian Warburton Nov 08 '16 at 14:10
In which other environment might I be using VBScript and ADO? – Ian Warburton Nov 08 '16 at 14:11
1

There are plenty, VBScript is used as a scripting language for a lot of different hosts. But you can most commonly call it using `cscript.exe` and `wscript.exe` from most Windows OS based computers. Yes I added the tag you can see [the revision here](http://stackoverflow.com/revisions/40477942/6). – user692942 Nov 08 '16 at 14:29

Can't pass a string to a stored procedure

2 Answers2

Useful Links