Dropdown and Lookup are very important attributes in database presentation. Results of queries are rarely displayed as is. The contents is often linked or referenced by a lookup list. In ASP-db, the lookup 'columns' are setup up as independent units and they can be either the master or the slave. Even uneven columns can participate in the lookup as when there is no corresponding values is returned (shorter column) then a blank will be returned. These lookup tables are setup only once in the new session and any operations (Edit, Filter, Display) can perform lookups using them. Note that during lookup, the key (master) list is case sensitive.
The first step is to create lookup lists or Columns. ASP-db.Net provides abundant tools to create such lists -
Each list is created with a name. After the lists are created, all need to be done is to specify the Key and the Lookup. In case of dropdowns in SelectBox, Filter and Edit input. The source and name of the list are the same but only one is needed.
Note: Use dbOptions = "DropFirst=First item Text" to set the first item of dropbox.
|Simple Lookup Example|
The following simple example display the 'employeeid' and 'reportsto' fields looked up as First & Last name. The lookup table is setup using an SQL query. Name of the Employee and FirstLast Name list t is EID and Fullname.
<script language="VB" runat="server"> Sub Page_Load(Src As Object, E As EventArgs) Dim Look As New Tornado.z With Look .dbQP = "U=8| S=Winter| D=NWIND| Q=employees| Gdf=EmployeeId, ReportsTo| Th=Ti=LookUp" .dbCommonTables = "index=EID,FullName|sql=Select employeeid, Firstname & ' ' & Lastname from employees" .dbLookUpFlds = "field=Employeeid| keyindex=EID| lookupindex=FullName, field=ReportsTo|keyindex=EID| lookupindex=FullName" .ASPdbNET() End With End Sub </script>
|SelectBox Form Example|
It is a common practice to select a keyword and then execute the application based on the selected value. The way to select the keyword is a SelectBox. A select-box is used as the master and the Form used as the detail. Note that very little code is required to set this kind of classic applications. The SelectBox-Form mode is preprogrammed to accomplish this. All use need to use is to fill in the data sources. The rest are all automatic.
<script language="vb" runat="server"> Sub Page_Load(Source as Object, E as EventArgs) Dim GD As New tornado.getdata() Dim E15 As New Tornado.Z() With E15 .dbUnit = 15 .dbSkin = "space" .dbMode = "type=SBoxForm|SboxMacro=#0# - #1#" .dbGridDisplayFlds = "CustomerID, CompanyName, ContactName" .dbDSN = "Nwind" .dbSQL = "SELECT * FROM Customers" .dbStatusBar=false .dbPageSize = -1 .dbTextHolder = "Title=Tornado Demo - Master / Detail SelectBox - Form" .ASPdbNET() Dim fs as string = GD.Get_SelectBoxFormSelection if fs <>"" then response.write("<P>Get_SelectBoxFormSelection = " & fs) End with End Sub </script>
|Filter DropDown Example|
This example shows a simple way to make the Filter input box a drop down box containing all the DISTINCT choices of the field.
<script language="vb" runat="server"> Sub Page_Load(Source as Object, E as EventArgs) Dim Mydb As New Tornado.z With Mydb .dbQP = "U=6| S=France| D=nwind| ni=none|th=Ti=Grid Filter Mode With LookUp and DropFirst" .dbMode = "Ty=Grid| FilterStart=t" .dbSQL = "SELECT OrderID, CustomerID, Freight From Orders" .dbCommonTables = "Index=CID| Q=Select DISTINCT CustomerID From Orders" .dbFilterFlds = "Fi=CustomerId| Ty=SB| Val=CID" .dbOptions = "DropFirst=[Select a Customer]" .ASPdbNET() End With End Sub </script>