The ADOQuery Component - Dynamic Queries
(Page 4 of 4 )
A dynamic query gives you flexibility in retrieving data from a table. For example, say you want to retrieve all the people in the database with the surname of Smith. You are going to use a parameterized query:
SELECT * from person WHERE surname=:parameter
The parameter acts as a placeholder for a value in the WHERE clause of a SQL statement. So you need to provide a value for this parameter, like so:
SELECT * from person WHERE surname=:parameter
q1.Parameters.ParamByName('parameter').text:='Smith';
You can use as many parameters as you like, just make sure to give them the values as above.
Let’s put this in practice. 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.Parameters.ParamByName('parameter').text:='Smith';
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;
The parameter value in this case is "Smith." Once the "q.open" code is executed, the results of the query will be ready to be displayed. The while loop runs until the end of the file and at the same time adds the results in the memo1:
while not q1.Eof do begin
memo2.Lines.add(q1.FieldByName('name').Text+ '
'+q1.FieldByName('surname').Text);
q1.Next;
end;
The "q1.next" simply moves the pointer to the next record.
In memo1 type the following SQL statement:
select name, surname from person WHERE surname=:parameter
Here’s a sample run:
Conclusion
In the next article we are going to discuss creating reports with ADO. This will give you the complete set of knowledge that you need to produce really productive and useful database applications.
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
