Deleting Updating and Inserting Data

For every nonstatic table you create in your database, it's highly likely that you will need to create a form to delete, update, and insert data in that table. In some simple database applications, you can edit the data in a grid, but more complex applications often present a dialog or new page with a form where users enter data. For many tables, such forms are similar. For readers who have never written database applications where users can change data, here are some guidelines/rules:

1. Never let a user enter freeform data if you can avoid it. Provide choices whenever possible.

2. Choose the input control type based on the column type or contents. For example, use a check box for Boolean fields. Use radio button lists or drop-down lists for selecting single values from a fixed set. Use the Calendar control for date entry.

3. Validate all data input, clean it up, and cast it to the correct type before storing it in the database.

You can usually use a single form for all three operations. The user must be able to select an item to edit, meaning that you must provide a way to browse the records and select a row. The user must be able to add a new row or delete a selected one. The Web Form ch14-5.aspx lets users modify the contents of the Students table.

I also want to give you an upfront warning—one I'll repeat later in the chapter. In the rest of this chapter, you're going to build a complete but small application to delete, update, and insert data. I've built it this way because it's useful for you to see the "raw" code placed within a Web Form. Here's the warning. What you'll see is not a good application; it hasn't been designed properly. Instead, it's representative of applications that are written all too often—you may have even written some yourself. So as you're going through the rest of this chapter, see if you can spot some of the reasons why it isn't a good application, and think about what you might do to correct its flaws.

You saw how to use the SQL insert, delete, and update commands in Chapter 12. Applying them is straightforward. Most databases update data a row at a time. They delete the existing row, insert the new row, and update the indexes. In SQL Server, updates happen within a transaction, so you can't partially update a row—either the update succeeds completely or it fails completely. Note that just because a data modification process finishes successfully doesn't automatically mean that you got the results you wanted, so you need to test carefully; just as with regular code, it's very easy to code a logic error into T-SQL.

Note In this section and the rest of this chapter, I use dynamic SQL statements. They're easier to see when you're reading examples like this, but you should move them to stored procedures in a production application as soon as you've tested them thoroughly—then test again.

By extending the formatting template so you have two links associated with each student rather than one, it's easy to provide a way to delete a student record.

Note Don't worry about changing or deleting the data in the ClassRecords database. Whenever you want to recapture the original database, run the CreateClassRecords.sql file in the Query Analyzer. It will restore the original database tables and values.

The Web Form ch14-7.aspx displays the student names from the Students table using the template file ch14-7itemTemplate.ascx. The template uses an imageButton control to add a small trashcan icon next to each name. An ImageButton is just like a LinkButton except that it displays a clickable image. You can set the CommandArgument property for an ImageButton in exactly the same way as you set the CommandArgument property for a LinkButton. In addition, the template places each name in a <span> tag that provides a fixed width and a border. Because Repeater controls by themselves don't have a position property, a Panel control on the ASP page contains the Repeater control. You can drag the Panel control around the screen to set the layout. Listing 14.5 shows the template code.

Listing 14.5: itemTemplate Code for the Repeater Control in the Web Form chl4-7.aspx (ch14-7ItemTemplate.ascx)

<span style="border-style:solid; border-width:1; width:180px"> <asp:ImageButton CommandArgument='<%# DataBinder.Eval(CType(Container, RepeaterItem).DataItem, "StudentID") %>'

ImageURL="../images/trash.gif" ImageAlign="middle" width="25"

height="24" runat="server" title="Delete Student"> </asp:ImageButton> </asp:ImageButton>&nbsp;&nbsp; <asp:LinkButton CommandArgument='<%# DataBinder.Eval(CType(Container,

RepeaterItem).DataItem, "StudentID") %>' style="Z-INDEX: 101" runat="server" text='<%# DataBinder.Eval(CType(Container, RepeaterItem).DataItem, "Name") %>' id="LinkButton2" title="Edit Student" /> </span> <br>

When you run the ch14-7 Web Form, it looks like Figure 14.10. For now, ignore the radio buttons in the figure that provide delete options; I'll get to that in a minute.

Figure 14.10: The list of students from the Web Form ch14-7.aspx

Now all you need to do to delete students is to wire up the click event from the imageButton so that it runs the Delete command. You need to add some code to the item_Command event to differentiate between the user clicking the ImageButton and the LinkButton. Because both controls expose the CommandArgument property, the first step is to cast the

RepeaterCommandEventArgs.CommandSource property to the correct type. Use the GetType.Name method to discover the control type name.

For example, if you placed this code in the Repeater1_itemCommand method, you would see the StudentID and the student's name when you click a name but only the StudentID if you click a trashcan icon.

private void Repeater1 ItemCommand(object source,

System.Web.UI.WebControls.RepeaterCommandEventArgs e) { switch (e.CommandSource.GetType().Name) { case "LinkButton" :

Response.Write("You clicked: " +

((LinkButton) e.CommandSource).CommandArgument +

Response.Write(((LinkButton) e.CommandSource).Text); break; case "ImageButton" : String aStudentID =

((ImageButton) e.CommandSource).CommandArgument; Response.Write("You clicked: " +

((ImageButton) e.CommandSource).CommandArgument + "&nbsp;");

break;

Was this article helpful?

0 0

Post a comment