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()GridEditUpdate using SELECTBOX and CHECKBOX
Dim x As New Tornado.z x.dbQP = "U=1| S=1| D=NWIND| EGU=t| Ni=b5,GridUpdate| Q=products| BM=products;0" '...use either one of these two lines to test SELECTBOX and CHECKBOX effect x.dbEditGridUpdateFlds = "fi=discontinued| Ty=selectbox| Val=TF" 'x.dbEditGridUpdateFlds = "fi=discontinued| Ty=checkbox| Val=TF" x.dbGridUpdateSumbitModifiedRowOnly = 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.
- Create an Access DB called MultipleEdit.mdb.
- Create 2 tables - FLname and Address
- FLname table -
ID Lastname Firstname 1 John Doe 2 Bill Clinton - Address table -
ID Addr 1 Somewhere 2 White House - 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 -
Note -
|
| 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 -
- Grid display representation of true/false
- Edit display representation of true/false in a drop box
- The actual values submitted used to build the Edit SQL query.
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
| Blank Edit Input Fields |
|---|
When an input field is blank during an Edit, there are two ways to build
the Edit SQL ->
1. field = null
2. field = ''
Look at the following code. If you include the line
.dbEditAllowZeroLength = True
Then you'll have Required=''. Otherwise, you'll get the standard
Required=null.
Send comments on this topic.
Copyright 1998-2006 ASP-db