Revising the Query and Commenting Code for Testing and Debugging

The region links send URL variables to tour_detail.asp, but that page has nothing set up to do anything with those variables. In this task, you will put that functionality in place. But there is a catch.

Usually, when you bind a URL variable to the page, an error message will be displayed if a user accesses the page and that URL variable is not present. But in this case, when the user clicks the View All link, no URL parameter is sent. Later, once you've added the form that enables users to choose a country from the list, users will have another way of accessing tour_detail.asp without using URL variables. Therefore, you must set up the page in a way that it does not depend on the presence of URL variables.

When you create applicationseven applications as simple as a search and results page pairit is incumbent on you to prevent errors from occurring. Prevention often requires some strategic planning, so let's think this through before we continue building. The goal is to find a way to enable the page to return the proper results based on the type of data that is (or is not) available to it.

If data is available to the page (such as URL or form variables), then that data will be used only in one place: inside the SQL query as a filtering mechanism. Nowhere else on the page depends on that data. To prevent errors, you need to prevent code from running that depends on variables that don't exist. You already know that if…else structures are good at preventing or enabling certain blocks of code. Therefore, one solution to the problem is to embed multiple SQL statements in an if…else structure, with one query for each type of search. The if…else structure will ensure that only the correct query will be executed, based on the presence or absence of variables that you specify.

You'll write the if…else structure at the end of this lesson. But for now, you need to create the SQL statement that will run if a URL variable (regionid) has been sent to the page. That statement will be different from the existing SQL statement, which simply returns all records. You still need that original query, though, in case no URL parameters are sent. So you will copy the existing SQL statement and revise it to work with URL variables.

But that leads to another problem: Your code will have two incompatible queries. You'll fix that later with an if…else structure. But how do you work around the problem while you build and debug the new query? You will temporarily disable the existing query. This disabling will prevent it from running, but the code will still be there on the page when you need it for later. To create this win-win situation, you designate the original query as a comment. As you know from before, ASP and ColdFusion ignore comments, so the code will still be there, but it won't interfere with the testing of your page. This technique, as you've learned previously, is called commenting out code, and it is a critical strategy when developing sophisticated applications.

NOTE

One strategy that programmers use is to write code to solve one neatly defined problem at a time. In this case you could try to create the new query and the if…else structure simultaneously, but if something goes wrong, you may not know which section of code is the culprit. Focusing on one task at a time makes it easier to debug your code.

In this task, you'll duplicate the original query, comment it out, and revise and test the copy.

Open tour_detail.asp in code view. Locate the query near the top of the page.

For me, in both ASP and ColdFusion, the query code begins on line 4. The goal in this step is to disturb the ASP or ColdFusion code as little as possible, while trying to isolate the SQL code.
ASP users: Select the entire line that begins rs_tourDetail.Source =. Copy this line, and paste it on the next line, so that you have two identical copies. Position the insertion point just before the first of these two lines, and enter a single quote (') character, to comment out that line.

You have copied the original query and temporarily disabled it. You are ready to start revising and testing the new query.
ColdFusion users: Find the SQL statement enclosed inside the <cfquery> tags. If necessary, remove any line breaks, so all of the SQL is on a single line. Select the whole line of SQL, copy it, and paste it on the next line. Position the insertion point just before the first SQL statement, and type .

You have copied the original query and temporarily disabled it. You are ready to start revising and testing the new query.
Just before the ORDER BY clause near the end of the statement, insert the following code:

ASP users:
```
WHERE tbl_country.region=" & Request.QueryString("RegionID") & "
```
ColdFusion users:
```
WHERE tbl_country.region = #url.regionid#
```
Notice that there should be a space after the closing quotes (") in ASP and the closing pound sign (#) in ColdFusion. That is, the word ORDER should not be directly against either the quotes or the pound sign.

So what does this addition to the query do? It creates another criterion, which further limits the original search. The original search retrieves all of the information needed to build all of the tour descriptions. This query does that, too, except it limits the set to those tours whose countries share the same regionID as the one passed as a URL parameter. Let's look at an example. Each tour has a field specifying its country. This country listing is, as you'll recall, a foreign key, which points to tbl_country. So both tours that take place in France have the countryID for France as one of their records. This shared field links the tours table to the country table, in this case, linking all tours in France to the record for the country France in tbl_country.

One of the fields that each country has is a foreign key for the region table. Thus, the record for France has a 3 in its region field, because 3 is the regionID for Europe, which contains France. Europe also contains the United Kingdom and Italy, so each of these countries' region fields also contains a 3. Japan's region value is 4, which is the regionID for East Asia. Therefore, all of the tours that list Japan as their country are related to a regionID of 4, and not of 1 or 3. Because Thailand is also in East Asia and has a regionID of 4, all of its tours are related to regionID 4.

To summarize, then, each regionID has a one-to-many relationship with countries. That is, each region may have several countries that belong to it. Likewise, each country has a one-to-many relationship with tours, which means that a single country can have multiple tours. However, no country can belong to multiple regions, and no tour can go to multiple countries. Therefore, you can write a query that retrieves tours where the tour's country belongs to a given region, and only those tours will be retrieved, and all others will be excluded.

That's what you've specified with this WHERE clause in the SQL statement. Test the file to make sure it works as expected.
Save, upload, and close tour_detail.asp. Select tours.asp in the Site panel, and press F12 to test that file.

You cannot test tour_detail.asp directly, because you would get an error. The SQL query that you just wrote needs the regionID URL variable to execute. If you test the page directly, that variable won't be available, which will break the SQL query and return an error.
Click any of the regions in the menu on the page.

After you click, you should see only tours from that region displayed on the page, so that part of the functionality works.

But there's some bad news as well: The Recordset Navigation Bar table is still visible. All of its cells are empty, because no region (at the moment) has more than five tours, so the page is always the first and last page. Remember, the Repeat Region server behavior on this page that displays the tours is limited to showing five records per page. It would be good to hide the Recordset Navigation Bar, when the page is the first and last page, that is, when five or fewer records are showing. But if that number ever exceeds five (say, for example, Newland adds tours to Germany, Norway, and Spain next year), the Recordset Navigation Bar needs to be made visible again.