CODEWITHSUNDEEP
stored-proceduresvbscriptasp-classicadodb
Ian Warburton
Ian Warburton

Reputation: 15676

Can't pass a string to a stored procedure

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.

Upvotes: 1

Views: 637

Answers (2)

user692942
user692942

Reputation: 16672

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

Upvotes: 4

Ian Warburton
Ian Warburton

Reputation: 15676

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

Upvotes: 0

Related Questions