Passing Optional Arguments in Classic ASP for Stored Procedure

Question

I'm trying to run some stored queries in an Access database from an ASP page. I'd like to use an ADO Command object to run the procedure instead of simply sending a string to the database to execute. My trouble is occurring when I try to create the parameters to send to the stored procedure. I'm using the 'CreateParameter' method of the Command object. It takes 5 optional arguments, of which I only want to use two; name and value (arguments 1 and 5 respectively).

I've tried the following approaches to set up the parameters:

1) Using named arguments

command.Parameters.Append command.CreateParameter name:="name", value:="value"

2) Using named arguments inside brackets

command.Parameters.Append command.CreateParameter(name:="name", value:="value")

3) Leaving out optional parameters I don't need

command.Parameters.Append command.CreateParameter("name", , , , "value")

What is a simple way to achieve what I'm trying to do, and why does my syntax fail in these cases? I'm clearly missing something here!

Kul-Tigin Kul-Tigin · Accepted Answer · 2011-09-22T01:39:15

VBScript doesn't support named arguments with or without brackets. All parameters required to pass (specific or blank) for CreateParameter method of the Command object.

For the section 3 in the question, have a look:

CreateParameter(
name`[optional],                              <- fits ("name")
type [optional, default = adEmpty],           <- NOT fits (type is not empty)
direction[Optional default = adParamInput],   <- fits (blank)
size [optional default = 0],                  <- NOT fits (at least 5 for "value")
value[optional]                               <- fits ("value")
)

So, you should specify at least type and size for a text valued parameter. Direction is adParamInput already.

Const adVarChar = 200 ' From adovbs.inc or Ado TypeLib
command.Parameters.Append command.CreateParameter("name", adVarChar, , 5, "value")

Passing Optional Arguments in Classic ASP for Stored Procedure

2 Answers