TSQLQUERY Eof Bof No Work

[Hapg18]

First of all receive a cordial greeting from me, I am very new in programming with Lazarus, 1 week ago I made an application to verify prices from a DB MS SQL Server, to the part where I want to validate if the product exists or not,
I appreciate your help
This Code:
{open query }
     //SQLQueryExample01.open;
     with SQLQueryErxample01 do

     begin
    // while SQLQueryExample01.Eof do
     //DataSourceExample01.DataSet.First;
        //Reg := SQLQueryExample01.RecordCount.MinValue;
//     if SQLQueryExample01.EOF then
   if (DataSourceExample01.Dataset.BOF) and not (DataSourceExample01.DataSet.EOF) then
//         SQLQueryExample01.IndexFieldNames:=;

       begin
        showmessage('*No Exist..!*');
          //raise Exception.Create('*A Laas...!*');
        STextNoExiste.Visible := True;
        STextNoExiste.caption := 'EL Producto No Existe en el Inventario...!';
       // edit1.SetFocus;

     end
     else
      begin

       dbtext1.DataSource := DataSourceExample01;//**SQLQueryExample01.FieldByName(Precio).asstring;
       dbtext2.DataSource := DataSourceExample01;
       dbtext1.DataField := 'Precio';
       dbtext2.DataField := 'c_descri';
      end;

    if Active then
      Close;
      Open;
      edit1.SetFocus;
     end;
   //
     end;
    { begin
    key:=#0;
    SelectNext(ActiveControl,true,true);
      end;}           

[jamie]

Code: Pascal  [Select][+][-]

1.      //SQLQueryExample01.open;
2.      with SQLQueryErxample01 do
3.  
4.      begin
5.     // while SQLQueryExample01.Eof do
6.      //DataSourceExample01.DataSet.First;
7.         //Reg := SQLQueryExample01.RecordCount.MinValue;
8. //     if SQLQueryExample01.EOF then
9.    if (DataSourceExample01.Dataset.BOF) and not (DataSourceExample01.DataSet.EOF) then
10. //         SQLQueryExample01.IndexFieldNames:=;
11.  
12.        begin
13.         showmessage('*No Exist..!*');
14.           //raise Exception.Create('*A Laas...!*');
15.         STextNoExiste.Visible := True;
16.         STextNoExiste.caption := 'EL Producto No Existe en el Inventario...!';
17.        // edit1.SetFocus;
18.  
19.      end
20.      else
21.       begin
22.  
23.        dbtext1.DataSource := DataSourceExample01;//**SQLQueryExample01.FieldByName(Precio).asstring;
24.        dbtext2.DataSource := DataSourceExample01;
25.        dbtext1.DataField := 'Precio';
26.        dbtext2.DataField := 'c_descri';
27.       end;
28.  
29.     if Active then
30.       Close;
31.       Open;
32.       edit1.SetFocus;
33.      end;
34.    //
35.      end;
36.     { begin
37.     key:=#0;
38.     SelectNext(ActiveControl,true,true);
39.       end;}          
40.  

Your welcome

[eljo]

do you have the checks mixed or something? if BOF is true then apparently the dataset is at the start and if it has data then EOF must be false. In any way, in SQLDB the dataretreival is paged, it returns data as you move through the dataset records so in order to get all the data you need to either disable paging or call dataset.last; dataset.first; after that you can use the dataset.RecordCount to see how many records where returned.

[Hapg18]

The code as it works, but does not show "StaticText" when the TSQLQUERY is without registration, but sends it blank to the DBText1, and I want to validate that if it does not get the registration then show the "StaticText1" "No Exist ...! " grateful for what you can help
-------#
   if DataSourceExample01.Dataset.EOF then// and not (DataSourceExample01.DataSet.EOF) then
//         SQLQueryExample01.IndexFieldNames:=;

       begin
        showmessage('*No Exist...!*');

        STextNoExiste.Visible := True;
        STextNoExiste.caption := 'EL Producto No Existe en el Inventario...!';
       // edit1.SetFocus;

     end
     else
      begin

       dbtext1.DataSource := DataSourceExample01;//**SQLQueryExample01.FieldByName(Precio).asstring;
       dbtext2.DataSource := DataSourceExample01;
       dbtext1.DataField := 'Precio';
       dbtext2.DataField := 'c_descri';
      end;

    if Active then
      Close;
      Open;
      edit1.SetFocus;
     end;
Sorry dor my bad English
Thanks!

First of all receive a cordial greeting from me, I am very new in programming with Lazarus, 1 week ago I made an application to verify prices from a DB MS SQL Server, to the part where I want to validate if the product exists or not,
I appreciate your help
This Code:
{open query }
     //SQLQueryExample01.open;
     with SQLQueryErxample01 do

     begin
    // while SQLQueryExample01.Eof do
     //DataSourceExample01.DataSet.First;
        //Reg := SQLQueryExample01.RecordCount.MinValue;
//     if SQLQueryExample01.EOF then
   if (DataSourceExample01.Dataset.BOF) and not (DataSourceExample01.DataSet.EOF) then
//         SQLQueryExample01.IndexFieldNames:=;

       begin
        showmessage('*No Exist..!*');
          //raise Exception.Create('*A Laas...!*');
        STextNoExiste.Visible := True;
        STextNoExiste.caption := 'EL Producto No Existe en el Inventario...!';
       // edit1.SetFocus;

     end
     else
      begin

       dbtext1.DataSource := DataSourceExample01;//**SQLQueryExample01.FieldByName(Precio).asstring;
       dbtext2.DataSource := DataSourceExample01;
       dbtext1.DataField := 'Precio';
       dbtext2.DataField := 'c_descri';
      end;

    if Active then
      Close;
      Open;
      edit1.SetFocus;
     end;
   //
     end;
    { begin
    key:=#0;
    SelectNext(ActiveControl,true,true);
      end;}           

[jamie]

If I understand you correctly, you want to verify if a record with a specific value already exist before adding one ?

 TheDataSet.Locate('FieldName', 'ValueOfField',[loCaseInsensitive]);

This will return True if one exists..

 You can also use the LookUp(…_ which will select the record...


This of course only applies if I  understand it 

[Hapg18]

 Ok, thank you, indeed if the article does not exist, verify if the dataset is empty or void to perform the validation and thus be able to indicate that the article does not exist, I will not add records is just to consult, I will try what you tell me I am new to Lazarus ... very grateful

[devEric69]

@Hapg18, unless I'm mistaken, I have the impression that you're unnecessarily complicating your code, by wanting to use, and BOF, and EOF:
- you can test ...

Code: Pascal  [Select][+][-]

1. qryExample01.recordCount

...if you really want to know if you have fetched records.
- Otherwise, the easiest way is to code \ use your query could be simply:

Code: Pascal  [Select][+][-]

1. qryExample01.Open
2. qryExample01.First;
3. while not qryExample01.EOF do begin
4.     /* write, everything that needs to be done here */
5.     qryExample01.Next;
6. end;

[af0815]

my 2 cent: If you query qryExample01.EOF and qryExample01.BOF you can check if the resultset is empty. The only place where EOF and BOF both true is when a resultset is complete empty.

[Hapg18]

ok, thanks, I have already tried with SQLQuery.RecordCount = 0 but I did nothing, it just showed the DBEdit.DataField: = ('Price'); blank, send me an example please, thank you ...

[Hapg18]

Greetings, I have already tried all the examples and the StaticText.Caption does not show me, to show the message does not exist ... any other way, or what will I be doing wrong? Thank you for your support ... I am very new to Lazarus ..

[af0815]

Dont mix local database and server database things. Locate and recordcount is more for oldshool desktopdatabsaes like dbase or access.

To find out if a record for the given query exists.

Code: Pascal  [Select][+][-]

1. qryExample01.Open
2. if  qryExample01.BOF and qryExample01.EOF then // instead of recordcount = 0
3.    showmessage('Nothing found');
4.  

to count how much records exist in the db use the count() function inside the query. recordcount says only how much records are fetched from server. This value have nothing to do how much records exist on the server. I never use this if i work with a server. If your db is csvdb, dBase or Access recordcount is ok. 

[Hapg18]

In English:
Greetings, thank you very much for your support, solved ... my mistake was that I did not close the TSQLQUERY, after Andres told me that I could do with .EOF and BOF, I tried again and nothing that happened, until I decided to close the TSQQuery.Close; and it works as expected Many thanks to all, excuse me if it was a rookie mistake I am just learning with Lazarus again Thank you ... attached 2 PrintScreen for you to see ...
En Español:
Saludos, muchas gracias por su apoyo, resuelto... mi error era que no cerraba el TSQLQUERY, después de que Andres me reitio que sibse podia hacer con .EOF y BOF, lo intenté nuevamente y nada que resultaba, hasta que decidí cerrar el TSQQuery.Close; y listo funciona como esperaba Muchas Gracias a todos, me dusculpan si fue error de novato apenas estoy aprendiendo con Lazarus de nuevo Gracias... adjunto 2 PrintScreen para que lo vean...

[Zvoni]

If he just wants to check, if an article (or whatever) exists before inserting it, then it implies he's checking against an UNIQUE value/Field.
Just declare that field as UNIQUE in the Database, and fire the INSERT against it.
If no error returns, all is smiles and sunshine, if it exists, the database is going to slap him with a database-specific error-code.

Why in blazes the convoluted stuff with BOF/EOF, querying beforehand and what not?

[af0815]

Just declare that field as UNIQUE in the Database, and fire the INSERT against it.

IMHO not a good design, i have learned the hard way not to do this. If somethings is changed in years in the DB you have a not running App and nobody can help. You have not always the exclusive rights on a server, nor can you say it is unique forever.   

[Zvoni]

Just declare that field as UNIQUE in the Database, and fire the INSERT against it.

IMHO not a good design, i have learned the hard way not to do this. If somethings is changed in years in the DB you have a not running App and nobody can help. You have not always the exclusive rights on a server, nor can you say it is unique forever.

OK, i can understand that from that POV.
OTOH, why not just fire a Select against that (unique?) value, and then just check RecordCount=0?
No running around the recordset with BOF/EOF