Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Delphi sql query using DateTimePicker

Posted on 2011-02-24
17
Medium Priority
?
2,397 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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:Ephraim Wangoya
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:Ephraim Wangoya
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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:Ephraim Wangoya
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 400 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:Ephraim Wangoya
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:Ephraim Wangoya
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:Ephraim Wangoya
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:Ephraim Wangoya
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:
Ephraim Wangoya earned 1600 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:Ephraim Wangoya
ID: 34974153

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

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

The uses clause is one of those things that just tends to grow and grow. Most of the time this is in the main form, as it's from this form that all others are called. If you have a big application (including many forms), the uses clause in the in…
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…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Suggested Courses

661 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