Change database query at runtime

Question

I'm trying to filter a TDBGrid via a TEdit, handling the TEdit onChange event. I have a TIBQuery to list all the records in the grid:

SELECT id,obra,cliente,fecha,nro_estudio,sondeo FROM proyecto;

When the user inserts characters in the TEdit, the grid must be refreshed by the Cliente field.

My code tries to do this, but when it executes the handler, the query returns an empty resultset. I'm using Firebird 2.5 and IB Components.

Relevant Code:

query := Self.qryTodos;
query.Close();
query.SQL.Clear();
query.SQL.Add('SELECT id,obra,cliente,fecha,nro_estudio,sondeo FROM proyecto WHERE cliente LIKE :Cliente');
query.ParamByName('Cliente').AsString := QuotedStr('%'+Self.busqueda.Text+'%');
query.Open();
DMConnect.Transaction.Commit();

Remove/comment the last line (`DMConnect.Transaction.Commit();`) and try again. By committing the transaction you're implicitly closing all attached datasets. — LightBulb, Jul 08 '13 at 18:13

score 2 · Answer 1 · answered Jul 08 '13 at 17:07

2

You don't need QuotedStr when you're supplying string parameters. By using it, you make it not match any more (e.g. "String" becomes '''%String%''')

answered Jul 08 '13 at 17:07

Curt

5,518
1
21
35

I try this `...LIKE ' + '%''' + Self.busqueda.Text + '%'''`. But, the program ¿crash? with this message: "Attempt to execute an unprepared dynamic SQL statment" – ramiromd Jul 08 '13 at 17:21
Couple problems there: if you still have the parameter referent (:Cliente), the query needs to be assigned parameter values before executing. Also, in the expression above, you're adding the wildcard *before* the opening quote in the string: that won't parse correctly. Also, as a side-note, if your string value has any embedded single quotes, that approach will break--it's better to use parameters to construct query strings. – Curt Jul 08 '13 at 17:27

score 0 · Answer 2 · answered Jul 09 '13 at 04:15

If you're surrounding your search term in % I would use the containing clause, so you could write your query like:

query := Self.qryTodos;
query.Close();
query.SQL.Clear();
query.SQL.Add('SELECT id,obra,cliente,fecha,nro_estudio,sondeo FROM proyecto WHERE cliente CONTAINING :Cliente');
query.ParamByName('Cliente').AsString := Self.busqueda.Text;
query.Open();

and remove the Commit statement as @LightBulb stated. This will work if you want to include the % in your search term for any reason.

Change database query at runtime

2 Answers2