-----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:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBQl68aIechKqOuFEgEQLEfACgp/Rs2dJpB4QP86dLqTTTt68ghDgAniPI
nrj2pBygatiDkYlbMK/aKzSb
=cKPN
-----END PGP SIGNATURE-----
Zlatko Matiæ wrote:[color=blue]
> 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.ActiveConnection = "Provider=SQLOLEDB.1;Data Source=(local);" &
> "Integrated Security=SSPI;Initial Catalog=DatabaseName"
>
> 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.CreateParameter("@PARAMETER1", adDate, adParamInput)
> cmd.Parameters.Append PARAMETER1
> PARAMETER1.Value = Format("01.01.2000", "yyyy-mm-dd")
>
> Set PARAMETER2 = cmd.CreateParameter("@PARAMETER2", adDate, adParamInput)
> cmd.Parameters.Append PARAMETER2
> PARAMETER2.Value = Format("01.01.2007", "yyyy-mm-dd")
>
> Set PARAMETER3 = cmd.CreateParameter("@PARAMETER3", adInteger, adParamInput)
> cmd.Parameters.Append PARAMETER3
> PARAMETER3.Value = 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
>
>
>[/color]
