CODEWITHSUNDEEP
sqldelphisql-likeprepared-statementunspecified
Simon Erasmus
Simon Erasmus

Reputation: 134

Unspecified Error when search a table for a string in sql delphi using parameters - Delphi SQL

there. I am trying to search for a string contained in any column of a sql table by adding all the Fieldnames to the WHERE clause using a for loop. Also I use parameters to protect against SQL injection. But when I run I get an error like this:

Unspecified error

How can this be fixed and what is the problem (Not necessarily in that order). Here is my code. I am running Delphi 7

procedure TfrmView.edtSearchChange(Sender: TObject);
var
  i, i2: integer;
  obj: TEdit;
  QueryText: string;
begin
  obj:= Sender as TEdit;

  with dmInfo do
    begin
      qryInfo.SQL.Clear;
      qryInfo.SQL.Add('SELECT * FROM ' + tableName);
      qryInfo.Open;

      tblInfo.SQL.Clear;
      tblInfo.SQL.Add('SELECT * FROM ' + tableName);
      tblInfo.SQL.Add('WHERE (' + qryInfo.Fields[0].FieldName + ' LIKE :SQuery0)');
      QueryText:= '%' + obj.Text + '%';
      tblInfo.Parameters.ParamByName('SQuery0').Value:= QueryText;
      ShowMessage(QueryText);
      ShowMessage(tblInfo.Parameters.ParamByName('SQuery0').Value);
      for i:= 1 to qryInfo.FieldCount - 1 do
        begin
          tblInfo.SQL.Add(' OR (' + qryInfo.Fields[i].FieldName + ' LIKE :SQuery' + IntToStr(i) + ')');
          tblInfo.Parameters.ParamByName('SQuery' + IntToStr(i)).Value:= '%' + obj.Text + '%';
        end;
    tblInfo.Open;
end;

Upvotes: 0

Views: 1430

Answers (1)

moskito-x
moskito-x

Reputation: 11958

The whole code makes no sense.

  • You let the code run every time you change a letter. run by an onChange event
  • five input/letter to the edtSearch input field means the code is executed five times without interruption immediately
  • If you delete all content in the edtSearch input field at once, which is also a change event. This time it runs with an empty edtSearch.text
  • How can you expect that this works without exceptions
  • You open each time two tables without to close them before.

  • you are using a variable, which suggests that this event is also connected to other TEdits.

    obj: TEdit;
    obj:= Sender as TEdit;

  • You clear the SQL on an open table.

  • you have two tables the first one does nothing more than SQL.clear and open
  • You're using fields from first table and create the SQL for second table
  • Even if both tables are the same, it makes no sense to use the fields from the first table.
    It is confusing, misleading and unnecessary.

First of all remove the code out of the onChange event

What you want to do with this code
to search for values on all fields from the tblInfo can be done without the first tabel qryInfo

you do not need to increment the params.

Do not create the params all the time from SQuery1 to maybe SQuery100

if you only use one param (the search value is always the same)
You can set all params with a single use tblInfo.Parameters.ParamByName() before the tblInfo.Open
but NOT in the loop.

This will replace all params :SQuery all at once with the value

SQL.Text :

SELECT * FROM tableName
WHERE (IDmember LIKE :SQuery)
OR (memberName LIKE :SQuery)
OR (petName LIKE :SQuery)
OR (Address LIKE :SQuery)

Maximum Length Of An SQL Statement

Is different from database to database

How many OR clauses can I use in a single WHERE condition in MySql query?

I know there was a limit in the past. But now the experts knowledge is different

only one of the opinions

The truth is that it's limited to the resources available on the database, the size of the data set in question, the indexes being addressed (or lack thereof) and the complexity of each clause.

If your goal is to find a person record that meets multiple criteria, I am willing to bet you won't run in to a limit. You could easy OR condition with 20 to 30 conditions and no user is going to provide more that that / person records won't have more than one that meets that many conditions.

Upvotes: -1

Related Questions