The ADOQuery Component - A Practical Example
(Page 3 of 4 )
Drop an ADO Query component (located on the ADO tab) on a form, rename it to q1, and set its connection string property to where the "people database" is located. Download the database from the previous article. Drop a button, two memos and two labels on the form and arrange them so they look something like this:
The memo on the top of the form will be used to enter SQL statements and the bottom memo will display the results of the executed statements.
The TADOQuery component has a SQL property which is used to store the SQL statement. You can set the SQL property's value with the Object Inspector at design time or through code at runtime. We are going to use both methods to set the SQL.
To set the SQL statement at design time, select the query component, go to the object inspector and click on the ellipses button on the SQL property:
A string list editor should pop up:
Now, add the following SQL statement:
SELECT name, surname from person
and press the okay button. This SQL statement retrieves all the names and surnames in the table person. You should now be back on your form. Double click the button and add the following code:
procedure TForm1.Button1Click(Sender: TObject);
begin
q1.Active:=true;
while not q1.Eof do begin
memo2.Lines.add(q1.FieldByName('name').Text+ ' '+q1.FieldByName
('surname').Text);
q1.Next;
end;
end;
What is going on here? Well the first line of code activates the query component. Then we loop through the table until it reaches the end of the table. The line "while not q1.Eof do begin" literally means “do this before you reach the end of the file (eof).”
The code " memo2.Lines.add(q1.FieldByName('name').Text+ ' '+q1.FieldByName('surname').Text);" literally means “add the names and surnames to memo2”
The “q1.next” line calls the next record.
Now, run the program and press the button. You should see something like this:
As you can see from the results in the screen shot, all the names and surnames in the database are displayed.
Unfortunately, because we are hard coding the SQL statements by using the SQL editor, we are not going to be able to make multiple requests to the database. Every time we require different information we are going to have to exit the program and re-enter the data in the SQL editor. This of course is unworkable.
Therefore, we are going to use the memo1 on the form to enter our SQL statements. This way, we do not have to close the program down to enter the SQL statements. We simply delete the previous statement and write a new one.
Let's do just that. First of all, select the ADOquery component and go to the SQL property. Click on the ellipses button, clear everything from the SQL editor, and close it. Then double click on the button and add the following code:
procedure TForm1.Button1Click(Sender: TObject);
begin
q1.Close;
q1.SQL.Add(memo1.Lines.Text);
q1.Open;
q1.First;
while not q1.Eof do begin
memo2.Lines.add(q1.FieldByName('name').Text+ ' '+q1.FieldByName('surname').Text);
q1.Next;
end;
end;
As you can see I’ve added a few things to the code to make it more compatible with the new requirements. First I’ve added a line that closes the query component. We are going to use a lot of different SQL statements while the program is running, so we want to clear previous SQL statements before entering a new one. In short, the close statement prepares the query for a new statement.
The second piece of new code is the “q1.first” statement. Now this statement sets the table pointer to the first record, so that when we loop, we start from the very first record.
Here’s the output from the SQL statement:
Next: Dynamic Queries >>
More Delphi-Kylix Articles
More By Jacques Noah
