Throughout this chapter, we have been building upon a single ASP.NET Web page that uses a single DataGrid to provide sorting, paging, editing, and deleting support to the Comments database table. In each of the code sections, though, we've only been examining the pertinent sections of code, leaving out code sections that we had discussed in prior code listings. By displaying only the pertinent code in each listing, the listings were less cluttered, and allowed you to focus on the key lines of code. However, it is also helpful to see the complete source code in understanding how all the code sections fit together. Listing 10.6 contains the complete source code for our multi-functional DataGrid. There are no comments or screenshots following the listing because we've already dissected the various pieces of the ASP.NET Web page. The listing is intended to present all the code as it would appear in your ASP.NET Web page. Listing 10.6 The Complete Code for the Multi-Functional DataGrid[View full width] 1: <%@ import Namespace="System.Data" %> 2: <%@ import Namespace="System.Data.SqlClient" %> 3: <script runat="server" language="VB"> 4: 5: Sub Page_Load(sender as Object, e as EventArgs) 6: If Not Page.IsPostBack then 7: ViewState("SortExpr") = "DateAdded ASC" 8: BindData(ViewState("SortExpr")) 9: End If 10: End Sub 11: 12: 13: Sub BindData(sortExpr as String) 14: '1. Create a connection 15: Const strConnString as String = "server=localhost;uid=sa;pwd=; database=pubs" 16: Dim objConn as New SqlConnection(strConnString) 17: 18: '2. Create a command object for the query 19: Dim strSQL as String 20: strSQL = "SELECT CommentID, Name, Comment, DateAdded " & _ 21: "FROM Comments ORDER BY " & sortExpr 22: 23: Dim objCmd as New SqlCommand(strSQL, objConn) 24: 25: '3. Create a DataAdapter and Fill the DataSet 26: Dim objDA as New SqlDataAdapter() 27: objDA.SelectCommand = objCmd 28: 29: objConn.Open() 30: 31: Dim objDS as DataSet = New DataSet() 32: objDA.Fill(objDS, "titles") 33: 34: objConn.Close() 35: 36: 'Finally, specify the DataSource and call DataBind() 37: dgComments.DataSource = objDS 38: dgComments.DataBind() 39: 40: objConn.Close() 'Close the connection 41: 42: ShowPageInformation() 43: End Sub 44: 45: 46: Sub ShowPageInformation() 47: 'This sub displays paging information in the appropriate label 48: lblPagingInfo.Text = "Displaying Page " & _ 49: (dgComments.CurrentPageIndex+1).ToString() & " of " & _ 50: dgComments.PageCount 51: End Sub 52: 53: 54: Sub dgComments_Paging(sender As Object, e As DataGridPageChangedEventArgs) 55: 'Turn off editing 56: dgComments.EditItemIndex = -1 57: 58: dgComments.CurrentPageIndex = e.NewPageIndex 59: BindData(ViewState("SortExpr")) 60: End Sub 61: 62: 63: Sub dgComments_Sorting(sender as Object, e as DataGridSortCommandEventArgs) 64: 'Set the sortExpr ViewState variable accordingly 65: ViewState("SortExpr") = e.SortExpression 66: 67: 'Reset the data to show the FIRST page of data 68: dgComments.CurrentPageIndex = 0 69: 70: 'Turn off editing 71: dgComments.EditItemIndex = -1 72: 73: 'Bind the data! 74: BindData(ViewState("SortExpr")) 75: End Sub 76: 77: 78: Sub dgComments_EditRow(sender As Object, e As DataGridCommandEventArgs) 79: dgComments.EditItemIndex = e.Item.ItemIndex 80: BindData(ViewState("SortExpr")) 81: End Sub 82: 83: Sub dgComments_UpdateRow(sender As Object, e As DataGridCommandEventArgs) 84: 'Make sure the Page is Valid 85: If Not Page.Isvalid Then Exit Sub 86: 87: 'Get information from columns... 88: Dim nameTextBox as TextBox = e.Item.Cells(1).FindControl("txtName") 89: Dim commentTextBox as TextBox = e.Item.Cells(2). FindControl("txtComment") 90: Dim dateAdded as Calendar = e.Item.Cells(3).FindControl("calDate") 91: 92: Dim iCommentID as Integer = dgComments.DataKeys(e.Item.ItemIndex) 93: 94: 95: 'Update the database... 96: Dim strSQL as String 97: strSQL = "UPDATE Comments SET Name = @NameParam, DateAdded = @DateAddedParam, " & _ 98: "Comment = @CommentParam WHERE CommentID = @CommentIDParam" 99: 100: Const strConnString as String = "server=localhost;uid=sa;pwd=; database=pubs" 101: Dim objConn as New SqlConnection(strConnString) 102: 103: Dim objCmd as New SqlCommand(strSQL, objConn) 104: 105: Dim nameParam as New SqlParameter("@NameParam", SqlDbType.VarChar, 50) 106: Dim commentParam as New SqlParameter("@CommentParam", SqlDbType.VarChar, 255) 107: Dim dateAddedParam as New SqlParameter("@DateAddedParam", SqlDbType.DateTime) 108: Dim commentIDParam as New SqlParameter("@CommentIDParam", SqlDbType.Int, 4) 109: 110: nameParam.Value = nameTextBox.Text 111: objCmd.Parameters.Add(nameParam) 112: 113: commentParam.Value = commentTextBox.Text 114: objCmd.Parameters.Add(commentParam) 115: 116: dateAddedParam.Value = dateAdded.SelectedDate 117: objCmd.Parameters.Add(dateAddedParam) 118: 119: commentIDParam.Value = iCommentID 120: objCmd.Parameters.Add(commentIDParam) 121: 122: 'Issue the SQL command 123: objConn.Open() 124: objCmd.ExecuteNonQuery() 125: objConn.Close() 126: 127: dgComments.EditItemIndex = -1 128: BindData(ViewState("SortExpr")) 129: End Sub 130: 131: Sub dgComments_CancelRow(sender As Object, e As DataGridCommandEventArgs) 132: dgComments.EditItemIndex = -1 133: BindData(ViewState("SortExpr")) 134: End Sub 135: 136: Sub dgComments_DeleteRow(sender As Object, e As DataGridCommandEventArgs) 137: 'Turn off editing 138: dgComments.EditItemIndex = -1 139: 140: Dim iCommentID as Integer 141: iCommentID = dgComments.DataKeys(e.Item.ItemIndex) 142: 143: 'Update the database... 144: Dim strSQL as String 145: strSQL = "DELETE FROM Comments WHERE CommentID = @CommentIDParam" 146: 147: Const strConnString as String = "server=localhost;uid=sa;pwd=; database=pubs" 148: Dim objConn as New SqlConnection(strConnString) 149: 150: Dim objCmd as New SqlCommand(strSQL, objConn) 151: 152: Dim commentIDParam as New SqlParameter("@CommentIDParam", SqlDbType.Int, 4) 153: commentIDParam.Value = iCommentID 154: objCmd.Parameters.Add(commentIDParam) 155: 156: 'Issue the SQL command 157: objConn.Open() 158: objCmd.ExecuteNonQuery() 159: objConn.Close() 160: 161: BindData(ViewState("SortExpr")) 162: End Sub 163: 164: Sub dgComments_RowDataBound(sender as Object, e As DataGridItemEventArgs) 165: If e.Item.ItemType <> ListItemType.Header AND _ 166: e.Item.ItemType <> ListItemType.Footer then 167: Dim deleteButton as LinkButton = e.Item.Cells(1).Controls(0) 168: 169: 'We can now add the onclick event handler 170: deleteButton.Attributes("onclick") = "javascript:return " & _ 171: "confirm('Are you sure you want to delete the following comment?');" 172: End If 173: End Sub 174: 175: </script> 176: <form runat="server"> 177: <asp:DataGrid runat="server" 178: Font-Name="Verdana" Font-Size="9pt" CellPadding="5" 179: AlternatingItemStyle-BackColor="#dddddd" 180: AutoGenerateColumns="False" Width="75%" 181: PageSize="10" AllowPaging="True" 182: OnPageIndexChanged="dgComments_Paging" 183: AllowSorting="True" OnSortCommand="dgComments_Sorting" 184: OnEditCommand="dgComments_EditRow" 185: OnUpdateCommand="dgComments_UpdateRow" 186: OnCancelCommand="dgComments_CancelRow" 187: DataKeyField="CommentID" 188: OnDeleteCommand="dgComments_DeleteRow" 189: OnItemDataBound="dgComments_RowDataBound"> 190: 191: <HeaderStyle BackColor="Navy" ForeColor="White" Font-Size="13pt" 192: Font-Bold="True" HorizontalAlign="Center" /> 193: 194: <PagerStyle BackColor="Navy" ForeColor="White" Font-Size="8pt" 195: Font-Bold="True" HorizontalAlign="Right" 196: NextPageText="Next >" PrevPageText="< Prev" /> 197: 198: <Columns> 199: <asp:EditCommandColumn EditText="Edit" UpdateText="Update" 200: CancelText="Cancel" ButtonType="LinkButton" /> 201: <asp:ButtonColumn Text="Delete" ButtonType="LinkButton" 202: CommandName="Delete" /> 203: <asp:TemplateColumn HeaderText="Name" SortExpression="Name"> 204: <ItemTemplate> 205: <%# DataBinder.Eval(Container.DataItem, "Name") %> 206: </ItemTemplate> 207: <EditItemTemplate> 208: <asp:TextBox runat="server" Columns="15" 209: MaxLength="50" Font-Name="Verdana" Font-Size="9pt" 210: Text='<%# DataBinder.Eval(Container.DataItem, "Name") %>' /> 211: <asp:RequiredFieldValidator runat="server" 212: ControlToValidate="txtName" Display="Dynamic" 213: ErrorMessage="<br />You must provide a Name." /> 214: </EditItemTemplate> 215: </asp:TemplateColumn> 216: <asp:TemplateColumn HeaderText="Comment" SortExpression="Comment"> 217: <ItemTemplate> 218: <%# DataBinder.Eval(Container.DataItem, "Comment") %> 219: </ItemTemplate> 220: <EditItemTemplate> 221: <asp:TextBox runat="server" Width="95%" 222: MaxLength="255" Font-Name="Verdana" Font-Size="9pt" 223: TextMode="MultiLine" Rows="5" 224: Text='<%# DataBinder.Eval(Container.DataItem, "Comment") %>' /> 225: <asp:RequiredFieldValidator runat="server" 226: ControlToValidate="txtComment" Display="Dynamic" 227: ErrorMessage="<br />You must provide a Comment." /> 228: </EditItemTemplate> 229: </asp:TemplateColumn> 230: <asp:TemplateColumn HeaderText="Date Added" SortExpression="DateAdded" 231: ItemStyle-HorizontalAlign="Center"> 232: <ItemTemplate> 233: <%# DataBinder.Eval(Container.DataItem, "DateAdded", "{0:d}") %> 234: </ItemTemplate> 235: <EditItemTemplate> 236: <asp:Calendar runat="server" 237: SelectedDate='<%# Convert.ToDateTime(DataBinder.Eval (Container. DataItem, "DateAdded")) %>' 238: VisibleDate='<%# Convert.ToDateTime(DataBinder.Eval (Container. DataItem, "DateAdded")) %>' /> 239: </EditItemTemplate> 240: </asp:TemplateColumn> 241: </Columns> 242: </asp:DataGrid> 243: <asp:label runat="server" Font-Name="Verdana" 244: Font-Size="9pt" Font-Italic="True" Width="75%" HorizontalAlign="Right" /> 245: </form> |