Delphi sql query using DateTimePicker

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
TG-SteveAsked:
Who is Participating?
 
Ephraim WangoyaCommented:

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
 
ThommyCommented:
DateTimePicker1.DateTime:=query3.fieldbyname('DateTimeCreated').AsDateTime;
0
 
Ephraim WangoyaCommented:
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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Ephraim WangoyaCommented:
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
 
ThommyCommented:
You can also try this data-aware DBDateTimePicker component (freeware)...

DBDateTimePicker
http://www.delphipages.com/comp/dbdatetimepicker-3946.html
0
 
Ephraim WangoyaCommented:

@Thommy

Do you really understand the question or you are just throwing anything in here and hoping something sticks
0
 
TG-SteveAuthor Commented:
ewangoya i am getting an error stating "cannot access protected symbol  tcontrol.text" could you help please

thank you
0
 
8080_DiverCommented:
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
 
Ephraim WangoyaCommented:

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
 
TG-SteveAuthor Commented:
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
 
Ephraim WangoyaCommented:

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

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
 
TG-SteveAuthor Commented:
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
 
Ephraim WangoyaCommented:
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
 
TG-SteveAuthor Commented:
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
 
TG-SteveAuthor Commented:
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
 
Ephraim WangoyaCommented:

Thanks TG-Steve
I'm always happy to help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.