The last piece of information we need to call our UpdateTitles function is the Title Id, which can be found by looking at the bound datakeys collection and passing the edited rows itemindex value:

sTitleId = titlegrid.datakeys.item(e.item.itemindex)

Now that we have the Title Id and the updated price, we can call our UpdateTitles function and pass these values. Finally, before we finish we will need to set the DataGrid control back to ReadOnly and rebind the updated data to the grid so that the cell is set to a read-only status.

titleGrid.EditItemIndex=-1 BindData()

The following code provides a listing of the UpdateTitles subroutine, which actually performs the database update of the price:

Sub UpdateTitles(price As Decimal, title_id As String)

Dim connection as New OleDBConnection(“provider=sqloledb;Data Source=localhost;Initial Catalog=pubs;User ID=sa;pwd=”)

Dim command as New OleDbCommand(“UPDATE titles SET [price]=? WHERE [title_id] =?”, connection)

Dim param0 as New OleDBParameter(“price”, OleDBType.Currency) param0.Value = price

command.Parameters.Add(param0)

Dim param1 as New OleDBParameter(“title_id”, OleDBType.VarChar) param1.Value = title_id

command.Parameters.Add(param1)

connection.Open()

command.ExecuteNonQuery()

connection.Close() End Sub

Deleting Data with the OnDeleteCommand Event

Unfortunately, the EditCommandColumn doesn’t provide an automated way to generate a Delete or Select hyperlink. However, you can use the ButtonColumn to implement a Delete method for the DataGrid control as shown in the following code:

<asp:ButtonColumn Text=”Delete” CommandName=”Delete”> </asp:ButtonColumn>

We attach a subroutine to execute when the hyperlink is selected as illustrated in Figure 24-2 with the following attribute of the DataGrid control:

OnDeleteCommand=”OnDelete”

The following code illustrates the subroutine that fires when the hyperlink is selected:

Sub OnDelete(sender As Object, E As DataGridCommandEventArgs)

Try

Dim sTitleId as String

Dim sAuthorID as String sender.EditItemIndex=-1

sTitleId = titlegrid.datakeys.item(e.item.itemindex) sAuthorID = AuthorList.SelectedItem.Value DeleteTitles(sTitleId, sAuthorId)

BindData()

Message.Text=”Status: Delete Successful” Catch myException as Exception

Message.Text = (“Exception: “ + myException.ToString()) End Try

End Sub

When the OnDelete subroutine is executed, we first set the DataGrid control to ReadOnly to prevent any misplaced user interfaces once the selected row is deleted as illustrated in the following code segment.

sender.EditItemIndex=-1

Next, we obtain the Title Id and Author Id in the same way that we collected them in the update scenarios earlier. Once we have the Title Id and Author Id we can call the DeleteTitles subroutine to execute the actual delete as shown in the following example:

Sub DeleteTitles(title_id as String, au_id as String)

Dim connection as New OleDBConnection(“provider=sqloledb;Data Source=localhost;Initial Catalog=pubs;User ID=sa;pwd=”)

Dim command as New OleDbCommand(“DELETE FROM TitleAuthor WHERE [title_id]=? And [au_id]=?”, connection)

Dim param0 as New OleDBParameter(“title_id”, OleDBType.VarChar) param0.Value = title_id

command.Parameters.Add(param0)

Dim param1 as New OleDBParameter(“au_id”, OleDBType.VarChar) param1.Value = au_id

command.Parameters.Add(param1)

connection.Open()

command.ExecuteNonQuery()

connection.Close() End Sub

Finally, we call the BindData function to refresh the DataGrid control.

Sorting Columns with the DataGrid Control

Because the DataGrid control doesn’t perform the actual sorting of the data but instead handles the related events and methods, several steps must be taken in order to implement sorting on the DataGrid control.

The first step is to set up the following two attributes on the <ASP:DATAGRID>:

OnSortCommand=”OnSorted”

AllowSorting=”True”

The OnSortCommand is the event that will be called when a user initiates a sort event. The OnSorted subroutine builds the sorted DataView and rebinds the sorted information to

the DataGrid control. The AllowSorting attribute will turn the column headers of all template columns or bound columns that have implemented a SortExpression attribute into a hyperlink that users can click to initiate the OnSortCommand. An example of how to add the attribute to a TemplateColumn can be seen below:

<asp:TemplateColumn HeaderText=”Unit Price” SortExpression=”price” >

The OnSorted subroutine handles all of the heavy lifting associated with the sorting activity. The OnSorted subroutine used in our example can be reviewed in Listing 24-3.

Listing 24-3 Example of implementing a sorting function for a DataGrid control

Sub OnSorted(source As Object , e As DataGridSortCommandEventArgs) Try

Dim sSortField as String

Dim TitlesDataView As DataView = GetTitles(authorlist.SelectedItem.Value)

Dim bSortAsc as Integer

sSortField = e.SortExpression.ToString() source.EditItemIndex=-1

if Session(“bsortAsc”) = Nothing Then Session(“bsortAsc”) = 1

Else

bSortAsc= Session(“bsortAsc”) End If

If bSortAsc = 1 Then Session(“bSortAsc”) = 0

TitlesDataView.Sort= sSortField & “ DESC” Message.Text =”Sort Descending on “ & sSortField

Elseif bSortAsc =0 Then Session(“bSortAsc”)= 1 TitlesDataView.Sort= sSortField & “ ASC”

Message.Text =”Sort Ascending on “ & sSortField Else

Message.Text=”bSortAsc fell through” End If

titleGrid.DataSource = TitlesDataView titlegrid.databind()

Catch myException as Exception

Message.Text = (“Exception: “ + myException.ToString()) End Try

End Sub

To handle the sorting of a column on the DataGrid control, you need to first establish a DataView object that can be used to sort the information. In the OnSorted function, you call the GetTitles function and pass the appropriate Author Id:

Dim TitlesDataView As DataView = GetTitles(authorlist.SelectedItem.Value)

Next, you need to know what field the user selected for sorting; we can get the field name through the SortExpression property:

sSortField = e.SortExpression.ToString()

You also need to track whether the user desires the sort to be in ascending or descending order. In our example, we track this through the use of a session variable bSortAsc. When the value is equal to 1 then the sort is set for ascending, otherwise it is set for descending.

Once you know the field to sort on and the sorting direction, you can use the DataView to automatically sort the information using the Sort method of the DataView. You simply pass the name of the field to sort followed by the value ASC if you want the field sorted in ascending order or DESC if you want it sorted in descending order.

TitlesDataView.Sort= sSortField & “ ASC”

Finally, we simply bind the DataView to the grid as follows:

titleGrid.DataSource = TitlesDataView titlegrid.databind()

Figure 24-3 shows the resulting output of a user sorting the DataGrid control on the Unit Price field.

Figure 24-3 Output after sorting the Unit Price field

REVIEW

We have covered most of the major aspects of using the DataGrid control with ADO.NET and providing a user interface to support data updates, reads, and deletes. The examples provided for this session also demonstrate how to handle paging of data with the DataGrid control. You should explore these examples further to understand this technique.