CODEWITHSUNDEEP
sqldelphims-access
Norgardian
Norgardian

Reputation: 11

delphi how to search with a WHERE clause

How can I search a table (and multiple tables) at once where there is a WHERE clause. I know how to search a single table for either all (*) or the fields in the tables. That code is below, I was wondering how I should alter it for searching in multiple tables or with a WHERE clause.

Code I have on searching one table:

procedure searchonefield(sqlcommand, fieldwanted:string, list:TStringlist)
var i:integer;
templist:TStringlist;
begin
  ADOQ.ConnectionString:='Provider=Microsoft.ACE.OLEDB.12.0;Data  Source='+name+';';
  ADOQ.Close;
  ADOQ.SQL.Clear;
  ADOQ.SQL.Add(sqlcommand);
  ADOQ.Open;
  ADOQ.First;
  templist:=TStringlist.create;
  if ADOQ.RecordCount>1 then
    begin
      for I := i to ADOQ.RecordCount do
        begin
          templist.add(ADOQ.FieldByName(field).AsString);
          ADOQ.Next;
        end;
    end
  else
    templist.add(ADOQ.FieldByName(field).AsString);
  list:=templist;
  templist.free;
end;

Where my sqlcommand would be something like:

'SELECT Username FROM Usertable'

What I want to do is a command like:

'SELECT Username FROM Usertable WHERE ID='+id number+'

and

'SELECT Usertable.Username, Test.Testscore FROM Usertable, Test WHERE Usertable.ID=Test.ID AND Test.Testscore>10'

I am unsure if the above statements make sense or would work (should I structuring the statement differently)?

Upvotes: 0

Views: 1720

Answers (1)

Ken White
Ken White

Reputation: 125651

What I want to do is a command like:

 'SELECT Username FROM Usertable WHERE ID='+id number+'

That's the wrong solution, as it leaves you vulnerable to SQL injection. Use parameters instead:

var
  QueryStr: string;
begin
  QueryStr := 'SELECT UserName FROM UserTable WHERE ID = :ID';
  ADOQ.SQL.Text := QueryStr;
  ADOQ.Parameters.ParamByName('ID').Value := ID;
  ADOQ.Open;

and

   'SELECT Usertable.Username, Test.Testscore FROM Usertable, Test 
    WHERE Usertable.ID=Test.ID AND Test.Testscore>10'

This would be better written with a JOIN, and again using parameters if necessary.

SELECT 
  U.UserName, T.TestScore 
FROM 
  UserTable u INNER JOIN TestTable T on U.ID = T.ID
WHERE 
  T.TestScore > 10

It would appear that your next move should be to find a good SQL tutorial or book.

Upvotes: 1

Related Questions