Tornado Class Library

Special Topic - Edit

The overall activities of a Project is 90% viewing and 10% data editing. But if the 10% editing is not done right, the 90% display will be greatly affected. There is no short cut to editing. Simply offer a plain grid or form type setup editing the raw values of the table can only be of training nature. The real world is not complicated and demanding. ASP-db takes the editing task to the fullest extend by examining the options and user perspectives. No other product have editing features of such depth.

Edit can be performed in Normal and Grid Mode. Fields, Display template, SQL, validation, Dropdown, lookup, Magic filtering and ReadOnly attributes can be set on individual fields in both normal and grid mode.

 

Basic Editing

The following illustrates ASP-db.Net basic editing. No specific editing features are used. Debug is turned on to examine the actions. Simply specifying the Add and Update buttons in the navigation will automatic the editing functions by using all the fields in the table.

<script language="VB" runat="server">
Sub Page_Load(Src As Object, E As EventArgs)
  Dim ed As New Tornado.z
    ed.dbQP = "U=3|S=2|D=NWIND|Q=Employees|gdf=0,1,2"
    ed.dbNavigationItem = "b5,add,update"
    ed.dbBookMark = "Employees;0"
    ed.dbDebug = "EditAction, EditSetup"
    ed.ASPdbNET()
End Sub
</script> 

Unfortunately, the update action does not work as field 0 is an autonumber field and field Photo is a binary field. If we block off these 2 fields then the update will work. We'll use dbEditUpdateHideFlds to hide the fields instead of using .dbEditUpdateFlds to specify the edit fields.

<script language="VB" runat="server">
Sub Page_Load(Src As Object, E As EventArgs)
  Dim ed As New Tornado.z
   ed.dbQP = "U=3|S=2|D=NWIND|Q=Employees|gdf=0,1,2,4|Ni=b5,add,update|bm=employees;0"
   ed.dbDebug = "EditAction, EditSetup"
   ed.dbEditUpdateHideFlds = "0,Photo"
   ed.ASPdbNET()
End Sub
</script> 

 

Intermediate Editing

In actual practice, you need to show the non-updateable fields as R/O reference so the user would have a better picture of the edit. There are many ways  to draw data from their data sources to perform lookup, we'll just use two very popular methods - from a database and from a hard coded source. We'll build different 'Lists/Columns' and each can be a key or lookup value. Each 'List' will have it's own name. See LookUp & DropDown for details. In this example, we build the lookup lists as EID, ENAME and TT. The first two list is filled by the SQL and the third list is filled by the user supplied values. After the lists are built then it can be used by all the modules within the same application. The dbEditUpdateFlds defines filed #0 as a R/O field, field #4 (TitleofCourtesy) as a TT drop box and the ReportsTo as a EName dropbox. The Value/Text keywords refers to the Value and Text part of an input control.

<script language="VB" runat="server">
Sub Page_Load(Src As Object, E As EventArgs)
  Dim ed As New Tornado.z
  ed.dbQP = "U=3|S=2|D=NWIND|Q=Employees|gdf=0,1,2,4|Ni=b5,add,update|bm=employees;0"
  ed.dbCommonTables = "ind=Eid,Ename| SQL=Select Employeeid, firstname & ' '& lastname from Employees; ind=TT|value=Mr.,Ms.,Dr.,Mrs."
  ed.dbEditUpdateFlds = "fi=0|Ty=RONOUP,1,2,3,fi=4|ty=sb|val=TT,5, fi=ReportsTo|ty=selectbox|val=eid|text=Ename"
  ed.ASPdbNET()
End Sub
</script> 

The application of less than10 lines of code can easily be a good day's work if you can do it at all. Majority of the features and formats are from user inputs throughout the years.

 

Advanced Normal and Grid Editing

This example implements basic normal and grid add and update.

<script language="VB" runat="server">
Sub Page_Load(Src As Object, E As EventArgs)
Dim X As New Tornado.z
  X.dbQP = "U=1|S=1|M=Grid!sys=t|D=Nwind|ni=b5,add,gridadd,update,gridupdate|
             Q=Employees| bm=employees;0|gdf=1,2,3| Dt=Access"
  X.dbTextHolder = "Title=Normal & Grid Edit"
  X.dbCommonTables = "ind=TT|value=Mr.,Ms.,Dr.,Mrs."
  X.dbEditUpdateFlds = "fi=0|Ty=RONOUP,1,2,HireDate,HomePhone"
  X.dbEditGridUpdateFlds = "fi=0|Ty=RONOUP,1,2"
  X.dbEditAddFlds = "1,2,3,HomePhone"
  X.dbEditGridAddFlds = "1,2,fi=4|Ty=SelectBox|val=TT|Tex=TT,HireDate"
  X.dbDebug = "EditAction"
  X.dbGridUpdateTemplate = "<h2>My Grid Update template</h2><P> [[GridUpdate]]<BR>[[GridNav]]"
  X.dbGridAddTemplate = "<h2>My Grid Add template</h2><P>[[GridAdd]]<BR>[[GridNav]]"
  X.dbEditGridAddSQL = "INSERT INTO Orders (lastname,Firstname,TitleOfCourtesy,HireDate) VALUES ('[[1]]','[[2]]','[[4]]',#[[6]]#)"
  X.dbEditGridUpdate = True
  X.dbEditGridAdd = True
  X.ASPdbNet()
End Sub
</script>
Note: Adjust properties for different data sources (Access, SQL etc.)  and providers (SQLClient, OLEDB etc.).

The requirement of Grid Add/Update is the inclusion of the GridAdd and GridUpdate in NavigationItem and setting the dbEditGridUpdate and dbEditGridAdd switch to true. For startup, dbStartUp = "EditGridUpdate or EditGridAdd" must be used. Templates and SQL are actually the same or very close to the internal macros and are for demonstration only. Use these as a guide and modify them as  needed. The normal and grid edit parameters are purposely made different to indicated that they can be set separately.

<script language="VB" runat="server">
Sub Page_Load(Src As Object, E As EventArgs)
   Dim M As New Tornado.z
   M.dbQP = "U=1| D=NWIND| M=G!Sys=t| Q=Select employeeid, lastname, firstname from employees| ni=b5,update,gridupdate| bm=employees;0"
   M.dbEditUpdateFlds = "fi=0| Ty=RONOUPDATE,fi=lastname|type=RONOUPDATE,fi=firstname"
   M.dbEditGridUpdateFlds = "fi=0|Ty=RONOUP,fi=1|Ty=RONOUP,2"
   M.dbGridDisplayFlds = "0,lastname,firstname"
   M.dbEditMagicCell = "fi=lastname|Mac=<A Href=aspdbtest2.aspx?lastname=#(lastname)# TARGET=NEW>#lastname#</A>"
   M.dbMagicCell = "field=lastname|Mac=<A Href=aspdbtest2.aspx?lastname=#(lastname)# TARGET=NEW>#lastname#</A>"
   M.dbEditGridUpdate = True
   M.dbStartUp = "EditGridUpdate"
   M.ASPdbNET()
End Sub
</script>

The above code illustrates - normal edit, grid edit, startup grid edit, normal magic, grid edit magic. Quite a complex task all done in very few lines of code. Pay attention to the separation of normal and grid edit properties.

 

Grid Update and hidden fields - dbGridUpdateSubmitModifiedRowOnly (gusmro)

A common hidden field operation in edit update is the timestamp. A simple edit update timestamp example -

Dim x As New Tornado.z
Dim GD As New Tornado.GetData
x.dbQP = "U=1| M=grid!Sysind=t|S=1| TH=Ti=Update Timestamp| D=TestEdit| Ni=b5,Update| Q=TimestampTable| BM=TimestampTable;0"
x.dbEditUpdateFlds = "fi=1|type=ROUPDATE,2,fi=MyStamp|type=hidden|def=now(MMM-dd-yy HH:mm:ss)"
x.dbDebug = "EditAction"
x.ASPdbNET()

In this example, MyStamp is the datetime time stamp field. Editing a row will update the MyStamp field with a timestamp. When GridUpdate is used, it is necessary to specify a grid edit submit modified row only property such that the row(s) not modified will not be submitted. This would provide the timestamp with a correct values. The default is to submit all the rows together with their hidden values. See the following example -

Dim x As New Tornado.z
Dim GD As New Tornado.GetData
x.dbQP = "U=1| S=1| TH=Ti=EditUpdate Timestamp| D=TestEdit.mdb| EGU=t| Ni=b5,GridUpdate| Q=TimestampTable| BM=TimestampTable;0"
x.dbCommonTables = "(;,~)Index=TOC|Value=Mr.,Mrs.,Ms.,Dr."
x.dbEditGridUpdateFlds = "fi=1|type=ROUPDATE,2,fi=3|type=SelectBox|Val=TOC,fi=MyStamp|type=hidden|def=now(MMM-dd-yy HH:mm:ss)"
x.dbEditGridUpdate = True
x.dbGridUpdateSubmitModifiedRowOnly = True
x.dbDebug = "EditAction"
x.ASPdbNET()

 

User JavaScript Validation

The dbEditValidateName property will provide user JS() function for some express validation. The following example is identical to the above except one additional line -   X.dbEditValidateName = "MyJS()" and the user JS function MyJS(). That is all it takes to perform user JS validation.

<HEAD>
<SCRIPT LANGUAGE="JavaScript">
function MyJS() {
  answer = confirm("U sure?");
  if (answer == "true") {
  return true; // accept the submit
  }else{
  return false; // go back to the form
  }
}
</SCRIPT>
</HEAD>

<script language="VB" runat="server">
Sub Page_Load(Src As Object, E As EventArgs)
Dim X As New Tornado.z
  X.dbQP = "U=1|S=1|M=Grid!sys=t|D=Nwind|ni=b5,add,gridadd,update,gridupdate| Q=Employees| bm=employees;0|gdf=1,2,3"
  X.dbTextHolder = "Title=Normal & Grid Edit"
  X.dbCommonTables = "ind=TT|value=Mr.,Ms.,Dr.,Mrs."
  X.dbEditUpdateFlds = "fi=0|Ty=RONOUP,1,2,HireDate,HomePhone"
  X.dbEditGridUpdateFlds = "fi=0|Ty=RONOUP,1,2"
  X.dbEditAddFlds = "1,2,3,HomePhone"
  X.dbEditGridAddFlds = "1,2,fi=4|Ty=SelectBox|val=TT|Tex=TT,HireDate"
  X.dbDebug = "EditAction"
  X.dbGridUpdateTemplate = "<h2>My Grid Update template</h2><P> [[GridUpdate]]<BR>[[GridNav]]"
  X.dbGridAddTemplate = "<h2>My Grid Add template</h2><P>[[GridAdd]]<BR>[[GridNav]]"
  X.dbEditGridAddSQL = "INSERT INTO Orders (lastname,Firstname,TitleOfCourtesy,HireDate) VALUES ('[[1]]','[[2]]','[[4]]',#[[6]]#)"
  X.dbEditGridUpdate = True
  X.dbEditGridAdd = True
  X.dbEditValidateName = "MyJS()"
  X.ASPdbNet()
End Sub
</script>    

 

Advanced Editing - Multiple Tables

Edit SQL is an advanced provision for user to fully control the editing. Sometimes, a complex editing SQL is required. The dbAddSQL, dbUpdateSQL, dbEditGridAddSQL and dbEditGridUpdateSQL provide you a full deck of Editing cards so to speak. Let's look at a very popular example - Edit two tables.

  1. Create an Access DB called MultipleEdit.mdb.
  2. Create 2 tables - FLname and Address
  3. FLname table -
    ID Lastname Firstname
    1 John Doe
    2 Bill Clinton
  4. Address table -
    ID Addr
    1 Somewhere
    2 White House
  5. Cut/paste the following code and execute it. Make note of  what the following 18 lines of code do and try to duplicate that with other systems. Note that Edit SQLs can have multiple entries separated by [;]. Now you see that you can pick off the values on the screen and edit as many tables as you need to using these values.
    Note: Remember that {{Criteria value}} is the existing value of the record that governs which field to update and [[New Value]] will be filled with the User Input(s) from the editing form. The [[..]] and {{..}} wrappers are non-negotiable. User is responsible for the correct data type wrappers.
     
<script language='vb' runat='server'>
Sub Page_Load(Source as Object, E as EventArgs)
  Dim SQL As String = "SELECT Address.ID, Address.Addr, FLname.LastName, FLname.FirstName 
               FROM Address INNER JOIN FLname ON Address.ID = FLname.ID ORDER BY Address.ID"

  Dim AddSQL As String = "(|;)INSERT INTO FLname (ID, LastName, FirstName) 
                    VALUES ('[[0]]', '[[2]]', '[[3]]'); 
                    INSERT INTO Address (ID, Addr) VALUES ('[[0]]', '[[1]]')"

  Dim UpSQL As String = "(|;)UPDATE FLname SET LastName='[[2]]', FirstName='[[3]]' WHERE ID='{{0}}'; 
                     UPDATE Address SET Addr='[[1]]' WHERE ID='{{0}}'"

  Dim X As New Tornado.z
  X.dbQP = "U=1|M=Grid!RowEdit=true|D=MultipleEdit|NI=b5,add,update|PS=-1|TH=Ti= 2 Tables"
  X.dbSQL = SQL
  X.dbDebug = "ActiveSQL, EditAction, EditSetup"
  X.dbNameMap = "fi=0|Alias=ID|Fo=Address.ID, fi=1|Al=Addr|Fo=Address.addr,
                 fi=2|Al=First|Fo=FLname.firstname, fi=3|Al=Last|Fo=FLname.lastname"
  X.dbBookMark = "FLname;0"
  X.dbEditAddFlds = "0,1,2,3"
  X.dbEditUpdateFlds = "fi=0|ty=RONOUP,1,2,3"
  X.dbEditUpdateSQL = UpSQL
  X.dbEditAddSQL = AddSQL
  X.ASPdbNET()
End Sub
</script>

 

 

Unique Editing - Edit Add

Unique editing features for Edit Add.

Copy - You can select the record and use it as a template for adding a new record. In some cases, this feature is worth the price of the product. You can select from the grid a copy template record and click the 'Copy' button to copy the record to the Add grid all w/o any programming,

 

<script language='vb' runat='server'>
Sub Page_Load(Source as Object, E as EventArgs)
   Dim X As New Tornado.z
   X.dbQP = "U=1|S=1|M=Grid!sys=t|D=Nwind|ni=b5,add|Q=Employees| bm=employees;0|gdf=1,2,3|eaf=1,2,3|Dt=Access|TH=tit=Edit Add Copy"
   X.dbButtonsOnOff = "AddCopy=t"
   X.ASPdbNET()
End Sub
</script>

 

EditFlds Options

Obj.dbEdit???Flds = (;,~)Field| Type | Tag | Def | ValueIndex | TextIndex | Notes | Mask | Event| Min | Max | Req | ErrorText| Oper| CompVal | Help| Title
Obj.dbEditGrid???Flds = Same
Obj.dbFilterFlds = Same
Obj.dbMyInputFlds = Same

Keyword Description
Field Fieldname or field ordinal.
Type Type of input field -
  • Text, TX (Def to SIZE=25, NO OPTIONS) (Super Validator)
  • Password (Super Validator), PW
  • TextCalendar
  • TextArea, MEMO, TA (Super Validator)
  • Radio, Radio+BN0 (B=Blank N=Null 0=Zero), RA
  • Checkbox,Checkbox+BN0 (B=Blank N=Null 0=Zero)
  • SelectBox, SelectBox+BN0 (B=Blank N=Null 0=Zero), SB
  • ROUpdate, ROUP
  • RONOUpdate, RONOUP
  • ROAdd
  • RONOAdd
  • RONOSEND (for RO dbMyIuputFlds)
  • ROSEND (for RO dbMyIuputFlds)
  • Hidden, HI

Note -

  • SuperValidator type = Text, TextArea and Password only.
  • For CheckBox -

    <INPUT TYPE='CHECKBOX' NAME=_EVName CHECKED> TextIndex

    Only need to specify the TextIndex and The Checked status is determined by the Default parameter

Tag Input control tags for both regular and validator controls. For example, use Tags=SIZE=10 to set the size of the INPUT box
Def Default value. Use DEF=Now() for timestamp
ValueIndex ValueList for dropdown. TF = BooleanText True/False Drop. For uneven lists, the length of this list will be trimmed to the textlist length.
TextIndex TextList for dropdown TF = BooleanText True/False Drop
Notes Notes for field.
ErrorText Error Msg for Validator
Mask Regular Expression or Pre-Programmed Masks

RE Limitation -

Validation is performed on the validation mask using 'Perl' regular expressions format. Validation mask should always starts with "\A" and ends with "\Z". It means that matched string should be the begin and the end of string.

Event OnKeyUp | OnBlur | Both
Min,Max Min, Max range values allowed. Use this to implement <5 or >100 using min=0| Max=5 and min=100 | max=999
Req True | False (def) - Required field
ErrorText Error Msg for Validator
Oper MagicValidate Operator - same as MagicCell operators.
CompVal Compare value for Oper. e.g. GT|5 (for MagicValidate)
Help Help File name. Only need to specify once in any of the field entries (usually in the last field). Place a help button in the navigation area for a popup window to pop this file so you can customize help messaged. If a "/" is not present in the filename then the file is assumed to be in the standard template directory. Note that different Edit modes can have it's own help file. Help file can contain any information and not limited to 'editing' syntax.

e.g. ....|Help=UpdateHelp.htm;200;200;300;300

Title This is the Title for the Combo Edit screen where the grid and the edit form are displayed. A normal Edit only screen use the EditTitle Textholder and this title is the top row of the form table. The title defaults to 'Update Record' or 'Add Record'.

 

Boolean Field Editing

SQL values of Boolean fields varies from true/false to -1/0. Tornado allows user to have full control of these values. There are three types of values -

 

Look at the following sample code. The sample SQLExpress DB is 'SQL1' with a table name as 'table1'. The boolean field 'required' display in the grid is the happyface.gif and sadface.gif. The submitted value of the drop box to build the SQL query is -1 and 0. The dropbox true/false display is ya and na. This and many other complexed implementation are built into Tornado.

 

Dim SP As New Tornado.z
   With SP
      .dbQP = "Unit=1|S=7|M=G!sys=t|Ps=-1|Ni=b5,update|Th=ti=SQL Queries|Pv=SQLCLIENT| dt=SQL| bm=table1;0"
      .dbBoolText = "DisplayTrue=<IMG SRC='/tornado/images-net/happyface.gif'>| DisplayFalse=<IMG SRC='/tornado/images-net/sadface.gif'>| DropTrue=-1| DropFalse=0"
      .dbEditUpdateFlds = "0,1,fi=required|ty=SelectBox|va=TnF|te=BOOL"
      .dbCommonTables = "(;,~)Index=TnF,BOOL|Value=-1,0~Ya,Na"
      .dbDSN = "ultra\sqlexpress;SQL1;sa;sasa"
      .dbSQL = "SELECT * FROM Table1"
      .ASPdbNET()
   End With

 


Send comments on this topic.
Copyright 1998-2006 ASP-db