Recent

Author Topic: TSQLQUERY Eof Bof No Work  (Read 1707 times)

Hapg18

  • New member
  • *
  • Posts: 8
TSQLQUERY Eof Bof No Work
« on: March 01, 2020, 06:43:54 pm »
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

  • Hero Member
  • *****
  • Posts: 3637
Re: TSQLQUERY Eof Bof No Work
« Reply #1 on: March 01, 2020, 09:00:47 pm »
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
The only true wisdom is knowing you know nothing

eljo

  • Sr. Member
  • ****
  • Posts: 408
Re: TSQLQUERY Eof Bof No Work
« Reply #2 on: March 01, 2020, 10:42:39 pm »
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

  • New member
  • *
  • Posts: 8
Re: TSQLQUERY Eof Bof No Work
« Reply #3 on: March 02, 2020, 03:01:29 am »
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

  • Hero Member
  • *****
  • Posts: 3637
Re: TSQLQUERY Eof Bof No Work
« Reply #4 on: March 02, 2020, 03:29:50 am »
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  :-[
The only true wisdom is knowing you know nothing

Hapg18

  • New member
  • *
  • Posts: 8
Re: TSQLQUERY Eof Bof No Work
« Reply #5 on: March 02, 2020, 04:43:03 pm »
 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
« Last Edit: March 02, 2020, 04:46:35 pm by Hapg18 »

devEric69

  • Sr. Member
  • ****
  • Posts: 350
Re: TSQLQUERY Eof Bof No Work
« Reply #6 on: March 02, 2020, 05:23:13 pm »
@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;
use: Linux 64 bits (Ubuntu 20.04 LTS).
Lazarus version: 2.0.4 (svn revision: 62502M) compiled with fpc 3.0.4 - fpDebug \ Dwarf3.

af0815

  • Hero Member
  • *****
  • Posts: 584
Re: TSQLQUERY Eof Bof No Work
« Reply #7 on: March 02, 2020, 06:20:40 pm »
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.
regards
Andreas

Hapg18

  • New member
  • *
  • Posts: 8
Re: TSQLQUERY Eof Bof No Work
« Reply #8 on: March 02, 2020, 08:32:31 pm »
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

  • New member
  • *
  • Posts: 8
Re: TSQLQUERY Eof Bof No Work
« Reply #9 on: March 02, 2020, 11:06:05 pm »
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

  • Hero Member
  • *****
  • Posts: 584
Re: TSQLQUERY Eof Bof No Work
« Reply #10 on: March 03, 2020, 07:06:23 am »
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. 
regards
Andreas

Hapg18

  • New member
  • *
  • Posts: 8
Re: TSQLQUERY Eof Bof No Work
« Reply #11 on: March 03, 2020, 01:36:12 pm »
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

  • Sr. Member
  • ****
  • Posts: 400
Re: TSQLQUERY Eof Bof No Work
« Reply #12 on: March 05, 2020, 11:47:11 am »
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?
One System to rule them all, One IDE to find them,
One Code to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
People call me crazy, because i'm jumping out of perfectly fine aircrafts

af0815

  • Hero Member
  • *****
  • Posts: 584
Re: TSQLQUERY Eof Bof No Work
« Reply #13 on: March 05, 2020, 03:40:28 pm »
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.   
regards
Andreas

Zvoni

  • Sr. Member
  • ****
  • Posts: 400
Re: TSQLQUERY Eof Bof No Work
« Reply #14 on: March 05, 2020, 03:45:07 pm »
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
« Last Edit: March 05, 2020, 03:46:52 pm by Zvoni »
One System to rule them all, One IDE to find them,
One Code to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
People call me crazy, because i'm jumping out of perfectly fine aircrafts

 

TinyPortal © 2005-2018