Microsoft KB Archive/299931

= HOW TO: Send Form Results to a Database and an E-mail Message and Upload a File in FrontPage 2002 =

PSS ID Number: 299931

Article Last Modified on 10/27/2002

-

The information in this article applies to:


 * Microsoft Windows 2000 Server
 * Microsoft Windows 2000 Advanced Server
 * Microsoft Windows 2000 Professional
 * Microsoft FrontPage 2002
 * FrontPage 2002 Server Extensions from Microsoft
 * Microsoft Windows NT Server 4.0
 * Microsoft Internet Information Services 5.0
 * Microsoft Windows NT version 4.0 Option Pack
 * Microsoft Access 2000
 * Microsoft Access 2002

-



This article was previously published under Q299931



IN THIS TASK

 * SUMMARY
 * ** Hardware and Software Requirements
 * Setting Up the Web Project in FrontPage 2002
 * Setting Up the Web Pages
 * *** Confirmation.htm
 * Default.htm
 * Process1.asp
 * Process2.asp
 * Display.asp
 * View the Results
 * Troubleshooting
 * REFERENCES



SUMMARY
This step-by-step article explains how to use the new Microsoft FrontPage 2002 File Upload component while you write the form data to an Access database and send it to an e-mail address.

The File Upload component cannot be used with the Send To Database component in FrontPage 2002, because the former requires a page with an .htm file extension, and the latter requires .asp. Specifically, the File Upload component is processed on the server by the Shtml.dll file. When the user requests a page with an .asp file extension from the server, this indicates to the server that the page contains server-side ASP code and must be processed by the Asp.dll file. The server cannot process the page through both DLL files before sending the results to the client.

back to the top

Hardware and Software Requirements

 * Microsoft Windows 2000 Server, Microsoft Windows 2000 Advanced Server, or Microsoft Windows NT 4.0 Server
 * Microsoft Internet Information Services (IIS) 4.0 or 5.0
 * Microsoft FrontPage version 2002
 * Microsoft FrontPage 2002 Server Extensions on the Web server
 * Microsoft Data Access Components (MDAC) version 2.1 or later

back to the top

Setting Up the Web Project in FrontPage 2002
 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:

confirmation.htm

process1.asp

process2.asp

display.asp

 Click Start, point to Programs, point to Accessories, and then click Notepad.  Type the following code into the Notepad page: [form_results.txt] ColNameHeader=True Format=CSVDelimited MaxScanRows=25 CharacterSet=OEM Col1=NAME Char Width 255 Col2=EMAIL Char Width 255 Col3=COMMENTS Char Width 255 Col4=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.</li> In Notepad, click New on the File menu.</li> Leave this page blank, and save it to the same location as Schema.ini but with the following name: form_results.txt.</li> Quit Notepad.</li> 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.</li> The two files should now be in the _private folder in your Web.</li> With your Web open in FrontPage, click Web Settings on the Tools menu, and then click the Database tab.</li> Select Add.</li> In the Name box, type Text .</li> Under Type of connection, make sure that the File or folder in current Web option is selected, and then click Browse.</li> In the Files of Type list, select Microsoft Text Driver.</li> Navigate to the _private folder, and select the form_results.txt text file. Click OK three times to close all dialog boxes.</li> In the Folder List, right-click the top-level folder, click New, and then click Folder on the shortcut menu.</li> Name the folder Upload .</li> Right-click this new folder, and then click Properties on the shortcut menu.</li> 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.</li></ol>

The Web project is now set up.

back to the top

Confirmation.htm
The custom confirmation page is used only to send the user to the first ASP page. This occurs after Form.htm is submitted. To set up this page, follow these steps: <ol> <li>Open Confirmation.htm and then switch to HTML view.</li> <li> Directly under the <HTML> tag, type the following code: <meta http-equiv=&quot;refresh&quot; content=&quot;0; url=../../process1.asp&quot;> </li> <li>Save this page and then close it.</li></ol>

back to the top

Default.htm
Default.htm is the form page, an HTML page that contains the form to be submitted. To set up this page, follow these steps: <ol> <li>On the Insert menu, point to Form and then click Text Box.</li> <li>Double-click the text box, type Name for the name, and then click OK. You can also add a label if you want.</li> <li>Repeat steps 1 and 2, and name the new text box Email .</li> <li>Insert a Text Area form component.</li> <li>Double-click the text area box, type Comments for the name, and then click OK.</li> <li>Insert the File Upload component. On the Insert menu, click Form and then click File Upload.</li> <li>Double-click the text box, type File for the name, and then click OK.</li> <li>Use the form on this page to create your Access database and database connection. To do this, follow these steps: <ol style="list-style-type: lower-alpha;"> <li>Right-click inside the form, and then click Form Properties on the shortcut menu.</li> <li>Click the Send to database option.</li> <li>Click the Options button.</li> <li>Click the Create Database button.</li> <li>When FrontPage displays a message indicating that the database has been created, click OK. In the Options for Saving Results to Database dialog box, click Cancel.</li> <li>In the Form Properties dialog box, verify that the Send to option is selected, and that the File Name box (which indicates the location to store the form data) says: &quot;_private/form_results.txt&quot; (without the quotation marks).</li></ol> </li> <li>The Form Properties dialog box should be displayed, and the Send To option should be selected. Click the Options button.</li> <li>The file format should be Text database using comma as a separator.</li> <li>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).</li> <li>Click the E-mail Results tab.</li> <li>Type the e-mail address that you want to send the form results to.</li> <li>Click the Confirmation Page tab.</li> <li>Click the Browse button, select confirmation.htm, and then click OK.</li> <li>Click the Saved Fields tab.</li> <li>Select B1 and then press DELETE to remove B1 from the list.</li> <li>Click the File Upload tab.</li> <li>Click Browse and then select the Upload folder that you created earlier.</li> <li>Click OK to close the Saving Results dialog box, and then click OK again to close the Form Properties dialog box.</li> <li>Save and close the page.</li></ol>

back to the top

Process1.asp
Process1.asp contains custom code that pulls the last record from the form_results.txt page and then writes this information into a form with text input boxes, so that the user can verify their information before sending it to the Access database. The URL to the uploaded file is contained in a hidden field, because you do not want the user to change this. The file has already been uploaded, and, at this stage, there is no way for the user to make changes. <ol> <li>Open Process1.asp in the FrontPage Editor.</li> <li> Switch to HTML view, and then type the following code directly under the <HTML> 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(&quot;ADODB.Connection&quot;) Conn.Open Application(&quot;text_ConnectionString&quot;) '//////////////////////////////////////////////////////////    '//  Next you create a record set object, execute the '// database connection and a SQL query. '//////////////////////////////////////////////////////////

Set RS = Conn.Execute (&quot;SELECT * From form_results.txt&quot;)

'//////////////////////////////////////////////////////////    '//  You then use code to loop through the database and '// select the last record entered. '//////////////////////////////////////////////////////////

Dim iCnt Do Until RS.EOF iCnt = iCnt + 1 Name = RS(&quot;Name&quot;) Email = RS(&quot;Email&quot;) Comments = RS(&quot;Comments&quot;) File = RS(&quot;File&quot;) RS.MoveNext Loop '//////////////////////////////////////////////////////////    '//  Close the record set and the connection. '//////////////////////////////////////////////////////////

RS.Close Conn.Close %>

</li> <li>Switch to normal view.</li> <li>On the Insert menu, point to Form and then click Text box to insert a form with a text box on the page.</li> <li>Double-click the text box, and name it Name .</li> <li> In the Initial Value box, type the following: <% = Name %> </li> <li>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.</li> <li>Right-click inside the form, and then select Form Properties on the shortcut menu.</li> <li>Click the Send to other option (the list box should read Custom ISAPI, NSAPI, CGI, or ASP Script).</li> <li>Click the Options button. In the Action box, type process2.asp, and then click OK.</li> <li>Click the Advanced button in the Form Properties dialog box, and then click Add.</li> <li>In the name box, type File .</li> <li> In the Initial Value box, type the following code: <% = File %> </li> <li>Click OK three times to return to your page.</li> <li>Save this page and close it.</li></ol>

back to the top

Process2.asp
Process2.asp contains a database results region with a custom SQL statement that inserts the information passed from Process1.asp into the Access database. <ol> <li>Open Process2.asp in the FrontPage Editor.</li> <li>On the Insert menu, point to Database, and then click Results to start the Database Results Wizard.</li> <li>In page 1 of the wizard, choose the connection named Default, and then click Next.</li> <li>In page 2 of the wizard, select the Custom Query option, and then click the Edit button.</li> <li> In the Custom Query dialog box, type the following code: INSERT INTO Results (Name, Email, Comments, File) VALUES ('::Name::', '::Email::', '::Comments::', '::File::') </li> <li>Click OK and then click Next.</li> <li>In page 3, click More Options. Under Message to display if no records are returned, type the following:

Your profile has been accepted!

</li> <li>Click OK and then click Next.</li> <li>In page 4, click the list and select List - one field per item.</li> <li>Clear the Add labels for field values and Place horizontal separator between records check boxes, and then click Next.</li> <li>In page 5, clear the Add search form check box, and then click Finish.</li> <li>Save this page and close it.</li></ol>

back to the top

Display.asp
The final page of this example displays records from the Access database one at a time. You will incorporate a method to dynamically display images from a folder location that is stored in the database as the URL. This method is fully documented in the following Microsoft Knowledge Base article:

216285 FP: How to Display a Picture Using a Microsoft Access Database


 * 1) Open Display.asp in the FrontPage Editor.
 * 2) On the Insert menu, point to Database and then click Results to start the Database Results Wizard.
 * 3) In page 1 of the wizard, choose the connection named Default, and then click Next.
 * 4) In page 2, select Results as the record source, and then click Next.
 * 5) 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.
 * 6) Click Next.
 * 7) In page 4, select List - one field per item, and then click Next.
 * 8) In page 5, select the Split records into groups option, and then type 1 in the input box.
 * 9) Click Finish.
 * 10) Locate the database column value that reads <<File>> and delete it.
 * 11) In the table cell that you just removed the column value from, insert any picture from your own images or from clip art.
 * 12) Right-click the image that you just inserted, and then click Picture Properties on the shortcut menu.
 * 13) On the General tab, click Parameters under Picture Source.
 * 14) In the path field, remove the current path, click Insert Field Value, and then select File.
 * 15) Click OK two times to return to your page. You see a small broken image icon in the column under File.
 * 16) Save the page and close it.

back to the top

View the Results
Select Default.htm and then click Preview in browser on the File menu. Type your information in the form. When you submit the form, the data that you typed is written to the Form_results.txt file, and the e-mail message is sent. The custom confirmation page displays the Process1.asp page, where you see the information that you just submitted in text input boxes. Click Submit, and the data is written to the Access database.

Type the address for Display.asp to view the records that you submitted.

back to the top

Troubleshooting
Using a text file as a database is not a very scalable solution. If you expect heavy traffic on your Web site and the possibility of many users submitting this form simultaneously, you should consider a different solution.

back to the top

<div class="references_section">