| Editing and Validation |
ASPdb offers two levels of editing - system and template. The difference is the layout of the editing fields. A template will allow user to freely customize the screen layout. A very rich set of editing controls are available including edit source, dropdown, lookup, read only, show, hide, defaults, layout size, notes, bookmark, title etc.. Note that you can display a table and editing another table by specifying a linked field. You can also edit multiple tables (see dbEditAddSQL, dbEditUpdateSQL). These features plus may others simply don't exit in any competitor's product.
Validation is an advanced Gold package feature that uses a java regular expression applet to validate the user input on the client side. User can use the build-in expressions or specify their own for validation. On top of regular expression, it is also possible to customize an algorithm for validation. There are two build-in functions as an illustration - A credit card number validator and a Hong Kong ID card number checksum validator. Due to the fact that this kind of validation must be done in the java applet, it is out of the hand of the users and customized service must be provided to accomplish that.
EditFlds - Define edit fields and optionally with its defaults. Note that you cannot edit fields outside the bound of the SQL statement in dbSQL unless you specify the EditLinkFlds. You can edit the fields within the bound and optionally specify their default values by wrapping the default values with the wrap delimiters (def=[]). Optionally [TimeStamp()] will place the now() value in the input cell. See example below.
EditHideFlds - Define the edit field(s) to be hidden in the edit screen. Default is to show all if dbEditFlds is blank.
EditParams - Define edit parameters.
EditLookUpXRef - Define the EditField and the Lookup Index to perform Lookup in Edit operations. This property is usually used with the RO operation.
EditAddROFlds / EditUpdateROFlds - When this property is not blank, the default value of the field if any defined in dbEditFlds will be displayed as a ReadOnly Field in the Add / Update screen. If there is no default in the dbEditFlds property, this property will be ignored. If EditDropFlds is present, these two ReadOnly properties will win the conflict.
Edit Defaults (UNO Version only) -
[AddDEF]=Add def and NO update def
[AddDEF|UpDEF]= Add def and update def
[AddDEF|]=Add def and BLANK update default (be careful)
[|UpDEF]=Add def is BLANK and update def.
Edit@@
SELECT Edit@@??? after MS-SQL edit action. dbEditParams="Edit@@=action_key_word|Variable_Name|Repeat..."
User specify the variable name(s) and SELECT @@keyword as 'xxx' will be executed after the edit action
(like INSERT / UPDATE) and a session variable of the user specified name plus the dbUnit will be retuned.
User is responsible whether this action retrival is appropriate by specifying this option. To retrieve the variable, use
session(aspdb_variable_name_sx). If variable name is blank then it is the same as the keyword like session("aspdb_IDENTITY_999").
| Property Syntax |
(UNO)
(Pro)
Obj.dbEditParams=(;,)TableName=XXX,
TableTag=xxx, InputSize=RxC, SIZE200=RxC, BookMarkFlds=F1+f2, LinkFlds=F1+F2..., RecordScope=Single/Multiple, CriteriaSize=CxR, BooleanAsBit=True/false,
UpdateText=XXX, DeleteText=XXX, AddNewText=XXX, ReturnText=XXX,
HeaderFontTag=XXX,
ResetText=XXX, CopyText=XXX, ClearText=XXX, CriteriaText=XXX"
(ALL)
Obj.dbEditHideFlds="(;,)FieldNameNumber,…repeat"
Obj.dbEditLookUpXRef="EditFldNumber,DisplayLookUpFldNumber;
| Parameters |
|
Parameters |
Default Value |
Description |
| Delimiters | (;,[]|) | Group - Field - Def Left wrap - Def right wrap - Def separator (UNO version only) |
| FieldNameNumber | As always, unless specified, Field name and number can be mixed. | |
| AddNewText (Pro) | Add New Record | Text for Add button |
| ActionText (Pro) | Action | Text for Action button |
| BookMarkFlds (Pro) | None | As an autonumber field is not always available, these fields are used to construct a unique criteria to locate the record in edit. Using recordset editing methods are not reliable as the position of record in edit might be changed due to multi-user editing. |
| BooleanAsBit | False | SQL databases uses 1/0 as boolean instead of True/False. This parameter is automatically set in dbDBType. Use only if database type is not covered by dbDBType. |
| CancelButton (EP) | Cancel.gif | Image name
of cancel button. Image name defaults to "Cancel.gif" but can be defined by dbEditParams="CancelButton=Name_Of_Image". It is expected that it is stored in the ImageDir. Cancel.gif is supplied as part of the standard button files. |
| CopyText (Pro) | Copy | Text for Copy buttom |
| ClearText (Pro) | Clear | Text for Clear button |
| CriteriaSize | 60x30 | Size of text area holding the criteria statement |
| CriteriaText (Pro Only) | Record Update/Delete Criteria : | In Enterprise version, this parameter is set in the dbUserLocalText. |
| DeleteText (Pro) | Select Current Record | Text for the delete button |
| DisplayLookUpFldNumber (LookupRef) | The Number and NOT name of the lookup field defined by db | |
| DropButton (EP) | Drop.gif | Image
name of drop button. Image name defaults to "Drop.gif" but can
be defined by dbEditParams="DropButton=Name_Of_Image". It is expected that it is stored in the dbImageDir. Drop.gif is supplied as part of the standard button files. |
| EasyTextPrefix (Pro) | False | Activate easy text will convert single quotes to double single quotes. "*" will also be converted to "%". |
| EditDateFormat | Specifies the data format of the edit inputs. For example, in Britian, it is dd/mm/yyyy, then EditDateFormat=[DD/MM/YYY] will conver the input dates to the corresponding date values. | |
| EditFldNumber (LookupRef) | The Number and NOT name of the edit field for the lookup. | |
| EditLinkFlds (EP) | None | In order to
edit a different table other than the one in dbSQL, a "Link"
field must be specified. See example below. A
"Link" field is one that serves as a criteria in pointing to the
record(s) to be edited. Note: UserRS/JOIN/UNION are allowed. |
| EditValidateName | None | Name of user supplied Javascript function to perform validation. |
| HeaderFontTag (EP) | None | When used in EditParams, this parameter sets the Edit screen header fonts. If blank then the HeaderFontTag from dbOptions will be used. |
| InputSize | Row
x Col or Col |
Size of the Edit Input box. Col defaults to 25. |
| RecordScope (Pro) | Single | Allow single or multiple records editing. If records affected is > 1 and single is specified, the editing action will be rolled back. |
| ResetText (Pro) | Reset | Text for the Reset button. |
| ReturnText (Pro) | Return | Text for the Return or Cancel button. |
| Size200 (Pro) | None | Rows and Cols for data type 200 and 202. This type can be up to 2K bytes in size. Special provision is made to display that in a textarea. |
| StickyUpdate | False | This feature keeps the update screen just like the add screen for repeat updating. Example - conference type of updating. |
| Tablename | None or SchemaTable Name | Name of table to edit. Table must be within the same connection source. |
| TableTag | None | Table tags (HTML) for the edit screen. |
| UpdateText (Pro) | Update Current Record | Text for the update button. |
| Validation params | none | Parameters for validation. vType: 1=INPUT 2=TEXTAREA 3=PASSWORD vMask: Regular expression or preprogrammed Masks
vEvent: 1=OnkeyUp 2=OnBlur 3=Both |
| EditIcon (*) | false | Place an Edit Icon in the grid |
| UpdateIcon (*) | false | Place an Update Icon in the grid |
| DeleteIcon (*) | false | Place an Delete Icon in the grid |
| AddIcon (*) | false | Place an Add Icon in the grid |
| EditIconsLayout | Left | Where to place the above Icons |
| Edit@@ | SELECT Edit@@??? after MS-SQL edit action |
(*) Note - The names of the edit Icons are stored in the system as ????Icon.gif. You can create your own icons and name them as ????Icon.gif.
| Example - Validator |
| <% Set X= Server.CreateObject("ASP.DB") X.dbUnit = "999" X.dbMode = "Grid" X.dbDSN = "DSN=CARDB" X.dbEditFlds = "(;,[]|~)Car, Manufacturer, Year[|2000|||1~ISNUMBER~1~1999~2002~SIZE=30], Comment[||||2~~~~ROWS=5 COLS=30]" X.dbValidatorParams="http://localhost/DLL,true,blue,red,magenta,OK,ERROR IN INPUT FIELD,INCOMPLETE FIELD" X.dbEditParams = "(;,)TableName=CarSpecs, BookMarkFlds=Car" X.dbEditUpdateROFlds = "Car" X.dbNavigationItem = "Top,Prev,Next,Bottom,GridRow,Reload,Edit,Update,Delete,Add" X.dbSQL = "SELECT Car,Manufacturer,Year,Transmission,Comment FROM CarSpecs" X.ASPdb %> |
| Example - Add and Update Read Only fields |
| <% Set X=Server.CreateObject("ASP.DB") X.dbDSN="DSN=NWIND" X.dbdbType="ACCESS" X.dbMode="grid" X.dbSQL="SELECT * from orders" X.dbNavigationItem = "top,prev,next,bottom,update,Edit,Add" X.dbEditParams = "TableName=orders,BookMarkFlds=0" X.dbEditFlds="0,1[1234],2,3[timestamp()],4[1/1/99],8" X.dbEditUpdateROFlds="0,3" X.dbEditAddROFlds="4" X.ASPDB %> |
 |
 |
| Example - Add and Update Read Only fields |
|
How can I edit a table independent of the table(s) in the query ? For example, to edit one of the table in the JOIN'ed query or to edit a table with a User Supplied RS and Connection. Pro version of ASPDB has redefined the editing format. Edit now is detached from the normal viewing and carries it’s own independent properties. The result is that user can edit a table which is not defined in the dbSQL property. The key property of the new editing properties is the EditLinkFlds keyword in the dbEditParams property. |
|
Example #1 – <%
SELECT * FROM Orders WHERE CustomerID = 'HANAR' AND OrderID = 10250 How is this SQL statement derived ? From EditLinkFlds=0+2 which means use field 0 and 2 as criteria. The rest of the editing parameters are identical to a normal editing setup. So, ONE keyword is required to define the editing record. EditBookMarkFlds defines the criteria to modify the "Orders" table. The Editing SQL statement is as follows - UPDATE Orders SET CustomerID = 'HANAR', EmployeeID = 4 , OrderDate = #8/8/94#, RequiredDate = #9/5/94#, ShippedDate = #8/12/94# WHERE OrderID = 10250 AND CustomerID = 'HANAR' Note that the criteria is -> WHERE OrderID = 10250 AND CustomerID = 'HANAR' as defined by EditBookMarkFlds=0+1. Field 0 and 1 of the "Orders" table is OrderID and CustomerID. Now, Uncomment line #13 will result that upon entry, the "Orders" table will be prepared for EditAdd. If you tried to edit the first two records which has a non-valid criteria to define the edit record you’ll get an error box warning about an invalid Bookmark. This mean that all the editing conditions of the database must be valid under the asp constraint. Note: The editing table must be in the same connection as defined by dbDSN, dbMDB, dbDat or dbRecordSetConnection. Under this new independent editing setup, user supplied RS would have the same editing capability. See example #2. |
|
Example #2 – <%
%> This example illustrates how to use user supplied RS and connection and execute editing. Note that connection string must also be supplied as it is assumed that editing table is within the same source. |
| Example - EditLookUpXRef fields |
| How to do Lookup in Delete using EditLookUPXRefFlds
! <% 'Just hit delete and you'll see the lookup. Set X = Server.CreateObject("ASP.DB") X.dbUnit = 30607991 X.dbGridInc = 6 X.dbMode="dual-horiz" X.dbFormDisplayFlds = -1 X.dbDSN = "NWIND" X.dbDBType="ACCESS" X.dbGridTableTag = "Border=2 cellspacing=2" X.dbSQL = "SELECT EmployeeID,LastName,FirstName,Title,ReportsTo from Employees Order By EmployeeID" X.dbDisplayLookUpList ="(;|)ReportsTo||SELECT EmployeeID, FirstName & ' ' & Lastname as Name FROM Employees" X.dbFilterDropFlds ="(;|)ReportsTo||||SELECT EmployeeID, FirstName & ' ' & Lastname as Name FROM Employees ORDER BY FirstName" X.dbEditDropFlds ="(;|)ReportsTo||||SELECT EmployeeID, FirstName & ' ' & Lastname as Name FROM Employees" X.dbEditLookUpXRef = "4,4;" X.dbEditUpdateROFlds="0" X.dbEditAddROFlds="0" X.dbEditFlds="0,1,2,3,4" X.dbEditParams="TableName=employees, EditBookMarkFlds=0" X.dbNavigationItem="top,bottom,prev,next,reload,filter,update,delete,Add" X.ASPDB %>
|
| Notes - New Syntax |
