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 3 23709
-----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 @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
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 @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
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.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 @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
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Trey Guy |
last post by:
I have an ASP page I use as a front end to an Access db. I am running a
query from that page that is returning a "Too few parameters..." error. The
query works fine in Access. I have checked all...
|
by: Johnd |
last post by:
I am running into an issue when I try to write more than 1024 characters to
a memo field. Apparantly the odbc connection I am using does not permit
literals to be larger that 1024 characters. ...
|
by: Bruno Alexandre |
last post by:
Hi guys,
withou using SP, I want to be able to add a Parameter to the SQL Query and
retrive the Recordset so I can use the Paging property under the recorset
object.... how can I do this?
I'm...
|
by: Jenni |
last post by:
Hi, my simple solution is not so simple. I have done a bit of VB
programming, and am now tasked with trying to make access work using
VBA, and battling my butt off. This probably has a really...
|
by: CSDunn |
last post by:
Hello,
I have a situation in which I need to address three SQL Server 2000
Stored Procedure parameters in the OnClick event of an Option Group.
The Option Group lives on an Access 2000 ADP form.
...
|
by: Zlatko Matić |
last post by:
Let's assume that we have a database on some SQL server (let it be MS SQL
Server) and that we want to execute some parameterized query as a
pass.through query. How can we pass parameters to the...
|
by: javediq143 |
last post by:
Hi all,
I got one more prob in another ASP page linked to the earlier one. I've created an Access DB and created a query using Access Query wizard. The query is running fine in Access and it is...
|
by: Dave |
last post by:
I am getting the error above intermittantly with an ASP 3.0 page using an MS
Access 2003 database.
I have searched Google extensively and found the following possible causes
for this error:
A...
|
by: ravysters |
last post by:
hi... i am using VC++ to connect to my database and i am trying to insert a new row into a table. I am also using the returning statement available in postgres to return a column of the currently...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: ryjfgjl |
last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
| | |