"Field 'COUNT(id)' has no dataset" TSQLQuery in Delphi

Question

Hi! Sorry for my english, but i hope you'll understand me :P

I'm trying to create new TSQLQuery component in code, without placing it on form. I wrote that code:

var
sql:tsqlquery;
pole:TFMTBCDField;

....
   sql:=tsqlquery.Create(self);
   sql.SQLConnection:=ddm.konekszyn;
   sql.SQL.Text:='SELECT COUNT(idrap) FROM raporty WHERE idkier="'+lvkierowcy.Selected.Caption+'";';
   pole:=TFMTBCDField.Create(self);
   pole.Name:='sqlilerap';
   pole.FieldName:='COUNT(idrap)';
   pole.FieldKind:=fkData;
   pole.DisplayLabel:='COUNT(idrap)';

   sql.Fields.Add(pole);
   sql.Open;
   showmessage(sql.FieldByName('COUNT(idrap)').AsString);
   sql.Free;
   pole.Free;

but i'm getting exception when i try to access data:

First chance exception at $75999617. Exception class EDatabaseError with message 'Field 'COUNT(idrap)' has no dataset'. Process htstrm2.exe (2308)

What should I do ?

Answer 1

Your database driver reports the empty field name for the aggregate expression.

Alias your field:

   sql:=tsqlquery.Create(self);
   sql.SQLConnection:=ddm.konekszyn;
   sql.SQL.Text:='SELECT COUNT(idrap) AS cnt FROM raporty WHERE idkier="'+lvkierowcy.Selected.Caption+'";';
   pole:=TFMTBCDField.Create(self);
   pole.Name:='sqlilerap';
   pole.FieldName:='cnt';
   pole.FieldKind:=fkData;
   pole.DisplayLabel:='cnt';

   sql.Fields.Add(pole);
   sql.Open;
   showmessage(sql.FieldByName('cnt').AsString);
   sql.Free;
   pole.Free;

Answer 2

Try adding this line to your query:

AND idrap <> nil

Answer 3

You must explicitly assign the dataset to the field, try adding this line

pole.DataSet:=sql;

Bye.

Answer 4

Don't even make a field. Queries such as this return one and only one field. So just reference from the fields array:

var
sql:tsqlquery;

....
   sql:=tsqlquery.Create(self);
   sql.SQLConnection:=ddm.konekszyn;
   sql.SQL.Text:='SELECT COUNT(idrap) FROM raporty WHERE idkier="'+lvkierowcy.Selected.Caption+'";';
   sql.Open;
   showmessage(sql.fields[0].AsString);
   sql.Free;

Answer 5

Alias the column being returned. You can then access it by that aliased name:

sql.SQL.Text:='SELECT COUNT(idrap) AS iDrapCount FROM raporty WHERE dkier 
="'+lvkierowcy.Selected.Caption+'";';
....
pole.FieldName := 'iDrapCount';