How to see if a record exists in firebird table

to check if a record exists, changing your code above

'WHERE EXISTS(SELECT 1 FROM AGENDA '+

however, " through rdb$database (so I can see if another user has appended that value)"
this doesn't make sense, I cannot see how rdb$database is relevant to you either another user has added (& committed) the record or they haven't

Hi, I'm a newbe to this sort of programming. The RDB$database contains only one record of a table (if I'm not wrong), so the code above should be enough, but I get an error : 'token unknown' SELECT.'

rdb$database does only contain 1 record

where are you running this query from, you don't need the leading :

SELECT CAST (A AS DATE) FROM RDB$DATABASE '+
                          'WHERE EXISTS(SELECT 1 FROM AGENDA '+
                          'WHERE DATUM = CAST (A AS DATE))';
or

select first 1 datum from agenda A where DATUM = CAST (A AS DATE)

When I run the query like this from Firebird Maestro

SELECT '07-24-2012' FROM RDB$DATABASE
                          WHERE EXISTS(SELECT 1 FROM AGENDA
                          WHERE DATUM = '07-24-2012')

it gives the correct result, from within my program I then get the error: cannot find field Datum

Agenda table contains 3 fields: datum, afspraak (blob) , autonummer (autoincrement)
Agendaquery: 'SELECT * FROM AGENDA'

please show the actual code that is giving that error regarding Datum

this is the code I use now

  Agendaquery.Close;
   Agendaquery.SQL.Text:= 'SELECT CAST (:A AS DATE) FROM RDB$DATABASE '+
                          'WHERE EXISTS(SELECT 1 FROM AGENDA '+
                          'WHERE DATUM = CAST (:A AS DATE))';
   Agendaquery.ParamByName('A').AsDate:= Kalender.Date;
  Agendaquery.Open;

I think you need

Agendaquery.Close;
   Agendaquery.SQL.Text:= 'SELECT CAST (:A AS DATE) FROM RDB$DATABASE '+
                          'WHERE EXISTS(SELECT 1 FROM AGENDA '+
                          'WHERE DATUM = CAST (:B AS DATE))';
   Agendaquery.ParamByName('A').AsDate:= Kalender.Date;
   Agendaquery.ParamByName('B').AsDate:= Kalender.Date;
  Agendaquery.Open;

Same problem: error: 'Agendaquery: field 'DATUM' not found

I think I found the solution:
when I use an empty query (agendaquery contains the fields from agenda-table), it works

so:   Algquery1.Close;
   Algquery1.SQL.Text:=   'SELECT CAST (:A AS DATE) FROM RDB$DATABASE '+
                          'WHERE EXISTS(SELECT 1 FROM AGENDA  '+
                          'WHERE DATUM = CAST (:A AS DATE))';
   Algquery1.ParamByName('A').AsDate:= Kalender.Date;
  Algquery1.Open;

instead of
 Agendaquery.Close;
   Agendaquery.SQL.Text:=   'SELECT CAST (:A AS DATE) FROM RDB$DATABASE '+
                          'WHERE EXISTS(SELECT 1 FROM AGENDA  '+
                          'WHERE DATUM = CAST (:A AS DATE))';
   Agendaquery.ParamByName('A').AsDate:= Kalender.Date;
  Agendaquery.Open;

strange ....

very strange

what can be the problem ?????? I always try to find out why, but in this case my brain can't follow ....

Sorry, but i think you need more simple, just use plain "select " like this...

select count(AGENDA.DATEM) from AGENDA where AGENDA.DATEM = '2012-07-24'

NOTA: the table "AGENDA" must have a column named "DATEM"

if the column "DATEM" are "datetime" then select

select count(AGENDA.DATEM) from AGENDA where cast(AGENDA.DATEM as DATE) = '2012-07-24'

I know select count , but what I want to know is, if a record still EXISTS in the table or not , and since rdb$database can me tell this (or am I wrong) ... Another user could already have deleted the record for intance.

WHERE EXISTS is much faster than a select count, which itenerates through all of the records in the table each time.

By the way: table AGENDA has the field DATUM in it

well the select count is another technique that will give you the same logical answer regarding existance, although as you correctly point out where exists can be faster (if there is a unique index/constraint on datum they will be almost the same)

Well I allways use a select to see if exist a record just test count > 0.