Database connectivity is becoming one of the most important issues in website development. With an Internet database, a plethora of possibilities is opened up. Businesses can catalog their products, even sell them online. Information about members of a special group can be stored and retrieved. In fact, any functionality which requires information storage and retrieval can be achieved. I have created database-connective websites which password-protect areas, conduct auctions, provide employment services, and even created a database-driven discussion area, to mention a few.
FrontPage 97 has some nice utilities for doing relatively easy database connectivity with IDC (Internet Database Connector). IDC is being slowly "phased out" by Microsoft, in favor of ASP (Active Server Pages) and ADO (ActiveX Data Objects). However, it is still a viable and relatively simple way of achieving many of the same functions which ASP provides, without the necessity of a great deal of programming capability. I will discuss ASP and ADO in future articles. Today I want to introduce you to IDC, with an example from my website, the "Simplified Password Protection" tutorial. More detailed explanations of the following can be obtained by going to the page, which I've linked above.
The first page has a form for entering user name and password, as illustrated below:
As you can see, the user names and password which are contained in the sample database are provided. Once the user enters them correctly, they are taken to the following page:
This page contains links to other password-protected pages.
Of course, if the wrong user name and/or password is typed in, this is all they will see:
The only link on this page takes the user back to the password page.
How does it work? Actually, it's pretty simple. The second 2 pages are actually derived from a single "HTX" page. The HTX page is a template which IDC uses to format the results of a database query written in SQL (Structured Query Language), a nearly universally recognized language for communicating with databases. IDC sends an SQL query to an ODBC (Open Database Connectivity) driver on the host machine. Information about the name and location of the database is stored in a System DSN (Data Source Name), which the IDC file also sends to ODBC. The ODBC driver executes the query and returns the results to the server. The server opens the HTX template file, and using special tags in the HTX file, combines the data with the file to produce a "virtual" page to the browser. The "chain" of events looks something like this:
HTML Page >> Server >> IDC File >> ODBC Driver >> Server >> HTX file >> Browser
How to Create the "Chain"
NOTE: This is all pertaining to FrontPage 97. FrontPage 98 has little support for IDC, although there are ways of doing IDC with FrontPage 98, which I won't go into here.
First, of course, you create the original form in a standard HTML page. The Form has an ACTION property and a METHOD property. You're going to use an IDC file and an HTX file in the chain as well. So, before you set the ACTION property of the form, create your HTX file, then your IDC file. Why? So you can browse to them. Start with the HTX file.
Select File|New from the menu, and "Database Results" from the list. This creates your HTX file. (NOTE: IDC and HTX files should be stored in a folder which is set to "Execute" and not "Read" - you can set the properties for this folder in FrontPage Explorer). The HTX file in this case will contain database column value tags and if/else conditional tags. Below you can see a representation of the top portion of the HTX page:
Note the red diamond. This is the FrontPage Editor's symbol for an if/else conditional tag, or more accurately, the "if" tag. This page will display the "Sorry, Charlie" portion of the page if the SQL statement does not return any data. To create this portion, you would create the area you want to be displayed in the case of no record returned, and below it, the area which you want to show if the query is successful. In a sense, you're creating 2 pages on the same page. Only the area which satisfies the conditions you define will be returned to the browser. Select the first area, and select "Edit|Database|If-else Conditional Section" from the menu. You will see the following dialog box:
The top box indicates that you are testing the value of a database column in the record you've retrieved. The name of the field (column) is "id," so I've entered the name of the field in the "Value" box. The third box indicates that you are testing for equality. The fourth box indicates that you are testing against a "Constant Value," and the fifth box indicates that the value is an empty string. Translated into the actual tag, this will look (in HTML) like this: <%if id EQ ""%> Translated into English, what this says is "If the value of the 'id' field is equal to an empty string, display the following." It applies to all of the page which you've selected, and is terminated by an "<%endif%> tag. This tag looks like a red backwards arrow. However, you also want to indicate that if the first condition tests false, display the second section. So you highlight the second section and select "Edit|Database|Else Conditional Section. This places an "<%else%>" tag before the second section and moves the "<%endif%>" tag to the end of the second section.
Note the red arrow in front of the second section. This represents the "<%else%>" tag. Also, note the "<%id%> tag on the page. This is a database column value tag. It indicates that the value of the "id" field should be inserted here. To create a database column value tag, you place the cursor where you want the value to appear, and select Edit|Database|Database Column Value from the menu. In the resulting dialg box, just type in the column name. Hit "ENTER" and it appears on the page. NOTE: Don't just type the tag in as it appears above. Use the menu command. Otherwise, you will see "Welcome <%id%>" on the page instead of "Welcome Johnny" when the IDC file is run.
Now you're ready to create your IDC file. Select File|New|Internet Database Connector from the menu. It brings up the IDC Wizard. The Wizards's dialog box has 3 pages. The first looks like this:
For "ODBC data source" you will want to type in the System DSN which you've created for this database. As you recall, the System DSN is created through the ODBC driver administrator in Control Panel. When you create the System DSN for this database, you assign a Data Source Name (DSN). This is the name which you want to enter here.
User Name and Password are for secured databases, and it is doubtful that you will need to use them. You can use the "browse" button to browse to the HTX file which you've created. Then on to page 2:
Here is where you enter your SQL statement. Note the parameters (they are enclosed with "%" characters) which are inserted. These correspond to the form field names in the form which the user has filled out. When comparing to a "text" field in the database, you should additional enclose the parameters in single quotes. If the field in the database is numeric, leave the quotes out. Now for the third page:
Default parameter values are used when no value is passed from the form. There are several uses for default parameter values, the most common of which is to have some value to send to the ODBC driver if a form field is left blank. In this case, I've used form field validation in the form page, to ensure that something is typed in, so there are no default parameter values defined.
Save the IDC file in your executable folder and give it a name. the IDC file is actually just a script, in text form, which you could create without the wizard if you wanted to. Here's the above IDC file in its' text form:
Datasource: employment
Template: gateway.htx
SQLStatement: SELECT * FROM password
+WHERE id LIKE '%id%' AND password LIKE '%password%'
Now back to the form page.
Right-click the form and select "Form Properties" from the popup menu. It should have POST already set as the default METHOD. In the top drop-down list, select "Internet Database Connector." Click the "Settings" button and browse to the IDC file. Click "Okay," and you're all set to begin testing your IDC "Chain."
A Final Word
This has been an introduction to IDC only. There is a lot more to know about IDC to really employ it successfully, and not enough space here to go into all the "gory details." That's one reason why my website at http://www.connectrans.com/takempis contains so much information and tutorials for working with IDC. If you want to use IDC, stop by my website for all kinds of help.
Some other things worth mentioning: IDC is a built-in function of IIS; therefore, you can only use IDC if the host machine for your web is NT running IIS, or Windows 95 running MS PWS. IDC doesn't require FrontPage extensions to work. And finally, FrontPage 98 has removed much support for IDC, in favor of ASP. However, you can still create IDC files with a text editor, and you can create HTX pages as well. To create an HTX page in FrontPage 98, create a normal HTML page and save it with an "HTX" extension. Open it again, and all of the IDC database ocmmands will be available for customizing it.
I will cover ASP, as I said, in future articles. I wanted to start with IDC, as it is easier to implement, since ASP requires a good bit of programming to use. I hope this has been helpful and enlightening. See you next week.
Author: Kevin
Spencer
Date: 10/10/97
More
articles about Microsoft FrontPage
More
articles by Kevin Spencer
Author
Biography