Board index » delphi » Help needed on S.Q.L.

Help needed on S.Q.L.

Good day to all of you.

Please, please if there is anyone who can help me with the
following problem, please could you shout.

I am trying to run a program using Delphi2, which has the following.

An edit box(edit1), a button(button1), a S.Q.L. component(Query1),
a datasource(datasource1), a grid (grid1) and a table(table1).

When the user clicks the button, I would like to use the query
to select certain records and place them in the grid.
The table is connected to the correct database and table name.
The grid is also connected correctly with the datasource.

The code behind button1 is as follows:

(* 1. Trying to select a part ordered by machanic no - each machanic has
a *)
(*    seperate no. - and by part name alphabetically *)
(* 2. Last of all - trying to find out which part was used on a
certain    *)
(*    date with the no. 1. included

Query1.active := false ;
With Query1 do
Begin
        SQL.Clear ;
        (* The next line works on its own perfectly *)
        SQL.Add('SELECT * FROM PART ORDER BY PART_MAC_NO, PART_NAM ASC') ;
        (* The next line fails - copied from a book for Delphi 3 *)
        SQL.Add('format('WHERE PART_USEDAT' ' = %d''',[edit1.text])) ;
End ;
Query1.active := false ;

The error I receive when running the program, is the following:
        Invalid use of keyword. Token:WHERE. Line Number:2.      

If anyone can tell me how to use the S.Q.L. searching by a date and
inbetween
two dates, please could you e-mail me.

Many Thanks,
Ryan Brown
e-mail address:  r_s_br...@hotmail.com

 

Re:Help needed on S.Q.L.


Quote
Ryan wrote in message <34FBB5A3.2...@iaccess.za>...
>Good day to all of you.

>Please, please if there is anyone who can help me with the
>following problem, please could you shout.

>I am trying to run a program using Delphi2, which has the following.

>An edit box(edit1), a button(button1), a S.Q.L. component(Query1),
>a datasource(datasource1), a grid (grid1) and a table(table1).

>When the user clicks the button, I would like to use the query
>to select certain records and place them in the grid.
>The table is connected to the correct database and table name.
>The grid is also connected correctly with the datasource.

>The code behind button1 is as follows:

>(* 1. Trying to select a part ordered by machanic no - each machanic has
>a *)
>(*    seperate no. - and by part name alphabetically *)
>(* 2. Last of all - trying to find out which part was used on a
>certain    *)
>(*    date with the no. 1. included

>Query1.active := false ;
>With Query1 do
>Begin
> SQL.Clear ;
> (* The next line works on its own perfectly *)
> SQL.Add('SELECT * FROM PART ORDER BY PART_MAC_NO, PART_NAM ASC') ;
> (* The next line fails - copied from a book for Delphi 3 *)
> SQL.Add('format('WHERE PART_USEDAT' ' = %d''',[edit1.text])) ;
>End ;
>Query1.active := false ;

>The error I receive when running the program, is the following:
> Invalid use of keyword. Token:WHERE. Line Number:2.

>If anyone can tell me how to use the S.Q.L. searching by a date and
>inbetween
>two dates, please could you e-mail me.

>Many Thanks,
>Ryan Brown
>e-mail address:  r_s_br...@hotmail.com

** this should work ***
With Query1 do
Begin
SQL.Clear ;
(* The next line works on its own perfectly *)
SQL.Add('SELECT * FROM PART ') ;
(* The next line fails - copied from a book for Delphi 3 *)
SQL.Add(' format('WHERE PART_USEDAT' ' = %d''',[edit1.text])) ;
SQL.Add(' ORDER BY PART_MAC_NO, PART_NAM ASC') ;
End ;
Query1.active := false ;

You can use :
SELECT  * FROM PART WHERE date BETWEEN date1 AND date2

regards

Trond Norderhaug norderh...@ibm.no
IBM Norway

Re:Help needed on S.Q.L.


Ryan <o...@iaccess.za> schrieb am Tue, 03 Mar 1998 09:47:47 +0200:

Quote
>Good day to all of you.

>With Query1 do
>Begin
>    SQL.Clear ;
>    (* The next line works on its own perfectly *)
>    SQL.Add('SELECT * FROM PART ORDER BY PART_MAC_NO, PART_NAM ASC') ;
>    (* The next line fails - copied from a book for Delphi 3 *)
>    SQL.Add('format('WHERE PART_USEDAT' ' = %d''',[edit1.text])) ;
>End ;
>Query1.active := false ;

Hello Ryan!

You have to insert the WHERE clause before the ORDER BY clause (see
Local SQL Help).

Use the Database Explorer or the 'Params' property of the Query, this
makes it a lot easier to create and test the SQL code without running
the program.

Greetings,

Mike
--
BetaSoft
http://www.{*word*104}city.de/user/10794/delphi.htm
email: betas...@kagi.com

Re:Help needed on S.Q.L.


I believe SQL syntax varies according to the database you are using.
The answer I am giving here applies to the standard (dbase and paradox)
and I have only tested it with D3.

Remember that SQL is a string and the string often requires quotes so
you have to build it up accordingly:

What you want the string to look like is

  SELECT * FROM PARTS
  WHERE RDATE BETWEEN '02/02/97' AND '03/05/97'

So if you were hard coding the dates you would have to put

query1.sql.add('SELECT * FROM PARTS');
query1.sql.add('WHERE RDATE BETWEEN ''02/02/97'' AND ''03/05/97''');

If you are taking your dates from an edit box it is more tricky.
SQL only understands dates in American (mm/dd/yy) format.  So if you
cannot be *certain* the dates are entered American style you will need
to use something like formatdatetime to change it to American style.  

query1.sql.add('where rdate between
'''+formatdatetime('mm/dd/yy',strtodate(edit1.text))+''' and '''+
formatdatetime('mm/dd/yy',strtodate(edit2.text))+'''');

You may know, but you don't actually need a table to do this.  A query,
a datasource and a grid are quite adequate.  The database property of
the query tells it where to look for the table and you name the table in
the SQL.

I hope this is an answer to what you were asking.

P.

Other Threads