Solved

SQL search query needed for delphi form with 3 search fields

Posted on 2010-09-15
7
743 Views
Last Modified: 2012-05-10
I have a delphi form with 3 search fields.
First Name, Last Name, City

I want to allow the user to search either 1 field (which the query is very simple) but I am having difficulties if the user enters in 2 or more criteria. Because of the OR in the statement, when user searches for 'John' in First Name and 'Oakland' in City, it returns all records where John is first name, oakland is city AND Last Name is blank.  How can I allow users to search for 1, 2 or all 3? Do I have to state every possible search combination?
0
Comment
Question by:Bianca
7 Comments
 
LVL 78

Expert Comment

by:arnold
ID: 33685052
You need to set the parameters according to the data submitted.
i.e. user chooses field1 and field2
your query would need to be
select * from table where column1=data_extracted_from_form_field1 or column2=data_extracted_from_form_field

The query has to be built when the form is submitted based on the select fields.
It sounds as though you have the select set with all three fields whether data has been submitted or not.

unfortunately, not familiar with the delphi forms to provide a more concrete example.
0
 
LVL 2

Expert Comment

by:cetafudd
ID: 33686080
This query will do the trick by using CASE statements... When calling the stored procedure and the user did not enter the search criteria you send an empty string...

      SELECT Field1, Fields2, Fieldn
      FROM YOUR_TABLE
      WHERE FirstName =  CASE @FirstName      
                              WHEN '' THEN FirstName
                              ELSE @FirstName
                              END       
      AND LastName =  CASE @LastName
                              WHEN '' THEN LastName
                              ELSE @LastName
                              END      
      AND City =  CASE @City
                              WHEN '' THEN City
                              ELSE @City
                              END      

Please let me know if you have questions...
0
 
LVL 78

Expert Comment

by:arnold
ID: 33686331
He does not want the exclusivity of AND.
The other issue, there should not be a check for an empty strings as that will through off the results.
i.e. if I am looking for firsname="john" or city='orlando' I do not want to see entries for lastname='' i.e. someone entered firstname only.
Is it possible to dynamically create queries in delphi?
0
Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

 
LVL 32

Accepted Solution

by:
Ephraim Wangoya earned 500 total points
ID: 33688148
Try

procedure TForm1.Button1Click(Sender: TObject);
var
  S: String;
  function AddToQueryStr(const ABaseStr, AFieldName, AValue: string): string;
  begin
    if ABaseStr = EmptyStr then
      Result := Format(('%s = %s'), [AFieldName, QuotedStr(AValue)])
    else
      Result := Format('%s OR (%s = %s)', [ABaseStr, AFieldName, QuotedStr(AValue)]);
  end;
begin
  S := '';
  if edtFirstName.Text <> EmptyStr then
     S := AddToQueryStr(S, 'FirstName', edtFirstName.Text);
  if edtLastName.Text <> EmptyStr then
     S := AddToQueryStr(S, 'LastName', edtLastName.Text);
  if edtCity.Text <> EmptyStr then
     S := AddToQueryStr(S, 'City', edtCity.Text);

  Query.Close;
  Query.SQL.Clear;
  Query.SQL.Add('SELECT * FROM ' + TableName);
  if S <> EmptyStr then
    Query.SQL.Add('WHERE ' + S);
  Query.Open;
end;

//where FirstName, LastName and City are your field names
0
 
LVL 24

Expert Comment

by:jimyX
ID: 33692800
Why not using the wildcard ?
var
  ParFName, ParSName, ParCity : string;
begin
  if EdFName.Text <> '' then
    begin
      ParFName := EdFName.Text;
    end
  else
    begin
      ParFName := '%';
    end;
  if EdSName.Text <> '' then
    begin
      ParSName := EdSName.Text;
    end
  else
    begin
      ParSName := '%';
    end;
  if EdCity.Text <> '' then
    begin
      ParCity := EdCity.Text;
    end
  else
    begin
      ParCity := '%';
    end;

  // the SQL code will be:
  QueryDB.SQL.add('Select * from Table1 where FirstName like '+ QuotedStr(ParFName) +' and LastName like '+ QuotedStr(ParSName) +' and City like '+ QuotedStr(ParCity));

Open in new window

0
 
LVL 5

Expert Comment

by:briangochnauer
ID: 33693312
I assume your using like a TADOQuery to get the data so it would be like;
(Changing the field names and table to be valid)

Set ADOQuery1 SQLStrings property to;
  Select * from <table_name>
    where FirstNameField  like :FirstName
       and LastNameField  like :LastName
       and CityField like :City
           

This creates parameters :First Name, :Last Name and :City in the ADOQuery object
Then you just assign the edit fields to the parameters with code something like this;
procedure Button1(Sender:TObject);
begin
    ADOQuery.Close;
  ADOQuery.Parameters.FindParam('FirstName').Value := editFirstname.text+'%';
    ADOQuery.Parameters.FindParam('LastName').Value := editLastname.text+'%';
    ADOQuery.Parameters.FindParam('City').Value := editCity.text+'%';
   ADOQuery.Open;

end.
This is the best I could come up with given the limited information you've given.
0
 
LVL 8

Expert Comment

by:GeneralTackett
ID: 33720972
Are you using SQL with parameters or building the query in code.  if using parameters then the examples with like an d% are good options

if building in code just check for "" and then skip that section as per the example above  
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

679 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question