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

Question

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 ?

Can you just clarify - are you getting that at the point you open the query, or at the point at which you're trying to read the contents of the new field? Does it barf at 'SQL.Open' or at the 'ShowMessage' line? — robsoft, Nov 08 '09 at 19:55
ok - as a matter of interest, what happens if you change 'FieldByName('cnt').AsString' to 'Fields[0].AsString' - same error? — robsoft, Nov 08 '09 at 20:26
change your sql query to "SELECT COUNT(idrap) AS A FROM ".and use FieldByName('A') or Fields[0] . use parameterized query instead of string concat. — MajidTaheri, Apr 25 '12 at 12:38

score 1 · Accepted Answer · answered Nov 09 '09 at 13:09

1

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;

answered Nov 09 '09 at 13:09

M Schenkel

6,294
12
62
107

Thanks, it just works :D didnt know that i dont have to add fields. Thank you ! – WombaT Nov 09 '09 at 20:04

score 0 · Answer 2 · answered Nov 08 '09 at 18:35

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;

nope, didnt help. Still error: First chance exception at $75999617. Exception class EDatabaseError with message 'Field 'cnt' has no dataset'. Process htstrm2.exe (2864) — WombaT, Nov 08 '09 at 18:53

RRUZ · Answer 3 · 2009-11-09T02:38:23.090

0

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

pole.DataSet:=sql;

Bye.

edited Nov 09 '09 at 02:38

answered Nov 09 '09 at 02:20

RRUZ

134,889
20
356
483

you must change the field type from TFMTBCDField to TIntegerField – RRUZ Nov 09 '09 at 20:48

score 0 · Answer 4 · answered Nov 09 '09 at 14:08

0

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';

answered Nov 09 '09 at 14:08

Ken White

123,280
14
225
444

You need to be more specific. What does "isn't helping" mean? Are you getting an error message? Is your computer throwing up it's hands and saying "No way I'm helping you"? Providing nothing useful means that's what you get for help - nothing useful. – Ken White Nov 09 '09 at 20:43
inst helping in this case means that nothing changes after making an alias, still same error – WombaT Nov 09 '09 at 21:26
Can you single step through the code in the debugger and show exactly which line is causing the exception? – Ken White Nov 09 '09 at 21:37
showmessage(sql.FieldByName('COUNT(idrap)').AsString); here is a problem, but now its solved, as M Schenkel written above, a field isnt required. Thanks for trying to help. :) – WombaT Nov 10 '09 at 07:05

score -1 · Answer 5 · answered Nov 08 '09 at 19:13

-1

Try adding this line to your query:

AND idrap <> nil

answered Nov 08 '09 at 19:13

Mihaela

2,482
3
21
27

but, its not a query problem (i think), query executed in phpmyadmin works without problems, and returns values between 2 and 16. – WombaT Nov 08 '09 at 20:05
I think that some values of idrap in your dataset might be nil. Some database engines won't do aggregates if all values upon which aggregates are calculated are not <> nil. – Mihaela Nov 08 '09 at 20:38
it didnt help. idrap is a primary key column in table so it cant be nil (null?), it always have a value. – WombaT Nov 08 '09 at 21:16
Did you traverse the dataset with sql.First, sql.Next ? You just executed the query. – Mihaela Nov 08 '09 at 22:23

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

5 Answers5