asked on

Using Select & Where clause for mySQL

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;

girlswants_me

ASKER

Thanks in advance.

JDSkinner

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

ASKER CERTIFIED SOLUTION

calinutz

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

girlswants_me

ASKER

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????

JDSkinner

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.

SOLUTION

atul_parmar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Using Select &amp; Where clause for mySQL

Using Select & Where clause for mySQL