Tags: access, adcmdtext, ado, adodb, call, commandobject, database, execute, microsoft, mysql, oracle, parameterized, parameters, procedure, query, sql, stored

ADO, adCmdText, parameters

On Database » Microsoft Access

13,260 words with 3 Comments; publish: Mon, 31 Dec 2007 00:31:00 GMT; (250107.42, « »)

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 .ms-access.questionfor.info.PARAMETER1 datetime, .ms-access.questionfor.info.PARAMETER2 datetime,

.ms-access.questionfor.info.PARAMETER3 bit;" & _

"SELECT * FROM blah, blah... " & _

"WHERE something>= .ms-access.questionfor.info.PARAMETER3 AND " & _

"something BETWEEN .ms-access.questionfor.info.PARAMETER1 AND .ms-access.questionfor.info.PARAMETER2"

cmd.CommandType = adCmdText

Set PARAMETER1 = cmd.CreateParameter(".ms-access.questionfor.info.PARAMETER1", adDate, adParamInput)

cmd.Parameters.Append PARAMETER1

PARAMETER1.Value = Format("01.01.2000", "yyyy-mm-dd")

Set PARAMETER2 = cmd.CreateParameter(".ms-access.questionfor.info.PARAMETER2", adDate, adParamInput)

cmd.Parameters.Append PARAMETER2

PARAMETER2.Value = Format("01.01.2007", "yyyy-mm-dd")

Set PARAMETER3 = cmd.CreateParameter(".ms-access.questionfor.info.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

All Comments

Leave a comment...

  • 3 Comments
    • --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:

      > 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 .ms-access.questionfor.info.PARAMETER1 datetime, .ms-access.questionfor.info.PARAMETER2 datetime,

      > .ms-access.questionfor.info.PARAMETER3 bit;" & _

      > "SELECT * FROM blah, blah... " & _

      > "WHERE something>= .ms-access.questionfor.info.PARAMETER3 AND " & _

      > "something BETWEEN .ms-access.questionfor.info.PARAMETER1 AND .ms-access.questionfor.info.PARAMETER2"

      > cmd.CommandType = adCmdText

      > Set PARAMETER1 = cmd.CreateParameter(".ms-access.questionfor.info.PARAMETER1", adDate, adParamInput)

      > cmd.Parameters.Append PARAMETER1

      > PARAMETER1.Value = Format("01.01.2000", "yyyy-mm-dd")

      > Set PARAMETER2 = cmd.CreateParameter(".ms-access.questionfor.info.PARAMETER2", adDate, adParamInput)

      > cmd.Parameters.Append PARAMETER2

      > PARAMETER2.Value = Format("01.01.2007", "yyyy-mm-dd")

      > Set PARAMETER3 = cmd.CreateParameter(".ms-access.questionfor.info.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

      >

      #1; Mon, 31 Dec 2007 00:32:00 GMT
    • 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.CreateParameter("something", adInteger,

      adParamInput)

      cmd.Parameters.Append PARAMETER1

      PARAMETER1.Value = 0

      Set PARAMETER2 = cmd.CreateParameter("someotherthing", adDate,

      adParamInput)

      cmd.Parameters.Append PARAMETER2

      PARAMETER2.Value = <datevariable

      Set PARAMETER3 = cmd.CreateParameter("someotherthing", adDate,

      adParamInput)

      cmd.Parameters.Append PARAMETER3

      PARAMETER3.Value = <someotherdatavariable

      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.ActiveConnection = "Provider=SQLOLEDB.1;Data Source=(local);" &

      > "Integrated Security=SSPI;Initial Catalog=DatabaseName"

      > cmd.CommandText = "DECLARE .ms-access.questionfor.info.PARAMETER1 datetime, .ms-access.questionfor.info.PARAMETER2

      datetime,

      > .ms-access.questionfor.info.PARAMETER3 bit;" & _

      > "SELECT * FROM blah, blah... " & _

      > "WHERE something>= .ms-access.questionfor.info.PARAMETER3 AND " & _

      > "something BETWEEN .ms-access.questionfor.info.PARAMETER1 AND .ms-access.questionfor.info.PARAMETER2"

      > cmd.CommandType = adCmdText

      > Set PARAMETER1 = cmd.CreateParameter(".ms-access.questionfor.info.PARAMETER1", adDate,

      adParamInput)

      > cmd.Parameters.Append PARAMETER1

      > PARAMETER1.Value = Format("01.01.2000", "yyyy-mm-dd")

      > Set PARAMETER2 = cmd.CreateParameter(".ms-access.questionfor.info.PARAMETER2", adDate,

      adParamInput)

      > cmd.Parameters.Append PARAMETER2

      > PARAMETER2.Value = Format("01.01.2007", "yyyy-mm-dd")

      > Set PARAMETER3 = cmd.CreateParameter(".ms-access.questionfor.info.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

      #2; Mon, 31 Dec 2007 00:33:00 GMT
    • An easier way to use the parameters is

      cmd.Parameters(".ms-access.questionfor.info.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.CreateParameter("something", adInteger,

      > adParamInput)

      > cmd.Parameters.Append PARAMETER1

      > PARAMETER1.Value = 0

      > Set PARAMETER2 = cmd.CreateParameter("someotherthing", adDate,

      > adParamInput)

      > cmd.Parameters.Append PARAMETER2

      > PARAMETER2.Value = <datevariable>

      > Set PARAMETER3 = cmd.CreateParameter("someotherthing", adDate,

      > adParamInput)

      > cmd.Parameters.Append PARAMETER3

      > PARAMETER3.Value = <someotherdatavariable>

      >

      > 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.ActiveConnection = "Provider=SQLOLEDB.1;Data Source=(local);" &

      >>"Integrated Security=SSPI;Initial Catalog=DatabaseName"

      >>

      >>cmd.CommandText = "DECLARE .ms-access.questionfor.info.PARAMETER1 datetime, .ms-access.questionfor.info.PARAMETER2

      > datetime,

      >>.ms-access.questionfor.info.PARAMETER3 bit;" & _

      >>"SELECT * FROM blah, blah... " & _

      >>"WHERE something>= .ms-access.questionfor.info.PARAMETER3 AND " & _

      >>"something BETWEEN .ms-access.questionfor.info.PARAMETER1 AND .ms-access.questionfor.info.PARAMETER2"

      >>

      >>cmd.CommandType = adCmdText

      >>

      >>Set PARAMETER1 = cmd.CreateParameter(".ms-access.questionfor.info.PARAMETER1", adDate,

      > adParamInput)

      >>cmd.Parameters.Append PARAMETER1

      >>PARAMETER1.Value = Format("01.01.2000", "yyyy-mm-dd")

      >>

      >>Set PARAMETER2 = cmd.CreateParameter(".ms-access.questionfor.info.PARAMETER2", adDate,

      > adParamInput)

      >>cmd.Parameters.Append PARAMETER2

      >>PARAMETER2.Value = Format("01.01.2007", "yyyy-mm-dd")

      >>

      >>Set PARAMETER3 = cmd.CreateParameter(".ms-access.questionfor.info.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

      >

      #3; Mon, 31 Dec 2007 00:34:00 GMT