Monday 29 March 2010

Delphi - Interbase Tutorial # 7 : Modifying Query’s SQL Statement

In the previous tutorial I have shared how to use a Query component. We can modify the SQL statement in a Query. Use the previous form. Add a Label component, two Edit components and a Database component. A Database component is used for data connection, so when we run the program, the login form will appear at the first place. Our form will be like this :

Set the properties of the new added components

Label1

Caption<>

Edit1

Text"blank"

Edit2

Text"blank"

Database1

AliasNameALIASMYDATA
DatabaseNameMyDatabase

Change the DatabaseName property of the Query

Label1

DatabaseNameMyDatabase

Type the code below for the OnClick event of  the GO button :

procedure TForm1.Button1Click(Sender: TObject);

begin

  Query1.Close;

  Query1.SQL.Clear;

  Query1.SQL.Add

   ('Select * from EMPLOYEE');

  Query1.SQL.Add

   ('where AGE >= :firstparameter');

  Query1.SQL.Add

   ('and AGE <= :secondparameter');

  Query1.Prepare;

  Query1.Params[0].Value := StrToInt(Edit1.Text);

  Query1.Params[1].Value := StrToInt(Edit2.Text);

  Query1.Open;

end;

If you run the program and fill 32 in Edit1 and 35 in Edit2, you’ll get the result like this picture :

To modify the SQL statement in a Query component, first you have to deactivate the Query (Query1.Close;), clear the SQL statement (Query1.SQL.Clear;) and then you can add some SQL statement (Query1.SQL.Add(‘SQL statement’);). You can type SQL statement as much as you need. If you use parameters in the SQL statement, you can name the parameters with any meaningful names. In this example I name the first parameter with firstparameter and the second one with secondparameter. Then put the actual value into params() property of the Query. I put StrToInt(Edit1.Text) into params(0) and StrToInt(Edit2.Text) into params(1). You can also have parameters as much as you need. It begins with params(0).

Go to Previous Tutorial

2 comments:

  1. The site SQL Reports has a great SQL tutorial. Highly recommended for people just getting started on SQL selects. Sql tutorial

    ReplyDelete
  2. so good to be back

    ReplyDelete

These links are part of a pay per click advertising program called Infolinks. Infolinks is an In Text advertising service; they take my text and create links within it. If you hover your mouse over these double underlined links, you will see a small dialog box containing an advertisement related to the text. You can choose to move the mouse away and go on with your browsing, or to click on the box and visit the relevant ad. Click here to learn more about Infolinks Double Underline Link Ads.