Delphi and Access walkthrough
Using SQL to search your tables
Searching through a large database for a piece of information is an old and important problem.
There are two different approaches, shown in the illustration. The 'dumb' approach is what you're used to using Access at school. The Client-Server SQL approach means that there's less traffic on the network, less processing for the client workstation to do and better security. SQL Search commandsSearch for a specific value
WHERE USERNAME = "Keith" Or for a pattern
WHERE CITY LIKE "S%" .. searches for all cities starting with 'S'
WHERE CITY LIKE "%S%" .. searches for cities with 'S' anywhere in their name.
|
How to compose and issue your own SQL commands in Delphi
Example code which uses an edit control (edit1) to allow the user to type a search term. It will then search for that term appearing anywhere in the username field.
- Set up a string. Write into that string the SQL that you need for your standard report/form. Add to it the 'WHERE' statement, indicating the field(s) that you want to search and using a parameter for the search term. A parameter in Delphi SQL is a colon followed by the parameter name.
- Use the FDQuery.ParamByName procedure to set the parameter with your search string.
- Finally run the query with FDQuery.Open
procedure TForm3.searchButtonClick(Sender: TObject);
var
sqlquery : string;
begin
sqlquery := 'SELECT PUPILS.ID, PUPILS.USERNAME, PUPILS.USERSCORE, SURNAMES.SURNAME '+
'FROM PUPILS '+
'INNER JOIN SURNAMES '+
'ON (PUPILS.USERNAME = SURNAMES.FIRSTNAME)' +
'WHERE PUPILS.USERNAME LIKE :firstname'; //the :firstname is a parameter
fdquery1.SQL.text := sqlquery;
fdquery1.ParamByName('firstname').AsString := '%'+edit1.text+'%'; //set parameter
fdquery1.Open; // run the query
end;