Lazarus

Programming => Databases => Topic started by: soymoe on January 18, 2019, 11:20:13 pm

Title: SQL Query using TEdit.text
Post by: soymoe 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;

Title: Re: SQL Query using TEdit.text
Post by: daveinhull 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?

Title: Re: SQL Query using TEdit.text
Post by: soymoe on January 18, 2019, 11:48:16 pm

The field is called CUIL / CUIT, the error is sql logic error

Title: Re: SQL Query using TEdit.text
Post by: daveinhull 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>

Title: Re: SQL Query using TEdit.text
Post by: soymoe 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

Title: Re: SQL Query using TEdit.text
Post by: GAN 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.

Title: Re: SQL Query using TEdit.text
Post by: madref 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);

Title: Re: SQL Query using TEdit.text
Post by: soymoe on January 19, 2019, 11:44:45 pm

IT  WORK THANKS!

Title: Re: SQL Query using TEdit.text
Post by: madref 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?

Title: Re: SQL Query using TEdit.text
Post by: soymoe 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;                                                      

Title: Re: SQL Query using TEdit.text
Post by: ASerge 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.

Title: Re: SQL Query using TEdit.text
Post by: Zoran on January 20, 2019, 07:00:34 am

Quote from: ASerge 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.

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

Title: Re: SQL Query using TEdit.text
Post by: ASerge on January 20, 2019, 07:16:38 am

Quote from: Zoran on January 20, 2019, 07:00:34 am

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

In which database?

Title: Re: SQL Query using TEdit.text
Post by: Zoran on January 20, 2019, 10:49:55 pm

Quote from: ASerge on January 20, 2019, 07:16:38 am

Quote from: Zoran on January 20, 2019, 07:00:34 am

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).