The Complete Source Code for a Multi-Functional DataGrid

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,  graphics/ccc.gif" & _   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. graphics/ccc.gifDataItem, "DateAdded")) %>'  238:                 VisibleDate='<%# Convert.ToDateTime(DataBinder.Eval (Container. graphics/ccc.gifDataItem, "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> 


ASP. NET Data Web Controls Kick Start
ASP.NET Data Web Controls Kick Start
ISBN: 0672325012
EAN: 2147483647
Year: 2002
Pages: 111

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net