The ADO Parameter object is a parameter passed along with a command to a database.

Basic Usage

The Parameter collection is the default member of the Command object. EG: These two examples are equivalent:

cmdObject.Paramters.Item(0)
cmdObject(0)

Here's how you might set the properties of a Parameter object in VB:

strName = "Hernandez"
Set par1 = Server.CreateObject("ADODB.Parameter")
par1.Name = "pstrName"
par1.Direction = adParamInput
par1.Type = adVarChar
par1.Size = Len(strName)
par1.Value = strName

For an example in JavaScript see Command.

Setting Parameter.Direction is fairly trivial (usually 1 for in and 2 for out). The tricky part is setting Parameter.Type and Parameter.Size. A great example can be found at "A example of how to retrieve multiple recordsets from ADO and how to set parameters in ADO for SQL Server Stored Procedures" [http://www.davidpenton.com/testsite/scratch/adodb.command3.asp]. I've taken the code on that page that and added the numeric values of each DataTypeEnum.

.Parameters.Append cmd.CreateParameter("RETURN", adInteger, adParamReturnValue, 4) 'adInteger=3

.Parameters.Append .CreateParameter("@myBigInt", adBigInt, adParamInput, 8, 996857543543543) 'adBigInt=20
.Parameters.Append .CreateParameter("@myInt", adInteger, adParamInput, 4, 543543) 'adInteger=3
.Parameters.Append .CreateParameter("@mySmallint", adSmallInt, adParamInput, 2, 32765) 'adSmallInt=2
.Parameters.Append .CreateParameter("@myTinyint", adTinyInt, adParamInput, 1, 254) 'adTinyInt=16
.Parameters.Append .CreateParameter("@myBit", adBoolean, adParamInput, 4, True) 'adBoolean=11

' Only Decimal and Numeric needs Precision and NumericScale
.Parameters.Append .CreateParameter("@myDecimal", adDecimal, adParamInput, 9, 765.5432321) 'adDecimal=14
With .Parameters.Item("@myDecimal")
    .Precision = 10
    .NumericScale = 7
End With

Set prm = .CreateParameter("@myNumeric", adNumeric, adParamInput, 5, 432.6544) 'adNumeric=131
prm.Precision = 7
prm.NumericScale = 4
.Parameters.Append prm
Set prm = Nothing

.Parameters.Append .CreateParameter("@myMoney", adCurrency, adParamInput, 8, 543.1234) 'adCurrency=6
.Parameters.Append .CreateParameter("@mySmallMoney", adCurrency, adParamInput, 4, 543.1234) 'adCurrency=6

.Parameters.Append .CreateParameter("@myFloat", adDouble, adParamInput, 8, 5.4E+54) 'adDouble=5
.Parameters.Append .CreateParameter("@myReal", adSingle, adParamInput, 4, 2.43E+24) 'adSingle=4

.Parameters.Append .CreateParameter("@myDatetime", adDBTimeStamp, adParamInput, 8, datetime) 'adDBTimeStamp=135
.Parameters.Append .CreateParameter("@mySmallDatetime", adDBTimeStamp, adParamInput, 4, datetime) 'adDBTimeStamp=135

.Parameters.Append .CreateParameter("@myChar", adChar, adParamInput, 4, "QWE") 'adChar=129
.Parameters.Append .CreateParameter("@myVarchar", adVarchar, adParamInput, 10, "Variable!") 'adVarchar=200

.Parameters.Append .CreateParameter("@myText", adLongVarChar, adParamInput, Len(titleString)) 'adLongVarChar=201
.Parameters.Item("@myText").AppendChunk titleString

.Parameters.Append .CreateParameter("@myNChar", adWChar, adParamInput, 4, "WIDE") 'adWChar=130
.Parameters.Append .CreateParameter("@myNVarchar", adVarWchar, adParamInput, 10, "") 'adVarWchar=202

.Parameters.Append .CreateParameter("@myNText", adLongVarWChar, adParamInput, Len(titleString)) 'adLongVarWChar=201
.Parameters.Item("@myNText").AppendChunk titleString

' note the difference in these - without the {} the string implicitly converts
' the adVarChar version is of course commented out
'.Parameters.Append .CreateParameter("@myGuid", adVarChar, adParamInput, 36, "58F94A80-B839-4B35-B73C-7F4B4D336C3C")
.Parameters.Append .CreateParameter("@myGuid", adGUID, adParamInput, 16, "{58F94A80-B839-4B35-B73C-7F4B4D336C3C}") 'adGUID=72

For the possible values of the DataTypeEnum, see Data Types and, of course, look it up at MSDN too.

For the max datatypes like varchar(max), set Parameter.Size = -1. This is not mentioned in the documentation! EG:

//In JavaScript:
var parPost = cmd.CreateParameter("Post", 200, 1, -1, Post);
cmd.Parameters.Append(parPost);

Properties, Methods, and Events

Properties Methods Events
  1. Attibutes
  2. Direction. The possible values are the ParameterDirectionEnum:
    • adParamInput. 1
    • adParamInputOutput. 3
    • adParamOutput. 2
    • adParamReturnValue. 4
    • adParamUnknown. 0
  3. Name
  4. NumericScale
  5. Precision
  6. Properties
  7. Size
  8. Type
  9. Value
  1. AppendChunk
None


GeorgeHernandez.comSome rights reserved