ADO, adCmdText, parameters

[Zlatko Matiæ]

Hello.

I know how to call a parameterized stored procedure by using ADODB command
object and parameters,
but how can I execute the same query using adCmdText instead of
adCmdStoredProc ?
Namely I created the CommandText string with declare statement for
parameters before the select statement. No error is displayed, but also, no
records are shown iether. It seems that the server can't recognize
parameters. The select statement is exactly the same as it was in the stored
procedure...
The code is somethingh like this:

Option Compare Database
Private Sub BLABLA()

Dim cmd As ADODB.Command
Dim par As ADODB.Parameter
Dim fld As ADODB.Field
Dim rs As ADODB.Recordset
Dim str As String

Dim PARAMETER1 As New ADODB.Parameter
Dim PARAMETER2 As New ADODB.Parameter
Dim PARAMETER3 As New ADODB.Parameter

Dim rst As ADODB.Recordset

Set cmd = New ADODB.Command

cmd.ActiveConne ction = "Provider=SQLOL EDB.1;Data Source=(local); " &
"Integrated Security=SSPI;I nitial Catalog=Databas eName"

cmd.CommandText = "DECLARE @PARAMETER1 datetime, @PARAMETER2 datetime,
@PARAMETER3 bit;" & _
"SELECT * FROM blah, blah.... " & _
"WHERE something>= @PARAMETER3 AND " & _
"something BETWEEN @PARAMETER1 AND @PARAMETER2"

cmd.CommandType = adCmdText

Set PARAMETER1 = cmd.CreateParam eter("@PARAMETE R1", adDate, adParamInput)
cmd.Parameters. Append PARAMETER1
PARAMETER1.Valu e = Format("01.01.2 000", "yyyy-mm-dd")

Set PARAMETER2 = cmd.CreateParam eter("@PARAMETE R2", adDate, adParamInput)
cmd.Parameters. Append PARAMETER2
PARAMETER2.Valu e = Format("01.01.2 007", "yyyy-mm-dd")

Set PARAMETER3 = cmd.CreateParam eter("@PARAMETE R3", adInteger, adParamInput)
cmd.Parameters. Append PARAMETER3
PARAMETER3.Valu e = 0

Set rst = New ADODB.Recordset
Set rst = cmd.Execute()

Do Until rst.EOF
str = ""
For Each fld In rst.Fields
str = str & fld.Value & Chr(9)

Next fld
Debug.Print str
rst.MoveNext
Loop

Set rst = Nothing
Set par = Nothing
Set cmd = Nothing
Set PARAMETER1 = Nothing
Set PARAMETER2 = Nothing
Set PARAMETER3 = Nothing
Exit Sub

End Sub

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I believe ADO parameters are ONLY for stored procedures. If you're
creating the SQL statement in VBA you can insert the parameter values in
the SQL string w/ concatenation:

param1 = "'2000-01-01'"
param2 = "'2007-01-01'"
param3 = 0
cmd.CommandText = "SELECT * FROM blah, blah.... " & _
"WHERE something>= " & param3 & " AND " & _
"something BETWEEN " & param1 & " AND " & param2

When I pass datetime values to SQL I put them in single quotes; SQL
automatically converts them to datetime data types. Also, I use the ISO
format, which is CCYY-MM-DD; where CC is the century number, YY the year
of the century, MM the month number, and DD the day of the month.
--
MGFoster:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQl68aIechKq OuFEgEQLEfACgp/Rs2dJpB4QP86dLq TTTt68ghDgAniPI
nrj2pBygatiDkYl bMK/aKzSb
=cKPN
-----END PGP SIGNATURE-----

Zlatko Matiæ wrote:

Hello.

I know how to call a parameterized stored procedure by using ADODB command
object and parameters,
but how can I execute the same query using adCmdText instead of
adCmdStoredProc ?
Namely I created the CommandText string with declare statement for
parameters before the select statement. No error is displayed, but also, no
records are shown iether. It seems that the server can't recognize
parameters. The select statement is exactly the same as it was in the stored
procedure...
The code is somethingh like this:

Option Compare Database
Private Sub BLABLA()

Dim cmd As ADODB.Command
Dim par As ADODB.Parameter
Dim fld As ADODB.Field
Dim rs As ADODB.Recordset
Dim str As String

Dim PARAMETER1 As New ADODB.Parameter
Dim PARAMETER2 As New ADODB.Parameter
Dim PARAMETER3 As New ADODB.Parameter

Dim rst As ADODB.Recordset

Set cmd = New ADODB.Command

cmd.ActiveConne ction = "Provider=SQLOL EDB.1;Data Source=(local); " &
"Integrated Security=SSPI;I nitial Catalog=Databas eName"

cmd.CommandText = "DECLARE @PARAMETER1 datetime, @PARAMETER2 datetime,
@PARAMETER3 bit;" & _
"SELECT * FROM blah, blah.... " & _
"WHERE something>= @PARAMETER3 AND " & _
"something BETWEEN @PARAMETER1 AND @PARAMETER2"

cmd.CommandType = adCmdText

Set PARAMETER1 = cmd.CreateParam eter("@PARAMETE R1", adDate, adParamInput)
cmd.Parameters. Append PARAMETER1
PARAMETER1.Valu e = Format("01.01.2 000", "yyyy-mm-dd")

Set PARAMETER2 = cmd.CreateParam eter("@PARAMETE R2", adDate, adParamInput)
cmd.Parameters. Append PARAMETER2
PARAMETER2.Valu e = Format("01.01.2 007", "yyyy-mm-dd")

Set PARAMETER3 = cmd.CreateParam eter("@PARAMETE R3", adInteger, adParamInput)
cmd.Parameters. Append PARAMETER3
PARAMETER3.Valu e = 0

Set rst = New ADODB.Recordset
Set rst = cmd.Execute()

Do Until rst.EOF
str = ""
For Each fld In rst.Fields
str = str & fld.Value & Chr(9)

Next fld
Debug.Print str
rst.MoveNext
Loop

Set rst = Nothing
Set par = Nothing
Set cmd = Nothing
Set PARAMETER1 = Nothing
Set PARAMETER2 = Nothing
Set PARAMETER3 = Nothing
Exit Sub

End Sub

[RoyVidar]

Hi!

I think you ou should be able to do something like this:

cmd.CommandText "SELECT * FROM blah, blah.... " & _
"WHERE something>= ? AND " & _
"something BETWEEN ? AND ?"

cmd.CommandType = adCmdText

Then supply the parameters in the same order

Set PARAMETER1 = cmd.CreateParam eter("something ", adInteger,
adParamInput)
cmd.Parameters. Append PARAMETER1
PARAMETER1.Valu e = 0

Set PARAMETER2 = cmd.CreateParam eter("someother thing", adDate,
adParamInput)
cmd.Parameters. Append PARAMETER2
PARAMETER2.Valu e = <datevariable >

Set PARAMETER3 = cmd.CreateParam eter("someother thing", adDate,
adParamInput)
cmd.Parameters. Append PARAMETER3
PARAMETER3.Valu e = <someotherdatav ariable>
Zlatko Matiæ wrote:

Hello.

I know how to call a parameterized stored procedure by using ADODB command object and parameters,
but how can I execute the same query using adCmdText instead of
adCmdStoredProc ?
Namely I created the CommandText string with declare statement for
parameters before the select statement. No error is displayed, but also, no records are shown iether. It seems that the server can't recognize
parameters. The select statement is exactly the same as it was in the stored procedure...
The code is somethingh like this:

Option Compare Database
Private Sub BLABLA()

Dim cmd As ADODB.Command
Dim par As ADODB.Parameter
Dim fld As ADODB.Field
Dim rs As ADODB.Recordset
Dim str As String

Dim PARAMETER1 As New ADODB.Parameter
Dim PARAMETER2 As New ADODB.Parameter
Dim PARAMETER3 As New ADODB.Parameter

Dim rst As ADODB.Recordset

Set cmd = New ADODB.Command

cmd.ActiveConne ction = "Provider=SQLOL EDB.1;Data Source=(local); " &
"Integrated Security=SSPI;I nitial Catalog=Databas eName"

cmd.CommandText = "DECLARE @PARAMETER1 datetime, @PARAMETER2 datetime, @PARAMETER3 bit;" & _
"SELECT * FROM blah, blah.... " & _
"WHERE something>= @PARAMETER3 AND " & _
"something BETWEEN @PARAMETER1 AND @PARAMETER2"

cmd.CommandType = adCmdText

Set PARAMETER1 = cmd.CreateParam eter("@PARAMETE R1", adDate, adParamInput) cmd.Parameters. Append PARAMETER1
PARAMETER1.Valu e = Format("01.01.2 000", "yyyy-mm-dd")

Set PARAMETER2 = cmd.CreateParam eter("@PARAMETE R2", adDate, adParamInput) cmd.Parameters. Append PARAMETER2
PARAMETER2.Valu e = Format("01.01.2 007", "yyyy-mm-dd")

Set PARAMETER3 = cmd.CreateParam eter("@PARAMETE R3", adInteger, adParamInput) cmd.Parameters. Append PARAMETER3
PARAMETER3.Valu e = 0

Set rst = New ADODB.Recordset
Set rst = cmd.Execute()

Do Until rst.EOF
str = ""
For Each fld In rst.Fields
str = str & fld.Value & Chr(9)

Next fld
Debug.Print str
rst.MoveNext
Loop

Set rst = Nothing
Set par = Nothing
Set cmd = Nothing
Set PARAMETER1 = Nothing
Set PARAMETER2 = Nothing
Set PARAMETER3 = Nothing


Exit Sub

End Sub

[Ol!v!é]

An easier way to use the parameters is
cmd.Parameters( "@something ") = thevalue

RoyVidar wrote:

Hi!

I think you ou should be able to do something like this:

cmd.CommandText "SELECT * FROM blah, blah.... " & _
"WHERE something>= ? AND " & _
"something BETWEEN ? AND ?"

cmd.CommandType = adCmdText

Then supply the parameters in the same order

Set PARAMETER1 = cmd.CreateParam eter("something ", adInteger,
adParamInput)
cmd.Parameters. Append PARAMETER1
PARAMETER1.Valu e = 0

Set PARAMETER2 = cmd.CreateParam eter("someother thing", adDate,
adParamInput)
cmd.Parameters. Append PARAMETER2
PARAMETER2.Valu e = <datevariable >

Set PARAMETER3 = cmd.CreateParam eter("someother thing", adDate,
adParamInput)
cmd.Parameters. Append PARAMETER3
PARAMETER3.Valu e = <someotherdatav ariable>
Zlatko Matiæ wrote:

Hello.

I know how to call a parameterized stored procedure by using ADODB

command

object and parameters,
but how can I execute the same query using adCmdText instead of
adCmdStoredPr oc?
Namely I created the CommandText string with declare statement for
parameters before the select statement. No error is displayed, but

also, no

records are shown iether. It seems that the server can't recognize
parameters. The select statement is exactly the same as it was in the

stored

procedure.. .
The code is somethingh like this:

Option Compare Database
Private Sub BLABLA()

Dim cmd As ADODB.Command
Dim par As ADODB.Parameter
Dim fld As ADODB.Field
Dim rs As ADODB.Recordset
Dim str As String

Dim PARAMETER1 As New ADODB.Parameter
Dim PARAMETER2 As New ADODB.Parameter
Dim PARAMETER3 As New ADODB.Parameter

Dim rst As ADODB.Recordset

Set cmd = New ADODB.Command

cmd.ActiveCon nection = "Provider=SQLOL EDB.1;Data Source=(local); " &
"Integrated Security=SSPI;I nitial Catalog=Databas eName"

cmd.CommandTe xt = "DECLARE @PARAMETER1 datetime, @PARAMETER2

datetime,

@PARAMETER3 bit;" & _
"SELECT * FROM blah, blah.... " & _
"WHERE something>= @PARAMETER3 AND " & _
"something BETWEEN @PARAMETER1 AND @PARAMETER2"

cmd.CommandTy pe = adCmdText

Set PARAMETER1 = cmd.CreateParam eter("@PARAMETE R1", adDate,

adParamInput)

cmd.Parameter s.Append PARAMETER1
PARAMETER1.Va lue = Format("01.01.2 000", "yyyy-mm-dd")

Set PARAMETER2 = cmd.CreateParam eter("@PARAMETE R2", adDate,

adParamInput)

cmd.Parameter s.Append PARAMETER2
PARAMETER2.Va lue = Format("01.01.2 007", "yyyy-mm-dd")

Set PARAMETER3 = cmd.CreateParam eter("@PARAMETE R3", adInteger,

adParamInput)

cmd.Parameter s.Append PARAMETER3
PARAMETER3.Va lue = 0

Set rst = New ADODB.Recordset
Set rst = cmd.Execute()

Do Until rst.EOF
str = ""
For Each fld In rst.Fields
str = str & fld.Value & Chr(9)

Next fld
Debug.Print str
rst.MoveNext
Loop

Set rst = Nothing
Set par = Nothing
Set cmd = Nothing
Set PARAMETER1 = Nothing
Set PARAMETER2 = Nothing
Set PARAMETER3 = Nothing
Exit Sub

End Sub