Assignment 7 - Using SQL to Read, Insert, Update and Delete Database Recordss

This is a two part assignment. In the first part of this assignment you will create a database and view its contents via the web. This assignment closely follows Chapter 16 of your M&A textbook. You will probably want to read the chapter before attempting the assignment. 

The second part of the assignment you will insert, update and delete database records. This assignment is modified from the example in Chapter 17 of M&A.

Part I - Reading Database Records

1. Create a Database   Create an Access database named widgetworld.mdb and put it in your Database directory on Aspen. Create the fields shown on M&A p. 510 and add the data shown on M&A p. 520. Save the data in a table named tblExpenses.

2. Database Connection SSI. The database connection tells your ASP script where the database is located. We put this information into a SSI because we often use the same database connection within several ASP scripts and we want to minimize the amount of code that we need to maintain. 

Paste the following code into a file named DatabaseConnect.asp and save it in a directory named A07.  Change the physical file path (starts with d:) to reflect the path to your database folder. You can see the physical path to your account by viewing the default.asp page located in the root directory of your Aspen account. If you deleted this file in your account your path should look something like this:
D:\Inetpub\wwwroot\03Fall\YourLastName\database\widgetworld.mdb.

<%
     dim objConn
     'Create a DSN-less database connection (M&A p. 512)
     Set objConn = Server.CreateObject("ADODB.Connection")
     objConn.ConnectionString="DRIVER={Microsoft Access Driver (*.mdb)};" & _
     "DBQ=d:\Inetpub\wwwRoot\03Winter\csandvig\database\widgetworld"
     objConn.Open
%>

3. ListExpenses.asp -- Copy the sample script (view source) and save it with the name ListExpenses.asp.  Use Access to create a SQL statement that reads the expenses table. Paste the SQL statement from access into the sample script. Make the necessary modifications to the sample script.

Try it out!

You have just created your first database-driven web page. Congratulations!

Part II - Using SQL Statements to Query Data

This assignment covers the material in M&A Chapter 19. You will find the assignment easier if you read the chapter before attempting the assignment. You may wish to use the sample script (view source) from the SQL lecture as a template. You will also find the handout distributed in class, SQL Syntax, to be useful.

Make your scripts user friendly, with back buttons and centered forms and tables. Put all files, except the database, in a folder named A06.

Static SQL Queries

1. Vendor4.asp -- Download the Clothier Database. Use Access to build a query that lists all items from vendor number 4, ordered by ItemName.
    

2. PriceUnder25.asp -- Create a page that lists only items that are under $25 each, ordered by type and item name.

Dynamic SQL Queries

3. VendorSelect.asp -- Create a form that asks the user to enter a vendor number. The response should list all items supplied by the vendor. Include vendor number, vendor name, item name (sort on name) and item price. Create two types of user input, text box and pull-down menu.
   Tip: The output requires the vendor name, which is located in the Vendor table. Before creating the query in Access, create a relationship between the Item table and the Vendor tables.  To do this from the "design query" screen simply drag and drop the VendorID field from one table to the other The relationship will show as a line between the two tables. 
      

4. SearchString.asp -- Create a page that searches the ItemName field for a user specified string.  No items should be shown the first time the form is displayed. Use a hidden field to set bolPostack to "true" and only display results when bolPostback is true.
    

5. RandomItem.asp -- Create a page that randomly selects one of the 22 items in the item table and displays it as shown in the example. The syntax for generating a random number is:
               Dim intItemID
        randomize
        intItemID = Int(22* rnd(1)) +1
    
   The suggested retail price is calculated by multiplying the database field "ItemPriceBuy" by 1.8.

Using SQL to Insert & Delete

1. SQLInsertDelete.asp -- SQL can be used not only for database queries, but also for inserting, updating and deleting records. This assignment lists the data, inserts and deletes. All the code is located in a single file that uses "If Then" statements to select the appropriate code to run.

Steps:

1. Create a new database named SQLNames.mdb with a table named tblNames. The table should contain three fields: PersonID (autonumber), strFirstName (text), StrLastName (text). Add a couple of names to the database before uploading it to your database folder in the server.

2. Copy your DatabaseConnect server-side include file, rename it, and point the path at your new database.

3. Copy the SQL sample script (view source) and modify it to read the SQLNames database.

4. Add an HTML input form above your listing. You can copy the HTML from the sample if you like.

5. SQL Insert: Immediately after the statement that creates the recordset object add an "If Then" block that checks to see if data has been submitted from the HTML form.  Inside the block add the following code:
   
   strSQL = "Insert ..."
   
   objRS.Open strSQL, objConn, , 3
   
   (Note: the 3 is needed in the open statement for statements that change the data. It tells the database to open with "Lock Optimistic."
   
   Here is the syntax for the SQL Insert statement. Use the version that specifies the field names (since we can't add data to the autonumber field).
   
   If you get an error on the objRS.Open statement, the problem is most likely in the SQL statement.  Check the syntax of the SQL statement with a response.write statement before the objRS.Open statement, as follows:
   
    response.write "strSQL:" & strSQL & "<br>"
    

6. SQL Delete: After the insert block add a "If then" statement for deleting records. The code is very similar as insert, except for the SQL statement. Here is the syntax for the SQL Delete statement.
    
    

2. SQLInsertDeleteSort.asp -- It is ridiculously easy to sort with SQL, requiring only that "ORDER BY fieldName" be added at the end of the SQL statement.
   Steps:

   1. Add anchor tags to the output table headings.  Use them to pass the sort field name in the querystring.

   2. Remove the semicolon from the end of the Select SQL statement.

   3. After the SQL statement and before the open statement add an "If then" statement to see if a sort field has been specified.  If so, concatenate the following onto the SQL statement:
      
      strSQL = strSQL & " order by " & Request.queryString("SortBy")

Extra Credit Exercise:

This optional extra exercise is intended for you to further strengthen your ASP & SQL skills.  Credit will be used to improve your assignment grade if you have an error on an earlier exercise. It is more difficult than the earlier exercises.

1. SqlUpdate.asp -- This exercise uses the full CRUD (Create, Read, Update, Delete) data lifecycle. Updating is more complicated that adding or deleting records because it is a two step process. The first step is to load the user's data into the textboxes and the second step is to write the data to the database. We use variable names in the querystring (UpdateID1 and UpdateID 2) to identify the current update stage.  The overall flow of the program is:

   1. Open database connection and create a recordset object.

   2. If user is editing, retrieve user data and assign to local variables.

   3. Display HTML form. The textbox fields are filled with user data from step b. If we are not updating, then the local variables have no data assigned to them so the textboxes are empty.

   4. Three "If then" blocks that execute SQL to add, delete and update records in the database.

   5. Read and display names in database.

   Steps:

   1. Add update hyperlinks to the output table. Pass the name "UpdateID1" with the value of the appropriate UserID.

   2. Above the HTML form add an "If then" statement to check if UpdateID1 contains a value. If so, use a SQL statement to retrieve the user's data and assign the data to local variables. Also create a local variable named "strAction" and set it to "Update" when updating and "Add" otherwise. This variable is used to change the value displayed on the form's button.

   3. Assign the user's data to the HTML form's two textboxes by adding code similar to the following to the textbox tag:
         value = "<% = strUserFirstName %>"
      where strUserFirstName is one of the local variables you created in step b.

   4. Name the form's button "action" and assign it the value of your local variable "strAction".

   5. Add a hidden field that stores the value of UpdateID2.

   6. Below the form add another "If Then" block that checks the value of "UpdateID2". If it has a value then we know that we are in the second state of updating. Write a SQL statement to update the database. Here is the syntax for the SQL update statement. Use the version "Update Several Columns in a Row."

    

.