Solved

Database filtering...

Posted on 1997-11-15
10
288 Views
Last Modified: 2010-08-05
I want to filter a database acording to some cretiria.
For example i have a database with the following fields:

   -Firstname
   -Secontname
   -Code

 I want to show every record that:
   a)the firstname begins with 'A'
   b)the secontname begins with 'B'
   c)the code is greater than '500'

 I tried to "setrangestart" and "setrangeend" but it doesn' work.
                      help!!!
0
Comment
Question by:harisis
[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
  • 3
  • 3
  • 3
  • +1
10 Comments
 
LVL 4

Expert Comment

by:itamar
ID: 1350546
With Paradox or dBASE tables, these methods work only with indexed fields. With SQL databases, they can work with any columns specified in the IndexFieldNames property.

Provide some code and additional information if it is not the problem, such:

1) What indexes do you have in this table;

2) Did you try the Filter property;

3) Are you using TTable or TQuery;

4) What database (Paradox, Interbase, ...);

                                            ????
IHTH
0
 

Author Comment

by:harisis
ID: 1350547
I use a TTable component with a Paradox table and i have created a secondary index named "secind" with all the field of the table in the above order.
 I have also put key in every field .

 I use the code below:

  With table1 do
   begin
    indexname:='secind';
    setrangestart
    fieldbyname('firstname').asstring:='A';
    fieldbyname('secondname').asstring:='B';
    fieldbyname('code').asinteger:=500;
    setrangeend;
    applyrange;
   end;

 What the filter property has to do with my problem?
 
0
 
LVL 4

Expert Comment

by:itamar
ID: 1350548
The correct usage of SetRangeStart and SetRangeEnd must be:

With Table1 do begin
    IndexName := 'secind';
    SetRangeStart; //Starts setting the starting values
    FieldByName('FirstName').asstring := 'A';
    SetRangeEnd; //Starts setting the ending values
    FieldByName('FirstName').asstring := 'A' + #255; // All A's
    ApplyRange;
end;

The filter has everything to do with your problem! Try for example:

With Table1 do begin
    Filter := 'Code>500';
    Filtered := True;
end;

This will solve part of your problem. You'll still have some troubles cause when using composite indexes, it seems that you must provide exactly the first part of it to get the second range, for example:

With OtherTable do begin
    IndexName := 'DeptSal';
    SetRangeStart; //Starts setting the starting values
    FieldByName('Department').asstring := 'XPTO';
    FieldByName('Salary').asinteger := 1000;
    SetRangeEnd; //Starts setting the ending values
    FieldByName('Department').asstring := 'XPTO';
    FieldByName('Salary').asinteger := 5000;
    ApplyRange;
end;

So, AFAIK (As far as I Know), it's better using TQuery to get the better results in your case. It will take the advantages of the index in a simple way, like:

SELECT *
FROM MyTable
WHERE FirstName LIKE 'A%' AND SecondName LIKE 'B%' AND Code > 500

I know it's a workaround, that's why it's a comment not an answer (the first one was supposed to be the same - Sorry). I also have some hope that other experts can provide a solution to use Ranges with composite indexes. Otherwise, i'd like to submit this comment as an answer since we can only give answers, but they are  not always solutions.

----[S.M.I - Sori mi inglige] ---------

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 6

Expert Comment

by:tecbuilder
ID: 1350549
Just use the table's filter property.

codevalue := 500;
fnamevalue := 'A';
lnamevalue := 'B';

table1.filtered := false;
table1.filter := 'table1firstname.Value >= ' + '''' + fnamevalue + '''' +
' and table1lastname.Value < ' + '''' + lnamevalue + '''' +
' and table1code.Value = ' + '''' + IntToStr(codevalue) + '''';
table1.filtered := true;

Since the table is now filtered, you can only access those records that meet the criteria.  If you use the table's RecordCount function (table1.RecordCount) the returned value will be the number of records that meet this critiria as well.
0
 
LVL 6

Expert Comment

by:tecbuilder
ID: 1350550
Oops, I mis-read what you wanted to filter on.  The code should be as shown below with some additional explanation at the end.

codevalue := 500;
fnamebegin := 'A'; fnameend := 'B';
lnamebegin := 'B'; fnameend := 'C';

table1.filtered := false;
table1.filter :=
'firstname >= ' + '''' + fnamebegin + '''' +
'firstname < ' + '''' + fnameend + '''' +
' and lastname >= ' + '''' + lnamebegin + '''' +
' and lastname < ' + '''' + lnameend + '''' +
' and code > ' + '''' + IntToStr(codevalue) + '''';
table1.filtered := true;

The problem that you raise is how to select all the names that begin with A for firstname and B for lastname.  There is no way to access just the first character directly from the record when filtering or setting the range.  So you have to declare the begin and end for what you want to select.  I have hard coded these variables, but they can be easily modified so that all a user has to do is select a value to fill the fnamebegin variable and the fnameend variable can be generated by what ever function you decide.
0
 
LVL 4

Expert Comment

by:itamar
ID: 1350551
As i had put in my comment you can really use the filter property to get the results, but be very carefull !!!
Filter doesn't do magic ! If you have a large table, it will not be efficient ! The best solution is use TQuery or mixing the SetRange... methods with Filter property.
I can provide some example if you reject tecbuilder's answer ( indeed it's really an example over one of the options in my comment)
I know you are goint to be fair ;)
0
 
LVL 6

Expert Comment

by:tecbuilder
ID: 1350552
harisis, I suggest using the filter option first.  It's very simple, easy to use, and you don't have to worry about updating your tables since the filter works on the table itself.

The SetRange properties are no faster than a filter when used on a TTable.  It works exactly as a filter.  The only difference being that the SetRange requires you to use the table's key fields to limit what you can see and the filter doesn't.  Also, when you want to go find a record you have to use the FindKey or SetKey..GotoKey methods which require you to only use the keyed fields.  The filter can be set via any field whether it's keyed or not.  With a filter to find a record you use the Locate method and again what you look for doesn't require the fields to be keyed.

If the filter doesn't do the job that you need it to do, then go with a TQuery.  TQuery is powerful because it allows you to use SQL.  However, it's also a bit more difficult since it works on a subset of the original table.
0
 

Author Comment

by:harisis
ID: 1350553
  Sorry ! I use Delphi 1 (client/server) and it doesn't recognize the filter property for a TTable.( or am i doing something wrong here?)

  If i use TQuery can i have the same functionality as if i use a TTable?
0
 

Accepted Solution

by:
Wilfred earned 300 total points
ID: 1350554
A TQuery  can be accessed the same way as a TTable component. So this could be a solution.
You could also use the OnFilter event of a TTable. It's a event in which you can set a return value (accept) to include or exclude the record in your result set.

0
 

Author Comment

by:harisis
ID: 1350555
 I will use a TQuery finally... So the points go to Wilfred (he is the lucky one) Thank you all folks...
0

Featured Post

Enroll in June's Course of the Month

June’s Course of the Month is now available! Experts Exchange’s Premium Members, Team Accounts, and Qualified Experts have access to a complimentary course each month as part of their membership—an extra way to sharpen your skills and increase training.

Question has a verified solution.

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

This article explains how to create forms/units independent of other forms/units object names in a delphi project. Have you ever created a form for user input in a Delphi project and then had the need to have that same form in a other Delphi proj…
Hello everybody This Article will show you how to validate number with TEdit control, What's the TEdit control? TEdit is a standard Windows edit control on a form, it allows to user to write, read and copy/paste single line of text. Usua…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

707 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