Our main page has pretty much remained the same, the only thing you should notice is simply because we need more data to page through to make this thing work, I have changed the database we're using, field names, SQL query and connection string used in the code from the Pubs database to Northwind.
Main Page
<%@ Page Language="VB" Strict="True" Explicit="True" Buffer="True" Debug="False" Trace="False"
Inherits="MultiDeleteDG.WebForm" Src="mDatagrid.aspx.vb" EnableSessionState="True" %>
<html>
<head></head>
<body>
<form runat="server">
<h3>Selecting, Confirming & Deleting Multiple Checkbox
Items In A DataGrid (i.e. HotMail & Yahoo) -<br>
Part 2: Maintaining CheckBox State Across Pages with Sorting </h3>
<br />
Current Page: <%=MyDataGrid.CurrentPageIndex +1%> <br />
<ASP:DataGrid id="MyDataGrid" runat="server"
Width="700"
BackColor="white"
BorderColor="black"
CellPadding="3"
CellSpacing="0"
Font-Size="9pt"
AutoGenerateColumns="False"
HeaderStyle-BackColor="darkred"
HeaderStyle-ForeColor="white"
AllowPaging="True"
AllowCustomPaging="False"
AllowSorting="True"
OnPageIndexChanged="MyDataGrid_Page"
OnSortCommand="MyDataGrid_Sort"
PageSize="10"
PagerStyle-Mode="NumericPages"
PagerStyle-HorizontalAlign="Right"
DataKeyField="ID">
<Columns>
<asp:TemplateColumn>
<HeaderTemplate>
<asp:CheckBox ID="CheckAll" OnClick="javascript: return select_deselectAll (this.checked, this.id);"
runat="server" />
<font face="Webdings" color="white" size="4">a</font> </HeaderTemplate>
<ItemTemplate>
<asp:CheckBox ID="DeleteThis" OnClick="javascript: return select_deselectAll (this.checked, this.id);"
runat="server" />
</ItemTemplate>
</asp:TemplateColumn>
<asp:BoundColumn HeaderText="StoreID" SortExpression="ID asc" Datafield="ID" runat="server" />
<asp:BoundColumn HeaderText="Store" SortExpression="Company asc" Datafield="Company" runat="server" />
<asp:BoundColumn HeaderText="Address" SortExpression="Address asc" Datafield="Address" runat="server" />
<asp:BoundColumn HeaderText="City" SortExpression="City asc" Datafield="City" runat="server" />
<asp:BoundColumn HeaderText="State" SortExpression="State asc" Datafield="State" runat="server" />
<asp:BoundColumn HeaderText="Zip" SortExpression="Zip asc" Datafield="Zip" runat="server" />
</Columns>
</ASP:DataGrid>
<br />
<asp:Button id="Confirm" onclick="DeleteAllIds" runat="server" Text="Delete Items" />
<asp:Button id="ClearAll" onclick="ClearDataGrid" runat="server" Text="Clear All" />
<span id="OutputMsg" runat="server" EnableViewState="false" />
</form>
</body>
</html>
The Code-Behind
Imports System
Imports System.Collections
Imports System.Data
Imports System.Data.SQLClient
Imports System.Text
Imports System.Web
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.Web.UI.HtmlControls
Imports Microsoft.VisualBasic Namespace MultiDeleteDG
Public Class WebForm
Inherits System.Web.UI.Page
Protected MyDataGrid As System.Web.UI.WebControls.DataGrid
Protected CheckBox As System.Web.UI.WebControls.CheckBox
Protected OutputMsg As System.Web.UI.HtmlControls.HtmlGenericControl
Protected objConnect As SqlConnection
Protected myDataAdapter As SqlDataAdapter
Protected myCommand As SqlCommand
Protected DS as Dataset
Protected dgItem As DataGridItem
Public deletedIds As String = ""
Public ChkdItems As String = ""
Public SortField As String = ""
Public ChkBxIndex As String = ""
Public BxChkd As Boolean = False
Public CheckedItems As ArrayList
Public Results() As String
Sub Page_PreRender (ByVal Sender As Object, ByVal E As EventArgs)
Dim nl As String = Environment.NewLine
Dim jsScript As New StringBuilder ()
With jsScript
.Append ("<script language=JavaScript>" & nl)
.Append ("<!--" & nl & nl)
.Append ("function confirmDelete (frm) {" & nl & nl)
.Append (" // loop through all elements" & nl & nl)
.Append (" for (i=0; i<frm.length; i++) {"& nl & nl)
.Append (" // Look for our checkboxes only" & nl)
.Append (" if (frm.elements[i].name.indexOf ('DeleteThis') !=-1) {" & nl & nl)
.Append (" // If any are checked then confirm alert, otherwise nothing happens" & nl)
.Append (" if(frm.elements[i].checked) {" & nl & nl)
.Append (" return confirm ('Are you sure you want to delete your selection(s)?')" & nl & nl)
.Append (" }" & nl)
.Append (" }" & nl)
.Append (" }" & nl)
.Append (" }" & nl & nl)
.Append ("/*Using modified select_deselectAll script function of my original one,")
.Append (" from Developerfusion.com forum members - ketcapli & thombo")
.Append (" Forum Post - [http://www.developerfusion.co.uk/forums/topic-22773]*/")
.Append ("function select_deselectAll (chkVal, idVal) {" & nl)
.Append (" var frm = document.forms[0];" & nl)
.Append (" if (idVal.indexOf('DeleteThis') != -1 && chkVal == true){" & nl)
.Append (" var AllAreSelected = true;" & nl)
.Append(" for (i=0; i<frm.length; i++) {" & nl)
.Append(" if (frm.elements[i].id.indexOf('DeleteThis') != -1 && frm.elements[i].checked == false){ " & nl)
.Append (" AllAreSelected = false;" & nl)
.Append (" break;" & nl)
.Append (" } " & nl)
.Append (" } " & nl)
.Append (" if(AllAreSelected == true){" & nl)
.Append (" for (j=0; j<frm.length; j++) {" & nl)
.Append (" if (frm.elements[j].id.indexOf ('CheckAll') != -1) {" & nl)
.Append (" frm.elements[j].checked = true;" & nl)
.Append (" break;" & nl)
.Append (" }" & nl)
.Append (" }" & nl)
.Append (" }" & nl)
.Append (" } else {" & nl)
.Append (" for (i=0; i<frm.length; i++) {" & nl)
.Append (" if (idVal.indexOf ('CheckAll') != -1) {" & nl)
.Append (" if(chkVal == true) {" & nl)
.Append (" frm.elements[i].checked = true; " & nl)
.Append (" } else {" & nl)
.Append (" frm.elements[i].checked = false; " & nl)
.Append (" }" & nl)
.Append (" } else if (idVal.indexOf('DeleteThis') != -1 && frm.elements[i].checked == false) {" & nl)
.Append (" for (j=0; j<frm.length; j++) {" & nl)
.Append (" if (frm.elements[j].id.indexOf ('CheckAll') != -1) { " & nl)
.Append (" frm.elements[j].checked = false;" & nl)
.Append (" break; " & nl)
.Append (" } " & nl)
.Append (" } " & nl)
.Append (" } " & nl)
.Append (" } " & nl)
.Append (" } " & nl)
.Append (" } " & nl & nl)
.Append ("//--> " & nl & nl)
.Append ("</scr" & "ipt>")
End With
RegisterClientScriptBlock ("clientScript", jsScript.ToString())
jsScript = Nothing
Dim button As WebControl = CType(Page.FindControl("Confirm"), WebControl)
button.Attributes.Add("onclick", "return confirmDelete (this.form);")
End Sub
Sub Page_Load (ByVal Sender As Object, ByVal E As EventArgs)
objConnect = New SqlConnection("server=(local);database=Northwind;uid=sa;pwd=;")
If Not IsPostBack Then
Session.Clear()
'Set up default column sorting
If IsNothing(Session ("SortOrder")) Then
BindData ("ID asc")
Else
BindData (Session ("SortOrder"))
End If
End If
End Sub
Sub MyDataGrid_Page (sender As Object, e As DataGridPageChangedEventArgs)
'Get CheckBoxValues before paging occurs
GetCheckBoxValues()
MyDataGrid.CurrentPageIndex = e.NewPageIndex
BindData(Session ("SortOrder"))
'Populate current DataGrid page with the current page items from Session after databind
RePopulateCheckBoxes ()
End Sub
Sub GetCheckBoxValues() 'As paging occurs store checkbox values
CheckedItems = New ArrayList
'Loop through DataGrid Items
For Each dgItem In MyDataGrid.Items
'Retrieve key value of each record based on DataGrids DataKeyField property
ChkBxIndex = MyDataGrid.DataKeys(dgItem.ItemIndex)
CheckBox = dgItem.FindControl("DeleteThis")
'Add ArrayList to Session if it doesnt exist
If Not IsNothing(Session ("CheckedItems")) Then
CheckedItems = Session ("CheckedItems")
End If
If CheckBox.Checked Then
BxChkd = True
'Add to Session if it doesnt already exist
If Not CheckedItems.Contains(ChkBxIndex) Then
CheckedItems.Add(ChkBxIndex.ToString())
End If
Else
'Remove value from Session when unchecked
CheckedItems.Remove(ChkBxIndex.ToString())
End If
Next
'Update Session with the list of checked items
Session ("CheckedItems") = CheckedItems
End Sub
Sub BindData (SortField As String)
'Setup Session Cache for different users
Dim Source As DataView = Session ("dgCache")
If (IsNothing (Source)) Then
Dim sqlQuery As String = "Select OrderId As Id, ShipName As Company," _
& " ShipAddress As Address, ShipCity As City, ShipCountry As State, ShipPostalCode As Zip from Orders"
myDataAdapter = New SqlDataAdapter(sqlQuery, objConnect)
DS = New Dataset()
myDataAdapter.Fill(DS, "MyDataGrid")
'Assign sort expression to Session
Session ("SortOrder") = SortField
'Setup DataView for Sorting
Source = DS.Tables(0).DefaultView
'Insert DataView into Session
Session ("dgCache") = Source
End If
Source.Sort = SortField
MyDataGrid.DataSource = Source
MyDataGrid.DataBind ()
'Close connection
objConnect.Close
End Sub
Sub RePopulateCheckBoxes ()
CheckedItems = New ArrayList
CheckedItems = Session ("CheckedItems")
If Not IsNothing(CheckedItems) Then
'Loop through DataGrid Items
For Each dgItem in MyDataGrid.Items
ChkBxIndex = MyDataGrid.DataKeys(dgItem.ItemIndex)
'Repopulate DataGrid with items found in Session
If CheckedItems.Contains(ChkBxIndex) Then
CheckBox = CType(dgItem.FindControl("DeleteThis"), CheckBox)
CheckBox.Checked = True
End If
Next
End If
'Copy ArrayList to a new array
Results = CheckedItems.ToArray(GetType(String))
'Concatenate ArrayList with comma to properly send for deletion
deletedIds = String.Join(",", Results)
End Sub
Sub DeleteAllIds (ByVal sender As Object, ByVal e As EventArgs)
'Regrab values in case the deletion occurs on the given page and any checkboxes were unchecked
'on the current page without any postback to correct the values in Session
GetCheckBoxValues ()
If BxChkd = True Then
RePopulateCheckBoxes ()
'Delete the rows of data containing the checkbox values
Dim deleteSQL As String = "DELETE from Orders WHERE OrderId IN (" + deletedIds + ");"
myCommand = New SqlCommand (deleteSQL, objConnect)
With myCommand
.Connection.Open()
.ExecuteNonQuery()
End With
'Close connection
objConnect.Close()
OutputMsg.InnerHtml += "<font size=4><b>Store information has been deleted.</b></font>"
OutputMsg.Style("color") = "green"
'Clear all Session values
Session.Clear()
'Reset DataGrid to top
MyDataGrid.CurrentPageIndex = 0
BindData (Session ("SortOrder"))
End If
End Sub
Function SortOrder (Field As String) As String
Dim so As String = Session ("SortOrder")
If Field = so Then
SortOrder = Replace (Field,"asc","desc")
ElseIf Field <> so Then
SortOrder = Replace (Field,"desc","asc")
Else
SortOrder = Replace (Field,"asc","desc")
End If
'Maintain persistent sort order
Session ("SortOrder") = SortOrder
End Function
Sub MyDataGrid_Sort (Sender As Object, E As DataGridSortCommandEventArgs)
'To retain checkbox on sorting
GetCheckBoxValues ()
MyDataGrid.CurrentPageIndex = 0 'To sort from top
BindData (SortOrder (E.SortExpression).ToString()) 'Rebind our DataGrid
'To retain checkbox on sorting
RePopulateCheckBoxes ()
End Sub
Sub ClearDataGrid (ByVal sender As Object, ByVal e As EventArgs)
'Clear All Session Values
Session.Clear()
'Reset DataGrid to top
MyDataGrid.CurrentPageIndex = 0
BindData ("ID asc") 'Rebind our DataGrid
End Sub
End Class
End Namespace
So that being said, let’s now get to the next section where there obviously have been more apparent changes and tweaks, and we’ll now explore these.
Comments