Solved

SQL search query needed for delphi form with 3 search fields

Posted on 2010-09-15
7
745 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
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

Why You Need a DevOps Toolchain

IT needs to deliver services with more agility and velocity. IT must roll out application features and innovations faster to keep up with customer demands, which is where a DevOps toolchain steps in. View the infographic to see why you need a DevOps toolchain.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Need multiple Group By's 8 55
How to add an Index to a date/time field? 15 41
ms sql + help with query 2 44
T-SQL: How to append a column for serialized JSON data? 2 49
As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

732 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