EDITOR.ASP Reference

Download EDITOR.ZIP (contains the EDITOR.ASP program, this documentation, and a sample CSS file).

Introduction

EDITOR.ASP is a general-purpose editor for Microsoft Access tables. The database with the table and EDITOR.ASP are stored on the server. EDITOR.ASP then can be called from any Web browser. It executes on the server and delivers the result back to the Web browser. EDITOR.ASP supports the following database editing functions:

• List the contents of an Access table.
• Update any record in the Access table.
• Insert a new record into the Access table.
• Delete a record from the Access table.

I have written EDITOR.ASP primarily as a learning exercise to understand how ASP can work together with my Web browser and my Microsoft Access tables. EDITOR.ASP is freeware. You may use or distribute the program, as long as you keep the copyright notice. If you modify or enhance the program, you must keep a reference to the original copyright notice (and I would appreciate getting a copy of the enhanced code - just to learn from you). You use EDITOR.ASP at your own risk. Although I use the program on my home Intranet on a regular basis, I do not accept any responsibility for the correctness of the results you get from EDITOR.ASP.

This document describes EDITOR.ASP version 1.1. I'm assuming that you have a general understanding of the ASP concepts, Microsoft Access, Visual Basic and HTML. The documentation is structured as follows:

1. System Requirements
2. Installation
3. Customisation
   1. Mandatory Customisations
   2. Optional Customisations
   3. Database Connection
4. Using EDITOR.ASP
5. Inside EDITOR.ASP
6. Issues and Limitations
7. Support
8. Change History

Acknowledgements: I'd like to thank all who have helped developing and enhancing EDITOR.ASP, especially:

• Eli Robillard - his GenericDB program was my starting point
• 4GuysFromRolla - I have found many important hints and tips on their site, and they also were first to publish EDITOR.ASP
• Rama Ramachandran - his article about displaying paged recordsets was of big help for implementing the pagewise navigation
• All users of EDITOR.ASP who provided feedback about the initial version of EDITOR.ASP
• My wife Marianne, who still loves me even when I spend my nights in front of my computer.

18.3.2000 / Roman Koch
Homepage: http://www.romankoch.ch

System Requirements

As I have developed EDITOR.ASP as a learning exercise, I did test it on my home Intranet only. I therefore can only document what I'm using and take some assumptions about other platforms.

Installation

Installation is easy - just unzip EDITOR.ZIP and copy the EDITOR.ASP file to any directory on your Web server. You need one copy of EDITOR.ASP for every Access table you want to edit, and you can rename EDITOR.ASP as you like to keep multiple instances of the program in one directory. The program and the Access database can reside in different directories. The directory where you store EDITOR.ASP must support ASP execution. After installing EDITOR.ASP, you must customise the program.

Customisation

Before you can use EDITOR.ASP, you must customise it to indicate the Microsoft Access database and table as well as some other values. Use your favorite ASCII Editor, for example Notepad, to open EDITOR.ASP and make the necessary modifications in the code. There are mandatory customisations as well as optional customisations.

EDITOR.ASP does not check your customisations for errors. If you do a mistake in your customisations, you will usually get an ASP error message in your Web browser when you load the program. The ASP error message usually contains the line number of the ASP script that has produced the error.

Note: Do not write any HTML or VB statements above the initial <% or below the closing %> statement.

Mandatory Customisations

The mandatory customisations have no default value; you must enter a valid value. At the top of EDITOR.ASP, you will find the following code:

' --------------------------------------------
' Mandatory customisations

' myMDB         The absolute or relative path to
'               your .mdb file, as seen on the server
' myTable       The name of the Access table
' myKey         The field name of the primary key field,
'               must be "Autonumber" type
' myTrueWord    The value for a boolean "true" in your local language,
'               e.g. "TRUE" in English or "WAHR" in German
' myPage        The number of records per page

Const myMDB         = "C:\WEBSHARE\WWWROOT\HAUSNETZ\TOOLS\Servi.MDB"
Const myTable       = "buchung"
Const myKey         = "ID"
Const myTrueWord    = "TRUE"
Const myPage        = 10

The constants have the following meanings:

myMDB

The path and filename of your Microsoft Access database, as seen on your server. The path can be absolute, e.g. "C:\WEBSHARE\WWWROOT\HAUSNETZ\TOOLS\Servi.MDB" or relative, e.g. "..\TOOLS\Servi.MDB".

myTable

The name of your Microsoft Access table. The table must have a primary key field of type "Autonumber".

myKey

The name of your primary key field.

myTrueWord

The word your version of Microsoft Access is using to display boolean "True" values. This mainly depends on the OS language or regional settings of your server. If all components are English, the value usually is "True". On my server, with the German version of Windows 98, the value needs to be "Wahr".

myPage

The number of records to be displayed per page. This value should usually be between 5 and 25, depending on your screen resolution.

Optional Customisations

All optional customisations have an internal default value; if you don't want to use an option, just leave the value of the constant as a zero-length string (""). Below the mandatory customisations, you will find the following code:

' ---------------------------------------------------------------------
' Optional customisations

' myOrder       SQL "Order By" clause, e.g. "ORDER BY Name ASC, Age DESC"
' myWhere       SQL "Where" clause, e.g. "WHERE Age > 20"
' myStyle       Cascading Style Sheet, e.g.  "../Tools/css_body.css"
' myStripes     Alternate color, e.g. "#CCB6B5"
' myDates       Date formatting (0=GeneralDate, 1=LongDate, 2=ShortDate,
'                                3=LongTime, 4=ShortTime)

Const myOrder       = ""
Const myWhere       = ""
Const myStyle       = ""
Const myStripes     = "#CCB6B5"
Const myDates       = 0

The constants have the following meanings:

myOrder

The SQL "Order By" clause. Use this option to sort the table (if not specified, the records are displayed in the default sort order). This must be a fully qualified and valid piece of SQL syntax, e.g. "ORDER BY Age DESC".

myWhere

The SQL "Where" clause. Use this option to return only a subset of all records (if not specified, all records are returned). This must be a fully qualified and valid piece of SQL syntax, e.g. "WHERE Age > 20".

myStyle

The relative path to a cascading style sheet. If not specified, no styles are applied (great for Netscape Communicator). If specified, the CSS file is embedded. You must specify the path as it is seen on the Web, e.g. "http://servi/hausnetz/tools/css_body.css" or "../Tools/css_body.css".
A sample style sheet, EDITOR.CSS, is included in the EDITOR.ZIP package.

myStripes

If specified, every second table row will be displayed with a different background color. Use a valid HTML color code, e.g. "#CCB6B5".

myDates

Specifies how date/time fields are formatted. 0 for general date, 1 for long date, 2 for short date, 3 for long time, 4 for short time.

Database Connection

EDITOR.ASP uses DSN-less connections to connect to your database. Below the customisation section, you will find the following connection string:

objConn.ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
myMDB & ";Persist Security Info=False"

You may need to modify this string if you need to use System DSNs, or if you are using another version of the JET engine.

Using EDITOR.ASP

Once you have installed and customised EDITOR.ASP, you can launch it from your client Web browser. Enter the URL to EDITOR.ASP, e.g. http://servi/hausnetz/tools/editor.asp. Do not provide any parameters (e.g. ?num=) to the program yourself. EDITOR.ASP then is executed on the server. It processes your customisations, connects to the database, opens your table and displays the records. Each record in your Access table is displayed as a row in a HTML table.

On the top and left of the page, you will find hyperlinks to navigate through your table and to work with your records:

All record operations present the current (or new) record as a form on a new page where you can either modify the data, fill in the blank fields for a new record, or confirm the deletion of the current record. Again, EDITOR.ASP does not apply any error checking. If you enter data that is considered invalid by the JET engine, you will get an error message.

Inside EDITOR.ASP

I have tried to keep the VB code clean and easy to read. This chapter therefore documents the main principles only. Play around with the code to get an understanding of the details.

EDITOR.ASP works by calling itself recursively with varying parameters. When first called, without any parameters, EDITOR.ASP establishes the ADO connection to the database, calculates the page navigation hyperlinks, and displays the first set of records. For each record, it writes calculates hyperlinks for record operations. Let's take the following assumptions for the example below:

• The table has 50 records.
• The primary key is "clean", e.g. it is 1 for the first record, 2 for the second record, and 50 for the last record.
• We display 10 records per page.
• We are looking at page 3.
• We intend to work with the third record on this page, which is equal to the 23rd record in the table.

EDITOR.ASP calculates the hyperlinks as follows:

Now let's look at the hyperlinks for record operations in more detail. The following steps are performed for each record operation:

1. Call EDITOR.ASP to display a form with the field values and a confirmation button.
2. Call EDITOR.ASP to apply the changes to the table.
3. Call EDITOR.ASP to refresh the list.

The Edit command uses three parameters. The page=3 parameter tells EDITOR.ASP to return to the third page once Edit command processing has been completed. The action=update parameter tells EDITOR.ASP to write a new page where the fields of the record can be updated. The num=23 parameter tells EDITOR.ASP that the user wants to edit record #23. When the user clicks the hyperlink, EDITOR.ASP processes the parameters and calls the editUpdate function. A form is written with a text-type input field for each field, and the contents of record 23 are filled into the form. Then a button is displayed at the end of the form to confirm the update. The "action" property of the form is set to "editor.asp?page=3&ampaction=updateexec&ampnum=23". When the Update button is clicked, EDITOR.ASP is called again with these parameters. It processes the parameters, calls the editUpdateExec function and writes the contents of the form fields into the table. Finally, it calls EDITOR.ASP with only the page=3 parameter to return to the list view and display page 3 again.

The New command differs from the Edit command in two aspects: It displays an empty form where the user can fill in new values for each field. And, after inserting the new record into the table, it displays the last page of the table to make sure the user can see the new record. This behaviour is used only if the myOrder constant has not been customised, e.g. is a zero-length string.

The Delete command uses the same logic as the Edit command.

Issues and Limitations

There are a couple of issues and limitations with the current version of EDITOR.ASP. Let me know if you know how to overcome them:

• Field Types: The program is designed for handling basic field types only. It may not work with fields of type Memo, OLE Object or Hyperlink.
• Double Quotes: You may get an error if a text field contains double quotes.

Support

The program is provided "as is". I'm not in a position to give support for it, neither by eMail nor by phone. I will try to answer your eMails, but this may take a couple of days, and most often all I can say you is "it works on my home Intranet".

Change History

From 1.03 to 1.1

• Uses DSN-less connection.
• Uses constants instead of session variables.
• Paged Recordsets; number of records per page can be customised. Displaying large tables now works much faster.
• Now correctly formats currenty strings.
• Optional customisation for date formats.
• Now correctly handles empty tables.
• Documentation in HTML format.

From 1.01 to 1.03

• The restriction for text fields (Allow Zero Length must be Yes) has gone.
• Support for boolean fields (requires customisation).
• Update and Insert forms now indicate field types to avoid typos.
• Add function now is much faster, especially for large tables.
• Optional customisations for the WHERE and ORDER clauses.

Copyright Roman Koch · Nutzungsbestimmungen · Datenschutz