Adding the Insert Functionality

The form is now fully ready. The problem is that no script yet exists to write the form data into the database. In this task, you'll write the script used to insert data. The script you'll write has the same functionality as the Insert Record behavior, but the code will be somewhat leaner, and this time around, you'll understand it.

  1. Open admin_cp_insert_processor.asp in code view.

    The file should be empty of all code. If not, remove any code.

    Before adding code, let's review what this page should do. It should retrieve the data entered into the form and insert it as a new record into tbl_country. Once that's complete, it should redirect the user to admin_cp_master.asp.

  2. Insert the code that creates the connection to the database.

    In ASP:

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

    In ColdFusion:

    <cfquery name="rs_insertCountry" datasource="newland'>
    
    </cfquery>
    

    This is essentially the same code used in the previous task to create the recordset that was used to populate the form menu. The difference here is that you don't even need to have a recordset returned. You are inserting data into the database, but you are not retrieving a recordset.

    graphics/16inf06.gif

  3. Insert the code that inserts the data into the table in the blank space you left in the previous step.

    In ASP (all on one line):

    [View full width]
    dbConn.Execute("INSERT INTO tbl_country (region, countryName, population, graphics/ccc.gifcountry_currency, description, imageURL, imageALT) VALUES ('" & Request.Form("region") & graphics/ccc.gif"', '" & Request.Form("countryName") & "', '" & Request.Form("population") & "', '" & graphics/ccc.gifRequest.Form("country_currency") & "', '" & Request.Form("description") & "', '" & graphics/ccc.gifRequest.Form("imageURL") & "', '" & Request.Form("imageALT") & "');")

    In ColdFusion (can be on multiple lines):

    [View full width]
    INSERT INTO tbl_country (region, countryName, population, country_currency, description, imageURL, imageALT) VALUES ('#form.region#', '#form.countryName#', '#form.population#', '#form.country_currency#', graphics/ccc.gif'#form.description#', '#form.imageURL#', '#form.imageALT#')

    Understanding these lines is easier if you recall the basic syntax of an INSERT statement in SQL:

    INSERT INTO tbl_table
      (field1, field2, field3)
    VALUES
      ('value1', 'value2', 'value3')
    

    Both the ASP and ColdFusion code blocks are constructing SQL statements that use this syntax. But again, since both are replacing variables with actual values, you need to let ASP or ColdFusion know to evaluate these expressions before it inserts them into the database. As a consequence, you'll see quite a few pound signs in the ColdFusion version, and a lot of concatenation in the ASP version.

    TIP

    ASP users should use code coloring to their advantage. In all cases, Request.Form should be purple, while the form field name should always be green. The ampersands (&) used to concatenate should always be blue. The coloring typically changes the moment you enter an error, making it easy to spot problems before you test the page.

    NOTE

    The ASP version uses both double quotes (") and single quotes ('), which can be confusing. Content enclosed in double quotes is a part of ASP, specifically, string literals that ASP should pass without evaluating. Content enclosed in single quotes belongs to SQL and represents the values being inserted into the database.

  4. Insert the code that redirects the user to the master page once the insert is completed.

    In ASP, before the closing %>:

    Response.Redirect("admin_cp_master.asp")
    

    In ColdFusion:

    <cflocation url="admin_cp_master.cfm">
    

    Because both ColdFusion and ASP will display an error message if they experience problems, the redirect line will only be executed if the insertion is successful.

    graphics/16inf07.gif

    Test the page to make sure it works (you can just make up the details about your country). Once you insert your data, you'll be sent to an as-yet incomplete admin_cp_master.asp. But there's an easy way to see whether the insertion was successful: Go to the country profiles. Updating the country profiles is the whole point of this application. You should find your newly inserted country there. Don't worry about nonsense datayou'll be building a delete application later in the lesson, and you'll need a bogus country or two to test it, so just leave your creation in the database for now.

    NOTE

    As you test the functionality of these pages, do not enter any apostrophes in any of your descriptions. If you do, you'll see an error message. This issue is addressed later in the lesson.