To all aspDB users and "to be" users,

The following is a FAQ collection of mine in responding to many interesting questions about how to use aspDB effectively. This list is updated almost every week. I`ll re-post this list from time to time for your reference.

FAQ - 9/1/98

Q. What is purpose of the dbSELECTFrom property ?
A. This property should be used for high speed open. This property is used to construct the following in retriving the record count of the recordset defined by the SQL statement –
SELECT COUNT(*) FROM ….statement…..
Do not include non record number related statement like “ORDER BY”.
Example - X.dbSQL="Daily_Cane WHERE Billno > 20000 ORDER BY Billno"
X.dbSELECTFrom="Daily_Cane WHERE Billno > 20000"
Y.dbSQL=”SELECT FI, F2 FROM TableName”

Q. Is there any way to execute stored procedures or SQL statements with no data returns before openning 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=def) 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 UPDATE 2 records before proceed to execute the regular dbSQL.

Q. Is there any control over the MAXLENGTH of the edit input text? In some database, if the input text is over the limit, it would cause a problem.
A. When TEXT type of 129 and 200 is detected in the edit input text box, aspDB will retrieve the DefinedSize of the text filed and use as the MAXLENGTH value. If the DefinedSize is greater then the user specified size, the smaller one will be used as the SIZE value.

Q. How can I include in QuickProps UID and PWD of the datasource?
A. User controlled delimiters has been applied to the QuickProps property to enable this. See the syntax listing at the end of this file. Example -
X.dbQuickProps = "(+,)1+ dsn=pubs; uid=sa; pwd= + titles + dual-horiz + 4,auto,lightgreen"

Q. Can you give an example in how to use the Store Procedure in aspDBPro ?
A. Look at the following examples which utilize the standard stored procedures in the “pubs” database. Note that this version of aspDBPro has a limitation of returning the first recordset returned by the store 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
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>")
X.dbStoredProc="byroyalty, 100"
response.write("Method #2 (byroyalty) - Using full Parameters<br>")
Set X=Server.CreateObject("AspDB.Pro")
`Standard params - name, type, direction, size, value
X.dbStoredProcCmdParams="byroyalty;@percentage,3,1 ,5,100"
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.dbStoredProcCmdParams="reptq3;@lolimit,3,1,5,2;@ hilimit,3,1,5,20; @type,200,1,12,`business`"

Q. 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 defined by the ADO is documented against ACCESS and MS-SQL. Other database product use these datatype in a different context. In case there is a field that aspDB is not aware of then this message will show and edit will terminate. Report this number and it’s corresponding datatype and it’ll be added to the type detection routine of aspDB.

Q. Filter DropDown field is powerful by providing a list of distinct values in the field for selection. However, it doesn’t permit user to input a “new” 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 <> “”. This button when clicked will toggle the Dropdown State of the value input box. You can proceed to program the drop downs and allow user to see them as reference values. Afterwards if user needs to input new values, user can click this button to turn dropdown fields to blank fields. Also, if user programmed dropdown values are used then, this button can be forced to be invisible by setting dbDropButtonText=”none”.

Q. How can I control which fields to be downloaded ?
A. Use dbDownloadHideFlds to lock out the field(s) from download. For maximum flexibility, the GridHideFlds would not be taken into account. You must specify the full set of lock out fields with dbDownloadHideFlds.

Q. I have the image (JPG, GIF, PNG) filename stored in the database and I would like to display a thumbnail (GIF) and linked 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 in displaying a thumbnail as a lead to the original JPEG file. Use MagicCell 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>"

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

Q. Even though the FilterDropFlds 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.

Q. 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.

Q. 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".

Q. 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 space(s) between the keyword and the “=” sign (heading= Hello, this is my favorite heading, style=simple)

Q. How can I hide duplicated “cells” of a column ?
A. Apply the dbGridHideDupRecFlds. (Example: dbGridHideDupRecFlds ="0,car,3". Fields can be either number or name. Memo and BLOB field type will be rejected. The hiding field(s) will be excluded in the first record of the page (top of grid).

Q. 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).

Set X=Server.CreateObject("AspDB.Pro")
X.dbSQL="Select * FROM pub_info"

` to display in-cell GIF image, use the following

` to display a hot GIF link use the following -


Q. 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 as “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.

Q. How 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 to customizing the edit screen as follows -
X.dbEditParams="TableTag=xxxxx, InputSize=xx, CriteriaSIZE=Rows x Cols"
TableTag could be border and spacing tags. InputSize control the maximum length of the edit input box.
If field.definedsize > InputSize then
SIZE = InputSize
SIZE = field.definedsize
End if
CriteriaSIZE is the height x width of the criteria box in the when RecordScope = Multiple or All.

Q. 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, it’ll be display in a TEXTAREA box and is physically editable but logically not included in the UPDATE and INSERT SQL statements. It is the duty of the programmer to warn the user not to consider the Memo Field when set to ReadOnly. All other fields except BLOB fields will 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.

Q. How can I use customized color via the dbColor property and at the same time specify the horizontal and vertical color strips ?
A. Use dbColor properties to support the colors strips. Syntax for the dbColor is –
dbColor=”ColorIndex, Auto, HZebra, Vzebra”
dbColor="TBBGcolor, THFGcolor, THBGcolor, TDFGcolor, HZebra, Vzebra".

Q. I would like to set the initial sort order of the grid column sort order to DESC. That mean when I “Clicked” to sort a “new” column, it’ll start at 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. A subsequent repeated click on the same column will reverse the sort order.

Q. 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 yield different result recordset and offer more flexible choices compare to the standard SQL “Between” operator. Note that MS-SQL user must set dbDateWrap=Chr(39) (single quote) when using EasyFilterText!

Note that the above assumed dbFilterParams=”EasyFilterText=True”. 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 –

BirthDay > #12/8/1948# and < #5/29/1960# (ACCESS)
BirthDay > ‘12/8/1948’ and < ‘5/29/1960’ (SQL)
Lastname = ’Nancy’ or ‘Mary’
Lastname LIKE ’Na%’ or ‘Ma%’

Grouping Examples –
(Price > 10000 and <20000) or (>30000 and < 40000)
Price 2 (Price = 2 style=simple)
Price 2 or 4 (Price = 2 or Price = 4 style=simple)
Price 2 or =4 (Price = 2 or Price = 4 style=simple)
Price >2 (Price > 2 style=simple)
Price >2 and <4 (Price > 2 and Price < 4 style=simple)
Price >2 and 4 (Price > 2 and Price > 4 style=simple – Error – Invalid criteria!)

Grouping Rules –
 In detailed mode (style<>simple) if there are even number of parenthesis then a closing parenthesis will be added at the beginning. If there are no parenthesis (0), it is considered to be an even count.
 Grouping must be valid. If and or is mixed in an invalid mode then an error would occur.
 The connector is called the master connector. If the user input value has no preceding connector, the master connector will be applied. There Field = A and B will be converted to Field = A and = B and eventually converted to Field = A and Field = B.
 If EasyFilterText is set to false then no conversion will take place. However, if the first parenthesis encountered is a “)”, then a corresponding “(“ will be added to the beginning.
 Use easy text as much as possible. All values type will be automatically detected.
 Remember to set DateWrap=’ (singlequote) in the dbOption property for SQL databases.
 Grouping applies to all fields and can be linked by the fields’s connector (AND/OR radio buttons).
 In “style=simple” mode, all apply except that the fields connector is set to AND. The master (default) operator is set to “=”. When the first token is an operator, it will be recorded as the master operator.

Warning !!! – Do not include singlequote in filter text input box. Even though a double singlequote is a valid syntax in SQL statement, it is illegal when used as recordset filter text in the current version of ADO 1.5. It is also illegal to use %X as search text. Only %X% or X% is illegal in ADO 1.5.

Q. For some reason, my input text in the filter value input box has been masked or wrapped by different quotation characters. What are the rules ?
A. The EasyFilterText keyword of the dbFilterParams has a default “True” value. Unless you specify –
aspDB will automatically process you input text and insert the correct “wrappers” characters.

Q. I cannot find any properties to allow me to put a border around the Filter screen like the Edit screen. Is there one ?
A. Use the “dbFilterParams” property. Example -
Since color of filter screen is already synchronized with the system table color, the only meaningful tags for this keyword would be border, cell spacing and padding.

Q. I am unable to put a [,] in the mapname. Is there any way out ?
A. Beginning Version 1.7.17. user controlled delimiter is incorporated into dbNameMap. User can apply their preferred delimiters. For example –

X.dbNameMap="(;+)0 + Car Name, Maker + span=2; comment + My Notes"
X.dbNameMap = "0, Car and Maker, span=2; comment, My Notes"

Q. I am using aspDB-Pro in conjunction with MS-SQL. Due to the high traffic of the database access, I would like to disconnection the connection at the end of every page and let ODBC to handling the connection polling ? How can I accomplish that ?
A. Beginning Version 1.7.18. a new keyword “CloseConnection” is available in dbOptions. When set to true, aspDB will disconnect the connection and release the resource at the end of every page. The default is false. In a high traffic setup, user should observer the following –

 Set dbOptions=”CloseConnection=True”
 Do not set command connection timeout to a value higher than you need. (e.g. dbOptions=”ConnectionTimeOut=30”)
 Use the latest version of the ODBC drivers in order to take advantage of the system connection polling management.

Q. I would like to change the font type and size of the memo field displayed in a grid cell. How can I do that ?
A. Beginning Version 1.7.14. dbMagicCell support Memo field. However, #me# and #field# are not supported. For Example –
X.dbMagicCell="Notes,,<font color=Red>#notes#</font>"

Q. How can I include the entire memo field in download just like a regular text field ?As for now, it only download the memo field as “memo” ?
A. Beginning Version 1.7.10. a new keyword “MemoDownload” is available in dbOptions. When set to True, it’ll download the memo field as is. However, user will be responsible in controlling the tabs and (“)
in the memo content accordingly. If (") is present, memo will be truncated at that point. If tab is present, it`ll
place the memo content in the adjacent cell. Therefore, user should use (`) and (spaces) in lieu of (") and (tabs).

Q. When I tried to “add” or “update a record, sometimes it works and sometimes it doesn’t work with various database and edit fields. What is the reason for that ?
A. In aspDB-Pro user has a lot of control in specifying what fields to show and edit. The combination will create an invalid criteria in an addnew operation. Use the following checklist to confirm the settings –

1. Does you edit fields include data fields? Are you using ACCESS or SQL database ? Did you set the date wrap to the correct value in dbOptions like DateWrap=’ or chr(39) for SQL databases. Default wrap character is “#” for ACCESS. Did you set the BooleanAsBit=True in the dbEditParams property for MS-SQL database to handle the Boolean type correctly as SQL use type 11 as bit(1/0) compare to True/False (-1/0) for ACCESS.

2. Check the “allow blank” attribute of your database. You must include all the fields which has the attribute “allow blank”=No. This is the most popular criterion that caused the addnew operation to fail. However, the same fields will function correctly in an update operation because all the required non-blank fields exist in updating a record.

3. Check the input values of the addnew operation. Are the values of the right type. For example, a string input in a numeric field will cause a type mismatch error.

4. Did you set the singlequote keyword in dbEditParams to value other than 2 (default). If so, aspDB-Pro would not process you input text/memo to convert all single quotes to double (original double single would be preserved). Without embedded double single quotes the input text string will be truncated and might cause unexpected results.

5. Did you include AutoNumber/IDENTITY fields as edit fields ? You should not do that as it would create an invalid criteria. You must set dbEditUpdateROFlds to avoid the processing of ReadOnly fields.

6. Did you include any linked fields in you edit fields that would create an invalid criteria. For example if you have a ID, name, SS and address table and this table is pointed to or linked to by other tables. If you tried to update or delete records in this table, you’ll be rejected. Again, test the SQL statement in your desktop or enterprise manager or MSQuery to confirm.

7. Did you specify a valid bookmarkflds in dbEditParams? If this combo fields do not correspond to a unique record and you specified RecordScope keyword as “single” (default) and the result recordset contains more than one record. Transaction will be rolled back and you’ll get an error message.

Q. How do I make the download to work with IE 3.xx and presents a programmable filename at the save-to-disk prompt? As for now it works with all Netscape version and IE 4.0+. we have a lot of users that are still on IE 3.02.
A. In order to present a user programmable filename at download, a more recent http header must be used. IE 4.0+ and Netscape supported that but not the older version of the IE browser. The symptom would be that IE 3.x will present the name of the program as the download name and user has to physically change the filename. It is inconvenient but not fatal. In order to defeat this problem you can do the following –

 Go to IIS administration Script Mapping section and define .csv as an additional valid file extension for asp.
 Name your asp program with the csv extension like “quotes.csv”.
 Execute the csv program and you download filename would be “quotes.csv” every time user hit the download button.

Q. My favorite editor supports user define macros strings. Is there a complete set of aspDB properties available so I can incorporate into my editor ?
A. The following is a listing of all the properties that is used internally in the aspDB development. An update version of the following list will be available in our website upon a new release.

X.dbBLOB = "Field, Context-Type, Return String, BLOB Offset"
X.dbBoolText = "Text when True, Text when False"
X.dbButtonAnchor = True
X.dbColor = "ColorStr, Auto-True-False, HorizStripeColor, VertStripColor"
X.dbCsvDelimiter = "," or chr(8)
X.dbCsvName = "c:downmyDownLoadFile.Csv"
X.dbColGraph="TableName, FieldNameNumber, Width, Height, Icon, Style, Min, Max; repeat from fieldnamenumber"
X.dbDat = "ServerName, DatabaseName, uid, pwd"
X.dbDebug = True
X.dbDownloadHideFlds="0, f1, f2…."
X.dbDSN = "System DSN Name"
X.dbEditHideFlds="Key, FullText"
X.dbEditDropFlds = "(;,/+)DropFieldNameNumber1, Connection1, TableName1, FieldName1, Xchgfieldname1; DropFieldNameNumber2, Connection2, TableName2, FieldName2, Xchgfieldname2"
X.dbEditDropFlds = "(;,/+)DropFieldNameNumber,,, Sel1/Sel2/Sel3; repeat….."
X.dbEditFlds = "f0,f1,f3, EditHeading=XXX, AddHeading=XXX, DeleteHeading=XXX, UpdateHeading=XXX"
X.dbEditHideFlds = "f1,f2,f3, memofld, blobfld"
X.dbEditParams="EditTable=xxx, TableName=xxx, TableTag=xxxxx, InputSize=xx, BookMarkFlds=f1+f2+f3, RecordScope=Multiple, CriteriaSIZE=Rows x Cols, SingleQuote=2, BooleanAsBit=true"
X.dbEditReadOnlyFlds=“employeeID, 2,3”, EditSemiColon=;
X.dbExportFlds = "0, f1, f2 ..."
X.dbFilterDropFlds = "(;,/+)DropFieldNameNumber1, Connection1, TableName1, FieldName1; DropFieldNameNumber2, Connection2, TableName2, FieldName2"
X.dbFilterFlds = "0,1,2,3,4,5, heading=My filter heading, Style=simple"
X.dbFilterHideFlds = "0,1,2, ,memofld, blobfld"
X.dbFilterParams = "TableTag=Border=1, EasyFilterText=False, ApplyButtonText=My Text, DropButtonText=xxxx (or none to hide), UseRSFilter=false"
X.dbFormCol = NumberOfCols
X.dbFormDisplayFlds = "0, f1, f2 ..."
X.dbFormHideFlds = "0, f1, f2 ..."
X.dbFormMemo = "Rows x Cols, f1, f2, ..."
X.dbFormTableTag = "Form Table tags (e.g. border=1)"
X.dbFormTHBGTag = "Form Header BackGround Color tag"
X.dbGridCol = GridCol
X.dbGridColSort = True
X.dbGridColSortHideFlds = "0, f1, f2 ..."
X.dbGridDisplayFlds = "0, f1, f2 ..."
X.dbGridHideFlds = "0, f1, f2 ..."
X.dbGridInc = GridPageSize (-1 = all)
X.dbGridIndex = True
X.dbGridTableTag = "Grid Table tags (e.g. border=1)"
X.dbGridTHBGTag= "Grid Header BackGround Color tag"
X.dbHeader = True
X.dbImageDir = "System Image Directory"
X.dbMagicCell="(;,[]#) FieldNameNumber, Celltags, MagciCellMacro, Index, indexanchor ;...
X.dbMemoTextSize = 0 or -1 or size (0="Memo" -1=Entire Memo size=Partial Memo)
X.dbmdb = "Physical Location of mdb file"
X.dbMode = "grid or form or both ot dual or dual-horiz"
X.dbNameMap = "(;,)FieldNameNumber,Alias, Cell Tag, Calc Field Expression"
X.dbNavigation = " Top or Bottom or Both"
X.dbNavigationIcon = "gif, grid, form, top, prev, next, bottom, rowplus, rowminus, gridplus, gridminus, formplus, formminus, filter, resetfilter, download, color, reload, tables, edit, add, update, delete"
X.dbNavigationIcon = "std"
X.dbNavigationItem = "grid, form, top, prev, next, bottom, rowplus, rowminus, gridplus, gridminus, formplus, formminus, filter, resetfilter, download, color, reload, tables, edit, add, update, delete"
X.dbOptions = "(;,)ConnectTimeOut=30, CloseConnection=True, Heading=</b>My Heading</b>, GridInitSortDESC=True, MemoDownload=True; DateWrap=’, FontTag=SIZE=small FACE=””Arial”” "
X.dbQuickProps="(;,)Unit; DSN or mdb; Tablename; mode; dbColor; dbNavigationIcon"
X.dbRecordCount = RecordCount
X.dbRecordSet = UserRs
X.dbSilentCmdText=”(;,)Command Text [,Type][,Tout]; repeat”
X.dbSelectBox = "(;,!)Size, TitleText, ButtonText; MagicCell"
X.dbSQL = "SQL statement"
X.dbSQLFrom = "SQL FROM statement"
X.dbStartup = "filter or editadd"
X.dbStatusBar = True
X.dbStoredProc="Procedure Name"
X.dbStoredProc="Precedure Name, Param1, Param2 , ... "
X.dbStoredProcCmdParams="Procedure Name; Name1, Type1, In-Out1, Size1, Param1, Param2...; Repeat"
X.dbSuppressMsg = True
X.dbTables = "box Size, heading text, button text"
X.dbUnit = UnitNumber (def = 999)