Universal Table Editor

• Download source files - 190 Kb

In this article:

• 1 Overview
• 2 Features
• 3 Details
  • 3.1 How to install UTE
  • 3.2 How to use UTE
    • 3.2.1 List Tables within Database
    • 3.2.2 View Table
    • 3.2.3 View Definitions
    • 3.2.4 Add/Edit/Delete Data
    • 3.2.5 Define Filters
    • 3.2.6 Export Data
    • 3.2.7 Show SQL Statement
  • 3.3 How to include UTE into your webpage
  • 3.4 How to use UTE in different languages
• 4 UTE Reference
  • 4.1 URL Parameter
    • 4.1.1 Public URL Parameter
    • 4.1.2 Private URL Parameter
  • 4.2 Public Properties
  • 4.3 Public Methods
    • 4.3.1 Connection Types
  • 4.4 Files
• 5 UTE Life Demo
• 6 Some final words
• 7 Revision History

 

1 Overview

Universal Table Editor is an ASP application to view and edit data in any database you can reach via ADO from your IIS. Compatible databases are e.g. MS Access�, MS SQL Server� or Oracle�. UTE is also able to export the data of a table into comma delimited text files (.CSV) which are readable e.g. by MS Excel�.

UTE is a set of files representing just one single VBScript class clsUTE which can be used in simple ASP pages or the most complex environments. Everything UTE uses is encapsulated within the clsUTE class so there shouldn't be any namespace problems at all. UTE defines all variables correctly so it is option explicit proof. The layout of UTE is completely style sheet driven so it is quite simple adjustable to any layout.

Former UTE versions (<= 1.4) used session variables quite extensively. This is history now! UTE uses no session variables any more. UTE includes an .inc file defining all language dependant settings and strings. It already comes with a number of pre-defined language files (English, German, French, Italian, Spanish) but it is not much of a problem for you to extend this list by your own.

 

2 Features

• Insert, edit and delete records in a table.
• Define any number of filters to shorten the number of records.
• Export complete table content as .CSV file.
• Show the exact field definitions.
• Sorting of each column.
• Automatic primary key detection.
• Display the currently used SQL statement to read the datas from the Database.
• UTE is one VBScript class with some few easy to use public properties and functions.
• In action UTE is controlled via URL parameters.
• The layout is completely style sheet driven.
• All displayed strings are defined in a special language include file.
• Error management.
• UTE is option explicit proof.
• Binary Data types will not be displayed and are not editable.

 

3 Details

3.1 How to install UTE

To install UTE you just have to copy all files from the ZIP (including sub directories) to a IIS virtual directory (e.g. ute30). That's all. Launch the index.html page to edit the included demo database.

UTE uses ADO (ActiveX Data Objects - installed on the Server) to access the database. I recommend the Microsoft ADO Pages to get more information on this technology. I have tested UTE with many databases based upon MS Access�, MS SQL Server� and Oracle�. If you run into any trouble after installing UTE you should check for the latest MDAC (Microsoft Data Access Components) version and database providers which are available here.

 

3.2 How to use UTE

3.2.1 List Tables within Database

By calling the ute.asp page without any URL parameters or using the above toolbar button UTE will list all tables within the current database. By clicking on them, the table editor will open for this table.

http://localhost/ute30/ute.asp

click to enlarge

 

3.2.2 View Table

To open UTE with a specific table you only need to set the name URL parameter to define the name of the table:

http://localhost/ute30/ute.asp?name=OrderDetails

click to enlarge

UTE comes with a small demo database being taken from the Microsoft Northwind demo. If you want to connect to another database you need to modify the ute.asp file (see chapter 4 UTE Reference).

By using the Pages links you can navigate through the entire table. By using the Records per Page you can select the number of records being displayed on one page.

UTE tries to detect all primary key fields of the table and display them in the most left columns in italic style. This won't work in every case. There are OLEDB provider which don't return these information (whether being called directly or via ODBC). If UTE is not able to detect the primary key fields of your table, you should try another connection type (see chapter 4.3.1 Connection Types) or define them in the URL with the pkey[counter] URL parameter:

http://localhost/ute30/ute.asp?name=OrderDetails&pkey1=OrderID&pkey2=ProductID

UTE is able to sort the contents of the table by any column (field) by clicking on the header of each column. Furthermore one can specify any number of fields as URL parameters to sort the table after. To do this, use the sort[counter] URL parameter. The default sort direction is ascending. Use additionaly the sortdir[counter] URL parameter to specify another sort order:

http://localhost/ute30/ute.asp?name=OrderDetails&sort1=CustomerID&
sort2=EmployeeID&sortdir2=desc

Please note that by clicking on a column header you will loose all sort parameters above "1".

UTE displays the fields of the table in the order they have been defined within the database. Earlier UTE versions sorted them alphabetically. If you want them now to be sorted alphabetically you need to set the sorted URL parameter:

http://localhost/ute30/ute.asp?name=OrderDetails&sorted=1

See chapter 4.1 URL Parameter for a reference for all URL parameters.

 

3.2.3 View Definitions

By using the above toolbar button in the table or form view UTE will display the definitions of the fields within the table.

click to enlarge

 

3.2.4 Add/Edit/Delete Data

By using the , and links in the table view it is possible to insert, edit and delete records to/in/from the table.

click to enlarge

With clicking on Ok the changes will be made permanent in the database. With clicking on Cancel it will just be returned to the table view without any changes in the database. UTE does not do any field validation but instead simply returns the error messages it receives from ADO. If the error is assignable to a special field the error message will be display directly at the field, otherwise it will be display at the top of the page.

click to enlarge

 

3.2.5 Define Filters

By using the above toolbar button you will be able to define any number of filters to reduce the amount of records within the table. Even though the dialog currently offers just 10 filters this is just a constant definition within UTE.

click to enlarge

Each filter will be taken as one WHERE clause. The filters can be connected by AND or OR. To remove filters again (and get all records form the table) just use the Clear button.

There is no direct validation of the values you enter for a filter. If you define invalid values for a filter field UTE will create an invalid SQL statement from it, get's an ODBC error and redirects back to this page and displays the original ODBC error message.

click to enlarge

 

3.2.6 Export Data

By using the above toolbar button UTE will export the content of the table into a "comma-separated" Text File (.CSV) which can be imported by other applications like e.g. MS Excel�. The file will look like:

"OrderID","ProductID","Discount","Quantity","UnitPrice"
"10248","42","0","10","9,8"
"10248","72","0","5","34,8"
"10248","11","0","12","14"
"10249","14","0","9","18,6"
"10249","51","0","40","42,4"
...

UTE will create the data on the fly and sends them to the browser. By setting setting the ContenType and an additional Header the browser will be able to start MS Excel� if desired.

Response.Buffer = TRUE
Response.Clear
Response.ContentType = "text/csv"
Response.AddHeader "Content-Disposition", "inline;filename=" & m_sTable & ".csv"

 

3.2.7 Show SQL Statement

By using the above toolbar button UTE will display the SQL statement being used to read the data from the database as well. This might be useful when working with filters.

 

3.3 How to include UTE into your webpage

Please use the ute.asp file as a quite easy example of how to include UTE into any webpage. There is not much to do at all:

<%@ language = "VBScript" %>
<% option explicit %>
<!--#include file ="ute_definition.inc"-->
<%
  Dim sDSN
  sDSN = "Provider=Microsoft.Jet.OLEDB.4.0;" &_
         "Data Source=" & Server.MapPath("test.mdb")
  Dim ute
  Set ute = new clsUTE
  ute.Init sDSN
%>
<!doctype html public "-//W3C//DTD HTML 3.2//EN">
<html>
<head>
  <title><%=ute.HeadLine%> - Universal Table Editor</title>
  <link rel="stylesheet" type="text/css" href="ute_style.css">
</head>
<body bgcolor="#FFFFFF" link="#0000A0" vlink="#0000A0" alink="#0000A0">
<%
  ute.Draw
  Set ute = Nothing
%>
</body>
</html>

Chapter 4 UTE Reference lists all public properties and functions the clsUte class exports and also explains how to connect to a database.

All definitions and the class itself will be included via the ute_definition.inc file. If you encounter problems e.g. with the URL parameter names UTE uses this is the place to simply give them some other names. I have written UTE as flexible as possible so you can change really a lot without need to dive in deeply into the logic. The layout is completely style sheet driven within the file ute_style.css.

 

3.4 How to use UTE in different languages

Within UTE all strings to be displayed on the screen are defined in a separate language include file: ute_language_*.inc. UTE comes already with a number of predefined languages files:

Language	File
English	ute_language_en.inc
German	ute_language_de.inc
French	ute_language_fr.inc (by Evelyn Berkemeier)
Italian	ute_language_it.inc (by Marco De Sortis)
Spanish	ute_language_es.inc (by Agustin Vega)

Per default UTE uses the English file. It is the first include statement within the ute_definition.inc file. So all you have to do to change the language of UTE is to change this include statement. If you translate UTE into another language please share it with the rest of us (see chapter 6 Some final words for how to share your work) !

 

4 UTE Reference

4.1 URL Parameter

There are two kinds of UTE parameters: public and private parameters. Well, URL parameters are not really public and private, but they are meant to. The public parameters should be used by you, the private parameters shouldn't. They are created and used by UTE only.

Please note that UTE will keep ALL your own URL parameters you set additionaly when opening a page where UTE is included in.

 

4.1.1 Public URL Parameter

Name	Default	Description
name		Name of the table to be viewed/edited. Note: some database might be case sensitive at this point.
pkey[counter]		If UTE is not able to detect the primary key fields of a table you should set all of them using this parameter, where [counter] is an integer counter of the field you want to define, starting with 1. Note: some database might be case sensitive at this point. Do not define gaps within the counting ! You will also note, that UTE will set these parameters by it's own while browsing through a table. This is to simplify the code.
sort[counter]		To ask UTE to sort the content of a table after a specific column, you can use this parameter (any number of it) to specify the sort field(s). This will result in a ORDER BY clause. The [counter] is an integer counter of the field starting with 1. By default all fields will be sorted ascending. If you want a different sort order for a field, you need to use the sortdir[counter] URL parameter.
sortdir[counter]	asc	By default UTE sorts all records in a sorted column ascending. To sort a field in a descending order, you need to use this URL parameter with the value desc. The [counter] is an integer counter of the field starting with 1. If you want the first and second field to be sorted ascending and the third field descending you have to the a parameter sortdir3=desc.
sorted	0	UTE displays the fields (columns) in the order as they are defined within the database (except primary key fields, which will always be displayed in the most left columns). Set this parameter to 1 if you want UTE to display the fields/columns alphabetically sorted.

 

4.1.2 Private URL Parameter

Name	Default	Description
page	1	Number of page being displayed
pagesize	10	Size of page in number of records.
mode	0/1	View mode of UTE.ASP page. There are three modes possible: 0 - List all tables within current database. 1 - Table Mode, display current table. 2 - Form Mode, display form to insert, edit or delete record. 3 - Export Data Mode, export data within current table to .CSV file. Please note: if the name parameter is not set the default is 0, otherwise 1.
formmode	1	Form mode of UTE.ASP page if in view mode 2 (Form Mode). There are three form modes possible: 1 - Insert Record 2 - Edit Record 3 - Delete Record
record		Number of record to be edited or deleted. This value is only used by UTE if there are no primary keys known (detected or manually set). In this case UTE uses all fields of the record to identify (select) it within the database. This might cause trouble when there are text (memo) fields within the table which contain some special characters. You should really make sure that UTE knows the primary keys of your table !
sql	0	Display the current SQL statement UTE uses to get all records from the database. Valid values are 0 and 1.
definitions	0	Display the field definitions of the table. They will be displayed in both view modes (table and form). Valid values are 0 and 1.
submitted	0	Signals if the form in form view mode was submitted or not. Both buttons (ok and cancel) do submit the form.
fltcount	1	The number of filters being selected to define currently.
fltfield[counter]		The name of the field the filter is defined for. The [counter] is an integer counter of the field starting with 1.
fltcomp[counter]	=	The comparison definition of the filter. The values being taken from the combobox on the filter definition page. Currently available values are: =, >, >=, <, <=, LIKE, IS, IS NOT. The [counter] is an integer counter of the field starting with 1.
fltvalue[counter]		The value of the filter. The [counter] is an integer counter of the field starting with 1.
fltcomb[counter]	AND	This is how the differnet filters will be combined. The values being taken from the combobox on the filter definition page. Currently available values are: AND and OR. The [counter] is an integer counter of the field starting with 1.
flterror		If UTE created an invalid SQL statement from an invlaid filter definition this parameter will hold the text of original ODBC error message. It will be used to be displayed on the filter definition page.

 

4.2 Public Properties

Name	Type	Read/Write	Description
DBName	string	R/W	Name of the current database. This is for displaying purpose only. If UTE displays the list of all tables within the current database, the DBName will be will displayed in the headline of the page.
HeadLine	string	R	Headline of the UTE page. This can be used within the <title> tag of your ASP page.
TableName	string	R	Name of currently displayed/edited table. This is the same string as passed via the name URL parameter.
ReadOnly	boolean	W	With setting this property to True UTE will display no links to modify any record and will also prevent modifying the database if the user manipulates the URL parameters. Default: False
ListTables	boolean	W	Using this property you can show and hide the List Tables in Database toolbar button. Additionaly it is possible to start UTE without any URL parameters and get all tables listed. If this parameter is set to False and UTE is called without URL parameters you will get an error message. Default: True
Filters	boolean	W	Using this property you can show and hide the Define Filter toolbar button. Default: True
Export	boolean	W	Using this property you can show and hide the Save as CSV (EXcel) file toolbar button. Default: True
SQL	boolean	W	Using this property you can show and hide the Show/Hide current SQL Statement toolbar button. Default: True
Definitions	boolean	W	Using this property you can show and hide the Show/Hide Field Definitions toolbar button in the table and form view. Default: True
ImageDir	string	R/W	Name of the directory the UTE images are located in. This can either be absolute or relative. Important is the trailing / (slash) ! Default: images/

 

4.3 Public Methods

Name	Returns	Parameter	Description
Init		sDSN	Initializes UTE. This includes the following steps: Read and verify all URL parameters. Setting the headline according to the view mode and form mode. Open the connection to the database. Analyze the table for primary key and other fields. Load the table (recordset) from the database. If in export view mode create the .CSV file and redirect to it. If in form view mode and the form was submitted do all necessary update stuff. This function must be called before the first HTML output of your ASP page ! The parameter sDSN is the connect string to the database to be used. See chapter 4.3.1 Connection Types for further informations.
Draw			Draws the HTML code for the table or form directly to the HTML output stream by using Response.Write.
getHTML	string		Returns the HTML code for the table or form as string.

 

4.3.1 Connection Types

You can either use dsn (ODBC) or so called dsn-less connections. Please note that depending on the connection type UTE is not always be able to detect the primary key fields of a table due to differences in the used drivers. If you encounter problems just switch to the other connection type if possible.

dsn (ODBC) connection

If you have created an ODBC database on your machine and connected it to a database you can use the datasource name as connect string:

ute.Init("myDatabase")

If you need to login to the database the login information can also be placed within the connect string:

ute.Init("dsn=myDatabase;uid=myName;pwd=myPassword")

dsn-less connection

Another way to connect to a database is using so called OLE-DB providers instead of ODBC. This is useful if your have no chance to create an ODBC datasource on your web server. An OLE-DB provider will connect to a database directly without using ODBC. The most common case would be to connect to an MS Access� database being placed in some directory of your website. This is how a dsn-less connect string would look like in this case:

ute.Init("Provider=Microsoft.Jet.OLEDB.4.0;" & _
 "Data Source=" & Server.MapPath("test.mdb")

Examples of other dsn-less connection strings can be found at the ADO Connection String Samples page compiled by Able Consulting, Inc.

 

4.4 Files

This is how the files within UTE are connected to each other:

• ute_definitions.inc
  Defines all global constants and includes the next files:
  
  
  • ute_language_*.inc
    All language depandent string definitions.
  • ute_adolib.inc
    A number of helper functions dealing the ADO.
    
    
  • ute_class.inc
    Defines the clsUTE class with a number of private functions being used on loading the page and includes the next files:
    
    
    • ute_class_database.inc
      Defines all functions being used in "database" mode.
    • ute_class_table.inc
      Defines all functions being used in "table" mode.
    • ute_class_form.inc
      Defines all functions being used in "form" (add/edit) mode.
    • ute_class_export.inc
      Defines all functions being used in "export" (create CSV file) mode.
    • ute_class_filter.inc
      Defines all functions being used in "filter" (define filter) mode.

 

5 UTE Life Demo

To take a view on how UTE looks and works like in real life just visit my UTE pages at

• http://www.wellige.com/tom/ute

 

6 Some final words

If you want to learn ASP and/or ADO this application might be a good choice to have a closer look on. On the other side it might be a little bit complex for a beginner, so you decide if you simply want to use it, or alter it.

From version 2.0 on I have placed UTE under the GNU General Public License (GPL). This is mainly to make sure that nobody earns big money from the work of others without sharing his own work also with the rest of us and/or hiding the fact that UTE isn't his own work. I think this is just fair, isn't it ?

Due to the fact that my job keeps my quite busy I have also decided to create a new location for the future development of UTE and to invite everyone to take part in it ! The simple key word is:

If you have some good ideas for UTE and want to implement and share them or you have created a new language file please feel free to join the ute-asp project at

• http://sourceforge.net/projects/ute-asp

Please feel free to join the discussion board here at CodeProject or at SourceForge and if you want just visit my homepage and leave a small note in my guestbook.

Thanks for your interest and enjoy UTE !

 

7 Revision History

1.0	16.02.2000	First Release
This version won the first CodeProject Article Contest.
1.1	08.08.2000
Bug Fix: Opening an empty table. Bug Fix: Saving NULL fields.
1.2	12.02.2001
Improved primary key field detection. Display fields in defined order (and not alphabetically).
1.3	08.03.2001
Improved primary key field detection. Bug Fix: Usage of Single Quotation Marks (') in string fields.
1.4	14.03.2001
Bug Fix: Wrong Date format in SQL statement when updating a record.
2.0	23.11.2002
Completely rewritten. No more session variables. Just one VBScript class to include into own webpage. Ready for localization into any language. Comes already with a number of languages. Option explicit proof. And a number of bug fixes being reported in the past. All future development of UTE will be coordinated on SourceForge: http://sourceforge.net/projects/ute-asp/ Feel free to join !
2.1	14.01.2003
List all tables of a database if no tablename is set as URL paramter. Any number of fields to sort a table after: sort[n], sortdir[n]. For compatebility porpose the single parameters "sort" and "sortdir" are still valid. Dropdown lists to select page and pagesize instead of links. Custom URL parameters will be kept while navigating through a table. Fixed width of MEMO fields in table view mode.
3.0	28.04.2003
Define (any number of) filters to search for records. A toolbar on top of the table view instead of some links below it. Display SQL statment being used to get table data. Exporting the table data as CSV file will no longer create a CSV file in the "/export/" folder. The data will be created and sent "on the fly" to the user's browser. Italian language definition file (by Marco De Sortis) Spanish language definition file (by Agustin Vega) Display Boolean fields as checkboxes instead of TRUE and FALSE. Bugifx: Removed table caching. This was responsible for heavy delay times when opening large tables. Bugfix: when using the sort parameters "sort" and "sortdir" the defined values where not resetted when switching the table using the LIST TABLES mode. Bugfix: unable to update boolean fields in environments other then German or English.
3.01	07.05.2003
Bugfix: !!! Syntax error ute_class.inc line 1010 (v2.10) !!! !!! Syntax error ute_class_filter.inc line 127 (v3.00) !!! Due to some mishandling of SourceForge's CVS system I got one INC file in UNIX format and one in MAC format. These formats are obviously *NOT* supported by NT4 PWS/IIS. All files are now plain MS-DOS foramt. Bugfix: error when calling UTE with "pkey" parameter. Improvements in primary key detection.