Recent

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

soymoe

  • New Member
  • *
  • Posts: 35
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: 297
  • 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: 35
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: 297
  • 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: 35
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

  • Sr. Member
  • ****
  • Posts: 370
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 2.0.8 FPC 3.0.4 Linux Mint Mate 19.3
Zeos 7̶.̶2̶.̶6̶ 7.1.3a-stable - Sqlite 3.32.3 - LazReport

madref

  • Hero Member
  • *****
  • Posts: 949
  • ..... A day not Laughed is a day wasted !!
    • 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 3.99 (rev main_3_99-649-ge13451a5ab) FPC 3.3.1 x86_64-darwin-cocoa
Mac OS X Monterey

soymoe

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

madref

  • Hero Member
  • *****
  • Posts: 949
  • ..... A day not Laughed is a day wasted !!
    • 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 3.99 (rev main_3_99-649-ge13451a5ab) FPC 3.3.1 x86_64-darwin-cocoa
Mac OS X Monterey

soymoe

  • New Member
  • *
  • Posts: 35
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: 2222
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: 1829
    • 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: 2222
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: 1829
    • 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).

 

TinyPortal © 2005-2018