Adding the Update Functionality

Open admin_cp_update_processor.asp in code view. Delete any code you see.

Once again, you'll be starting from a blank slate when producing this script-only page.

Write the code that creates the database connection.

In ASP:

<%
Dim dbConn
set dbConn = server.CreateObject("adodb.connection")
dbConn.open("newland")

%>

In ColdFusion:

<cfquery name=update_cp" datasource="newland">

</cfquery>

Now that you've got your connection, you can add the SQL.

Enter the code necessary to update the database in the blank line you left in the preceding step.

In ASP (all on one line):

[View full width]
dbConn.Execute("UPDATE tbl_country SET region='" & Request.Form("region") & "', 
countryName='" & Request.Form("countryName") & "', population='" & Request.Form(
"population") & "', country_currency='" & Request.Form("country_currency") & "', 
description='" & Request.Form("description") & "', imageURL='" & Request.Form("imageURL") 
& "', imageALT='" & Request.Form("imageALT") & "' WHERE countryID=" & Request.QueryString(
"countryID"))

In ColdFusion (you can use multiple lines):

UPDATE tbl_country
SET region='#form.region#',
  countryName='#form.countryName#',
  population='#form.population#',
  country_currency='#form.country_currency#',
  description='#form.description#',
  imageURL='#form.imageURL#',
  imageALT='#form.imageALT#'
WHERE countryID=#URL.countryID#

Again, use code coloring to your advantage, especially ASP users. Also, make sure you don't inadvertently add a comma at the end of the element just before the WHERE clause.

Insert the line of code needed to redirect the user back to the master page.

In ASP, in the line above the closing %> tag:

Response.Redirect("admin_cp_master.asp")

In ColdFusion, in the line after the closing </cfquery> tag:

<cflocation url="admin_cp_master.cfm">

This returns the user to the master page, so she or he can conveniently verify that the modifications took place.

Save and upload the file. Test the master page (F12), click a country, modify it, and click Submit. Return to that country's update page to change the value back.

The update functionality is complete and functionalat least for some of the countries. If you test using Argentina, Thailand, India, or a few others you will see an error. The cause of the problem is that the text in the description or imageALT text areas contains an apostrophe character, which SQL misinterprets. The solution to this problem is to remove all apostrophes from the textwhich is not a reasonable business practice. Or is it?

In the next couple of steps, you will add some code that replaces the apostrophes with their HTML character entity equivalent (&#39;) automatically, just before the text is updated in the database. SQL will ignore the character entity (which is what we want), and when it is sent back to an HTML page, the browser correctly converts the character entity into an apostrophe. Neither the end user nor the user who maintains the Newland Tours site content will ever know that this happening behind the scenes. The only trace of this change, other than the ASP or ColdFusion code you are about to write, is that &#39; will appear in the database in place of all apostrophes.

Return to admin_cp_update_processor.asp.

The find and replace script you'll add will appear on the processor page, just before the update is performed.

Add the following script at (or near, in the case of ASP) the top of the page.

In ASP, in a new line after the opening <% but in the line before Dim dbConn:

Dim description_fixed, imageALT_fixed
description_fixed = Replace(Request.Form("description"),"'","'")
imageALT_fixed = Replace(Request.Form("imageALT"),"'","'")

In ColdFusion, at the top of the document:

<cfset description_fixed = #Replace(form.description, "'", "&##39;", "ALL")#>
<cfset imageALT_fixed = #Replace(form.imageALT, "'", "&##39;", "ALL")#>

Both ASP and ColdFusion have built-in Replace() functions, with nearly identical syntax. Each takes (a) the text to be searched, (b) the string to search for, and (c) the string to replace it with as the first three parameters.

ColdFusion's Replace() function takes an additional, optional parameter which lets you specify how many instances of the search string should be replaced. Since you want to replace all instances of the apostrophe with its character entity, ColdFusion users specify ALL. ColdFusion has one more quirk: because # has special meaning in ColdFusion, when it appears in the character entity (&#39;), even though it is in quotes, ColdFusion gets confused and tries to terminate the Replace() function on the spot. To prevent this from happening, ColdFusion users must escape the # by adding another # in front of it.

If you were to test the file now, you'd still get an error. That's because even though you've created a variable that holds the value of the two text fields, you don't insert the contents of that variable. You need to modify the SQL code.

Replace the two variables in the SQL statement, so that the UPDATE statement uses the fixed versions.

In ASP, replace description='" & Request.Form("description") & "', with description='" & description_fixed & "', and imageALT='" & Request.Form("imageALT") & "' with imageALT='" & imageALT_fixed & "'.

In ColdFusion, replace description='#form.description#', with description='#description_fixed#', and imageALT='#form.imageALT#' with imageALT='#imageALT_fixed#'.

As you complete this step, in both ASP and ColdFusion, notice that the description portion is followed by a comma, while the imageALT portion (the last in the series) is not.

Save, upload, and test the page.

This time around, even countries that have descriptions and alt descriptions with apostrophes can be updated.

Now that you know how to fix the apostrophe problem, you can fix the insert country profile pages.

Return to admin_cp_insert_processor.asp and apply the fix there.

The changes in code that are necessary are virtually identical to those you input into admin_cp_update_processor.aspthe only difference is that the SQL statement looks slightly different, though what you have to replace is unchanged.