Solved

Delphi sql query using DateTimePicker

Posted on 2011-02-24
17
2,045 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
 
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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

This article explains how to create forms/units independent of other forms/units object names in a delphi project. Have you ever created a form for user input in a Delphi project and then had the need to have that same form in a other Delphi proj…
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…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

743 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now