CIS 410 Week 3 Homework Database Update

Tutorial: User Friendly

Web Applications

that Modify Databases

1. Overview 2

2. Download and Install the Sample Web Application 4

3. Prepare Your Web Application 4

4. Changes to the Record Sql Class 4

5. Order of Execution 6

6. Page Load Code of Show Form Can Delete a Record 6

7. Page Load Code of AddEdit Form (Two Modes: Add, Edit) 7

8. Submit Button Click Code of AddEdit Form (Insert or Update) 8

9. Your Homework (DBUpdate Part II) 9

10. TroubleShooting 10

1. Overview

In this assignment, you’ll be making the update functionality much more user friendly because the user won’t have to know the value of a key field in order to edit or delete it.

Delete will be handled by the page that shows data (see below).

The screen capture above was taken while the mouse was hovering over the delete link of the first row of data (stock Id “55555”). Note the address displayed in the lower left corner of the screen: frmShowStock.aspx?id=55555. This is the URL which will be invoked if the user clicks on that delete link. Here is the page that will be displayed if the user clicks on that link:

Both the Add and Edit link (see above) will point to a single web form -- the form will act differently depending on whether or not parameters are passed to it. The add/edit web form will look like this (add mode) if it’s called without any parameters. Note that there are no parameters encoded in the address box of the browser.

The add/edit web form will look like this (edit mode) if it’s called with a parameter (the value of the primary key of the record that the user wants to edit). Note the id parameter encoded in the query string of the address box of the browser.

When the user is finished editing the form (either entering new data into the form for Add mode or modifying data for Edit mode) and they click the submit button, the System will either insert (for Add) or update (for Edit).

2. Download and Install the Sample Web Application

From doc sharing, extract the files from “mod5DbUpdate2Sample.zip”. Create a new web application on your PC (preferably with the same project name so you don’t have to do a global search/replace on my files changing my project name to yours). Use Project – Add Existing Item to add in all the webform and class files from the sample project into the project you just created.

From windows explorer, right click on the database that’s stored in the project folder (c:\inetpub\wwwroot\mod5DbUpdate2Sample\InventoryDb.mdb) – select Properties. Click on the security tab and make sure the ASP.NET user has full control over the database.

This project shows an example of a completed homework using Stock as the sample data table.

3. Prepare Your Web Application

I suggest you submit/backup your web application from last week and then just continue to make this week’s modifications into your primary web application.

4. Changes to the Record Sql Class

Most of the changes we’ll make for this week’s homework will be in the two web forms (show and add/edit). The only other major change is to the method that generates the HTML table that shows our data. Now it has delete and edit links embedded in the HTML table (see below). Note that we pass in the names of the edit form and the delete form because we do not want to have “back end” code dictating what names should be used for “front end” web forms. Remember that the web form is the “boss” and it delegates work to the methods of classes. Those classes should be given everything they need to do their work and these classes should not dictate anything to their “bosses”.

/* Create html that holds the data read from the Stock table. */

public string showStockLikeDescription(string strDescLike,

string editForm, string delForm) {

string tmp = "<table border='1'>\n" +

" <tr>\n" +

" <td&nbsp</td>\n"+

" <td>Stock Id</td>\n"+

" <td>Description</td>\n"+

" <td>Next Sale Date</td>\n"+

" <td>Retail Price</td>\n"+

" </tr>\n";

if (dbc.isConnErr()) {

return "*** Database Unavailable (" + dbc.getErrMsg() +

"). Please try later. ***";

}

try {

string sql = "SELECT stockid, description, nextsaledate, " +

" retailprice FROM stock where description like @desclike " +

" order by description ASC";

OleDbCommand cmd = new OleDbCommand(sql, dbc.getConn());

cmd.Parameters.Add("@desclike",strDescLike+"%");

System.Data.OleDb.OleDbDataReader reader;

reader = cmd.ExecuteReader();

string keyvalue;

while (reader.Read()) {

keyvalue = reader.GetValue(0).ToString(); //stock ID

tmp += " <tr>\n" +

" <td<a href='" + editForm + ".aspx?id=" + keyvalue + "'>edit</td>\n" +

" <td<a href='" + delForm + ".aspx?id=" + keyvalue + "'>delete</td>\n" +

" <td>" + keyvalue + "</td>\n" +

" <td>"+this.stringFromReader(reader,1)+"</td>\n" +

" <td>"+this.dateFromReader(reader,2)+"</td>\n" +

" <td>"+this.currencyFromReader(reader,3)+"</td>\n" +

" </tr>\n";

}

tmp += "</table>\n";

reader.Close();

return tmp;

}

catch (Exception e){

return "Database unavailable (" + e.Message +

"). Please try later.";

}

5. Order of Execution

Consider the order of events, for example, when the user clicks on the delete link:

¾ Show form is displayed (no input parameters in its query string)

¾ User enters starting characters for description field and clicks on Go button

¾ System responds with an HTML table filled with data matching user’s input

¾ (Hover over link you intend to click on, note the URL that will be invoked if you click on that link.)

¾ User clicks on a delete link which simply calls the Show form but with a parameter this time.

¾ The Show form is invoked. PostBack is not true, because this rendering of the form is not the result of a submit button being clicked, it is the result of a fresh call.

¾ The page load event looks to see if the form’s been invoked with a parameter. If so, it deletes the record and displays a message (record deleted or error message).

¾ The user can then continue displaying data if s/he wants to.

6. Page Load Code of Show Form Can Delete a Record

Here is the Page Load code that actually deletes the record. You’ll see that the code is very similar to the code you had last week. The only difference is that the code is in a different place and it gets the id from the query string of the URL.

private void Page_Load(object sender, System.EventArgs e)

{

// Delete a record before doing anything else if this webform

// is called with a parameter (e.g., ?id=23455).

string stockId = Request.QueryString["id"];

if (!Page.IsPostBack ) {

if (stockId != null & stockId.Length > 0) {

DbConn dbc = new DbConn();

StockSql stksql = new StockSql(dbc);

this.lblDelMsg.Text = stksql.delete(stockId);

dbc.closeConn();

}

Work on your web application until you have the Show form displaying and deleting data – you should only have to make the two changes described above.

7. Page Load Code of AddEdit Form (Two Modes: Add, Edit)

If the AddEdit form is called with no parameters in the URL’s query string, then the form knows it’s in Add mode. It doesn’t have to read a record and pre-populate the textboxes (as it would if it were edit mode). It does, however, need to store the fact that it was called to Add (not edit). This is because once the user clicks on the Submit button, the page load code will fire again (postback will be true this time) and the form will not have the parameters in the URL’s query string when the form is re-rendered on the submit/postback.

When it’s not postback (i.e., its first display of the form, before anyone has clicked on the submit button), the page load code just prepares the form for Add (not much work) or Edit (extracts id from the Query string, reads that record from database, pre-populates textboxes to be ready for the edit). It also saves the mode (add or edit) so that when the user later does click on the submit button (postback will be true in this case), the program knows whether to insert the new record or update the old record.

private static string editMode; // must declare this string static so its value sticks between renderings of the form

private void Page_Load(object sender, System.EventArgs e) {

// see if there's an id parameter in the URL's query string

string stockId = Request.QueryString["id"];

// Page.IsPostBack is true after button clicks,

// it's not true on first display of webform

if (!Page.IsPostBack ) {

if (stockId != null & stockId.Length > 0){

// if an id parameter was passed, set mode to edit

editMode="Edit";

// make the key field uneditable (user's not allowed

// to change key field. They could delete and re-enter

// if they really made a mistake entering this.

this.txtStockId.Enabled=false;

this.txtStockId.BackColor=Color.LightGray;

// Read the record they want to edit,

// the record with the stock id extracted from the

// URL's query string.

DbConn dbc = new DbConn();

StockSql stksql = new StockSql(dbc);

Stock stk = stksql.find(stockId);

// Extract found record into textboxes of the form

this.txtStockId.Text=stk.getStockId();

this.txtDesc.Text=stk.getDesc();

this.txtNextSaleDate.Text=stk.getNextSaleDate();

this.txtRetailPrc.Text=stk.getRetailPrc();

dbc.closeConn(); // close every connection you open

}

else {

editMode="Add"; // don’t have to do much here

}

// Display Add or Edit on the form

this.lblModeMsg.Text=editMode + " Stock Item";

}

8. Submit Button Click Code of AddEdit Form (Insert or Update)

When the user clicks on the submit button, the code you have is similar to the code you had before in the Submit button (create data repository record, validate the record, display any error messages, then process the insert or update). The only difference is that you have to check the static variable “editMode” to see if you should do an insert or update.

private void btnSubmit_Click(object sender, System.EventArgs e) {

// gather the data for a stock record (from values

// in textboxes on the form)

Stock stock = new Stock(

this.txtStockId.Text,

this.txtDesc.Text,

this.txtRetailPrc.Text,

this.txtNextSaleDate.Text);

//Create a new Stock Validation object

ValidateStock valStock = new ValidateStock(stock);

// populate error messages (set new or clear old)

this.lblStockIdMsg.Text = valStock.getStockIdMsg();

this.lblDescMsg.Text = valStock.getDescMsg();

this.lblNextSaleDateMsg.Text = valStock.getNextSaleDateMsg();

this.lblRetailPrcMsg.Text = valStock.getRetailPrcMsg();

// Don't bother to process if there are validation errors.

if (valStock.isAllOK()) {

DbConn dbc = new DbConn();

if (!dbc.isConnErr()) {

// Create a new Stock Sql object

StockSql stockSql = new StockSql(dbc);

// do the insert or update

if (editMode.Equals("Add"))

this.lblMsg.Text = stockSql.insert(valStock);

else // mode must be edit

this.lblMsg.Text=stockSql.update(valStock);

dbc.closeConn();

}

else // connection not open.

this.lblMsg.Text=dbc.getErrMsg();

}

9. Your Homework (DBUpdate Part II)

Add all the functionality described in this writeup to your web application:

¾ Show form provides single add link.

¾ Show form provides edit and delete links for each record displayed.

¾ Show form handles delete (in page load event).

¾ Add/Edit form handles add or edit (page preparation in the page load code, insert or update in the button click code).

Compare your web application to the sample code to ensure that you’ve gotten all the new functionality. Unlike the sample code, your web application should still have it’s own look and feel, and its own additional pages.

10. TroubleShooting

Sometimes its hard to look at code like this

" <td<a href='" + editForm + ".aspx?id=" + keyvalue + "'>edit</td>\n" +

and feel confident that this is what is needed to generate the correct HTML to do the linking.

Assuming that your web page does show a link, hover over that link and you should see the URL as shown in the lower left of the screen.

If you don’t have a valid link, then right click on the webform in the browser after running your application. Valid HTML should look like this:

<tr>

<td<a href='frmAddModStock.aspx?id=33333'>edit</td>

<td<a href='frmShowStock.aspx?id=33333'>delete</td>

<td>Rainhat</td>

<td>Saturday, February 03, 2007</td>

</tr>