| Version Information |
| Class Name: |
clsTableEditor |
| File Name: |
clsTableEditor.asp |
| Version: |
1.8 |
| Purpose: |
To manipulate with tables (add/edit/delete records). |
| File Size: |
43,512 bytes |
| Release Date: |
03/18/2003 |
| Objects: |
|
| General |
| Definition: |
This class designed to ease editing data in database tables. The idea behind creating of this class was to have highly customizable, yet easy to use table editor. There's very little programming involved in utilizing this class. Obviously, appropriate database and all other support pages has to be created, before using this class. Once web application is completely setup to accept at least one page with this class, it will take very little effort to create interactive pages to edit data in other table of the same database.
|
| Database Standard: |
In order for this class to functioning properly, the database has to be created with certain standards. Name of the tables does not matter, however name of the fields is very important. Every name of a string table should begin with letters "str"; integers are "int"; boolean's are "bit"; and dates are "dat". This limitation does exist because we want to simplify configuration of the class. Everywhere in the class data should be processed differently depends on the type of field, therefore the class is looking for the 3 letters in the beginning of the name, and that's how it determines what to do next. |
| Properties |
| Name: |
AccessLevel |
| Data Type: |
Integer |
| Default Value: |
vbEmpty |
| Required: |
Yes |
| Description: |
Integer value used to compare with Session("AccessRightsID"). If this value is larger than Session("AccessRightsID") then class invokes function UnauthorizedAccess() which will also end the session. Higher this value - more stricted access to this page. |
| Example: |
clsPage.AccessLevel = 3 |
|
| Name: |
AdditionalFields |
| Data Type: |
String |
| Default Value: |
vbNullString |
| Required: |
No |
| Description: |
This property allow you to add more columns in the SELECT query. Sometimes you need to extract more columns without showing them in the table, for example to use them in the links or for custom displaying data from multiple columns in the same cell. This property allow you to list name of columns that will be used in the query.
In the example below we want to display a link to other table. In that link we want to use a reference to the field which is not displayed on the current page, but this field does exist in table one or more tables that used in the SELECT query on the current page. So all we need is to add our column inside the SELECT query, so we can create a reference for it. That's what this property is doing.
In the third example, we are using this property to display the content from multiple columns of another table linked to the combo-box of this table. Please note, when you combine multiple fields with the plus simbol and some other text, do not use comma, because this would screw the number of columns in the table. Use , instead. |
| Example: |
' *** Example 1:
clsPage.AdditionalFields = "intDomainID"
Dim clsLink
Set clsLink = New clsImageLink
clsLink.Add "intDmID", 1, "/ed.asp?m=ed&DD=", "/img/db.gif", False
clsPage.Link = clsLink
Set clsLink = Nothing
Response.Write clsPage.ShowTable()
' *** Example 2:
'Below is another example which will use 3 columns.
clsPage.AdditionalFields = "intCol1, OtherTable.strCol2, bitCol3"
' *** Example 3:
clsPage.ComboBox = Array("Tbl1.Col1")
' This makes the script to display full name in all the rows
clsPage.ComboBoxAlias = Array("Tbl1.Alias1")
' This makes the script to display custom combo-box with full name in the selection
clsPage.ComboBoxEditSQL = _
Array("SELECT ID, Col1 + ' ' + Col2 AS OptDescription FROM Tbl1 ORDER BY OptDescription")
clsPage.AdditionalFields = "Tbl1.Col1 + ' ' + Tbl1.Col2 AS Alias1"
|
|
| Name: |
ColumnName |
| Data Type: |
Array of Strings |
| Default Value: |
vbEmpty |
| Required: |
Yes |
| Description: |
This is an array of names used in the header of columns and as a label in Navigator or Search-box. The number of items in the array should be equal or greater than the number of columns specified in the Columns property. |
| Example: |
clsPage.ColumnName = Array("Site","Page", "Object", "Date") |
|
| Name: |
Columns |
| Data Type: |
Integer |
| Default Value: |
vbEmpty |
| Required: |
Yes |
| Description: |
This property tells to the class how many columns will be displayed. Throughout the class this value is being used to lookup in the arrays of parameters (the other properties). Typically, this number equals to the number of columns to be displayed in the table or on the page.. |
| Example: |
clsPage.Columns = 4 |
|
| Name: |
ComboBox |
| Data Type: |
Array of Strings |
| Default Value: |
Array(0) |
| Required: |
No |
| Description: |
This property sets a link to other tables and enables Combo-Box object in the editing mode. The string value inside of array should be in the form of "TableName.FieldName". The class will link each column to the table as specified in front of period, and will display values from the FieldName in the combo-box. If current object in the sequence is not combo-box, then current item in the array should be set to vbNullString or "". You can omit this property if the page does not have any combo-boxes |
| Example: |
'Display combo-boxes in the 1st and 3rd columns
clsPage.ComboBox = Array("devSites.strSite", "", "devPages.strPage", "") |
|
| Name: |
CookieName |
| Data Type: |
String |
| Default Value: |
vbNullString |
| Required: |
Yes |
| Description: |
Name of cookie to store local options for this page. |
| Example: |
clsPage.CookieName = "cn" |
|
| Name: |
DataType |
| Data Type: |
Array of reserved key-words (String) |
| Default Value: |
N/A |
| Required: |
No |
| Description: |
Finally, you don't have to use Hungarian Notation to name your fields. If you have to use at least one field that is not compatable with our naming convention system, then you need to define this property. This property is an array of reserved key-words. Important! You can't have mixed system. Even if you have only one field that is not comply with our Hungarian Notation and every other filed does - you still have to define datatype for every field. There's no default values for this property. If you have to use it, every element of the array has to be 3-lettered abbreviation representing data type of that field. Acceptable values are: "str", "int", "dbl", "dat" and "bit". |
| Example: |
clsPage.DataType = Array("str", "int", "dbl", "dat", "bit") |
|
| Name: |
FieldName |
| Data Type: |
Array of Strings |
| Default Value: |
N/A |
| Required: |
Yes |
| Description: |
This is most important property. This is an array of string values representing the name of fields in the table or tables. These values should be exact names of column in tables that are in use on this page (main table or related or both).
There is an important limitation in the class. Current version of the class only supports the following data types: String, Integer (including long), Double, Date & Time, Boolean. To simplify configuration of the page, the class has to be able automatially determine the data type. Therefore, it is important to use 3-letter Hungarian Notation as follows: str, int, dbl, dat, bit respectively. Number of elements in the array should be equal or greater than the number of columns on the page as specified in property Columns. |
| Example: |
clsPage.FieldName = Array("strName", "intQuantity", "dblAmount", "datBirthday", "bitEnable") |
|
| Name: |
FilterScriptName |
| Data Type: |
Array of Strings |
| Default Value: |
N/A |
| Required: |
No |
| Description: |
If used, the table will display every item in the cells as hyperlinks. Upon selecting the link, the class will only display items with selected item, so you can filter only items matched the selection. This array of string values is actual name of the file where browser will be redirected upon pressing on the link. It can be page on current server or on the other domain (providing protocol name, of course). If you want to skip filter for particular column, then substitute value in desired column with vbScriptName or "". To redirect filter to the current page, use variable ScriptName. There is other possible choice. Sometimes for boolean values it is more desirable to reverse the value of a cell. Of course, this has nothing to do with the filtering, but this is the right place to have that functionality, therefore you can specify reserved word "<update>". This custom tag could be used only with the boolean fields, and if selected, then boolean value in that cell will be reversed. This property is not requred to be used, and can be completely omitted, but if used, then the number of elements in the array should be greater or equal than number of columns (property Columns). |
| Example: |
cPge.FilterScriptName = Array(ScriptName, "<update>", "", "/othr.asp", "http://a.com/b.asp") |
|
| Name: |
HideID |
| Data Type: |
Boolean |
| Default Value: |
False |
| Required: |
No |
| Description: |
This property allow you to hide ID column. By default it is always displayed. If this property set to True, then the ID column will not be displayed. |
| Example: |
clsPage.HideID = True |
|
| Name: |
Install |
| Data Type: |
Boolean |
| Default Value: |
False |
| Required: |
No |
| Description: |
Normally this property suppose to be False or omited by default. When you set it True, the class executes SQL Script which set in property SQLString. Typically you would use it only once, at the time of installation. |
| Example: |
clsPage.Install = True |
|
| Name: |
Link |
| Data Type: |
Class |
| Default Value: |
N/A |
| Required: |
No |
| Description: |
This is a Sub-Class. With this sub-class you can add new image links into columns. By default, clsTableEdit displays only alpha-numerical informatino extracted from fields. Of course you can have that information appear as links, but you could have only one such link per cell. Not anymore! With this subclass you can add as many as you like image links in each cell. These images will appear on the right or left side of the cell text. To use subclass you have to define variable (in our case it's clsLink). After we assign to the variable the new class clsImageLink, we can set some properties. Width, Height and HSpace are not required. They all have default values, but you may modify them here. Then, we need to add actual objects with the Add method. This method has 5 required parameters. See method description for more information. After all objects has been added, we assign our sub-class variable to the Link property. After that we should terminate sub-class by setting its variable to Nothing.
Example bellow will add 3 links. All three images will have size 13x13 and will have horizontal spacing in the amount of 1 pixel. First link will be added to the second column. |
| Example: |
Dim cLnk
Set cLnk = New clsImageLink
cLnk.Width = 13
cLnk.Height = 13
cLnk.HSpace = 1
cLnk.Add "strF1", 2, "http://a.com/t.asp?v=", "i.jpg", False
cLnk.Add "strF2", 3, "/t.asp?v=", "/otherimg/b.gif", True
cLnk.Add "intF3", 4, "/t.asp?a=1&c=", "/img/c.gif", False
clsPage.Link = cLnk
Set cLnk = Nothing
|
|
| Name: |
Message |
| Data Type: |
String |
| Default Value: |
vbNullString |
| Required: |
No |
| Description: |
This is a string which will appear right under menu before Navigator. You can use this property to display some other forms or any other text. |
| Example: |
clsPage.Message = "Some text appeared under the menu" |
|
| Name: |
NavigatorRows |
| Data Type: |
Array of Booleans |
| Default Value: |
Array(0[, n]) |
| Required: |
No |
| Description: |
This is an array of switches indicating for which field you want to see navigator. For normal operation, you must set property UseNavigator = True. In the example below, you will see navigator for 2nd and 4th columns of your table with 5 columns. |
| Example: |
clsPage.NavigatorRows = Array(0, 1, 0, 1, 0) |
|
| Name: |
NextMode |
| Data Type: |
Boolean |
| Default Value: |
False |
| Required: |
No |
| Description: |
If this property set to true, then the class displays the Next button next to Create button in the new mode. Pressing this button will add new record and bring the form again for entering next data. It's like combining the create and new modes together. |
| Example: |
clsPage.NextMode = True |
|
| Name: |
NoWrap |
| Data Type: |
Array of Booleans |
| Default Value: |
Array(0[, n]) |
| Required: |
No |
| Description: |
This is an array of switches indicating which column should net be wrapped if the text does not fit in the cell. By default all cells could be wraped. To disable wrapping, create and array with the number of elements same as number of columnts on the page. 1 - indicates that this column should not be wrapped, 0 - indicates that column could be wrapped (by default). Example below indicated that 2nd and 4th columns should not be wrapped, where 1st and 3rd could be wrapped if necessary. |
| Example: |
clsPage.NoWrap = Array(0, 1, 0, 1, 0) |
|
| Name: |
ReadOnly |
| Data Type: |
Boolean |
| Default Value: |
False |
| Required: |
No |
| Description: |
If set to True, then editing would not be possible. |
| Example: |
clsPage.ReadOnly = True |
|
| Name: |
Relations |
| Data Type: |
String |
| Default Value: |
vbNullString |
| Required: |
No |
| Description: |
Part of SQL query which defines the relations between two or more tables. If this value used, then it always suppose to begin with word AND, because it's being used in query for statement WHERE immediately after first comparison. |
| Example: |
cPage.Relations = "AND intPgID = devPgs.ID AND intObjID = devObjs.ID" |
|
| Name: |
Secure |
| Data Type: |
Boolean |
| Default Value: |
False |
| Required: |
No |
| Description: |
If set to true, then this page can be viewed only through secure protocol, otherwise, "Access Denied" message will be displayed. |
| Example: |
clsPage.Secure = True |
|
| Name: |
SQLDelete |
| Data Type: |
String |
| Default Value: |
vbNullString |
| Required: |
No |
| Description: |
This is any SQL command that will be executed immideately after deleting records. In other words, when you click on the trash icon to delete a record, then the class will delete that record and then will execute SQL command (or commands separated by semicolon) specified in this propery. |
| Example: |
cPge.SQLDelete = "DELETE FROM tbl WHERE intVal = " & Request("ID") |
|
| Name: |
SQLString |
| Data Type: |
String |
| Default Value: |
vbNullString |
| Required: |
Yes if property Install=True, otherwise No |
| Description: |
SQL String used to create table (or tables) for this page. When you set property Install to True, class executes this SQL script. |
| Example: |
clsPage.Install = True
clsPage.SQLString = "CREATE TABLE tblName (" & _
" id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED," & _
" strRow VARCHAR(256) NULL," & _
" UpdateLogID INT NULL DEFAULT 1);" |
|
| Name: |
TableName |
| Data Type: |
String |
| Default Value: |
vbNullString |
| Required: |
Yes |
| Description: |
Name of the main table. |
| Example: |
clsPage.TableName = "Contacts" |
|
| Name: |
TextBoxLength |
| Data Type: |
Array of Integers |
| Default Value: |
Array(20[, n]) |
| Required: |
No |
| Description: |
Maximum number of characters that allowed to enter in the text-box (the internal length of the text-box). If this property is not defined, then it default value is an array of twenties. If the current column suppose to be a combo-box, then this value will be ignored and can be any number (preferably 0 for easy understanding). If there are only combo-boxes, then this property can be omitted. |
| Example: |
clsPage.TextBoxLength = "Array(5, 6, 0, 10)" |
|
| Name: |
TextBoxSize |
| Data Type: |
Array of Integers |
| Default Value: |
Array(20[, n]) |
| Required: |
No |
| Description: |
Physical size of the appearance of the text-box in characters. If this property is not defined, then it default value is an array of twenties. If the current column suppose to be a combo-box, then this value will be ignored and can be any number (preferably 0 for easy understanding). If there are only combo-boxes, then this property can be omitted. |
| Example: |
clsPage.TextBoxSize = "Array(3, 4, 1, 0)" |
|
| Name: |
UseNavigator |
| Data Type: |
Boolean |
| Default Value: |
False |
| Required: |
No |
| Description: |
With this property you can turn on and turn off navigator on the page. True - will display navigator, False - will not display. |
| Example: |
clsPage.UseNavigator = True |
| Methods |
| Name: |
Add |
| Return Value: |
None |
| Parameters: |
FieldName, ColumnNumber, URL, IconFileName, RightSide |
| Description: |
This method adds image objects to sub-class clsImageLink. There are 5 required parameters. FieldName is a string value of the column name of a table from which informatin will be extracted. ColumnNumber is numeric value of a column number - sequencial number of columns counting from the left. URL is string value of URL that will appear before information taken from the FieldName. It can file name of a script in any folder (relative paths are allowed), or it can be page on different server or even with differen than http protocol. Also special variable is allowed ScriptName it is the path and file name of current file. IconFileName is a string value of the image location include the path and if necessary server name. And finally RightSide is a boolean value indicating location of the image in relation to the cell content: True = right side and False = left side. |
| Example: |
clsLink.Add "strFld1", 2, "http://a.com/tst.asp?v=", "img.jpg", False
clsLink.Add "strFld2", 3, "/tst.asp?v=", "/otherimg/b.gif", True
clsLink.Add "intFld3", 4, "/tst.asp?a=1&b=2&c=", "/img/c.gif", False |
|
| Name: |
ShowTable |
| Return Value: |
String |
| Parameters: |
None |
| Description: |
This is the main method. Typically you will set all neccessary properties of this class with appropriate valued, and then call this method. It will return a string, which is the complete page. |
| Example: |
Response.Write clsPage.ShowTable() |
| History |
| 12/04/2004: |
Added acelerator keys. Now, every text-box has acelerator keys in the sequence number assigned to it, so the first text box could be accessed by pressing Alt+1, second Alt+2 etc. The last one is number 10 which is accessable with Alt+0. Also buttons Create, Next, Rename and Update are accessed by combination of Alt and the first letter of the button. |
| 02/27/2003: |
New property: .Update; This property is a class. It's method Execute() will be executed in the middle of script. This property allow you to execute any function defined outside of this class while you are in the middle of this class. Version 1.19 |
| 02/20/2003: |
New property: .AdditionalFields; This is comma-separated list of column which will be added to the SELECT query before displaying main table. It can be used when you need to select more columns for purposes other than displaying them in the table. Version 1.18 |
| 02/14/2003: |
New property: .SQLDelete; This is a part of SQL command which will be executed when Delete mode is invoked. Version 1.17 |
| 02/07/2003: |
2 new properties: .ComboBoxAlias and .ComboBoxFilter; These properties allow you to create a sophisticated reference to other tables. Version 1.16 |
| 01/28/2003: |
New property: .HideID; If set to True, then the ID column will not be displayed. Version 1.15 |
| 01/10/2003: |
New property: .ReadOnly; If set to True, then this boolean value tells to the class that current table is Read Only and can not be edited. Version 1.14 |
| 01/08/2003: |
New feature: New properties has been added: .EditableFields; .NewRow and .ColSpan With these, you can edit more fields than actual number of columns in the grid. Also aditional editing objects can appear in a multiple rows. Version 1.12 |
| 12/06/2002: |
TextBoxLength and TextBoxSize properties are now not required. Default values are 20. Attention! Watch out for overflow the data if your column is integer or less than 20 characters wide. Version 1.11 |
| 12/01/2002: |
New property: .Message; Allow you to display any text inside the page. Version 1.10 |
| 11/24/2002: |
New feature: New property has been added: .Link This property is a whole new Sub-Class with several other properties and methods. It allow you to have many links (images) in a single cell. Version 1.9 |
| 11/18/2002: |
New feature: New property has been added: .DataType Now you don't have to use Hungarian Notation to name your fields. You can specify data type of each field in this property. Version 1.8 |
| 11/11/2002: |
Fixed bug preventing from using class in tables with single field. Version 1.7 |
| 10/01/2002: |
Changed method of sending data from the form. By default it was method GET and still is, but now if RequireHTTPS property set to true, then method is POST. Version 1.6 |
| 09/15/2002: |
New feature: New property has been added: .NoWrap This property will disable default wrapping for any columns, consisted text, numbers or dates. Version 1.5 |
| 09/14/2002: |
Added new feature: Now in the URL you can type a word, instead of just a letter, in the value field for filtering. Before, to filter only records beginning with letter "M", you would have a url &value=M|dir. Now, you can have several letters, for example &value=Mail|dir will show only records begin with word Mail (case insensitive). Version 1.4 |
| 09/13/2002: |
Fixed bug with navigator, which showed an error when you had 0 in the first item of navigaro row. Version 1.3 |
| 08/26/2002: |
Added RequireHTTPS property. Version 1.2 |
| 08/14/2002: |
Added Install property. Now you can run this class (method Run) with Install property set to True. Set SQLString property to SQL commands to create a new table for current page. Version 1.1 |
| 07/08/2002: |
Renamed to clsTableEditor for multiple table access. Version 1.0 |
| 07/01/2002: |
Bug fixes. Beta Version release. Version 0.2b |
| 06/10/2002: |
Fixed some bugs. Version 0.2a |
| 06/03/2002: |
First Version (alpha) release under name clsSimpleTable. Version 0.1a |
clsTableEditor
Class_Initialize
