Solved

Delphi sql query using DateTimePicker

Posted on 2011-02-24
17
2,110 Views
Last Modified: 2012-05-11
Hi Guys

i have created a view within sql server and i am passing a query to that view from my application of which can be seen below. within the return data there is a DateTimeCreated column of which i would like to be able to have 2 datetimepicker components to select a start and stop date range and pass this within the query below

Query3.Close;
query3.sql.text := 'select Customer, "First Line", Postcode, "Order Ref", StockItemName, Barcode, LineQuantity, DateTimeCreated FROM dbo.ReturnsView '+
'WHERE Customer LIKE (''%' + edit5.text + '%'') AND Postcode LIKE (''%' + edit6.text + '%'')';
Query3.Open;

any help would be greatly appreciated
0
Comment
Question by:TG-Steve
  • 9
  • 5
  • 2
  • +1
17 Comments
 
LVL 19

Expert Comment

by:Thommy
ID: 34970699
DateTimePicker1.DateTime:=query3.fieldbyname('DateTimeCreated').AsDateTime;
0
 
LVL 32

Expert Comment

by:ewangoya
ID: 34970734
try this
Query3.Close;
 Query3.SQL.Clear;
 Query3.SQL.Add('select Customer, [First Line], Postcode, [Order Ref], StockItemName, Barcode, LineQuantity, DateTimeCreated FROM dbo.ReturnsView ');
 Query3.SQL.Add(Format('WHERE (Customer LIKE %s) AND (Postcode LIKE %s)',
   [QuotedStr('%' + edit5.text, + '%'), QuotedStr('%' + edit6.text + '%')]));
 Query3.SQL.Add(Format('AND (DateTimeCreated BETWEEN %s AND %s)',[DateTimePicker1.Text, DatetimePicker2.Text]));
 Query3.Open;

Open in new window

0
 
LVL 32

Expert Comment

by:ewangoya
ID: 34970749
correction

Query3.Close;
 Query3.SQL.Clear;
 Query3.SQL.Add('select Customer, [First Line], Postcode, [Order Ref], StockItemName, Barcode, LineQuantity, DateTimeCreated FROM dbo.ReturnsView ');
 Query3.SQL.Add(Format('WHERE (Customer LIKE %s) AND (Postcode LIKE %s)',
   [QuotedStr('%' + edit5.text + '%'), QuotedStr('%' + edit6.text + '%')]));
 Query3.SQL.Add(Format('AND (DateTimeCreated BETWEEN %s AND %s)',[DateTimePicker1.Text, DatetimePicker2.Text]));
 Query3.Open;

Open in new window

0
Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

 
LVL 19

Expert Comment

by:Thommy
ID: 34970760
You can also try this data-aware DBDateTimePicker component (freeware)...

DBDateTimePicker
http://www.delphipages.com/comp/dbdatetimepicker-3946.html
0
 
LVL 32

Expert Comment

by:ewangoya
ID: 34970956

@Thommy

Do you really understand the question or you are just throwing anything in here and hoping something sticks
0
 

Author Comment

by:TG-Steve
ID: 34971270
ewangoya i am getting an error stating "cannot access protected symbol  tcontrol.text" could you help please

thank you
0
 
LVL 22

Assisted Solution

by:8080_Diver
8080_Diver earned 100 total points
ID: 34971301
One of Thommy's suggestions is basically what I would recommend as well, however, I am not so sure about insisting on using the FORMAT function instead of simple capturing the text from the DateTimePicker.

Also, I would put the DateTimeCreated constraint as the first constraint because it will allow the query optimizer to actually use an index.  Currently, because you are using the LIKE constraint and are searching for the substrings anywhere within the data in the columns, I am willing to bet that your query is doing a table scan.  If you put the DateTimeCreated constraint first, the optimizer will be able to narrow down the portioon of the table that is being scanned (assuming that you have an index on the DateTimeCreated column ;-).

Alternatively, you might want to consider creating a stored procedure that accepts the 4 parameters and then executes the query.  The first gain you will have is that the stored proc will be precompiled once instead of every time you execute the query.  The second gain that you will have is that the maintenance of both the SP and the Delphi code will be somewhat easier.  (I'm a Development DBA now but I spent 10+ years developing in Delphi. ;-)
0
 
LVL 32

Expert Comment

by:ewangoya
ID: 34971330

Sorry, use date property
Query3.Close;
 Query3.SQL.Clear;
 Query3.SQL.Add('select Customer, [First Line], Postcode, [Order Ref], StockItemName, Barcode, LineQuantity, DateTimeCreated FROM dbo.ReturnsView ');
 Query3.SQL.Add(Format('WHERE (Customer LIKE %s) AND (Postcode LIKE %s)',
   [QuotedStr('%' + edit5.text, + '%'), QuotedStr('%' + edit6.text + '%')]));
 Query3.SQL.Add(Format('AND (DateTimeCreated BETWEEN %s AND %s)',
   [QuotedStr(DateToStr(DateTimePicker1.Date)), QuotedStr(DateToStr(DateTimePicker2.Date))]));
 Query3.Open;

Open in new window

0
 

Author Comment

by:TG-Steve
ID: 34971763
ewangoya i now recieve another error stating "The conversion of a char data type to a datetime data type resulted in an 'out of range datetime value'"

8080_diver i really appreciate your suggestions and both of you guys have saved my life many times over with previous postings. i will reply back to your alternative methods shortly thank you
0
 
LVL 32

Expert Comment

by:ewangoya
ID: 34971786

What datatype is DateTimeCreated  in your database table and what query control are you using (Is it ADOQuery?)
0
 
LVL 32

Expert Comment

by:ewangoya
ID: 34971804

Also what is the format of the date mm/dd/yyyy or dd/mm/yyyy

Depending on the control you are using, you may be better of using Parameterized query rather than sending string literals to the query
0
 

Author Comment

by:TG-Steve
ID: 34971855
dd/mm/yyyy is expressed in the results back under my old query but i just realised that the result also has the timestamp in there which was out of view in my column width sorry .

so the result shows this 15/06/2010 11:07:50
i am not to bothered about searching by time and only want to search within the date parameters. i am not sure whether this is still possible now or is it?
0
 
LVL 32

Expert Comment

by:ewangoya
ID: 34971929
The dateformat seems to be the problem, in the query we need to change it to MM/DD/YYYY
Query3.Close;
 Query3.SQL.Clear;
 Query3.SQL.Add('select Customer, [First Line], Postcode, [Order Ref], StockItemName, Barcode, LineQuantity, DateTimeCreated FROM dbo.ReturnsView ');
 Query3.SQL.Add(Format('WHERE (Customer LIKE %s) AND (Postcode LIKE %s)',
   [QuotedStr('%' + edit5.text + '%'), QuotedStr('%' + edit6.text + '%')]));
 Query3.SQL.Add(Format('AND (DateTimeCreated BETWEEN %s AND %s)',
   [QuotedStr(FormatDateTime('MM/DD/YYYY', DateTimePicker1.Date)),
    QuotedStr(FormatDateTime('MM/DD/YYYY', DateTimePicker2.Date))]));
 Query3.Open;

Open in new window

0
 

Author Comment

by:TG-Steve
ID: 34972358
I seem to be getting a grid index out of range error now. i noticed in your code it has 'MM/DD/YYYY', DateTimePicker2.Date. but the result set data is DD/MM/YYYY. would this work still? i did try changing it to DD/MM/YYYY but still failed
0
 
LVL 32

Accepted Solution

by:
ewangoya earned 400 total points
ID: 34972438

Even though the result is returned in dd/mm/yyyy, the database expects it in mm/dd/yyyy so the query will work fine and return results

Grid Index range error is something different. You are probably using persistent fields, or the number of fields you request with the query is different form the number of fields that are initially shown on the grid.

Make sure the number fields and field positions are the same, or disable controls and clear the grid fields and when the query reopens, it will populate the grid again

0
 

Author Closing Comment

by:TG-Steve
ID: 34973962
The solution you gave was correct and worked perfect as do all yours and others skills show.

Thank you very much for your prompt responces and again you are a true asset to EE.

8080_diver i will indeed go away with your opinions in mind and later revisit this within the project. i really do value everyones opinions and knowledge and hope i have reflected this in an acceptable manner in the way i have awarded the points
0
 
LVL 32

Expert Comment

by:ewangoya
ID: 34974153

Thanks TG-Steve
I'm always happy to help.
0

Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

In this tutorial I will show you how to use the Windows Speech API in Delphi. I will only cover basic functions such as text to speech and controlling the speed of the speech. SAPI Installation First you need to install the SAPI type library, th…
Introduction I have seen many questions in this Delphi topic area where queries in threads are needed or suggested. I know bumped into a similar need. This article will address some of the concepts when dealing with a multithreaded delphi database…
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

786 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