JSI Tip 4599. How do I use the new FrontPage 2002 File Upload component while you write the form data to an Access database and send it via e-mail?

Start FrontPage 2002.

From the task pane, select Web Site Templates.

Select Empty Web from the templates, specify a location for the Web to be created, and then click OK.

NOTE: If you are creating a disk-based Web, you must publish the Web to a server that meets the requirements listed at the beginning of this article in order to successfully run these pages.

Create the following pages by clicking the New Page icon on the toolbar, and then click Save As on the File menu:

Click Start, point to Programs, point to Accessories, and then click Notepad.

Type the following code into the Notepad page:

[form_results.txt]ColNameHeader=TrueFormat=CSVDelimitedMaxScanRows=25CharacterSet=OEMCol1=NAME Char Width 255Col2=EMAIL Char Width 255Col3=COMMENTS Char Width 255Col4=FILE Char Width 255  

Save this page to a location on your hard disk as Schema.ini. In the Save As dialog box in Notepad, click All Files on the Save as type list.

In Notepad, click New on the File menu.

Leave this page blank, and save it to the same location as Schema.ini but with the following name: form_results.txt.

Quit Notepad.

In FrontPage, click the _private folder. On the File menu, click Import and then click Add File. Browse to the location where you saved the two files that you just created. Select both files, click Open, and then click OK.

The two files should now be in the _private folder in your Web.

With your Web open in FrontPage, click Web Settings on the Tools menu, and then click the Database tab.

Select Add.

In the Name box, type Text.

Under Type of connection, make sure that the File or folder in current Web option is selected, and then click Browse.

In the Files of Type list, select Microsoft Text Driver.

Navigate to the _private folder, and select the form_results.txt text file. Click OK three times to close all dialog boxes.

In the Folder List, right-click the top-level folder, click New, and then click Folder on the shortcut menu.

Name the folder Upload.

Right-click this new folder, and then click Properties on the shortcut menu.

In the Properties dialog box, clear the Allow scripts to be run check box. Select the Allow anonymous upload to this directory and Allow uploaded files to overwrite existing filenames check boxes, and then click OK.

On the Insert menu, point to Form and then click Text Box.

Double-click the text box, type Name for the name, and then click OK. You can also add a label if you want.

Repeat steps 1 and 2, and name the new text box Email.

Insert a Text Area form component.

Double-click the text area box, type Comments for the name, and then click OK.

Insert the File Upload component. On the Insert menu, click Form and then click File Upload.

Double-click the text box, type File for the name, and then click OK.

Use the form on this page to create your Access database and database connection. To do this, follow these steps:

The Form Properties dialog box should be displayed, and the Send To option should be selected. Click the Options button.

The file format should be Text database using comma as a separator.

On the File Results tab, under File name, change the extension on the default file to send results to from .csv to .txt (for example, _private/form_results.txt).

Click the E-mail Results tab.

Type the e-mail address that you want to send the form results to.

Click the Confirmation Page tab.

Click the Browse button, select confirmation.htm, and then click OK.

Click the Saved Fields tab.

Select B1 and then press DELETE to remove B1 from the list.

Click the File Upload tab.

Click Browse and then select the Upload folder that you created earlier.

Click OK to close the Saving Results dialog box, and then click OK again to close the Form Properties dialog box.

Save and close the page.

Open Process1.asp in the FrontPage Editor.

Switch to HTML view, and then type the following code directly under the tag:

<%'////////////////////////////////////////////////////////// '//  The first two lines of code are calling the connection '//  object for the text file that is stored in the '//  global.asa file.'////////////////////////////////////////////////////////// Set Conn = Server.CreateObject("ADODB.Connection")Conn.Open Application("text_ConnectionString") '////////////////////////////////////////////////////////// '//  Next you create a record set object, execute the  '//  database connection and a SQL query.'////////////////////////////////////////////////////////// Set RS = Conn.Execute ("SELECT * From form_results.txt")'////////////////////////////////////////////////////////// '//  You then use code to loop through the database and  '//  select the last record entered.'////////////////////////////////////////////////////////// Dim iCntDo Until RS.EOFiCnt = iCnt + 1  Name = RS("Name")  Email = RS("Email")  Comments = RS("Comments")  File = RS("File")RS.MoveNextLoop'////////////////////////////////////////////////////////// '//  Close the record set and the connection.'////////////////////////////////////////////////////////// RS.CloseConn.Close%> 

Switch to normal view.

On the Insert menu, point to Form and then click Text box to insert a form with a text box on the page.

Double-click the text box, and name it Name.

In the Initial Value box, type the following:

<% = Name %> 

Repeat steps 4, 5, and 6 for both the E-mail field and Comments field (except that the Comments will be a text area), and then set the names and initial values appropriately.

Right-click inside the form, and then select Form Properties on the shortcut menu.

Click the Send to other option (the list box should read Custom ISAPI, NSAPI, CGI, or ASP Script).

Click the Options button. In the Action box, type process2.asp, and then click OK.

Click the Advanced button in the Form Properties dialog box, and then click Add.

In the name box, type File.

In the Initial Value box, type the following code:

<% = File %> 

Click OK three times to return to your page.

Save this page and close it.

Open Process2.asp in the FrontPage Editor.

On the Insert menu, point to Database, and then click Results to start the Database Results Wizard.

In page 1 of the wizard, choose the connection named Default, and then click Next.

In page 2 of the wizard, select the Custom Query option, and then click the Edit button.

In the Custom Query dialog box, type the following code:

INSERT INTO Results (Name, Email, Comments, File) VALUES ('::Name::', '::Email::', '::Comments::', '::File::') 

Click OK and then click Next.

In page 3, click More Options. Under Message to display if no records are returned, type the following:

Click OK and then click Next.

In page 4, click the list and select List - one field per item.

Clear the Add labels for field values and Place horizontal separator between records check boxes, and then click Next.

In page 5, clear the Add search form check box, and then click Finish.

Save this page and close it.

Open Display.asp in the FrontPage Editor.

On the Insert menu, point to Database and then click Results to start the Database Results Wizard.

In page 1 of the wizard, choose the connection named Default, and then click Next.

In page 2, select Results as the record source, and then click Next.

In page 3, you can edit the list of records that will be displayed, to remove any fields that you do not want to appear.

Click Next.

In page 4, select List - one field per item, and then click Next.

In page 5, select the Split records into groups option, and then type 1 in the input box.

Click Finish.

Locate the database column value that reads <> and delete it.

In the table cell that you just removed the column value from, insert any picture from your own images or from clip art.

Right-click the image that you just inserted, and then click Picture Properties on the shortcut menu.

On the General tab, click Parameters under Picture Source.

In the path field, remove the current path, click Insert Field Value, and then select File.

Click OK two times to return to your page. You see a small broken image icon in the column under File.

Save the page and close it.