Board index » cppbuilder » Problem with access database

Problem with access database


2006-11-18 01:12:32 AM
cppbuilder41
Hello,
I am using BCB6, Access 2003 and ADO for a small application. I
have a problem when I use datetime string in SQL.
My code list blow,
...
sql = "SELECT * FROM events WHERE started>= #11/12/2006# and
started <= #11/17/2006 11:56:32#";
...
adoQuery->Open();
...
Then I got error message, "Parameter object is improperly defined.
Inconsistent or incomplete information was provided.".
So I found out if I do like this,
...
sql = "SELECT * FROM events WHERE started>= #11/12/2006# and
started <= #11/17/2006#";
...
adoQuery->Open();
...
This time it works. But both sql can be executed in Access if I
just open query in Access.
The 'started' field is DATE/TIME type in table Events.
How do I fix it to query with time?
Thank
 
 

Re:Problem with access database

Use parameters instead of having the datetime values directly on your
SQL. Assuming you're using TADOQuery:
SQL = "SELECT * FROM events WHERE started>= :DT_START and started <=
:DT_END;
Then populate both DT_START and DT_END parameters with the needed
date-time values.
On Nov 18, 1:12 am, Daniel < XXXX@XXXXX.COM >wrote:
Quote
Hello,

I am using BCB6, Access 2003 and ADO for a small application. I
have a problem when I use datetime string in SQL.
My code list blow,
...
sql = "SELECT * FROM events WHERE started>= #11/12/2006# and
started <= #11/17/2006 11:56:32#";
...
adoQuery->Open();
...
Then I got error message, "Parameter object is improperly defined.
Inconsistent or incomplete information was provided.".
So I found out if I do like this,
...
sql = "SELECT * FROM events WHERE started>= #11/12/2006# and
started <= #11/17/2006#";
...
adoQuery->Open();
...
This time it works. But both sql can be executed in Access if I
just open query in Access.
The 'started' field is DATE/TIME type in table Events.

How do I fix it to query with time?

Thank
 

Re:Problem with access database

"Daniel" < XXXX@XXXXX.COM >wrote in message
Quote
...
sql = "SELECT * FROM events WHERE started>= #11/12/2006# and started
<= #11/17/2006 11:56:32#";
...
adoQuery->Open();
...
Then I got error message, "Parameter object is improperly defined.
Inconsistent or incomplete information was provided.".

Hi,
I worked out a solution in creating two TDate objects,
TDate dtFrom, dtTo;
Assign desired start and end times to these objects respectively, then
construct the SQL statement in this manner:
sql = "SELECT * FROM events WHERE started between #";
sql += "dtFrom.DateTimeString() + "# and #" + dtTo.DateTimeString() + "#";
Hope this help
 

{smallsort}