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

<%
Set x = server.CreateObject("ASP.DB")
x.dbUnit = "999"
x.dbMode = "Grid"
x.dbDBType = "MSSQL"
x.dbDSN = "Driver={SQL Server}; Database=pubs; UID=sa; Server=dell7500"
x.dbEditAddROFlds = "job_id"
x.dbEditFlds = "(;,[]|)job_desc, min_lvl, max_lvl"
x.dbEditParams = "(;,)TableName=jobs, BookMarkFlds=job_id, Edit@@=identity|AddID"
x.dbEditUpdateROFlds = "job_id"
x.dbEditAddROFlds = "job_id"
x.dbNavigationItem = "Top,Prev,Next,Bottom,GridRow,Reload,Edit,Update,Delete,Add"
x.dbSQL = "SELECT job_id,job_desc,min_lvl,max_lvl FROM jobs"
x.aspdb
Response.Write ("IDENTITY=" & Session("aspDB_AddID_999"))
%>

 

Property Syntax

(UNO)
Obj.dbEditFlds="(;,[]|~)FieldNameNumber[Add_Default|UpDate_Default|Add_Hidden|Update_Hidden|vType~vMask~vEvent~vDef~vMin~vMax~vAddParam~vReq~vErrorTxt], ...repeat" (See Notes)

(EP)
Obj.dbEditParams=(;,)TableName=XXX, EditCol, TableTag=xxx, InputSize=CxR, SIZE200=RxC, BookMarkFlds=F1+F2..., LinkFlds=F1+F2, RecordScope=Single/Multiple, CriteriaSize=RxC, BooleanAsBit=True/false,StickyUpdate=true, CancelButton=cancel.gif, DropButton=Drop.gif, CriteriaText=XXX, EditValidateName=fn(), EditLinkFlds=F1+F2
, EditIcon=true, UpdateIcon=true, AddIcon=true, DeleteIcon=true, EditIconsLayout=left|Right|Both, Edit@@=action_key_word|Variable_Name|Repeat..."

(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; 
...repeat"
Obj.dbEditAddROFlds
="FieldNameNumber,...repeat"
Obj.dbEditUpdateROFlds="FieldNameNumber,...repeat"

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

EMAIL = "\\A\\w+\\@(\\w+.*)\\w+\\Z"
PHONE = "\\A((\\+\\d+[- ])?\\(?\\d\\d\\d\\)?[- ])?\\d\\d\\d[- ]?\\d\\d\\d\\d\\Z"
USPHONE = "\\A(\\(?\\d\\d\\d\\)?[- ])?\\d\\d\\d[- ]?\\d\\d\\d\\d\\Z"
SSN = "\\A\\d\\d\\d-\\d\\d-\\d\\d\\d\\d\\Z"
ISNUMBER = "\\A(-\\d)?\\d*\\.?\\d*\\Z"
ISPOSITIVENUMBER = "\\A\\d*\\.?\\d*\\Z"
NOTBLANK = "\\A.*\\S.*\\Z"
ISUPPER = "\\A[^a-z]*\\Z"
ISLOWER = "\\A[^A-Z]*\\Z"
HKID, CREDITCARD, hh24:mi:ss, hh12:mi:ss, dd-month-yy, dd-mon-yy, (mm/dd/yy, dd/mm/yy -> / or . and yy ot yyyy)

vEvent: 1=OnkeyUp 2=OnBlur 3=Both
vDef: Default value
vMin,vMax: Low/High range
vAddParam: Styles and other tags
vReq: 0=Can be Blank(def) 1=Cannot be Blank
vErrorTxt: Friendly text to display upon validation error

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 –
<%

  1. Set X= Server.CreateObject("ASP.DB")
  2. X.dbUnit = 1
  3. X.dbMode="dual-horiz"
  4. X.dbFormDisplayFlds=-1
  5. X.dbDSN = "NWIND"
  6. SQL="SELECT Customers.CustomerID, CompanyName, OrderID FROM Customers "
  7. SQL=SQL & "LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID "
  8. SQL=SQL & "ORDER BY OrderID"
  9. X.dbSQL=SQL
  10. X.dbColor = "4,Auto,,,true"
  11. X.dbNavigationItem="top,bottom,prev,next,edit,add,update,delete"
  12. X.dbEditParams="TableName=Orders, EditLinkFlds=0+2, EditBookMarkFlds=0+1"
  13. 'X.dbstartup="editadd"
  14. X.dbEditFlds="0,1,2,3,4,5"
  15. X.dbEditUpdateROflds="0"
  16. X.ASPDB

%>

The above example has a query having a JOIN relationship between two tables – "customer" and "orders" with the current pointer at the 5th record. Line #12 contains the keyword EditLinkFlds=0+2 indicating that when editing is activated the Table "Orders" is to be used. Which record of the table "Orders" is to be edited ? It would be the record selected by the following SQL -

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 –

<%

  1. Set oRs=Server.CreateObject("ADODB.Recordset")
  2. Set oConn=Server.CreateObject("ADODB.Connection")
  3. SQL="Select * From Employees"
  4. Connection="NWIND"
  5. oConn.CommandTimeout = 30
  6. oConn.Open Connection
  7. oRs.open SQL,oConn,1,2,1
  8. '.....Starts ASPDB Code
  9. Set X= Server.CreateObject("ASP.DB")
  10. dbQuickProps="1;;;Grid;4,auto;;;ACCESS;5,1,1"
  11. X.dbRecordSet=oRs
  12. X.dbRecordSetConnection=oConn
  13. X.dbFormDisplayFlds=-1
  14. X.dbNavigationItem="top,bottom,prev,next,edit,add,update,delete"
  15. X.dbEditParams="TableName=Employees, EditLinkFlds=0+1, EditBookMarkFlds=0+1"
  16. 'X.dbstartup="editadd"     'works also
  17. X.dbEditFlds="0,1,2,3,4,5"
  18. X.dbEditUpdateROflds="0"
  19. X.ASPDB
  20. '.....Ends ASPDB Code
  21. oRs.close
  22. set oRs=nothing

%>

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