<script language='vb' runat='server'> Sub Page_Load(Source as Object, E as EventArgs) Dim Mydb As New Tornado.Z() Mydb.dbQP = "U=2| M=ty=dh!sys=t| PS=11| S=plain| D=Nwind.Mdb| Q=Orders| Gdf=0,1| Fmhf=0,1| TH=Title=Search Filter| navigationitem=b5,search| filterflds=0,1" Mydb.ASPdbNET() End Sub </script>
After defining a Dual display and the fields to display in the grid and fields to hide in the form, add the FilterFields and the Search navigation button parameters and the entire Search Filter implementation is done. Execute the live example and click the 'Search' button and you'll notice that all the search setup is ready to go. Note that OrderID is numeric and CustomerID is Text. Try the following actions -
Now, you noticed that this Filter Search implementation is not really 'simple'. The 'simple' means simple to program. A quick question will be - How about search for the Oregon state like State = AZ or State = OR or State = TX? Go ahead and test that by just entering A or OR or B in the CustomerID field and check out the Filter criteria in the status line to see whether it is correct. What about the datetime field wrapper in MS-SQL as they are different than Access? When you specify dbType, these differences will be resolved internally. How about that? These are the questions that has been asked by our customers for the pass 5 years and we honored majority of them. So, what is the chance that you can re-use many of these customer driven features.
|Advanced Search - Field Control|
The following example is packed with advanced features. Just image how much work it'll take you to product an application equivalent to this 17 lines of code or you can do it al all.
<script runat="server"> Sub Page_Load(Source as Object, E as EventArgs) Dim ct As String = "ind=EMPID,FULLNAME|Q=SELECT DISTINCT Employees.EmployeeID," ct &= "FirstName & ' ' & LastName AS FullName FROM Employees;" ct &= "ind=SHIPID,SHIPNAME|sql=SELECT DISTINCT ShipperID,CompanyName FROM Shippers;" ct &= "ind=CUID,CMPNAME|sql=SELECT DISTINCT CustomerID,CompanyName FROM Customers" Dim Fadv1 As New Tornado.z With Fadv1 Dim qp As String = "U=1| D=Nwind| S=Air| M=ty=dh!sys=t| Gdf=0,1| Fmhf=0,1| Ps=12|" qp &= "Q=Orders| Th=tit=Advanced Search| bof=filterdroptoggle=t| ni=b5,filter" .dbQP = qp .dbCalendar = "Excludedates(0)=12/26/2001|12/27/2001| Weeknumbers=true| buttonimage=/tornado/images-net/calendar_new1.gif" Dim ff As String = "field=CustomerID|type=SelectBox+BN|value=CUID|text=CMPNAME| note=Pick a Customer, " ff &= "field=EmployeeID|type=ROFilter| def=4| key=EMPID| lookup=FULLNAME| note=Pick an Employee, " ff &= "field=OrderDate| type=TEXTCALENDAR| tag=SIZE=30| note=Select an Order Date" .dbFilterFlds = ff .dbCommonTables = ct .dbLookUpFlds = "fie=1| key=CUID| look=CMPNAME,fie=2| key=EMPID| look=FULLNAME,field=6| keyindex=SHIPIDLookupindex=SHIPNAME" .dbNameMap = "fie=1|alias=Customer Name, fie=2|alias=Employee Name" .dbFilterSQL = "Select * From Orders where [[Filter]]" .ASPdbNET() End With End Sub </script>
What is going on in this example?
ASPdb.Net filter has a compound entries feature. The following are examples of various format -
|01/02/2001||= #01/02/2001# (Access)|
|>07/04/01||>'07/04/01' (non Access)|
|BETWEEN 1 and 10||>=1 and <=10|
|A or B||LIKE '%A%' OR LIKE '%B%'|
|OR ABC||..... OR LIKE '%ABC%'|
|'Barnes and Nobles'||Enclosed search phrase in single quotes to search for the entire phrase w/o any compound filtering|
|FilterAssistant - Connector and Operator|
The following is the default system FilterAssistant which provides quick choices of the filtering connector and operator in the filter screen. The checked values are default values with or without the Assistant.
|Contains Starts With Exactly|
|Connect all fields using:|
To change the appearance of the Assistant, use -
To set the operator and connector value manually, use -
Both Filter and Edit has a 'Notes' options such that if the keyword is specified then the notes will be placed next to the Input box. Our experience shows that a simple note to tell user how to make a selection or set an edit value often saves a lot of code. See the following example -
<script language="VB" runat="server"> Sub Page_Load(Src As Object, E As EventArgs) Dim x As New Tornado.z x.dbQP = "u=1|q=orders|D=Nwind|Ni=b5,Filter,ResetFilter|gdf=0,1,2" x.dbFilterFlds = "fi=0|Notes=This is Order,fi=1|No=This is customer ID,fi=2|No=Some more notes" x.ASPdbNET() End Sub </script>
How can you offer a selected records set for the user running the application based upon a criteria like a userid? If you use program.aspx?Userid=123 then there is no security at all. dbFilterOnEntry allows you to run the application with the dbSQL criteria set according to the property which is not accessible by the user.
<script language="VB" runat="server"> Sub Page_Load(Src As Object, E As EventArgs) Dim x As New Tornado.z x.dbQP = "u=1|D=Nwind|Ni=b5,Filter,ResetFilter|gdf=0,1,2" x.dbFilterOnEntry = "OrderId > 10260" x.dbSQL = "Select * from Orders" x.dbDebug = "ActiveSQL" x.ASPdbNET() End Sub </script>
|User controlled FilterSQL|
You can have total control of the filtering criteria by defining your own FilterSQL and allow user to fill in just the [[Filter]] tag. See the following example.
<script language="VB" runat="server"> Sub Page_Load(Src As Object, E As EventArgs) Dim x As New Tornado.z x.dbQP = "u=1|D=Nwind|Ni=b5,Filter,ResetFilter|gdf=0,1,2" x.dbFilterSQL = "Select * from Orders where OrderId > 10260 AND [[Filter]]" x.dbSQL = "Select * from Orders" x.dbDebug = "ActiveSQL" x.ASPdbNET() End Sub </script>
|Ucase in Filter|
Have you ever worked in some DB (Oracle?) that is all uppercase and you find out your filter mixed case input is not working.
<script language="VB" runat="server"> Sub Page_Load(Src As Object, E As EventArgs) Dim x As New Tornado.z x.dbQP = "u=1|D=Nwind|Ni=b5,Filter,ResetFilter|gdf=0,1,2" x.dbSQL = "Select * from Orders" x.dbFilterUCaseFunction = "UCase([[field]])" x.ASPdbNET() End Sub </script>
|Select multiple fields in Filter|
This is a new feature (Nov 2007) to enable user to select multiple fields to build a search filter with a criteria of A AND B AND C or A OR B OR C. See the following example code. Filter fields 0 and 1 with 1 is a drop down select box. Tag=Multiple creates the drop down effect. dbFilterConnection = OR condition is set to create A and B and C criteria. Submit the search query and watch the Criteria displayed at th estatus bar.
<script language="vb" runat="server"> Sub Page_Load(Source as Object, E as EventArgs) Dim x As New Tornado.z x.dbQP = "u=1|q=orders|D=Nwind|Ni=b5,Filter,ResetFilter|Gdf=0,1,2,3|TH=tit=Filter Test|fc=OR" x.dbCommonTables = "ind=OID|Q=SELECT DISTINCT OrderID from orders" x.dbFilterFlds = "Fi=0|ty=SB|Val=OID|tag=multiple, 1" x.ASPdbNET() End Sub </script>