Creating the Update Record Detail Page

The master page is ready, so the next step is to create the detail page. In this task, you'll build the detail page used for updating records. You won't actually need a detail page for deleting a record, because users don't need to see any details about the pagethey just need a script to process the deletion.

The update page that you are about to create looks quite a bit like the page that you built enabling users to insert new country files. In fact, the page will use a modified version of the same form. The only differences are that on the update page, the form fields will already be filled in, and of course when the user submits the form, a database record will be updated, rather than a new one created.

Open admin_cp_insert.asp. Choose File > Save As, and save it as admin_cp_update.asp (overwriting the original file). Change the page heading to Update a Country Profile, and change the page title to Newland Tours Admin: Update a Country Profile.

In doing so, you preserve the form you created as well as the rs_regions recordset, which retrieves all of the regions and their IDs, and populates the Region drop-down menu.

When you use this technique, you must keep in mind all of the things you will need to change. In this form, each of the fields must be automatically populated with appropriate data from the database for the chosen country; a new recordset will need to be created to facilitate this change. The action attribute of the <form> tag needs to point to admin_cp_update_processors.asp, rather than the insert processor page, and the form ought to be renamed.
In code view, find the opening <form> tag, and set its attributes, as follows.

action (ASP): admin_cp_update_processor.asp?countryID=<%=Request.QueryString("countryID")%>

action (ColdFusion): admin_cp_update_processor.asp?countryID=<cfoutput>#URL.countryID#</cfoutput>

method: post (same as before)

name: frm_updateProfile

id: frm_updateProfile

This is an easy step to overlook, so it's best to get it done right away.

The action attribute needs explanation. Remember, when this page first loads, the countryID variable appears in the URL sent from the master page. A query relies on this variable, so that it knows which country the user wants to modify. Now, the actual updating of the database will be done on a different page: admin_cp_update_processor.asp. However, that page, too, needs to know which record it should update. By appending a querystring or URL parameter to the form's action attribute, you are effectively sending this querystring variable to the processor page.

TIP

Another common way to pass along read-only data through a form is to use a hidden form field.
Create a new recordset, rs_countryDetail, which retrieves all of the information stored about the country profile selected on the master page. Position it just above the existing recordset on the page.

In ASP:
```
[View full width]
<%
Dim dbConn2
Dim rs_countryDetail
set dbConn2 = server.CreateObject("adodb.connection")
dbConn2.open("newland")
Set rs_countryDetail = dbConn2.Execute("SELECT * FROM tbl_country INNER JOIN tbl_region 
ON tbl_region.regionID=tbl_country.region WHERE tbl_country.countryID=" & Request.
QueryString("countryID"))
%>
```
In ColdFusion:
```
<cfquery name="rs_countryDetail" datasource="newland">
SELECT *
FROM tbl_country
INNER JOIN tbl_region ON tbl_region.regionID=tbl_country.region
WHERE tbl_country.countryID=#url.countryID#
</cfquery>
```
Once again, most of this code should look familiar to you. It's worth deconstructing the SQL statement, though. Remember, tbl_country contains most of the information that Newland tracks about a country. The exception is the region name, which is stored in a different table, and linked through a relationship to tbl_country. The SQL statement therefore uses JOIN to retrieve this additional information. Also of note, this SQL statement does not retrieve all of the country profilesonly the one that the user selected on the master page, whose ID was sent as a querystring/URL variable.

NOTE

To ASP Users: You must change the name of the ADO object from dbConn to dbConn2, because another recordset on the page already has an ADO object called dbConn. Another way you could create the rs_countryDetail recordset would be to insert the Set rs_countryDetail = line into the existing block of code for the first recordset. That way, you would be using the same ADO object (dbConn) to create two recordsets (rs_regions and rs_countryDetail). If you use this approach, don't forget to declare (Dim) the new variable.

You now have all of the information about the selected country that Newland keeps. You'll use this information to populate the form on the page with live data from the database. When users submit the form, a script will replace existing database data with the information in the form. Now that this data is available, you need to bind it as the default value of each respective form element.
ASP Users: Scroll down to the bottom of the page, and add the code necessary to close and destroy the recordset.
```
<%
rs_countryDetail.Close()
Set rs_countryDetail = Nothing
%>
```
Again, this frees up memory on the server, once your page no longer needs the recordset.
In code view, find the countryName form element. Give it a value attribute, as follows:
```
value="XX"
```
This is standard HTML, so it is the same for both ASP and ColdFusion. If you were to test the page now, the letters XX would appear in the Country Name field.

TIP

If you do test this page during the lesson, remember that it is expecting a querystring/URL parameter. If one is not supplied (and one never is when you press F12), you will see an error. To solve this problem, at the end of the URL in the Address/Location bar in your browser, type the following: ?countryID=3 and press Enter/Return. Doing so provides the necessary information for the query to run and the error is removed.
Replace the XX placeholder with the dynamic value, as follows:

In ASP:
```
<%=rs_countryDetail("countryName")%>
```
In ColdFusion:
```
<cfoutput>#rs_countryDetail.countryName#</cfoutput>
```
This way, when the page loads, the proper country name appears by default in the field. If users modify it, then the country's name will be updated in the database. If the user leaves it alone, then strictly speaking, the form value will still replace the value in the database, but since they are the same, nothing will change.
Using the correct ASP or ColdFusion syntax, repeat steps 5 and 6 for each of the remaining text field elements, using the following information (provided in pseudocode):

Population: rs_countryDetail.population

Country Currency: rs_countryDetail.country_currency

Image URL: rs_countryDetail.imageURL

Now the four text fields are ready for use. You still need to take care of the two text areas.
Between the two sets of opening and closing <textarea></textarea> tags (for description and imageALT), insert the code necessary to output dynamic data as their default values.

In ASP:
```
<%=rs_countryDetail("description")%>
<%=rs_countryDetail("imageALT")%>
```
In ColdFusion:
```
<cfoutput>#rs_countryDetail.description#</cfoutput>
<cfoutput>#rs_countryDetail.imageALT#</cfoutput>
```
The <textarea> tag uses slightly different syntax than other form element tags. Rather than having a value attribute, you place the default value between the opening and closing tags. The syntax for the ASP or ColdFusion code is the same, though.

When you are done, save and upload the file so you can test it.
Select admin_cp_master.asp in the Site panel, log in, and click to select any non-African country.

The update form appears, and all of its default values are already supplied. However, as long as you chose a non-African country, you'll see a problem: The country's region is listed as Africa. This occurs because a different recordset (rs_regions) is populating the drop-down menu, and Africa is the first region in the recordset, so it appears by default. You could change the menu, so that it displays data from rs_countryDetail, but since that has only one record, only one region would be displayed, making it impossible to change the region. And while it is unlikely that Italy will relocate to South America, you should preserve the flexibility to change regions, in case someone at Newland Tours decides to use a different regional division logic than continents, such as Northern Europe and Southern Europe, rather than simply Europe.

You might consider specifying to employees that they have to change this drop-down menu every time as appropriate, but that's poor usability. Sooner or later, an employee will forget to change it, and a country such as Mexico will be listed in Africa. The consequences of this mistake are significant: Remember that users can search by region, so they won't see Mexico if they search under Central America. And imagine the blow to credibility that a travel agent will suffer if it declares that Mexico is in Africa! This is a problem needing a solution.

But the solution is going to take some work. At the moment, the code sent to the browser for this menu element looks as follows:
```
<select name="region" id="region">
  <option value="6">Africa</option>
  <option value="8">America, Central</option>
  <option value="1">America, North</option>
  <option value="2">America, South</option>
  <option value="7">Asia, Central</option>
  <option value="4">Asia, East</option>
  <option value="5">Asia, West</option>
  <option value="3">Europe</option>
</select>
```
As you may be aware, the <select> element has an additional attribute, selected, which you can use to specify which item appears by default. So in the following list, Asia, Central would be the element that appears by default in the browser (bolding applied to make the change easier to spot).
```
<select name="region" id="region">
  <option value="6">Africa</option>
  <option value="8">America, Central</option>
  <option value="1">America, North</option>
  <option value="2">America, South</option>
  <option value="7" selected>Asia, Central</option>
  <option value="4">Asia, East</option>
  <option value="5">Asia, West</option>
  <option value="3">Europe</option>
</select>
```
What we need, then, is to insert the word selected into the code for the region corresponding to the active country. Now, the complication we face is that our menu is not hard-coded, but is rather populated by a loop, which inserts a new <option> line for each record in the rs_regions recordset.

The solution to the problem is this: Each time through the loop that populates the drop-down menu, a script will test to see whether the current record's regionID matches the region in the rs_countryDetail recordset. If it does match, then the script will output the word selected into the code. If it does not match, the script will proceed as usual. Here's how the block looks in pseudocode:
```
[View full width]
Loop the following code until rs_regions runs out of records
  Write the <option> line, using rs_regions.regionID as the value attribute, and 
rs_regions.regionName as the label
If the active rs_regions.regionID equals rs_countryDetail.region
  Write the word "selected" into the code
End If
Go back to beginning of loop and start next iteration
```
That's a lot to absorb conceptually, but once you understand the idea, the actual code is not that hard to write.
Update the code between the opening and closing <select></select> tags for the region, as follows:

In ASP:
```
<%
  Do Until rs_regions.EOF
%>
<option value="<%=rs_regions("regionID")%>"

<%
  If rs_regions("regionID")=rs_countryDetail("region") Then
    Response.Write(" selected")
  End If
%>
><%=rs_regions("regionName")%></option>
<%
  rs_regions.MoveNext
  Loop
%>
```
In ColdFusion:
```
[View full width]
<cfoutput query="rs_regions">
  <option value="#regionID#"<cfif (rs_regions.regionID EQ rs_countryDetail.region)> 
selected</cfif>>#regionName#</option>
</cfoutput>
```
I bolded the opening and closing portions of the <option> tag just to make them easier to readespecially the closing angled bracket (>), which looks orphaned in both ASP and ColdFusion. (That bracket, by the way, should be orange in the code editor for both ASP and ColdFusion.) The reason for this is that selected must be written before that closing bracket, so that bracket alone goes on the right of the if section of code, while the remainder of its tag goes on the left of the if section.

Pay close attention to spacing when you type this codeit is important that there is a space before selected, so that it does not run into the value="X" that precedes it.

ASP users may note that this section uses different syntax for the <output> statement than it did in the menu you built in the insert form at the beginning of the lesson. Rather than a single ASP block that concatenates multiple elements to construct the <output> line, it uses multiple ASP blocks, using the ASP delimiters, <% and %>. There is no functional impact to this change. The main difference is that the code is easier to understand once you add the if section, when broken out like this.
Save and upload the file. Test admin_cp_master.asp, and select a non-African country, as before.

The correct region is selected by default, and yet all of the other regions are available as well.

The update form is complete, and the worst is over.