ecDBTable is designed as an ASP/VBScript class library for programmers that allows you to add database table displays with navigation to your web pages. Currently ecDBTable works only with Access .MDB database files. It has been designed to allow for a great deal of flexibility in displaying data through the use of templates for the data layouts. We believe that the template approach is much more flexible and visual, when it comes to designing your data displays compared to systems where you just set a bunch of properties to determine your design.
ecDBTable also includes special dynamic macro functions which may be imbedded into your templates to provide modification to the output at runtime. Macros functions include logical and custom function execution abilities.
IMPORTANT! – Read this entire document carefully to understand the various approaches you can use to creating your program code and designing templates. Explore the examples carefully before trying to implement ecDBTable in your own applications to insure your best ability to use ecDBTable with success.
With this version of ecDBTable we are including an extra experimental class library file, ecUPDLib.asp. ecUPDLib was built to demonstrate a method by which ecDBTable can be used to update data in your database (example 10). ecUPDLib is not currently considered a part of ecDBTable and is provided FREE of charge but is not officially supported.
Required Files: The only files required by your ASP pages are ecDBTLib.asp and ecDBTFunc.asp Note: ecDBTFunc.asp is not required if you are not using the {EXEC} macro function, described later on. If you do not use this file you must remove the include statement at the end of the ecDBTLib.
ecDBTable is licensed on a per developer basis. You, the registered licensee of ecDBTable may install the code on any server where you are using the ecDBTable code library for development or deployment of a web site application you have built. You may not give the code to anyone else for the purpose of their development of web sites or any other products. You may not use the code library to develop any product which would directly compete with ecDBTable. We reserve the right to revoke your license to use ecDBTable library for further development should you violate this license agreement in any form. The original source code and intellectual property rights remain with NCC Technology Group. The included experimental library file, ecUPDLib.asp, is also covered by this same license policy.
|
Name |
Description |
|
sConn |
Connection string for you database access. Example: Provider=Microsoft.Jet.OLEDB.4.0;Data Source=”c:\yourdatapath\products.mdb" or Source=server.mappath(“products.mdb”) |
|
sSql |
The sql statement string used to query the database. Example: select id,sku,title,price from products |
|
bShowSql |
If set to true will display your sql statement at the beginning of the generated output. Used for debugging. |
|
Navigation |
|
|
sNavBarTpl (Optional) |
Template string to be populated by results of generating the navigation bar. The template must contain some of the following tokens which will be replace by the appropriate values at run time. Example: Page #crntpage# of #pagecount# Total Rows: #recordcount#<br>Navigate: #navprior# #navpages# #navnext# #maxmsg# (this is the default nav
bar layout) Token Descriptions: #crntpage# - the current page number displayed #pagecount# - number of total pages based on iDbPageSize property setting for rows displayed #recordcount# - total rows in sql select result #maxmsg# - if used displays a message based on iNavBarMaxLinks value and optional sMaxLinksMsgTpl #navprior# - displays the prior page link #navpages# - displays the numbered page links based on current page and iNavBarMaxLinks setting #navnext# - displays the next page link |
|
sNavBarLocation |
Determines where the navigation bar is displayed. May be any of the following: TOP|BOTTOM|BOTH|NONE Example: sNavBarLocation=”BOTH” |
|
sNavFirstLink |
Determines display for first page link; default is << |
|
sNavPriorLink |
Determines display for prior page link; default is < |
|
sNavNextLink |
Determines display for next page link; default is > |
|
sNavLastLink |
Determines display for last page link; default is > > |
|
|
NOTE: nav links may be any text string or you may use an image tag; i.e. sNavFirstLink=”<img src=images/first.jpg>” could be used for the first link. |
|
iNavBarMaxLinks |
Determines the number of numbered page links to display. By default ecDBTable will display all page links. Use this to reduce the number of displayed links if your data has a high number of pages. |
|
sMaxLinksMsgTpl |
You may specify an alternate display format template string for the #maxmsg# navigation bar token. The default is; “(viewing ?? page links at a time)” where ?? is replaced by the iNavBarMaxLinks value. If you create your own template you must put the ?? in it to display the max links value. NOTE: to not display this message at all set the property as follows: sMaxLinksMsgTpl=”HIDE” |
|
sNoDataMsg |
Message to display if no data is returned by your sql select. The default is: “<b>Request did not return any data...</b>” |
|
bNoData |
If not data is returned this flag will be set to true. If you wish to not display the default message above set sNoDataMsg=”” and handle the no data situation by evaluating the state of bNoData after dbtBuild is called. |
|
iDbPageSize |
Determines the number of rows to display per page; default is 10 |
|
|
|
|
Templates |
NOTE: ecDBTTable has several ways that you may determine the layout of the generated tables. These will be described in the Templates Section below. |
|
sTblAttr (Optional) |
A string that holds any table attributes example; bgcolor=green cellpadding=3 width=100% |
|
sHdrTpl (Optional) |
Applies to the <TD> for each column header row Format example: <td bgcolor=#CCCCCC>##</td> where the ## is a placeholder for the column header text (note this is the default if none is provided) |
|
sRowTpl (Optional) |
Applies to the <td> for each row/column data value Format example: <td bgcolor=white>##</td> where the ## is a placeholder for the row/column data value (note this is the default if none is provided) |
|
sAltRowTpl (Optional) |
Format is the same as sRowTpl, if set will be used to display format for every other row (alternating row) |
|
sOddColBg (Optional) |
If set will provide the bgcolor value for odd numbered columns (Note: if you set this one you must also set sEvenColBg and place a token #oddevenbg# into your Hdr and/or Row templates) |
|
sEvenColBg (Optional) |
If set will provide the bgcolor value for even numbered columns |
|
sOEStdColBg |
This color value should be set if you are using the iOddEvenStartCol property described below. |
|
iOddEvenStartCol |
This property determines the column number at which the odd/even color properties described above will begin at, by default it is 1. If set to a number greater than 1 then you must set the sOEStdColBg value above. |
|
sFtrTpl (Optional) |
The sFtrTpl has been left optional and is only required if you would like to place a footer row at the end of your table. If used it must look similar to this; <tr><td colspan=(number of columns you have)>Your footer message here</td></tr>. IMPORTANT: if you do not correctly format this template you may create invalid, improperly formatted table html. |
|
sTplFileName |
If used this file will override the default formatting by extracting the sTblAttr, sHdrTpl, sRowTpl, sAltRowTpl, sFtrTpl values from the file (see ecDBTTpl.htm example file) |
|
sTplFreeFormName |
If used this template uses a free form approach to displaying your data. With this format your template is specified by using tokens like this in the <ROW> section; #fieldname# (see ecDBTFFTpl.htm example file) Note: free form templates greatly enhance the flexibility in what you can do with your tables as are shown in our example files. |
|
Other Properties |
|
|
sMapFlds |
If you are using the standard sHdrTpl to display your table you may create a list of alternate headings for your field names. The format for the list is; <field name>:<alternate header text>,etc. Example: sMapFlds=”id:Item ID,sku:Item SKU” |
|
sAddLink |
Adds extra link(s) value to navigation link. Example: view=std or for multiple link values view=std&cat=1 (do not put an & at the end) |
|
sHighlightFlds |
You may provide a list of word, fields and color style used to highlight the word in the the field data. For example; if you wanted to highlight any occurrence of the words computer in the fields named title and description by setting a background color using a style on the font tag, you would set this property like this. The format for this property is: sHighlightFlds=”<field name>|<search word>|<css in-line style>” Example: sHighlightFlds=”title|computer|background-color : Aqua;~description|computer|background-color : Aqua;” NOTE: if you wish to highlight in more then one field you
must use the ~ character to separate your list of fields to highlight |
|
sNumericFlds |
This property is a list of field names that will automatically be formatted as numeric using the decimal setting in iNumericDecimal Example: sNumericFlds=”price,cost” |
|
iNumericDecimal |
Number of decimal places to display for values for sNumericFlds, default is 2 |
|
sCurrencyFlds |
This property is a list of field names that will be automatically be formatted as currency using the prefix (if any) in the sCurrencyPre property, it always uses 2 decimal places. Example: sCurrencyFlds=”price,cost” |
|
sCurrencyPre |
This property can be set to the character to precede the currency field value. Example: sCurrencyPre=”$ “ would display $ 24.95 |
|
bDisableAutoFormatOnDetail |
Set this property to true if you do not want the auto formatting to be applied to detail displays. NOTE: this is useful if you are using the detail display for editing purposes as we have done in Example 9. |
|
Sorting |
ecDBTable provides two methods of sorting your display. The first uses a dropdown list, the second uses links at the top of your table columns. See the Sorting Section for more information. |
|
sSortFlds |
This is an optional list of field names to be used to create a dropdown list to sort your table by. You must set this property before you call dbInit. See the SortBy Dropdown section for details. Example: sSortFlds=”price,cost” Example 4 uses this sort dropdown. Note that the dropdown list will display the field names in the case as entered i.e.; Price, price or PRICE. |
|
sDefaultSortFld |
This is an optional property to set the intial sort by when using sSortFlds above. Example: sDefaultSortFld=”price ASC”. Note that you should indicate the ASC (ascending order) or DESC descending order for the default. |
|
sSortTitle |
This is an optional title you may set which will be placed before your dropdown list. The default is “Sort By:” if no value is given for this property. |
|
sSortByDDForm |
This property will contain the form html for the sortby dropdown list after dbInit is called. You may use this property to place the dropdown form anywhere you wish on your page. Note: a special token is available, #sortbyform# which you may use when using the freeform template method (see below). |
|
sSortUpText |
Use this property to override the default ascending sort text “Up” for column based sorting. Here is an example where we set up images to be used for the sort links. Example: sSortUpText=”<image alt=””Sort Ascending”” border=0 src=up.gif>” Example 8 uses this column sort ability. |
|
sSortDownText |
This property is like the above only it can contain the link image or text for the descending sort link. The default is Down. See Example 8 for details on its use. |
|
sSortTextDlm |
This property is by default, it is used to place a delimiter between the sort up and sort down links. Example: sSortTextDlm=” | ” |
|
Searching |
|
|
sSearchFlds |
This is an optional list of field names to search in if you are using the simple search ability of ecDBTable. If set a search input box will be created and when a search value is input the sql will be modified to use the like ‘%searchvalue%’ syntax. For example; if you set sSearchFlds=”name,description” and entered a search value of ‘meta’ your sql would look like this; name like ‘%metal% or description like ‘%metal%’. |
|
sSearchTitle |
This is an optional title you my set which will be placed before your search input box. The default is “Search:” |
|
bAutoSortSearchForm |
This property is set to true by default. When true both the sort and the search are automatically wrapped in their own <form> tags. If you prefer to place both the sort and search into a single form you must set this to false and place your tokens inside a form in your template as we have done in example 6. You must set this property before calling dbInit or it will not override the default. |
|
sSearchByForm |
This property will contain the form html for the searchby input box after dbInit is called. You may use this property to place the form anywhere you wish on your page. Note: a special token is available, #searchbyform# which you may use when using a freeform template method (see below). |
|
sExportFldsList |
You can use this property to export field values for the current row to session variables during rendering of the output table. This allows you to use these values in other portions of your programming as we have done in example 7. Example: sExportFldsList="imagefile,description" Setting the above will create two session variables; dbt_imagefile and dbt_description
(the session variables are automatically prefixed with dbt_
) |
|
Methods |
|
|
AddMapColTpl |
This method can be used to create an alternate to the sRowTpl/sAltRowTpl (described below). |
|
AddMapSortDDTitle |
This method is used to change the titles used for the sortby dropdown list. By default the dropdown names are based on the sSortFlds field names with either an ASC or DESC appended to the name. For example: if sSortFlds=”Price” you would see “Price ASC” displayed. To change this you can enter the following in your code: AddMapSortDDTitle “<word to change>:<new word>. For example: AddMapSortDDTitle “ASC:Ascending” AddMapSortDDTitle “Price:The Price ” …would show this in the display: “The Price Ascending” |
|
PageReset |
This method is used internally to reset the session variables used to maintain the current page number, current row number, current sortby and current searchby values. If ecDBTable code is run from a page name that is different from the last run page this method is executed. You may call this method in your code if desired or you may send a querystring or form post parameter, for example; dbt_pagereset=true to trigger execution. (See the Page Navigation section below for more details) |
|
AddRptToken |
This method is used to add a runtime replaceable token into one of your display templates. This is an advanced feature and is explained in the “Replaceable Tokens” section later on. Example: AddRptToken “rpt_cat=BOATS” |
ecDBTTable provides three ways to specify the design layout used to create your data table. Each method will be described below.
Method 1 – Using built in properties to set table attributes, header column and data row/column formats.
This method uses the following built in properties; sTblAtrr, sHdrTpl, sRowTpl, sAltRowTpl, sFtrTpl
Property: sTblAttr – this property is used to set the attributes you want used when the table is generated. Your specified text will be replaced into the table tag like this: <table (your attributes go here)>. By default there are no attributes set for this property.
For example; if you set sTblAttr=”width=500 cellpadding=2 cellspacing=0” you would generate the following at runtime; <table width=500 cellpadding=2 cellspacing=0>
Property: sHdrTpl – this property contains the <td> format template for the field name (or mapped name if sMapFlds property has been set) and by default is set as <td bgcolor=#CCCCCC>##</td>. Note the use of the ## token, this token must appear in your template as it is replaced by the actual field name data.
For example; if you set sHdrTpl=”<td bgcolor=red><font color=white>##</td>” you would see your headings with a red background with white text.
Property: sRowTpl – this property represents the <td> format template that will be used to display the field data value for each row being displayed. The default format is <td bgcolor=white>##</td>. Modify this template as described in sHdrTpl.
Property: sAltRowTpl – this property is optional and if used will provide the template for formatting for every other (alternate) row being displayed. Formatting is the same as sRowTpl (there is no default value for this property).
Additional properties you may use with this method:
(demonstrated in example2)
Property: sOddColBg – this property allows you to set a background color value for odd numbered columns.
Property: sEvenColBg – this property allows you to set a background color value for even numbered columns.
Note: if you use either of the above two properties you must use both, you must also place a special token into your <td> templates in sHdrTpl, sRowTpl and sAltRowTpl which will be replace by the property color value during runtime. The token you must is #oddevenbg#. For example: sRowTpl=”<td #oddevenbg#>##</td>”
Property: sOEStdColBg – this property is used along with the next property iOddEvenStartCol. It should be set to the bgcolor value for all columns prior to the first alternating odd/even column. Example: #99CCFF
Property: iOddEvenStartCol – this property determines which column starts the odd/even column colors, it defaults to 1. If set to a value greater then 1 you must use the sOEStdColBg.
Hint: if you would like to color several columns at the beginning of your table to one color and the remaining columns to another use the odd/even properties with sOddColBg and sEvenColBg set to the same color value!
Method 2
Method 2 uses the same properties as Method 1 only the properties are stored externally in a text file. To use this method you do not set the sTplAttr, sHdrTpl etc. properties in your code but instead you set the sTplFileName property to the name of your external template value file, for example; sTplFileName=”ecDBTpl.htm” as in our example1/method2 sample program template.
The format of the file should look like this (note the lines starting with a ; are just comments, tags must be in upper case):
; put table attributes here
<TBL_ATTR>cellspacing=1 bgcolor=red
</TBL_ATTR>
; put td settings for header row here, place ## marker for data between td
<HDR><td bgcolor=tan>##</td>
</HDR>
; put td settings for data row here, place ## marker for data between td
<ROW><td bgcolor=white>##</td>
</ROW>
; put td settings for alternate data row here, place ## marker for the data between td
<ALT_ROW><td bgcolor=#CCCCCC>##</td>
</ALT_ROW>
; put any table html for a footer row here (optional, must include the starting/ending <tr></tr>)
<FTR>
</FTR>
; odd column bgcolor value
<ODDCOLBG>#99CCFF</ODDCOLBG>
; even column bgcolor value
<EVENCOLBG>#6699CC</EVENCOLBG>
; bgcolor for columns prior to ODDEVENSTARTCOL below
<OESTDCOLBG>#CCCCCC</OESTDCOLBG>
; first column to start using odd/even bgcolor values
<ODDEVENSTARTCOL>4</ODDEVENSTARTCOL>
Overriding a column
template
ecDBTable provides a method that allows you to override the column formatting described in method 1 & 2 above. The method is called AddMapColTpl and is used as follows:
AddMapColTpl “price”,”<td bgcolor=#cc9933>$ ##</td>”
When the table is created for each row the field price will use the above template value instead of the sRowTpl or sAltRowTpl templates. Our Example 5 demonstrats the use of this method. This method has no effect when using freeform templates as described in Method 3 below.
Method 3
Method 3 is the most flexible of our methods as it uses a freeform column by column template approach for the layout of each row of data. To use this form you set the sTplFreeFormName property to the file name containing your template. For example; sTplFreeFormName=”ecDBTFFTpl.htm” as used in our Example1/Method3 demo.
The template file contains three tag pairs for each part of our output; the <HDR> (header) tag, the <ROW> (template for each data row) tag, the <ALTROW> (template for alternate rows) and the <FTR> (footer) tag. The HDR tag content will be placed at the beginning our your output, the ROW at the end and may contain any HTML that you wish. IMPORTANT – the freeform template identifies output by field name tokens place into your output (ROW) template that look like this; #fieldname#, for example; #price# would be a token for a field name price in your data.
Below is the ROW template section from our example ecDBTFFTpl.htm file:
<ROW>
<table width=700 class=lstTbl><tr><td width=210>
<img src="images/#imagefile#">
</td>
<td valign=top>
<table width=100%>
<tr><td width=150 class=lstHdrCell>id</td><td>#id#</td></tr>
<tr><td width=150 class=lstHdrCell>SKU</td><td>#sku#</td></tr>
<tr><td width=150 class=lstHdrCell>Price</td><td>#price#</td></tr>
<tr><td width=150 class=lstHdrCell>Name</td><td>#name#</td></tr>
</table>
</td></tr></table>
</ROW>
NOTE: if you look at the ecDBTFFTpl.htm file you will see that in our example the HDR or FTR tag sections do not contain any of the table HTML (though they could). Also note that these sections are not required if you choose not to use them. In our above ROW sample code you will see that we have created a table to hold each row of data. How you structure your freeform templates is entirely up to you as long as final HDR + ROW + FTR output will create valid HTML.
Special FreeForm Template
Tokens
The freeform template supports a special token, #sortbyform#, which may be used to place the sortby dropdown form if you are using the sSortFlds property. You would normally place this token in the <HDR> section of your template file as we have done in Example4. Other tokens are described later on.
In some web sites you may be using multiple pages with ecDBTable code on them. ecDBTable uses several session variables to keep track of current page, sortby and searchby values. If you call another page with; a different number of pages, different sortby or searchby values, the most recently saved (session) values will most likely be incorrect. To deal with this ecDBTable incorporates an automatic page reset feature. ecDBTable monitors via a session variable named dbt_LastPage the current asp page name that you are running from and if that name changes calls an internal PageReset method. This method clears the appropriate session variables to the same state as the page when it initially loads. The current page is set to 1, the sortby and searchby values are set to blank and the current row value is set to 1. To disable this feature you must set the bAutoPageReset property to false in your page code before dbInit is called. You may also force a page reset by sending a url parameter from your querystring or form post. For example; mypage.asp?dbt_pagereset=true. The session variable cleared by this method are: dbtCrntRow, dbt_page, dbtsearchby, dbtsortby.
ecDBTable has several macro functions that allow you to imbed custom logic into your grid displays. Each will be explained separately below.
NOTE: in the current version of ecDBTable the macros are processed in this sequence: {EXEC}, {MATCH}, {IFB}, {IIF}. You may nest the macros inside of each other as long as the innermost macro comes first in the prior listed sequence. This means you could nest a {EXEC} macro inside of an {IIF} not the other way around. If you do not nest your macros it does not matter. You can have as many macros as you like in your templates.
{IIF}
Format: {IIF}<logical statement>|<true response>|<false response>{/IIF}
The IIF macro is a logical if/then/else macro. You can use it to perform custom formatting of your grid displays by imbedding the macro into your templates. For example; lets say you wanted to display values in blue if the are over 5000 otherwise just show the value. Assuming the column name is price, your macro would look like this:
{IIF}#price#>5000|<font color=blue>#price#</font>|#price#{/IIF}
Note: if the data type of your data column is text you need to enclose both the token and your comparison value in double quotes like this:
{IIF}”#category#”=”BOAT”|#category#<br><font color=red>** FEATURED **</font>|#category#</IIF}
{IFB}
Format: {IFB}<column value>|<if blank response>|<if not blank response>{/IFB}
The IFB macro is an if blank macro targeted at performing an operation if the column value is blank. For example; if you have a column called picture that may or may not contain the name of a .gif or .jpg image file, you could use IFB to determine what to display if the picture column is empty (instead of the problem of what to do with the broken img link you might otherwise generate). The IFB macro could look like this where we display some text if there is no picture otherwise we create an img tag.
{IFB}#picture#|<font color=red>[NO PICTURE]</font>|<img src=images/#picture#>{/IFB}
{MATCH}
Format: {MATCH}<column value>|<match value list>|<replace with list>{/MATCH}
The MATCH macro is similar to a programming CASE statement. The column value is compared to the match value list and if a match is found the ‘replace with list’ value is used for the item in the same list postion.
For example; if we set up the following match macro – {MATCH}#priority#|HIGH,MEDIUM,LOW|<font color=red>??</font>,<font color=yellow>??</font>,<font color=green>??</font>{/MATCH}. The value for the field named priority would be checked. If it contained HIGH the output created would look like this: <font color=red>HIGH</font>. Note that the ?? in our ‘replace with list’ value is replaced with the value of the priority data column. The ?? is a special marker you can use in your output template to tell the program to replace the token with the column value at runtime. In this example if your replacement template for HIGH was <font color=red>HIGH</font> your results would be the same. You do not have to use the ?? if you do not want to. Your template can contain anything such as image tags for example; <img src=images/highpic.gif> is an acceptable value in the ‘replace with list’.
{EXEC}
IMPORTANT! – though not a specific requirement in most cases the {EXEC} macro would only be used if you are using a freeform template. Placement of this macro in other templates would cause the macro to execute for each data field column.
The EXEC macro is the most powerful one yet. This macro allows you to call your own user built vbscript (ASP) functions. You must place your functions into the file named ecDBTFunc.asp as this file will be automatically loaded into the ecDBTLib.asp library file using an include statement making them available to your calls.
Format: {EXEC}<function name>|<parameter list>{/EXEC}
To show how this works lets look at the use of the sample function we include by default in the ecDBTFunc.asp stub file, FormatAsDollars. This function displays the passed value (price) in dollar format and a color value. This function is used in our Example3. Note: that parameter lists are comma separated and must be entered in order they are used in your function. String values must be placed inside of “ “ (double quotes).
Here is what the function looks like:
public function
FormatAsDollars(sVal,sColor)
if isnumeric(sVal)
then
sVal=FormatCurrency(sVal,2) & "
(US dollars)"
end if
FormatAsDollars="<font color=" & sColor & ">" & sVal & "</font>"
end function
Here is what your macro call would look like in your grid template:
{EXEC}FormatAsDollars|#price#,”#0099CC”{/EXEC}
The execute macro will create this vbscript (asp) call during execution if the value of price is 4500.00 and color is #0099CC:
InternalReturnval=FormatAsDollars(“4500.00”,”#0099CC”)
Your functions can perform any operation you like but they should return a string value (since the result is being inserted into the html being written). Though you could actually write functions that perform any function remember that your macro will be called on every row displayed and therefore you could experience performance issues, if for example you did some type of lookup to a large database etc.
Note: These abilities are all demonstrated in the provided example files.
SPECIAL NOTE: If you use the SortBy abilitiy of ecDBTable you must not specify any ‘order by’ clause as part of your sSql property value. ecDBTable appends the generated order by clause to your sSql property value therefore having an order by clause already part of your sql will cause an error. To specify a sort order use the sSortFls and sDefaultSortFld properties described below or do not use the sortby form or coloumn sort features and handle the sorting in your ASP page code.
Method 1 - SortBy Option
ecDBTable will automatically generate a sortby dropdown list form. To initiate the creation of the dropdown list you must enter a list of fields into the sSortFlds property.
Example: sSortFlds=”price,cost”
You must do this before calling dbInit. The resulting dropdown form generated by dbInit is placed into the sSortByDDForm property and may be used in two ways. If you wish you can display the contents of this property using your own response.write statement. If you are using a freeform template as described above you may place a special token into your template file; #sortbyform# which will be replaced with the contents of the sSortByDDForm property when the table is generated.
The values of the dropdown list will look like this (based on our example here);
price ASC
price DESC
cost ASC
cost DESC
The values of the list when selected are appended to the end of your sSql property value. IMPORTANT – if using the sSortFlds property DO NOT put your own order by clause on your sql statement.
If you prefer you can simply create your own custom sort handling in your ASP code and assign the order by clause to your sSql property value. If you want to take advantage of the #sortbyform# token handling place your custom sort html into the sSortByForm property before calling dbtBuild.
By default the title placed before the dropdown list is “Sort By:”. You may override this by using the sSortTitle property to assign your own title. You MUST do this before calling dbInit as noted above. For example; sSortTitle=”<font color=red>Sort data by: </font>”
Method 2 – Column
Sorting
You may also automatically create ascending and descending sort links at the top of your table columns using special built in sort tokens. The sort tokens look like this; #sortby_price#. As you can see they are simply the field name preceded by the sortby_ prefix.
By default the sortby text is ‘Up’ for ascending or ‘Down’ for descending. You may alter this using your own text or even images by simply setting the desired value using the sSortUpText or sSortDownText properties. Example 8 shows the use of column sorting using both the defaults (Up/Down) and with custom images for the links.
As with the dropdown sorting method, DO NOT put your own order by clause on your sql statement.
With this method you may also put the #crntsortby# token in your template to display the currently selected sort order. This is also shown in Example 8.
Setting SortBy default sort order
By default, even if you have set the sSortFlds property, no sorting is applied when the table is first displayed. Users will need to select a sort order to sort the displayed data. If you wish to have the data sorted by one of your sort fields use the sDefaultSortFld property to set the initial sort order. For example: sDefaultSortFld=”price ASC” (include either ASC or DESC along with your field name). Note that you should also set this property value before dbInit is called.
ecDBTable will automatically generate a searchby input box form. To initiate the creation of this form you must enter a list of fields into the sSearchFlds property.
Example: sSearchFlds=”name,description”
You must do this before calling dbInit. The resulting search input box generated by dbInit is place into the sSearchByForm property and may be used in two ways. If you wish you can display the contents of this property using your own response.write statement. If you are using a freeform template as described above you may place a special token into your template file; #searchbyform# which will be replaced with the contents of the sSearchByForm property when the table is generated.
If you prefer you may create your own custom search handling and append the where clause to your sql statement. If you want to take advantage of the #searchbyform# token handling place your custom search html into the sSearchByForm property before calling dbtBuild.
How the search works
If you use this feature ecDBTable will dynamically modify your basic sql statement by adding a ‘like’ statement for each field in sSearchFlds. In our example above you would see the following; where (name like ‘%metal%” or description like ‘%metal%’), if your search value was ‘metal’. Obviously this is a simple search and would not be efficient for large data tables. Note, if your sql statement already contains a where clause the new where clause will be place into the sql statement before the existing where value and use an ‘and’ to connect the statements. In our above example if the sql already had “where cat=5” the resulting statement would read like this: where (name like ‘%metal%” or description like ‘%metal%’) and cat=5.
Replaceable tokens are an advanced topic but once understood can prove to be valuable in your ecDBTable page development. A replaceable token is simply a specially named token that may be placed into your templates using any of the supported template methods. Replaceable tokens are replaced at runtime by a value before any other processing occurs.
A replaceable token looks like this: #rpt_myname# (note that a replaceable token must begin with “rpt_”).
The source of a replaceable token’s value can come from one of three sources:
1) from a querystring parameter like this; mypage.asp?rpt_myname=John.
2) from a input variable in a form post like this; <input type=hidden name=”rpt_myname” value=”John”
3) or finally, by using a provided method; AddRptToken which is placed in your page code before dbInit is called. For example: AddRptToken “rpt_myname=John”.
Though you may never find the need to use this feature it allows you to extend the programmability of your templates.
As a simple example, lets say your web page has an sql statement that looks like this:
select * from product where cat=’” & SelectedCat & “’”
In this example the variable SelectedCat is provided by your program code. Now let’s say you wanted to put that SelectedCat value into the heading of an ecDBTable freeform template. Here is what you could do:
First place a token into your template, perhaps like this: <b>Selected Category: #rpt_cat#</b>.
In your page code, before dbInit is called, put this statement:
oDbt.AddRptToken “rpt_cat=” & SelectedCat
Now when your page runs your category value is dynamically placed into the template. See how this might be of help? Replaceable tokens can be used anywhere in a template including inside of Macro Functions. How you might use them is up to your own creativity.
NCC Technology Group provides no warranty as to the suitability or usability of the ecDBTable product for any purpose. Use at your own risk, we assume no liability of any kind for this product.
There, that said and done… for support email: support@ncctg.com. Please note that we do not support changes made to the ecDBTLib.asp file.