Solved

SQL search query needed for delphi form with 3 search fields

Posted on 2010-09-15
7
740 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 76

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 76

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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 32

Accepted Solution

by:
ewangoya 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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

705 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now