ADO Command Object with Parameters

BHeidecker · March 12th, 2004, 03:59 PM

Does anyone have sample code showing how to create and use parameters with the creation and use of the ADO Command Object? I need to pass a parameter or two with my SQL statement. In the book on page 445 it says âWe wonât cover parameters here.â Itâs not covered anyplace else either.

joefawcett · March 13th, 2004, 05:04 AM

Let's say you have a stored procedure in SQL Server, or saved query in Access called "stp_GetAuthorsBySurname" which accepts a parameter called "Surname" which is a unicode string up to a length of 100. You will need to include the various constants needed for ADO, either by referencing adovbs.inc or defing the constants yourself. Your open connection is called oConn

Code:

Dim oCommand
Set oCommand = CreateObject("Adodb.Command")
Set oCommand.ActiveConnection = oConn
oCommand.CommandText = "stp_GetAuthorsBySurname"
oCommand.CommandType = adCmdStoredProc
Dim oParam
Set prmByRoyalty = CreateObject("ADODB.Parameter")
oParam.Type = adVarWChar
oParam.Size = 100
oParam.Direction = adParamInput
oParam.Value = "F%"
oCommand.Parameters.Append oParam

You are now ready to open a recordset with the command.

A good reference is
http://msdn.microsoft.com/library/de...asp?frame=true

--

Joe

BHeidecker · March 15th, 2004, 03:52 PM

Quote:

quote:Originally posted by joefawcett
Let's say you have a stored procedure in SQL Server, or saved query in Access called "stp_GetAuthorsBySurname" which accepts a parameter called "Surname" which is a unicode string up to a length of 100. You will need to include the various constants needed for ADO, either by referencing adovbs.inc or defing the constants yourself. Your open connection is called oConn

Code:

Dim oCommand
Set oCommand = CreateObject("Adodb.Command")
Set oCommand.ActiveConnection = oConn
oCommand.CommandText = "stp_GetAuthorsBySurname"
oCommand.CommandType = adCmdStoredProc
Dim oParam
Set prmByRoyalty = CreateObject("ADODB.Parameter")
oParam.Type = adVarWChar
oParam.Size = 100
oParam.Direction = adParamInput
oParam.Value = "F%"
oCommand.Parameters.Append oParam

You are now ready to open a recordset with the command.

A good reference is
http://msdn.microsoft.com/library/de...asp?frame=true

--

Joe

BHeidecker · March 15th, 2004, 03:54 PM

Joe,
Thanks for your help.
I got it to work!
Barbara