SQL Query fails on empty result

Question

I have a function that performs a query on a SQL database with an ADO connection, it is simply designed to provide a single result for a database entry that can only have one match for a SELECT type of query (i.e. get me the x value from ID 45, where there is and can only be one ID 45 entry).

The function works fine, until I hit a query that returns no results. The query just hangs, and the application cannot continue. Here is an example query string:

'SELECT Cost FROM MaterialCost ' +
'WHERE MaterialType = ''' + 'MS' +
''' AND Thickness = ''' + '0.250' + '''';

Again this exact string will work fine until I maybe query for something that I know before hand doesn't exist, which should return null or an empty string. Here is the function:

function SelectOneQuery(AQueryString : String; AADOConnectionString : String) : String;
var
  ADOQuery: TADOQuery;
begin
  //Create empty ADO Query, then connect to connection string
  ADOQuery := TADOQuery.Create(nil);
  ADOQuery.ConnectionString:=AADOConnectionString;

  ADOQuery.SQL.Clear;
  ADOQuery.SQL.Add(AQueryString);
  ADOQuery.ExecSQL;

  ADOQuery.Open;

  //Set first query result and return first result
  ADOQuery.First;
  if(ADOQuery.Fields.Count > 0) then begin
    result:=ADOQuery.Fields[0].Value;
  end
  else begin
    result := '';
  end;
end;

I added the fields count thing, but I'm not sure if that's helping at all. Basically, if there are no result, i want result := ''

Answer 1

You have a few problems in your code snippet:

• The main problem is that you are checking FieldCount. FieldCount will always be nonzero because it contains the number of columns your query returns, regardless of the fact your query returned records or not. One option is to check RecordCount which represents the number of rows returned but a better option is to check EOF flag.
• you are leaking ADOQuery. Always use try/finally blocks to create and cleanup objects.
• ExecSQL is used for queries that don't return recordsets (like INSERT and DELETE), use Open instead
• No need to use First after Open
• If you use the same query over and over you are better off using parameters, as a bonus your code will be more readable.

Example:

ADOQuery.SQL.Text := 'SELECT Cost FROM MaterialCost WHERE MaterialType = :MaterialType AND Thickness = :Thickness';
ADOQuery.Parameters.ParamByname('MaterialType').Value := 'MS';
ADOQuery.Parameters.ParamByname('Thickness').Value := 0.25;

Your function code should be something like this:

function SelectOneQuery(const AQueryString, AADOConnectionString: string): string;
var
  ADOQuery: TADOQuery;
begin
  Result := '';

  ADOQuery := TADOQuery.Create(nil);
  try
    ADOQuery.ConnectionString := AADOConnectionString;
    ADOQuery.SQL.Text := AQueryString;
    ADOQuery.Open;
    if not ADOQuery.EOF then
      Result := ADOQuery.Fields[0].AsString;
  finally
    ADOQuery.Free;
  end;
end;