Microsoft KB Archive/172868

-

{| The information in this article applies to:
 * width="100%"|
 * Microsoft Visual InterDev, version 1.0
 * Microsoft Access versions 7.0, 97
 * Microsoft Active Server Pages, version 1.0b

SUMMARY
This article describes how to use a combo box in an Active Server Pages (ASP) page to return detailed information about the item selected in the combo box, and then use the detailed information to populate a form.

In addition, the following topics are covered:


 * How to create an Access Form based on the Customers table in the Adventure Works database (AdvWorks.mdb) and add a combo box that allows the user to obtain detailed information about the selected item.
 * How to create a Form based on the Customers table in the AdvWorks.mdb database.
 * How to export the Form to Active Server Pages and add the exported Active Server Pages files to a Visual InterDev Web project.
 * How to modify the code to implement the combo box in the Active Server Pages.

To complete the sample tutorial the user will need the following: - Visual InterDev 1.0.

- Internet Information Server 3.0

- Active Server Pages 1.0b with Adventure Works samples installed and

working. - Access 7.0 or Access 97.

MORE INFORMATION
Additional Notes

- The files adovbs.inc and SessionVars.inc have been

renamed to adovbs.txt and SessionVars.txt to allow them to be sent via email. These files will need to be renamed back to the .inc extension to work inside the Web project. They needed to be renamed due to the fact that .inc files appear to be lost in the email. - If you have Access 7.0 (Win95) you will not need to convert the database to Access 97 in step one below. This is done to allow editing of the database in Access 97. To export the Form as an ASP file you must open the database in Access 97.

Step One: Convert the AdvWorks.mdb database to Access 97
1) Open Access 97 and from the Tools > Database Utilities menue select Convert Database.

2) Locate the AdvWorks.mdb file located in the AdvWorks directory ([drive]:\InetPub\ASPSamp\AdvWorks), select it and click Convert.

3) Save the AdvWorks.mdb with the same name but to a temporary location.

4) After saving the converted database to the temporary location, copy it back into the AdvWorks directory replacing the original AdvWorks.mdb file with the new Access 97 AdvWorks.mdb file.

Step Two: Create the frmCustomers Form
1) Open Access, open the AdvWorks database ([drive]:\InetPub \ ASPSamp \ AdvWorks)and select the Forms tab. Click the New button. Select Form Wizard and in the drop-down box in the bottom of the "New Form" dialog, choose the Customers table from the listbox. Click OK.

2) Click the >> button to select all the fields and then click Next. Accept the defaults by clicking Next until you reach the dialog box asking you to name the Form. Name the Form frmCustomers to distinguish it from the existing Customers Form in the AdvWorks database. Click "Finish".

3) When the form appears, select View / Design from the menu. Move and resize the controls on the Form so they are clearly visible. Allow approxiamately 1/4 of an inch between the controls and the left hand side of the Form and the top of the Form so the controls will display correctly on the HTML page generated by the exported ASP file.

5) Drag the Detail bar down until there is about 1/2 of an inch of Form Header available. Make sure View / Toolbox is visible, and drop a ComboBox control onto the Form Header. The Combo Box Wizard dialog box will appear. Select the 'Find a record on my form based on the value I selected in my combo box' option button and click Next.

6) Click the > button and add the following fields: CustomerID and CompanyName and click next. On the next dialog box leave the 'Hide key column' checked and click Next.

7) Enter 'Select Customer' for the combo box lable and click Finish.

8) Resize the combo box so it will display correctly in the ASP page as described above.

9) Change the name property of the combo box label to lblSelectCustomers and change the name property of the combo box to cboCustomers. Rename each of the controls accordingly, renaming the lables with the lbl prefix and text boxes with the txt prefix (i.e., lblCustomerID and txtCustomerID).

10) Select Form View from the View menu and confirm that when a Customer is selected in the combo box that their detail information is shown below in the Detail section of the form. Close and save the changes made to frmCustomers.

11) Close the Form and say "yes" to saving changes.

Step Three: Export the frmCustomers form to ASP
1) From within Access 97 select the Forms tab and select the frmCustomers form.

2) From the File menu select Save As/Export.... Accept the default 'To an External File or Database' and click OK.

3) The 'Save Form frmCustomers in' dialog box will appear. In the 'Save as type' listbox select 'Microsoft Active Server Pages (*.asp)' and then browse to the wwwroot directory for the Web server (\InetPub\wwwroot for NT Server 4.0 and Internet Information Server and \WebShare\wwwroot for Windows 95 and Personal Web Server) for the "Save in:" box. You want to save/export the ASP files to the wwwroot directory of the respective Web server.

4) After browsing to the wwwroot directory click the Export button. The Microsoft Active Server Pages Output Options dialog will appear. In the Data Source Name textbox enter the name of the System DSN for Adventure Works (i.e. AdvWorks) and click OK. Leave everything else blank. The files frmCustomers.asp and frmCustomersalx.asp will be exported to the wwwroot directory.

5) To test the exported ASP pages Execute permissions will need to be set on the wwwroot directory from within the Internet Service Manager. Open the Internet Service Manager and double click on the www service to open its Properties page. Select the Directories tab and select the wwwroot diretory. Click the Edit Properties button and check the Execute checkbox under Access. Start and stop the www service to assure that the changes have taken effect. Once this has been done attempt to browse the frmCustomers.asp by entering the following URL in Internet Explorer's Address textbox: http:///frmCustomers.asp.

At this point you should be able to view the form, but the combo box will not function until some modifications are mde to the code in the ASP files.

Step Four: Add the exported ASP files to a Web project
1) Create a new Web project is Visual Interdev called Customers.

2) Add the files adovbs.inc (use Start/Find to locate the file).

3)Add the files frmCustomers.asp and frmCustomersalx.asp to the workspace.

4)Add a text file to the Web project named SessionVars and rename its file extension from .txt to .inc (i.e. SessionVars.inc). Open the SessionVars.inc file in Notepad and copy the following lines into it:

<%  If Session("URLPathInfo") = "" Then

Dim i    Dim strTemp Dim nTrim Dim ServerName Dim PathInfo Dim FullPathInfo

ServerName  = Request.ServerVariables("SERVER_NAME") PathInfo    = Request.ServerVariables("PATH_INFO") FullPathInfo = ServerName & PathInfo

For i = 1 To Len(PathInfo) strTemp = Right(PathInfo, i)      If ( Left(strTemp, 1) = "/" ) Then nTrim = i - 1 Session("URLPathInfo") = Left(FullPathInfo, _           (Len(FullPathInfo) - nTrim)) Exit For End If    Next

End IF  %> The code above uses the ServerVariables to obtain both the ServerName and the relative path to the Web project. A string is then built which forms the relative path to the ASP files regardless of where they are placed inside the Web project. URLPathInfo is set to a string with the following syntax: //. This string can then be dynamically inserted via ASP into the two exported files, thereby allowing them to be placed anywhere in the Web project and still be browsed.

Step Five: Modify frmCustomers.asp file
Open the frmCustomers.asp file and add the following line of code after the opening  tag:

The imports the text/code inside the .inc file into frmCustomers.asp prior to processing it. The adovbs.inc file declares various constants that are useful in ASP. Change the line 'rs.Open sql, conn, 3, 3' to the following:

rs.Open sql, conn, adOpenStatic, adLockOptimistic By using the INCLUDE statement and including the adovbs.inc file the constants can be referred to by name instead of by magic numbers. Delete or comment out the following lines of code:

If cstr(Request.QueryString("CustomerID")) <> "" Then rs.Fields("CustomerID").Value = Request.QueryString("CustomerID") End If The above lines of code need to be deleted because CustomerID is an AutoNumber primary key and will generate an error if an attempt is made to manually update the field. Microsoft Access, by default, exports the above code, so it will need to be removed or commented out. Inside the frmCustomers.asp file locate the following lines of code:

tempVar = Request.QueryString("nav_btn") On Error Resume Next

Add the following ASP code below immediately after the On Error Resume Next statement: If cstr(tempVar) = "nav_cbo_Customers" Then Dim strRecordsetCustomerID Dim strQueryStringCustomerID strQueryStringCustomerID = cstr(Request.QueryString("Customer_ID")) rs.MoveFirst For i = 1 To rs.RecordCount strRecordsetCustomerID  = cstr(rs("CustomerID")) If strRecordsetCustomerID <> strQueryStringCustomerID Then rs.MoveNext If rs.EOF Then rs.MovePrevious If rs.BOF Then rs.AddNew End If   End If       Else Exit For End If    Next End If When the user selects an item in the combo box, a QueryString is set up with two elements. The first element in the QueryString is called 'nav_btn' and its value is set the name of the control selected by the user. The second element in the QueryString is called 'CustomerID' and its value is set to the bound column of the combo box, which is the CustomerID of the selected item. The returned 'CustomerID' value from the QueryString is then compared record by record to the values in the recordset until a matching record is found or the end of the recordset is reached. Locate the  tag at the bottom of the frmCustomers.asp file and replace the VALUE attribute with the following code:

VALUE="http://<%= Session("URLPathInfo") %>frmCustomersalx.asp">

Back at the top of the code, find the closing  tag and insert the following code just before it: <%  Sub HandleError Response.Write("Error Source: " & Err.Source & " ") Response.Write("Error Number: " & Err.Number & " ") Response.Write("Error Description: " & Err.Description & " ") Err.Clear End Sub %> The above code allows the error checking code to be used below. Locate the 'nav_btn_MoveCancelUpdate' line and insert the following line of code after 'rs.CancelUpdate': If Err.Number <> 0 Then HandleError This allows the error being generated by the rs.CancelUpdate to be displayed. The rs.CancelUpdate will raise an error execpt in one case; when the user clicks AddNew and then decides they don't want to add a new record and immediatley clicks 'Cancel.' In this situation the rs.CancelUpdate cancels the update and restores the recordset to the state prior to the AddNew command. At other times when the statement is executed there have been no changes to the server side recordset. The rs.Resync requeries the underlying recordset and returns it to the client displaying the first record, which makes it appear to the client that the changes were cancelled. In reality no changes were ever made to the underlying recordset.

Step Six: Modify frmCustomersalx.asp file
Open the frmCustomersalx.asp file and locate the following line:

Set tempRS = Session("RS_frmCustomers_cboCustomers") Add the statement 'tempRS.Requery' immediately after it. This requeries the recordset so added records are visible in the combo box.

Locate the frmCustomersalx_OnLoad procedure:

Sub frmCustomersalx_OnLoad cboCustomers.list = cboCustomers_tempList End Sub Edit the procedure adding the three lines of code shown below: Sub frmCustomersalx_OnLoad txtCustomerID.Locked  = True txtCustomerID.TabStop = False cboCustomers.ListWidth = 150 cboCustomers.list = cboCustomers_tempList End Sub Locked and TabStop are run-time properties that determine if the control can be edited and receive tab focus respectively. ListWidth is a run-time property that determines the width of the drop-down list for the combo box. Depending on the number of columns displayed the width will need to be adjusted accordingly. Locate the nav_btn_MoveFirstRecord_Click event procedure, and just above it, copy the cboCustomers_Click event procudure listed here:

Sub cboCustomers_Click Call AddCtrlToList("cboCustomers", "Customer_ID") Window.Location.Href = "http://" & _ "<%= Session("URLPathInfo") %>" & _ frmCustomers.asp?nav_btn=nav_cbo_Customers&"& _         GetCtrlQueryString   End Sub For each of the Click event procedures modify the URL path being assigned to Window.Location.Href from:   ...Href = "frmCustomers.asp?... To: ...Href = "http://<%= Session("URLPathInfo") %> "frmCustomers.asp?... Locate the  tag for the cboCustomers object and edit the following line of code from:  To:  Note: The Width parameter controls the width of the columns.