?
Solved

Using Select & Where clause for mySQL

Posted on 2006-06-02
8
Medium Priority
?
158 Views
Last Modified: 2010-04-05
I don't know whats wrong with my code, why my query returns empty always (No record found). I checked my table and it has records, but when ever i run the code below it will return empty.
I suspect that there is something wrong with the "Where.." part. My table has the valid dates inside so i don't believe these will not be seen whenever the query will be executed.


 l_Query := TQuery.Create(fAppointmentScheduling);
 l_Query.Active := False;
 l_Query.DisableControls;
 l_Query.DatabaseName := fMain.pSQLAlias;
 l_Query.SQL.Clear;
 l_Query.SQL.Add('Select APNTDATE from '+const_Appointments);
 fromdate  := datetostr(MonthCalendar1.date-5);//+' 00:00:01';
 todate    := datetostr(MonthCalendar1.date+5);//+' 23:59:59';
 l_Query.SQL.Add('where ((DATE(APNTDATE) >= DATE('+fromdate+')) OR (DATE(APNTDATE) <= DATE('+todate+')))');
 showmessage(l_Query.SQL.text);
 l_Query.Open;
 l_Query.EnableControls;
 DataSource1.DataSet  := l_Query;
0
Comment
Question by:girlswants_me
6 Comments
 

Author Comment

by:girlswants_me
ID: 16818439
Thanks in advance.
0
 
LVL 4

Expert Comment

by:JDSkinner
ID: 16819582
Hi
It could be that you have opened the query before assigning  Datasource.Dataset, try moving this to just after  l_Query.Active := False;
0
 
LVL 11

Accepted Solution

by:
calinutz earned 252 total points
ID: 16820051
Did you try placing the query (select statement) inside the SQL property of a query that has the connection string pointing to a MySQL Database? And try running it from design? Perhaps there is something wrong with your variables that you assign while building the query in runtime.
So you should make your code showmessage to you before the Query.Active:=true
Also try to assign the datasource's dataset to the query before Activating the query.
It maybe your date format from your query. The best way to track your problem is to send the SQL build in runtime to a memobox before executing it and copy/paste it in a Query builder and analyze it from there.
   I strongly believe that your problem comes from the date format . check to see which date format is accepted by your mysql and what date format does your application use.

Regards

0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:girlswants_me
ID: 16821761
Still failed to display expected records. im 100% sure that there are dates that are in between my "where" clause. Is there something wrong with mySQL ver 5.1? i tried it manually in the mysql console and it does not return the records also.

Anybody can help me solve my problem????
0
 
LVL 4

Expert Comment

by:JDSkinner
ID: 16822801
Have you tried stepping through the code in debug mode?

First make sure that in the Project/Options/Compiler/Debugging panel all options are checked.

If you set the screen layout to Debug mode instead of Standard and then set a breakpoint on the
l_Query.SQL.Add('Select APNTDATE from '+const_Appointments); by clicking the mouse cursor
on that line. you can then run the program, when it reaches that block of code execution is halted until you step through the code line by line by clicking on the 'Trace Into' icon or by pressing F7.

After a line of code has been executed by pressing f7, you can place the mouse cursor over a variable in that line and check its current value which will be shown in a popup.

Local variable values are displayed in a panel.

This should enable you to check the values loaded in your where clause and see which one is playing up.
0
 
LVL 10

Assisted Solution

by:atul_parmar
atul_parmar earned 248 total points
ID: 16832899
As calinutz said, the problem is with dateformat. MySQL understands date time in 'YYYY-MM-DD HH:MM:SS' format only.

So, in SQL instead of simply using
datetostr(MonthCalendar1.date-5);//+' 00:00:01';

use
FormatDateTime('yyyy-mm-dd hh:mm:ss', MonthCalendar1.date-5);
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Creating an auto free TStringList The TStringList is a basic and frequently used object in Delphi. On many occasions, you may want to create a temporary list, process some items in the list and be done with the list. In such cases, you have to…
Introduction Raise your hands if you were as upset with FireMonkey as I was when I discovered that there was no TListview.  I use TListView in almost all of my applications I've written, and I was not going to compromise by resorting to TStringGrid…
Integration Management Part 2
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
Suggested Courses
Course of the Month15 days, 10 hours left to enroll

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question