Parameters in SQL - Delphi 7

Question

I am using Delphi 7 and Access 2007.

I want to know can anyone show me how to use Parameters with SQL statements and ADO.

What is the necessary coding and so forth. Sorry I am new to Delphi .

Crazed

+1 for asking how to use parameters, rather than using string concatenation (and SQL injection attacks)! – Lengthwise

Answer 1

Simply set the query's SQL, and then populate the parameters. Use parameter names that make sense to you, of course; I've just used LastName and FirstName for examples. I've updated to use TADOQuery instead of just TQuery after your edit to the question.

ADOQuery1.SQL.Clear;
ADOQuery1.SQL.Add('SELECT * FROM MyTable');
ADOQuery1.SQL.Add('WHERE LastName = :LastName AND');
ADOQuery1.SQL.Add('FirstName = :FirstName');

// Populate the parameters and open it
ADOQuery1.Parameters.ParamByName('LastName').Value := 'Jones';
ADOQuery1.Parameters.ParamByName('FirstName').Value := 'James';
ADOQuery1.Open;
// Use query results

ADOQuery1.Close;
// Populate parameters with new values and open again
// Populate the parameters and open it
ADOQuery1.Parameters.ParamByName('LastName').Value := 'Smith';
ADOQuery1.Parameters.ParamByName('FirstName').Value := 'Sam';
ADOQuery1.Open;
// Use new query results

ADOQuery1.Close;

Aeneus

Thanks a lot that helps. Great to have a site where you can get answers quickly. – Crazed

It is considered not a good idea to use 'select * from', especially when showing code to a beginner. It is always better to stipulate the exact field names. – Drud

@No'amNewman: The question didn't ask how to write SQL. It asked how to use parameters in Delphi, which is what I answered. If the question had provided information about the schema they were using, I would have used something different. I didn't use a real tablen ame, either. Thanks for your feedback, though. :-) – Aeneus

Just to make a note: In Oracle SQL we assign values to vars just like Pascal with := and that throws an error because TQuery thinks we are defining a parameter. So if you decide to use Oracle instead of Access, remember to add an escape as ::= instead of := So Delphi can read your SQL right. – Dene