NetChain Communications, Inc. _

TableEditor - Database Administration Class

www.NetChain.com • (858) 602-6342
Home - NetChain
Services
Web Site Hosting
Search Engine Optimization
Domain Names Registration
Registration Agreement
ICANN Policy
Network Administration
Digital Phone - VoIP
Log Analytics and Reports
Copywriting
Products
 NetCPS
 Access My Computer
 Biorhythm Calculator
 What is Biorhythm?
 Phonetic Keyboard Layout
Tools
About us
Our Portfolio
Contact Information
Contact Form
Cancellation Policy
Shipping Policy
Service Agreement
Employment Opportunity
Reference
Listen to Cool Music


Page updated on:
Mon, 11 Oct 2004 05:52:00 GMT
_

This is a documenation of ASP class clsTableEditor. For information on purchasing this class, please contact NetChain Communications at 619-512-2255.

 clsTableEditor Documentation 
Version Version Information
General General Information, description and usage
Properties Properties and Methods
Samples Samples of source code on using this object
History History of this object

 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:
Name Type Default Value       Required      
  clsTableEditor Class n/a n/a
  Class_Initialize Method n/a n/a
  Class_Terminate Method n/a n/a
  ShowTable Method n/a n/a
  AccessLevel Integer 0 Yes
  AdditionalFields String vbNullString No
  ColSpan Array of Integers None No
  ColumnName Array of Strings vbEmpty Yes
  Columns Integer 1 Yes
  ComboBox Array of Strings Array(Number of Columns) No
  ComboBoxAlias Array of Strings Array of Empty Strings No
  ComboBoxFilter Array of Strings Array of Empty Strings No
  CookieName String vbNullString Yes
  DataType Array of Strings (reserved key-words) Array of empty strings No
  DateFormat Array of Integers Array(Number of Columns) No (Yes if any column has Date type)
  DefaultOrder String vbNullString Yes
  DefaultSort String vbNullString Yes
  EditableFields Integer vbEmpty No
  FieldName Array of Strings .Columns Yes
  FilterScriptName Array of Strings Array(Number of Columns) No
  HideID Boolean False No
  Install Boolean False No
  Link Class n/a No
  Message Strings Array(Number of Columns) No
  NavigatorRows Array of Booleans Array(Number of Columns) No (Yes if UseNavigator = True)
  NewRow Array of Booleans None No
  NextMode Boolean False No
  NoWrap Array of Booleans vbNullString No
  OtherTables Strings vbNullString No
  PageName Strings vbNullString Yes
  PageTitle Strings vbNullString Yes
  ReadOnly Boolean False No
  Relations Strings vbNullString No
  Secure Boolean False No
  Search Boolean False No (Yes if ShowSearch = True)
  ShowReplace Array of Boolean Array(Number of Columns) No
  ShowSearch Array of Boolean Array(Number of Columns) No
  SQLDelete String vbNullString No
  SQLString String vbNullString No (Yes if Install = True)
  TableName String vbNullString Yes
  TextBoxLength Array of Integers Array(20[, n]) No
  TextBoxSize Array of Integers Array(20[, n]) No
  Update Class Object vbNull No
  UseNavigator Boolean False No
  UseReplace Boolean False No
 
  clsImageLink Class (Sub-Class) n/a n/a
  Class_Initializ Method n/a n/a
  Class_Terminate Method n/a n/a
  Add Method See note Yes
  Width Integer 13 No
  Height Integer 13 No
  HSpace Integer 0 No
 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
_

o t