| home / programming / javascript / rg / 1 |    [previous][next] |
|
|
|
As in the last article, since there are some ASP scripts, you will need to have a web server which supports ASP at your disposal. I used IIS 5 for my testing.
There are 7 files included with this article. They can be downloaded here. Here's an explanation of each:
1. vehicle lists 2000.mdb:
An Access database. It should reside in a folder called "URPMs
db" located directly parallel to the "WWWRoot" folder.
2. URPMs.dsn
A DSN file containing database connection information.
All the following files should be placed in a folder called "URPMs", directly under the "WWWRoot" folder.
3. ConvertRStoXML.xsl:
An xsl file used to populate the base URPM.
4. generateXML.html:
An Admin page which you can use to create a static xml file.
5. generateXML.inc:
The asp script which converts the database fields into XML.
6. generateXMLfile.asp:
Used in conjuction with the generateXML.html page to create a static xml file.
7. testXML.asp:
The main asp page - used for testing the script.
Unzip the files to the parent directory of your server’s WWWRoot. This will probably be inetpub folder. Here is a diagram of the resulting directory structure, once the files have been extracted:
<c:\inetpub>
|
|
WWWRoot------URPMs db----->vehicle lists 2000.mdb
| URPMs.dsn
|
|
URPMs------>ConvertRStoXML.xsl
generateXML.html
generateXML.inc
generateXMLfile.asp
testXML.asp
There are two ways to use the scripts, depending on the frequency of your database updates:
A: If you don't update the menu contents very often, say once a week or less, you can create a static XML file which will be read by the testXML.asp script. To do this, open the generateXML.html web page in your browser. The URL should look something like this: "http://localhost/URPMs/generateXML.html". There is some crude password security to stop would-be hackers from running the script, should you decide to upload the page to your production server. If you do plan on using this page on a publicly accessible server to update your XML files, you should, at the very least, change the password by modifying the file in a text editor.
Once you have supplied the correct id and password, it will generate a file named "URPM.xml" in the "URPMs db" folder. The testXML.asp script will use this file whenever it is present to avoid generating the XML from scratch.
B: If you always want the latest-and-greatest data to be fetched, you need only skip the previous step and run the testXML.asp script directly!
To use the script with your own asp page, you will have to make some changes to your database as well as add some asp scripts. Here's what you have to do:
1: Create the File DSN
In part 2, I used a dsn-less connection to connect to the database, for the sake of performance. This time I went with using a File DSN. My main reason for doing so is that it saves you from having to make changes to the code. To create the DSN:
That's all you really need to do, but for added security, you can hit the Options>> button and check the Read Only checkbox. You could also enter a user ID and Password in the Advanced dialog if your database requires it.
For those of you with really busy web sites, a dsn-less connection may be the way to go. Here are the two lines that you'll need to modify in the generateXML.inc file:
'modify the following line to use a dsn-less connection
'sConnString =
"DRIVER={Microsoft Access Driver (*.mdb)};" & _
"DBQ=" & Server.MapPath("../../URPMs
db/yourDatabase.mdb") & ";"
'oConn.Open "FILEDSN=c:\inetpub\URPMs db\URPMs.dsn"
You will have to change the database name to your own. It is also possible that you will have to change the path if your database does not reside in the default "URPMs db" folder situated parallel to the "WWWRoot".
2: Design the Query
In the generateXML.inc file, there is a reference to a query named "getLists." You will need to create this query to allow the script to retrieve the list data from your database. All you have to do is display the data in a description, code value format, starting with the parent list through its children, from left to right. In my test database, I wound up with Manufacturers, Models, and Levels, in that order. Remember to group the code fields together by sorting them in ascending order so that the script will know where one ends and the other begins. This is also the ideal place to format the list text such as formatting the case and sorting. I chose to sort the list contents in ascending order. Appearance-related formatting such as font and background color, on the other hand, should be done in a CSS stylesheet. Here is the SQL for the testXML.asp script:
SELECT Manufacturer.Manufacturer,
Manufacturer.[Manufacturer ID],
Model.Model,
Model.[Model ID],
Level.Level,
Level.[Level ID]
FROM (Manufacturer INNER JOIN Model ON Manufacturer.[Manufacturer
ID] = Model.[Manufacturer ID])
INNER JOIN [Level] ON Model.[Model ID] = Level.[Model ID]
ORDER BY Manufacturer.Manufacturer,
Manufacturer.[Manufacturer ID],
Model.Model,
Model.[Model ID],
Level.Level,
Level.[Level ID];
Running the getLists query in the Vehicle lists 2000.mdb database produces the following output:
| home / programming / javascript / rg / 1 | [previous][next] |
Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | Shopping | E-mail Offers | Freelance Jobs 
Created: March 27, 2003
Revised: June 2, 2005
URL: http://webreference.com/programming/javascript/rg/1
