CODEWITHSUNDEEP
sqlms-accessdelphi
Umsugi
Umsugi

Reputation: 53

Can't find a field in a table

I have a table containing the field 'cost', when I try to extract its value from the table I get an error saying the field was not found.

 function TSQLs.Get(value,room,hotel: string): string;
begin
  with Databasehub.DataModule1 do
  begin
    ADOQuery1.SQL.Clear;
    ADOTable1.TableName:='TblRoom';
    ADOQuery1.SQL.Add('SELECT "'+value+'" FROM TblRoom WHERE RoomType="' + Room + '" AND HotelName 
    ="'+Hotel+'"');
    DataSource1.DataSet:=ADOQuery1;
    ADOQuery1.Open;
    if (ADOQuery1.RecordCount = 1) then
    begin
      Result := ADOQuery1.FieldByName(value).AsString;
    end;
  end;
end;

Database table I'm extracting the value from Table its linked to

Upvotes: 1

Views: 1155

Answers (2)

Remy Lebeau
Remy Lebeau

Reputation: 596497

In an SQL statement, don't surround field names in double-quotes. If a field name has spaces (or other special characters, or reserved names), use square brackets instead. And since your field name is being provided by an input string, make sure to sanitize it before using it to avoid any SQL Injection attacks.

Also, you should be using single-quotes instead of double-quotes around string literals that are used in SQL statements:

if Pos(';', value) > 0 or Pos(',', value) > 0 then raise ...;
ADOQuery1.SQL.Text := 'SELECT [' + value + '] FROM TblRoom WHERE RoomType=' + QuotedStr(Room) + ' AND HotelName = ' + QuotedStr(Hotel);
ADOQuery1.Open;
...

A safer option to using literals is to use parameters instead (like @whosrdaddy's answer demonstrates), except that field names in a SELECT clause can't be parameterized, so that portion of the SQL will still have to use string concatenation (after sanitizing the string):

if Pos(';', value) > 0 or Pos(',', value) > 0 then raise ...;
ADOQuery1.ParamCheck := True;
ADOQuery1.SQL.Text := 'SELECT [' + value + '] FROM TblRoom WHERE RoomType=:Room AND HotelName=:Hotel';
ADOQuery1.Parameters.ParamByName('Room').Value := room;
ADOQuery1.Parameters.ParamByName('Hotel').Value := hotel;
ADOQuery1.Open;
...

Upvotes: 1

whosrdaddy
whosrdaddy

Reputation: 11860

First of all treat the values in your program as the type in the database. Cost should be defined as a currency type in your Access DB, Delphi has a currency type too, use it as long as you can and only at presentation time, use conversion to text (or use a control that can handle the native type). Also make sure to always use parameters in your queries, or else you are vulnerable to SQL injection, as a bonus it makes your queries more readable and avoids problems like quotes in names etc...

Here is an example of a parametrized query and how to create a query on the fly

function TSQLs.GetCost(const Room : string; const Hotel: string): Currency;

var
  Qry : TADOQuery;
begin
 Qry := TADOQuery.Create(nil);
 try
  Qry.Connection := Databasehub.DataModule1.AdoConnection; // assign your TADOconnection here
  Qry.ParamCheck := True; // parse parameters
  Qry.SQL.Text :='SELECT Cost FROM TblRoom WHERE RoomType=:Room AND HotelName=:Hotel'; 
  Qry.Parameters.ParamByName('Room').Value := Room;
  Qry.Parameters.ParamByName('Room').DataType := ftString;
  Qry.Parameters.ParamByName('Hotel').Value := Hotel;
  Qry.Parameters.ParamByName('Hotel').DataType := ftString;
  Qry.Active := True; // Qry.Open is also fine here
  if not Qry.EOF then // don't rely on recordcount here as it could be 0 when using dynamic cursors for example
    Result := Qry.FieldByName('Cost').AsCurrency;
 finally
  Qry.Free;
 end;
end;

Upvotes: 1

Related Questions