Recent

Author Topic: SQL Query using TEdit.text  (Read 1729 times)

soymoe

  • New Member
  • *
  • Posts: 17
SQL Query using TEdit.text
« on: January 18, 2019, 11:20:13 pm »
I need to make a query of a database that contains the code '%' and the text of an edit, something like this:

Code: Pascal  [Select]
  1. begin
  2.      if(RadioButtonCUIT.Checked)then
  3.           begin
  4.           ZQueryBusqueda.Close;
  5.           ZQueryBusqueda.sql.Clear;
  6.           ZQueryBusqueda.sql.Add('SELECT * FROM personas WHERE CUIL/CUIT LIKE
  7. '+'%'+EditBusqueda.Text);
  8.           ZQueryBusqueda.Open;
  9.           end;
  10.  
  11.      if(RadioButtonAPELLIDO.Checked)then
  12.           begin
  13.           ZQueryBusqueda.Close;
  14.           ZQueryBusqueda.sql.Clear;
  15.           ZQueryBusqueda.sql.Add('SELECT*FROM personas WHERE APELLIDO LIKE '+'%'+EditBusqueda.Text);
  16.           ZQueryBusqueda.Open;
  17.           end;
  18. end;

daveinhull

  • Sr. Member
  • ****
  • Posts: 264
  • 1 divided by nothing must still be 1!
Re: SQL Query using TEdit.text
« Reply #1 on: January 18, 2019, 11:42:02 pm »
Not sure if you can put '/' in the WHERE clause of the first SQL and what error do you get?
Version #:1.8.4 Date 2019-01-08 FPC Version: 3.0.4 and SVN Revision 57972 for x86_64-win64-win32/win64

soymoe

  • New Member
  • *
  • Posts: 17
Re: SQL Query using TEdit.text
« Reply #2 on: January 18, 2019, 11:48:16 pm »
The field is called CUIL / CUIT, the error is sql logic error

daveinhull

  • Sr. Member
  • ****
  • Posts: 264
  • 1 divided by nothing must still be 1!
Re: SQL Query using TEdit.text
« Reply #3 on: January 19, 2019, 12:02:03 am »
Do you need to put a space in between the end of LIKE and the start %?
I think the string ends up as
SELECT * FROM personas WHERE CUIL/CUIT LIKE%< what ever is in here EditBusqueda.Text>
Version #:1.8.4 Date 2019-01-08 FPC Version: 3.0.4 and SVN Revision 57972 for x86_64-win64-win32/win64

soymoe

  • New Member
  • *
  • Posts: 17
Re: SQL Query using TEdit.text
« Reply #4 on: January 19, 2019, 08:09:12 pm »
Code: Pascal  [Select]
  1. procedure TFormLlamador.EditBusquedaChange(Sender: TObject);
  2. begin
  3.      if(RadioButtonCUIT.Checked)then
  4.           begin
  5.           ZQueryBusqueda.Close;
  6.           ZQueryBusqueda.sql.Clear;
  7.           ZQueryBusqueda.sql.Add('SELECT * FROM personas WHERE CUIL/CUIT LIKE '+ QuotedStr('%' + EditBusqueda.Text));
  8.           ZQueryBusqueda.Open;
  9.           end;
  10.      if(RadioButtonAPELLIDO.Checked)then
  11.           begin
  12.           ZQueryBusqueda.Close;
  13.           ZQueryBusqueda.sql.Clear;
  14.           ZQueryBusqueda.sql.Add('SELECT * FROM personas WHERE APELLIDO LIKE '+ QuotedStr('%' + EditBusqueda.Text));
  15.           ZQueryBusqueda.Open;
  16.           end;
  17. end;
  18.                                    
The first sentence does not work, the second one does

GAN

  • Full Member
  • ***
  • Posts: 231
Re: SQL Query using TEdit.text
« Reply #5 on: January 19, 2019, 09:17:21 pm »
Code: Pascal  [Select]
  1. procedure TFormLlamador.EditBusquedaChange(Sender: TObject);
  2. begin
  3.      if (RadioButtonCUIT.Checked) then
  4.           begin
  5.           ZQueryBusqueda.Close;
  6.           ZQueryBusqueda.sql.Text:='SELECT * FROM personas WHERE (CUIL LIKE ''%'+EditBusqueda.Text+'%'') OR (CUIT LIKE ''%'+EditBusqueda.Text+'%'')';
  7.           ZQueryBusqueda.Open;
  8.           end;
  9.      if(RadioButtonAPELLIDO.Checked)then
  10.           begin
  11.           ZQueryBusqueda.Close;
  12.           ZQueryBusqueda.sql.Text:='SELECT * FROM personas WHERE APELLIDO LIKE '+ QuotedStr('%' + EditBusqueda.Text);
  13.           ZQueryBusqueda.Open;
  14.           end;
  15. end;

Hi Moe, try this and if you want post this in the spanish forum. I'm argentine too and know about CUIT, CUIL etc.
Lazarus 1.8.4 FPC 3.0.4 Linux Mint Mate 17.2 x86_64 GTK-2
Zeos 7.1.3 - Sqlite 3.8.2

Foro Lazarus en español http://forum.lazarus.freepascal.org/index.php/board,73.0.html

madref

  • Hero Member
  • *****
  • Posts: 700
  • ..... A day not Laughed is a day not Lived !!
    • Nursing With Humour
Re: SQL Query using TEdit.text
« Reply #6 on: January 19, 2019, 11:37:10 pm »
Put it between [ ] and it should work
Code: Pascal  [Select]
  1. ZQueryBusqueda.sql.Add('SELECT * FROM personas WHERE [CUIL/CUIT] LIKE '+ QuotedStr('%' + EditBusqueda.Text + '%'));
or this is what i do
Code: Pascal  [Select]
  1. sa := 'CD_Artiest LIKE ' + CHR(39) + '%' + txt_Artiest.Text + '%' + CHR(39);
« Last Edit: January 19, 2019, 11:45:19 pm by madref »
You treat a disease, you win, you lose.
You treat a person and I guarantee you, you win, no matter the outcome.

Lazarus 2.0.2 / FPC 3.0.4
Lazarus Trunc / FPC 3.0.4
Mac OS X Mojave

soymoe

  • New Member
  • *
  • Posts: 17
Re: SQL Query using TEdit.text
« Reply #7 on: January 19, 2019, 11:44:45 pm »
IT  WORK THANKS!

madref

  • Hero Member
  • *****
  • Posts: 700
  • ..... A day not Laughed is a day not Lived !!
    • Nursing With Humour
Re: SQL Query using TEdit.text
« Reply #8 on: January 20, 2019, 03:09:37 am »
Can you also give the solution for future reference.
So that other people also know how to recreate it?
You treat a disease, you win, you lose.
You treat a person and I guarantee you, you win, no matter the outcome.

Lazarus 2.0.2 / FPC 3.0.4
Lazarus Trunc / FPC 3.0.4
Mac OS X Mojave

soymoe

  • New Member
  • *
  • Posts: 17
Re: SQL Query using TEdit.text
« Reply #9 on: January 20, 2019, 03:12:08 am »
this is the solution:
Code: Pascal  [Select]
  1. procedure TFormLlamador.EditBusquedaChange(Sender: TObject);
  2. begin
  3.      if(RadioButtonCUIT.Checked)then
  4.           begin
  5.           ZQueryBusqueda.Close;
  6.           ZQueryBusqueda.sql.Clear;
  7.           ZQueryBusqueda.sql.Add('SELECT * FROM personas WHERE [CUIL/CUIT] LIKE '+ QuotedStr(EditBusqueda.Text+'%'));
  8.           ZQueryBusqueda.Open;
  9.           end;
  10.      if(RadioButtonAPELLIDO.Checked)then
  11.           begin
  12.           ZQueryBusqueda.Close;
  13.           ZQueryBusqueda.sql.Clear;
  14.           ZQueryBusqueda.sql.Add('SELECT * FROM personas WHERE APELLIDO LIKE '+ QuotedStr(EditBusqueda.Text+'%'));
  15.           ZQueryBusqueda.Open;
  16.           end;
  17. end;                                                      

ASerge

  • Hero Member
  • *****
  • Posts: 1418
Re: SQL Query using TEdit.text
« Reply #10 on: January 20, 2019, 06:46:57 am »
It is strange that nobody suggested to use the parameters. It is faster and safer and does not contain the difficulties mentioned here.

Zoran

  • Hero Member
  • *****
  • Posts: 1468
    • http://wiki.lazarus.freepascal.org/User:Zoran
Re: SQL Query using TEdit.text
« Reply #11 on: January 20, 2019, 07:00:34 am »
It is strange that nobody suggested to use the parameters. It is faster and safer and does not contain the difficulties mentioned here.

I don't think that you can use parameters in LIKE.

ASerge

  • Hero Member
  • *****
  • Posts: 1418
Re: SQL Query using TEdit.text
« Reply #12 on: January 20, 2019, 07:16:38 am »
I don't think that you can use parameters in LIKE.
In which database?

Zoran

  • Hero Member
  • *****
  • Posts: 1468
    • http://wiki.lazarus.freepascal.org/User:Zoran
Re: SQL Query using TEdit.text
« Reply #13 on: January 20, 2019, 10:49:55 pm »
I don't think that you can use parameters in LIKE.
In which database?

Actually I was wrong, now I tried and it works (Firebird).