To complete this demonstration, you'll need to add functionality that allows you to add and delete rows from your DataGrid control. Neither of these tasks is as difficult as updating data because you're working with an entire row rather than with individual fields.
Adding a New Row
Adding a row using the DataGrid control seems almost simple now, in comparison to some of the other code you've seen in this chapter. There are a few wrinkles, however.
First of all, there's no change to the DataGrid control required in order to allow you to add a row using the control as far as the DataGrid control is concerned, adding a row is the same as editing a row. Managing the differences between the two is up to your own code. To add a row using the DataGrid control, you must simply bind the control to a data source that consists of a blank row, make the edits, and then save the changes back to the data source.
Saving the data has already been discussed the grdProducts_UpdateCommand procedure showed how you could make a choice, when saving the data, based on the Session("AddMode") value. If the code determined that you were in "add" mode, it created a SQL INSERT statement (as opposed to a SQL UPDATE statement, otherwise).
When you click the Add a New Product link on the sample page, you run the code shown in Listing 17.6.
Listing 17.6 Adding a Row to the DataGrid Control Uses Some New Tricks
Private Sub ProductAdd() Dim ds As DataSet Dim dt As DataTable Dim dr As DataRow ds = CType(Session("DS"), DataSet) ' Copy the structure of the DataTable dt = ds.Tables(0).Clone ' Create new row dr = dt.NewRow() ' Add new blank row to DataTable dt.Rows.Add(dr) With grdProducts .EditItemIndex = 0 .DataSource = dt .DataBind() End With Session("AddMode") = True lnkAdd.Visible = False End Sub
The sample code isn't lengthy, but it does handle some tricks you might not have seen before. The code takes these actions:
It rehydrates the DataSet object stored in the Session variable, so the code doesn't need to go out and requery the data source:
ds = CType(Session("DS"), DataSet)
It clones the structure of table in the DataSet object, creating, in effect, a new table with no rows with the same exact structure as the real table:
' Copy the structure of the DataTable dt = ds.Tables(0).Clone
It creates a new, blank row and adds the row to the new table:
' Create new row dr = dt.NewRow() ' Add new blank row to DataTable dt.Rows.Add(dr)
It sets the EditItemIndex property of the grid, indicating that it should edit row 0 (the first row in the table the empty row) and that its data source should be the table the code just created. The procedure calls the DataBind method of the grid to bind it to its data source, creating a single blank row in edit mode on the page:
With grdProducts ' Set editing on first row .EditItemIndex = 0 ' Assign one blank row to data source .DataSource = dt ' Draw the DataGrid .DataBind() End With
It sets the Session variable AddMode to True so that the updating code knows that you are in "add" mode, and then it hides the Add a New Product link:
Session("AddMode") = True lnkAdd.Visible = False
Once you add your data and click the Update link, you'll find yourself back in the grdProducts_UpdateCommand procedure, described earlier in the chapter.
Deleting a Row
Deleting the selected row takes almost no effort at all, in comparison to editing a row. When you click the Delete link in the grid (you added the Delete link at the same time you added the Edit link, earlier in the chapter), the DataGrid control posts back to the server and runs the DeleteCommand event handler you provide. In the DeleteCommand code for the sample page, the code must perform the following tasks:
Retrieve the selected product ID from the page
Build a SQL DELETE statement that will delete the row
Execute the SQL, deleting the row
Indicate to the DataGrid control that if it is displaying a row for editing, the edit mode has been cancelled
Rebind the DataGrid control to the data source (minus the deleted row)
Because of referential integrity rules applied to the sample database, you won't be able to delete products that have orders associated with them. If you want to test deleting a row, you'll need to add a new product first.
Listing 17.7 shows the DeleteCommand event handler in the sample form.
Listing 17.7 Deleting a Row Is the Simplest Task of All
Private Sub grdProducts_DeleteCommand( _ ByVal source As Object, _ ByVal e As System.Web.UI.WebControls. _ DataGridCommandEventArgs) _ Handles grdProducts.DeleteCommand Dim strProductID As String Dim strSQL As String Dim strConn As String Try strProductID = e.Item.Cells(2).Text ' Build SQL and Connection strings. strSQL = _ "DELETE FROM Products " & _ "WHERE ProductID = " & strProductID strConn = Session("ConnectString").ToString ' Execute SQL DataHandler.ExecuteSQL(strSQL, strConn) Catch exp As Exception lblError.Text = exp.Message lblError.Visible = True End Try lnkAdd.Visible = True grdProducts.EditItemIndex = -1 Call GridLoad() End Sub
This procedure executes these actions:
It retrieves the product ID. The code uses the Cells property of the current item, looking in Cells(2) to retrieve its value:
strProductID = e.Item.Cells(2).Text
It builds up SQL and connection strings. The SQL string is a SQL DELETE statement, using the product ID retrieved previously:
strSQL = _ "DELETE FROM Products " & _ "WHERE ProductID = " & strProductID strConn = Session("ConnectString").ToString
It executes the SQL statement, deleting the selected row:
It resets the editing mode of the grid and rebinds the data:
grdProducts.EditItemIndex = -1 Call GridLoad()
| || |
Now that you've worked through all the details involved in the ProductsEdit.aspx and ProductDetail.aspx pages, give them a complete workout. Try editing, adding, and deleting rows. Try setting the FORCEDOWNLEVEL constant to True and then False, and see how it affects editing.