Q38. How Can I Perform Data Validation During Filtering and Editing?

ASP-db FAQ

revised October 15, 1998

Q38. How can I perform data validation during filtering and editing?

A. ASPdb provides 3 options in input data validation.

Automatic type checking. This is a built-in feature and is not under the control of user. Text, numeric and date types are validated after the “Action” button is clicked.

Client-side JavaScript validating. Use the following keyword to set the JavaScript function name –
X.dbEditParams = "EditValidateName=checkEdit()"
X.dbFilterParams = "FilterValidateName=CheckFilter()"

The function must return a true value for ASPdb to proceed with the edit and filter operations.

Server-Side data validation. Users can intercept the click of the filter and edit “Action” buttons and perform server-sided data validating via the ASPdbClick_Unit variable at the beginning of the asp program. See Q34 for details. Validation algorithm can be in any language including a DLL.

Q37. Does ASPdb support quoted identifiers in SQL?

A. If a QUOTED_IDENTIFIER is needed, the user should use the following setup –

dbSilentCmdText=”SET QUOTED_IDENTIFIER ON”
dbDBType=”SQL”

If a field name with an embedded space is detected, it’ll be wrapped with chr(34).

Note: Every book of the trade recommends not to use “unconventional” field names (spaces, question marks, “>” signs, etc.).

Q36. How can I specify the database is of ACCESS or SQL type?

A. The following options will set the database type to ACCESS or SQL. Appropriate field syntax will be set accordingly.

dbDBType=“ACCESS (default) | SQL”
dbDat=“….” (assumed to be SQL)
dbDSN contains the phrase “SQL Server” (assumed to be SQL)
dbDateWrap=chr(39) or dbDateWrap = “’” (assumed to be SQL)

Q35. Can I control if there’s a leading wildcard in the search screen in “EasyText” mode?

A. Yes. Use dbEasyTextPrefix (default=false). When it defaults to false, XX and YY will be converted to XX% and YY%. When set to true, XX or YY will be converted to %XX% or %YY%.

Q34. How can I intercept the navigation buttons to insert my own code?

A. ASPdb exports the following button click state (lowercase text) -

The following example intercepts the “Apply Filter” button and extract the values off the filter screen (assumes dbUnit = 123). –

Response.write("<center<h3> Test button export<BR</h3</center>")
if request("ASPdbClick_123")="applyfilter" then
response.write("<B>Intercepted LastName Field Value as - " & request.form("LastName")(3) & "</B<BR>")
'Note, use (1) instead of (3) above if dbFilterFlds = "Style=Simple"
end if

Set X = Server.CreateObject("ASPdb.Pro")
X.dbQuickProps="123;NWIND;employees;grid;4,auto,lightgreen"
X.dbNavigationItem = "top,bottom,prev,next,filter"
X.ASPdbPro
%>

Note:

In the regular filter screen, the name of the INPUT cell is the third item in the array bearing the same name as the fieldname, in the “Simple” filter screen, it’s the first.
ASPdb also exports the following “state value” after the screen is displayed via a property – dbLastScreen = "grid | form | filter | edit | add | delete | modify | update".
Based on these two pieces of information, the user can insert codes before and after ASPdb is executed.

Q33. How can I Control the Drop Down list in Edit/Filter and specify criteria?

A (2.x). The syntax of dropdown fields is –

dbFilterDropFlds = "(;,/+)DropFieldNameorNumber1, Connection1, TableName1, FieldName1, Criteria1; …Repeat "

Note that a new item “criteria” is appended after the FieldName. Use this item to filter the dropdown list. A new keyword in dbOption = “DropDownEasyText=true” works in conjunction with the criteria. It defaults to true and will convert # and * to SQL syntax ’ and % when SQL data type is detected. When DropDownEasyText is set to false, then user inputted criteria text will be appended to the SELECT DISTINCT statement as is. Keyword “WHERE” is optional and is allowed for additional clarity.

Example :

X.dbFilterDropFlds="EmployeeID,,Employees,EmployeeID,EmployeeID<=4; 4,,Employees,TitleOfCourtesy,WHERE TitleOfCourtesy like 'm*'"

Q32. How can I execute an SQL statement involving a UNION operation?

A. You must execute an SQL statement with a UNION operation as a QUERY. For example - (ACCESS)

X.dbSQL = "SELECT CompanyName, City FROM Suppliers WHERE Country = 'Brazil' UNION SELECT CompanyName, City FROM Customers WHERE Country = 'Brazil' "

The above will fail ! Instead Put the SELCT statement in a QUERY called Q1 and execute

X.dbSQL = "SELECT * FROM Q1"

If your operation can afford to do so, always convert the query to a table for viewing for maximum speed.

In MS-SQL, it would be very likely that these statements would be contained in the Stored Procedures.

Q31. What is purpose of the dbUseRSFilter property?

A. This property defaults to false. Filtering will be done by modifying the SQL statement instead of using ADO's RS.filter property. The advantage is that you gain a lot of speed plus the standard set of SQL WHERE criteria options can be used like - [ ] _? etc. For example, in RS.Filter, there is no way to specify names like O'Neil or phrases such as: He said, “Hi”. Only SQL WHERE conditions can perform such a filter. The only reason to set this property to true is if the user supplied RS is used in which no SQL statement is available to ASPdb.

Q30. What is purpose of the dbSELECTFrom property?

A. This property defaults to true and as such will retrieval the exact amount of records for display necessary to fill the “current page”, and hence reduce a lot of overhead. The only reason to set this property to false is for a user supplied RS in which no SQL statement is available to ASPdb.

Q29. Is there any way to execute stored procedures or SQL statements with no data returns before opening the recordset for browsing?

A. Use the dbSilentCmdText property: dbSilentCmdText = "(;,)Command text [,Type][,Tout]". When this property is not blank, it will be executed before the main SQL statement is executed. The type should be either adCmdText (1=default) or adCmdStoredProc (4) while Tout defaults to 30 seconds. DbSilentCmdText is a multiple property and supports user controlled delimiters. Multiple property means it can hold repeated entries up to the 64K limit.

Example :

X.dbSilentCmdText="(;+)UPDATE Daily_Cane SET Car_no=9999,Quota_no=99, _
Type=99 WHERE Billno=20001 + 1 + 30; UPDATE Daily_Cane SET Car_no=8888, _
Quota_no=88, Type=88 WHERE Billno=20002"

This example UPDATEs 2 records before proceeding to execute the regular dbSQL statement.

Q28. Is there any control over the MAXLENGTH of text when entering data with the Pro version?

A. When TEXT with a data type of 129, or 200 is detected in the edit input text box, ASPdb will retrieve the DefinedSize of the text field and use that as the MAXLENGTH value. If the DefinedSize is greater then the user specified size, the smaller one will be used as the SIZE value.

Q27. How can I include in QuickProps, the UID and PWD of the datasource?

A. User controlled delimiters has been added to the QuickProps property to enable this. See the syntax listing at the end of this file. Example:

Q26. Can you give an example of how to use Stored Procedures in ASPdbPro ?

A. Look at the following examples which utilize the standard stored procedures in the MS SQL "pubs" database. Note that this version of ASPdbPro has a limitation of returning the first recordset returned by the stored procedure. Also, only one SP can be used at one time.

<CENTER<h2>ASPdb-Pro Store Procedure Demo </h2</CENTER>
<%
'pubs.titleauthor datatype :
'au_id = 200
'title_id=200
'au_ord=17
'royaltyper=3
response.write("Method #1 (byroyalty) : Using on the fly<br>")
Set X=Server.CreateObject("ASPdb.Pro")
response.write("<center<h3> Version " & X.dbVersion & "<BR>" & X.dbLicense & " <BR>")
response.write("</h3</center>")
X.dbQuickProps="1;PUBS;;grid;4,auto,lightgreen"
X.dbStoredProc="byroyalty, 100"
X.ASPdbPro
response.write("Method #2 (byroyalty) : Using full Parameters<br>")
Set X=Server.CreateObject("ASPdb.Pro")
X.dbQuickProps="2;PUBS;;grid;4,auto,lightgreen"
'Standard params : name, type, direction, size, value
X.dbStoredProcCmdParams="byroyalty;@percentage,3,1,5,100"
X.ASPdbPro
response.write("Another demo (reptq3) with multiple params : Note that this version only returns the first RS !!<BR>")
Set X=Server.CreateObject("ASPdb.Pro")
X.dbQuickProps="3;PUBS;;grid;4,auto,lightgreen"
X.dbStoredProcCmdParams="reptq3;@lolimit,3,1,5,2;@hilimit,3,1,5,20; @type,200,1,12,'business'"
X.ASPdbPro
%>

Q25. I got the following error message in edit - This DataType xx is not covered by ASPdb, please file a report. What is it all about?

A. DataType as defined by ADO is documented for ACCESS and MS-SQL. Other database products use these data types in different contexts. In case there is a field that ASPdb is not aware of then this message will show up and editing will terminate. Report this number and it's corresponding data type and it'll be added to the type detection routine of ASPdb in our next release.

Q24. Filter DropDown fields is powerful by allowing a list of distinct values in the field for selection. However, it doesn't permit user to input a "new" value or a wild card value. Is there a way to have the best of both worlds?

A. A new button with a default text of "DropDown?" will show when the dbFilterDropFlds > "". When clicked, this button will toggle the Dropdown boxes to regular input boxes. You can proceed to program the drop downs and allow user to see them as reference values. Afterwards if the user needs to input new values, they can click this button to turn the dropdown fields into blank fields for user-entered queries. Also, if user programmed dropdown values are used, then this button can be forced to be invisible by setting dbDropButtonText="none".

Q23. How can I control which fields are downloaded with the DOWNLOAD button?

A. Use dbDownloadHideFlds to lock out the field(s) from download. For maximum flexibility, the dbGridHideFlds would not be taken into account. You must specify the full set of lock out fields with dbDownloadHideFlds.

Q22. I have an image (JPG, GIF, PNG) filename stored in the database and I would like to display a thumbnail (GIF) and link to the original file (JPEG). Does ASPdb has any provisions to enable this without having to create another field to hold the name of the thumbnail file?

A. This is a very popular application - displaying a thumbnail as a lead-in to the original full size JPEG file. Use dbMagicCell to display the thumbnail (GIF) in the cell and then hot link to the JPEG file. There is a special provision in the #FilenameNumber# macro such that if the last character is a period, then only the text left to the period will be returned (filename). Example -

X.dbMagicCell="filename,,<A HREF=""#filename#""<IMG SRC=""#filename.#.gif""</A>"

Performance Note: Always resize the JPEG file to thumbnail size before converting to GIF.

Q21. Even though the dbFilterDropFlds provides accurate filtering criteria, the power of free entry, wild cards and grouping is also very desirable. Is there a way to have both of them available?

A. When the dbFilterDropFlds property is not blank, a "Toggle Drop" button will appear in the Filter Setup screen. This button will toggle the dropdowns of the filter values. Note that the text on this button as well as the "Apply Filter" button is user customizable. See question 24.

Q20. How can I customize the "Apply filter" button text label?

A. Use dbFilterParams = "ApplyButtonText=My preferred text". Do not put any quotes around the text phrase.

Q19. How can I set the font and size in the grid and the form?

A. Use dbOptions = "CellFontTag=???"and dbOptions = "HeaderFontTag=???" to control the header and cell font attributes. Do not use these keywords to control the color attribute. Note that if FACE is used and it involves multiple fonts separated by commas, you have to use user delimiters -

X.dbOptions="(;|)Heading=<h3<center>Product Database</center</h3<br>| HeaderFontTag= FACE=""Courier, Lucida Sans"" SIZE=4 | CellFontTag=SIZE=2".

Q18. I use the edit and filter heading(s) extensively. What kind of delimiter control can I use to gain maximum flexibility?

A. The filter and edit headings are implemented in the dbFilterFlds and dbEditFlds properties. Maximum flexibility has already been incorporated into the properties format. The field(s) keywords must be at the beginning of the property string. After the fields are the heading keywords and they can be in any order and any delimiters can be used with the value portions. The only requirement is that there cannot be any spaces between the keyword and the "=" sign (heading=Hello, this is my favorite heading, style=simple)

Q17. How can I hide duplicated "cells" of a column?

A. Apply the dbGridHideDupRecFlds. (Example: dbGridHideDupRecFlds ="0,car,3". Fields can be either field number or name. Memo and BLOB field types will be rejected. The hidden field(s) will be excluded in the first record of the page (top of grid).

Q16. How can I display BLOB fields embedded in MS SQL fields?

A. The pubs.pr_info table has an embedded BLOB field "Logo" containing embedded GIF images in the field. Apply the dbBLOB property to display the image inside the cell directly from the field. Note that you can only display images directly supported by the browser in an inline mode (GIF, JPG and PNG).

<%
response.buffer=true
Set X=Server.CreateObject("ASPdb.Pro")
X.dbDSN="PUBS"
X.dbmode="dual"
X.dbSQL="Select * FROM pub_info"
X.dbGridHideFlds="2"
X.dbGridTableTag="BORDER=1"

' to display in-cell GIF image, use the following
X.dbBLOB="Logo,image/gif,browser,0"

' to display a hot GIF link use the following :
'X.dbBLOB="Logo,image/gif,Logo,0"

X.dbFormMemo="12x50,pr_info"
X.dbdebug=true
X.ASPdbPro
%>

Q15. How can I use the grid field as an index and not the system # (leftmost column) as an index to navigate and sync with the form?

A. Set the last parameter of the MagicCell's group to "index", and the cell's "converted" content will be hot linked to the form's record. For example -

X.dbMagicCell="fieldnamenumber, table tag, MagicCell,index,indexanchor"

If the field after the index field is "indexanchor" then the url generated will include an anchor tag.

In this example, the last entry is to identify that field "0" is to be used as the hot link index. You should also disable the GridIndex by issuing the dbGridIndex=False to make this looks better. See the on-line example A13 at

Q14. How many options do I have to customize the look and feel of the edit screen?

A. The color of the edit screen is tied to the system colors defined by dbColor. However, there are 3 keywords available for customizing the edit screen as follows :

X.dbEditParams="TableTag=xxxxx, InputSize=xx, CriteriaSIZE=Rows x Cols"

TableTag could be border and spacing tags. InputSize controls the maximum length of the edit input box.

If field.definedsize > InputSize then
SIZE = InputSize
Else
SIZE = field.definedsize
End if

CriteriaSIZE is the height x width of the criteria box in the when RecordScope = Multiple or All.

Q13. How can I display field(s) in the Edit/Update box as a read only field? This is sometimes very helpful to display these fields as a reference during data entry and updating.

A. Use the dbEditReadOnlyFlds property to accomplish that. For example :

dbEditReadOnlyFlds = "employeeID,2,3"

Note: If Memo fields are included in this property, they will be displayed in a TEXTAREA box and are physically editable but logically not included in the UPDATE and INSERT SQL statements. It is the responsibility of the programmer to warn the user not to consider the Memo Field when set to ReadOnly. All other fields except BLOB fields can be displayed as ReadOnly. Be careful when applying dbEditReadOnlyFlds to the "Add" operation. If the required fields are blocked out then the AddNew operation will fail.

Q12. How can I use customized colors via the dbColor property and at the same time specify the horizontal and vertical color stripes?

A. Use dbColor properties to support the colors stripes. Syntax for the dbColor is:

dbColor="ColorIndex, Auto, HZebra, Vzebra"

dbColor="TBBGcolor, THFGcolor, THBGcolor, TDFGcolor, HZebra, Vzebra".

Q11. I would like to set the initial sort order of the grid column sort order to DESC. That means when I "Click" to sort a "new" column, it'll start in DESC order and a subsequent click in the same column would reverse the order back to ASC?

A. Use the "GridInitSortDESC" keyword of the dbOptions property to enable this feature. For example:

dbOptions="GridInitSortDESC=True" (Default=False)

If this is set to true, the first click on the column header will sort the column in DESC order and a subsequent repeated click on the same column will reverse the sort order.

Q10. I would like to apply multiple criteria to a field in the filtering. What is the way to accomplish that?

A. The value boxes of the filter screen have been expanded to support multiple criteria including grouping. The syntax is :

Value1 [ ) ] and | or [ ( ] [operator] Value2 and | or [operator] Value3 [ ) ]

Example : (Red = Filter screen display and operator drop down; Blue = User Input Box)

AND/OR( Lastname = Nancy or Mary

AND/OR( EmployeeID = 2 or 5

AND/OR( EmployeeID = 2 or =5

Firstname LIKE Na* or Ma*(* will be converted to %)

Firstname LIKE Na% or Ma%

Lastname LIKE O and Neil(Match O'Neil - see warning below !!)

EmployeeID > 1 and < 4 (connector must have space on each side !)

EmployeeID > 1 and <4(operator and Value : no space OK !)

BirthDay > 12/8/1948 and < 5/29/1960

BirthDay > 12/8/48 and <= 5/29/60

BirthDay >= 12/8/48 and < 5/29/60

BirthDay >= 12/8/48 and <= 5/29/60

All the above birthday selection yields different result recordsets and offers more flexible choices compared to the standard SQL "Between" operator. Note that a MS-SQL user must set dbDateWrap=Chr(39) (single quote) when using EasyFilterText!

Note that the above assumes dbFilterParams = "EasyFilterText=True" (which is the recommended option). If EasyFilterText is set to false, then user has to input all the appropriate quotations. Only brackets will be balanced by adding the leftmost "(" upon odd count of brackets. For example :