Using FTP Server Access for Site Development with UltraDev

by Judy Fontanella

Part C - Creating a Recordset

Last month we set up our site and created a connection to our database. Now all that we have left is to create a recordset and display our dynamic data. A recordset is a collection of data that has been retrieved from the database. To create it, you will use an UltraDev dialog box that helps you compose a query. The query will tell the database what information you need. The database responds to the query by returning a recordset containing the data that you requested.

We will be creating a web site that lists links of interest to UltraDev users. People who visit this site will be able to click on a topic name and get a list of the sites associated with that topic. The list of topics will not be hard coded into the page. Instead, it will be generated from the Topics table. This will make it easier to expand the choice of topics later on. Adding a new topic to the list will be as simple as adding a new record to the Topics table.

These are the tables and fields that we will use for our application:

  1. Topics Table
    • TopicID
    • TopicLabel
       
  2. Site Table
    • SiteID
    • SiteName
    • SiteURL
    • SiteDescription
    • SiteTopic

I have created a new Access database with these tables. You can download this links.mdb database file to use as you follow along with the tutorial. Follow the instructions in parts A and B of the tutorial to define your site and create a connection to the links database. Name your connection "connLinks."

Our minisite will consist of two ASP pages. On the first ASP page, the visitors will select a topic. We'll call this page "choose_topic.asp." On the second ASP page, they will see a list of sites that are associated with the topic they chose. We'll call this page, "see_sites.asp."

First ASP Page- choose_topics.asp

  1. Start UltraDev and open a new page. Save it in your site folder as "choose_topics.asp". It's very important to save the file before you create a recordset.
     
  2. Go to Window ->Data Bindings to bring up the Data Bindings Panel. Click the "+" in the upper left corner and select Recordset (Query).

  1. The Recordset dialog box will open up. If your dialog box doesn't look like the one in the picture below, it means that you are in the Advanced mode. Click the Simple button to get to the dialog box pictured below. For this page, we want a list of all the site topics. Name the recordset rsTopics. For the connection, choose the connLinks connection that we set up for the links database.
     
    Now UltraDev will connect to the server, find the database and retrieve a list of tables and fields for us to choose from. Within a few seconds, you will see the table names appear in the Table: drop down menu. Choose the Topics table. The fields in the Topics table will appear under Columns:. We only need one field, TopicLabel, so click the Selected: button and then select TopicLabel. We want all of the records in this table, so we don't need to apply a filter. It would be nice to arrange the topic list alphabetically, however, so under Sort:, choose TopicLabel as the field to sort on, and leave the default, Ascending. You can click Test and then OK when you are satisfied with the result. You will see the rsTopics recordset appear in the Data Bindings panel.

  1. Now it's time to put our dynamic data on the page. We want a list of topics that is generated by the data in our recordset. Drag the TopicLabel data source out of the Data Bindings panel on to the page where you would like the first topic to appear.

  1. If you save and upload your file to the server, then visit it with your browser, you will see that you now have the first topic listed on the page. Cool. We actually have some dynamic data. However, we want ALL of the topics listed, not just one. Go back to UltraDev and select the entire paragraph that holds the {rsTopics.TopicLabel} data source. Click on the Server Behaviors tab and then the "+" sign. Choose Repeat Region.

  1. The Repeat Region dialog box will look like the image below. Select the "All Records" option so that all of the topics will display on the same page.

  1. Now when you save, upload and view your file with your browser, you will see the whole list of topics.

  1. The last thing we have to do is to make our list of topics into links to the see_sites.asp page. Each topic link needs to carry a URL parameter that will inform the see_sites.asp page which topic the visitor has chosen. Go back to the choose_topics.asp page in the open UltraDev window. Select the {rsTopics.TopicLabel} data source in the document window. Click the browse folder icon in the Properties panel to open the Select File dialog box. Since we haven't created our see_sites.asp page, yet, we'll have to type that file name in the File Name: text box.

  1. Click the Parameters button. Click the "+" to add a parameter. Name the parameter topic. The value is going to be the same as the topic label that the link is attached to. Under Value, click the lightening bolt to bring up a list of our Data Sources. Choose the TopicLabel field under the rsTopics recordset list. Click OK twice to complete the page.

  1. Save, upload and test the page in your browser. You will see that each topic on the list is a link. Place your mouse over each link and observe the URL in the status bar of the browser window. Each link points to the see_sites.asp page, but the URL parameter changes for each topic to reflect the name of that topic. The ASP link says, "http://www.mysite.com/see_sites.asp?topic=ASP", the CSS link says, "http://www.mysite.com/see_sites.asp?topic=CSS", and so on. How exciting! Pat yourself on the back and let's go on to complete our last page, see_sites.asp.

Second ASP Page - see_sites.asp

  1. Open a new page and save it in your site folder as "see_sites.asp".
     
  2. To create the new recordset for this page, click on the "+" in the upper left corner of the the Data Bindings Panel and select Recordset (Query).

  1. The recordset dialog box should look like the one in the following picture. For this page, we want a list of sites. Name the recordset rsSites. For the connection, choose connLinks. Choose the Sites table. The fields in the Sites table will appear under Columns:. We will use all of the fields, so leave All selected.

    For this recordset, we don't want all of the records. We just want the sites that have the same topic as the one that our visitor chose. We will use a filter to select the right records. Next to Filter: select the SiteTopic column as the field that we will filter on. Choose the "=" sign, because we want the value of the field to equal the value of the URL Parameter. Choose URL Parameter as the object that we want to compare the SiteTopics field to. Finally, type in Topic, the name that we gave to the URL Parameter that is being passed from the previous page.

    It would be logical to arrange the site list alphabetically. Under Sort:, choose SiteName as the field to sort on, and leave the default, Ascending. You can click Test and then OK.

  1. Now we can put our list of sites on the page. There are many different arrangements you can use to display your data. I am using a table with 4 rows and 2 columns. You can drag your data source placeholders from the Data Bindings panel to the page and place them where you want them to go. Then you can select them and apply text formatting just as if they were ordinary text.

  1. The URL of each site will be a link to the actual site. Therefore, select the {rsSites.SiteURL} data source. Click on the browse for file icon on the Properties Panel. The Select File dialog box will appear. At the top it will say, "Select Name From:" and you can choose either File System or Data Sources. Choose Data Sources. From the list of Data Sources that appears, select SiteURL.

  1. The last thing we need to do is to apply a Repeat Region server behavior to the page so we see more than one site on our list. Select the entire table that holds the listing for the sites. Then click the "+" in the Server Behavior Panel and select Repeat Region. In the Repeat Region dialog box, select the rsSites recordset and All Records next to Show:. Note: The Repeat Region will work best if your table alignment is set to "center".

  1. Save your file and upload it to the server. Test it by first browsing to choose_topic.asp. Click on a topic link to see the list of sites on the see_sites.asp page.


 

  1. That's it. You've created an application for the Web!! Now, you can change the sites and the categories in the database to really customize your site. For ideas on how you might extend your new skills, you can visit the dynamic list of links for UltraDev that I created with UltraDev's help. It's at http://artbytes.net/UDlinks.

Copyright 2002, Judy Fontanella, All Rights Reserved

Photo of Judy Fontenella


Judy Fontanella owns her own Web design company called ArtBytes in San Diego, California. She also teaches both online and face-to-face Web design classes for Palomar Community College in San Marcos, California.