Problem You want to provide the ability for a user to insert a new row of data within a GridView. Solution Add a GridView and an updateable data source, such as an asp:SqlDataSource, to the .aspx file, set the ShowFooter attribute of the GridView control to TRue, add Footer-Template elements for each column that is to be inserted, add an Insert button in the footer, and initialize the properties of the data source in the code-behind. When the user clicks the Insert button, set the parameter values from the entered data and use the data source to insert the data in the GridView's RowCommand event handler in the code-behind. In the .aspx file: Add a GridView control where the data is to be displayed. Add an asp:SqlDataSource. Set the ShowFooter attribute of the GridView to TRue. Add FooterTemplate elements for each column that is to be inserted.
In the Page_Init event handler of the code-behind class for the page, use the .NET language of your choice to: Initialize the SelectCommand property of the SqlDataSource to the SQL statement used to get the data to display from the database. Initialize the InsertCommand property of the SqlDataSource to the SQL statement (with parameters) used to insert in the database the data the user enters. Add the parameters for the data the user can update to the InsertParameters collection of the SqlDataSource. In the RowCommand event handler, set the values of the InsertParameters of the SqlDataSource from the controls containing the data entered by the user and then call the Insert method of the SqlDataSource.
Figure 2-19 shows the output of a typical example in normal mode and Figure 2-18 shows the output in our application. Examples 2-45, 2-46 through 2-47 show the .aspx file and the code-behind files for an application that produces this output. Figure 2-19. GridView with row insert output 
Discussion Most applications provide the ability to add new records to a tabular display of data. Though the GridView does not directly support inserting a new record using an updateable data source, such as the SqlDataSource, it can be altered to provide the ability to insert a new record when combined with a small amount of custom code. In our example, we use the footer row of the GridView to provide a location for the textboxes and drop-down list necessary to enter the data for a new row. In addition, we add a new column on the right side of the GridView to provide a location for an Insert button, as shown in Figure 2-19. To make the footer visible, the ShowFooter attribute must be set to true. <asp:GridView runat="server" BorderColor="#000080" BorderWidth="2px" AutoGenerateColumns="False" HorizontalAlign="center" Width="90%" ShowFooter="true" OnRowCommand="gvProblems_RowCommand">
A FooterTemplate element is provided for each asp:TemplateField element to define the HTML that is to be rendered in the footer. The first and second columns contain an asp:TextBox control, the third column contains an asp:DropDownList control, and the fourth column contains an asp:Button control. <Columns> <asp:TemplateField HeaderText="Section" ItemStyle-HorizontalAlign="Center" FooterStyle-HorizontalAlign="Center"> <ItemTemplate> <%#Eval("SectionNumber")%> </ItemTemplate> <FooterTemplate> <asp:TextBox runat="server" Columns="3" /> </FooterTemplate> </asp:TemplateField> <asp:TemplateField HeaderText="Section Heading" ItemStyle-HorizontalAlign="Left" FooterStyle-HorizontalAlign="Left"> <ItemTemplate> <%#Eval("SectionHeading")%> </ItemTemplate> <FooterTemplate> <asp:TextBox runat="server" Columns="40" /> </FooterTemplate> </asp:TemplateField> <asp:TemplateField HeaderText="VB Example" ItemStyle-HorizontalAlign="Center" FooterStyle-HorizontalAlign="Center"> <ItemTemplate> <%#yesNoSelections.Item(CInt(Eval("HasVBExample")))%< </ItemTemplate> <FooterTemplate> <asp:DropDownList runat="server" DataSource="<%# yesNoSelections %>" DataTextField="Text" DataValueField="Value" /> </FooterTemplate> </asp:TemplateField> <asp:TemplateField FooterStyle-HorizontalAlign="Center"> <FooterTemplate> <asp:Button runat="server" Text="Insert" CommandName="Insert" /> </FooterTemplate> </asp:TemplateField> </Columns>
In the Page_Init event handler in the code-behind, the ArrayList used to provide the valid selections in the asp:DropDownList is initialized and bound to the drop-down list. In our example, we programmatically created the list. In your application, the list can be created from a database or any other data source, as required. The SqlDataSource is initialized in the normal manner with a couple of additions. The InsertCommand property is set to the SQL required to insert the data in the database. The SQL needs to use parameters for the data values. 
dSource.InsertCommand = "INSERT INTO EditProblem " & _ " (SectionNumber, SectionHeading, HasVBExample)" & _ " VALUES" & _ " (@SectionNumber,@SectionHeading,@HasVBExample)" 
dSource.InsertCommand = "INSERT INTO EditProblem " + " (SectionNumber, SectionHeading, HasCSExample)" + " VALUES" + " (@SectionNumber,@SectionHeading,@HasCSExample)";
In addition, Parameter objects need to be created and added to the InsertParameters collection of SqlDataSource for each of the data values in the inserted row:
param = New Parameter("SectionNumber", _ TypeCode.Int32) dSource.InsertParameters.Add(param) param = New Parameter("SectionHeading", _ TypeCode.String) dSource.InsertParameters.Add(param) param = New Parameter("HasVBExample", _ TypeCode.Int32) dSource.InsertParameters.Add(param)
param = new Parameter("SectionNumber", TypeCode.Int32); dSource.InsertParameters.Add(param); param = new Parameter("SectionHeading", TypeCode.String);
dSource.InsertParameters.Add(param); param = new Parameter("HasCSExample", TypeCode.Int32); dSource.InsertParameters.Add(param);
Finally, the DataSourceID property needs to be set to the ID of the SqlDataSource:
gvProblems.DataSourceID = dSource.ID
gvProblems.DataSourceID = dSource.ID;
In the RowCommand event handler (gvProblems_RowCommand in our example), the data entered by the user is retrieved from the controls in the footer and used to set the values for the InsertParameters:
get the section number and set the parameter value tBox = CType(gvProblems.FooterRow.FindControl("txtSectionNumber"), _ TextBox) dSource.InsertParameters("SectionNumber").DefaultValue = tBox.Text 'get the section heading and set the parameter value tBox = CType(gvProblems.FooterRow.FindControl("txtSectionHeading"), _ TextBox) dSource.InsertParameters("SectionHeading").DefaultValue = tBox.Text 'get the has sample selection and set the parameter value ddList = CType(gvProblems.FooterRow.FindControl("selHasVBSample"), _ DropDownList) dSource.InsertParameters("HasVBExample").DefaultValue = _ ddList.SelectedItem.Value
// get the section number and set the parameter value tBox = (TextBox)(gvProblems.FooterRow.FindControl("txtSectionNumber")); dSource.InsertParameters["SectionNumber"].DefaultValue = tBox.Text; // get the section heading and set the parameter value tBox = (TextBox)(gvProblems.FooterRow.FindControl("txtSectionHeading")); dSource.InsertParameters["SectionHeading"].DefaultValue = tBox.Text; // get the has sample selection and set the parameter value ddList = (DropDownList)(gvProblems.FooterRow.FindControl("selHasCSSample")); dSource.InsertParameters["HasCSExample"].DefaultValue = ddList.SelectedItem.Value;
Once the parameter values for the insert command have been set, the Insert method of the data source is called to perform the insert operation:
dSource.Insert()
dSource.Insert();
Updateable data sources, like the SqlDataSource used in this example, significantly reduce the amount of custom code required to implement common functionality, such as inserting a new record. This same approach can be used with a DataGrid, provided your application requires the use of a DataGrid. Example 2-45. GridView with row insert (.aspx) <%@ Page Language="VB" MasterPageFile="~/ASPNetCookbookVB.master" AutoEventWireup="false" CodeFile="CH02GridViewWithInsertVB.aspx.vb" Inherits="ASPNetCookbook.VBExamples.CH02GridViewWithInsertVB" Title="Inserting Row In a GridView" %> <asp:Content Runat="server" ContentPlaceHolder> <div align="center" > Inserting Row In a GridView (VB) </div> <asp:SqlDataSource runat="server" /> <asp:GridView runat="server" BorderColor="#000080" BorderWidth="2px" AutoGenerateColumns="False" HorizontalAlign="center" Width="90%" ShowFooter="true" OnRowCommand="gvProblems_RowCommand"> <HeaderStyle HorizontalAlign="Center" Css /> <RowStyle css /> <AlternatingRowStyle css /> <FooterStyle Css /> <Columns> <asp:TemplateField HeaderText="Section" ItemStyle-HorizontalAlign="Center" FooterStyle-HorizontalAlign="Center"> <ItemTemplate> <%#Eval("SectionNumber")%> </ItemTemplate> <FooterTemplate> <asp:TextBox runat="server" Columns="3" /> </FooterTemplate> </asp:TemplateField> <asp:TemplateField HeaderText="Section Heading" ItemStyle-HorizontalAlign="Left" FooterStyle-HorizontalAlign="Left"> <ItemTemplate> <%#Eval("SectionHeading")%> </ItemTemplate> <FooterTemplate> <asp:TextBox runat="server" Columns="40" /> </FooterTemplate> </asp:TemplateField> <asp:TemplateField HeaderText="VB Example" ItemStyle-HorizontalAlign="Center" FooterStyle-HorizontalAlign="Center"> <ItemTemplate> <%#yesNoSelections.Item(CInt(Eval("HasVBExample")))%> </ItemTemplate> <FooterTemplate> <asp:DropDownList runat="server" DataSource="<%# yesNoSelections %>" DataTextField="Text" DataValueField="Value" /> </FooterTemplate>> </asp:TemplateField> <asp:TemplateField FooterStyle-HorizontalAlign="Center"> <FooterTemplate> <asp:Button runat="server" Text="Insert" CommandName="Insert" /> </FooterTemplate> </asp:TemplateField> </Columns> </asp:GridView> </asp:Content>
|
Example 2-46. GridView with row insert (.vb) Option Explicit On Option Strict On Imports Microsoft.VisualBasic Imports System.Configuration Imports System.Data Namespace ASPNetCookbook.VBExamples ''' <summary> ''' This class provides the code behind for ''' CH02GridViewWithInsertVB.aspx ''' </summary> Partial Class CH02GridViewWithInsertVB Inherits System.Web.UI.Page 'The following variable contains the list of yes/no selections used in 'the dropdown lists and is declared protected to provide access to the 'data from the aspx page Protected yesNoSelections As ArrayList '''*********************************************************************** ''' <summary> ''' This routine provides the event handler for the page init event. It ''' is responsible for initializing the data source and the grid view ''' on the page. ''' </summary> ''' ''' <param name="sender">Set to the sender of the event</param> ''' <param name="e">Set to the event arguments</param> Protected Sub Page_Init(ByVal sender As Object, _ ByVal e As System.EventArgs) Handles Me.Init Dim param As Parameter 'build the arraylist with the acceptable responses to the '"Has VB Sample" field yesNoSelections = New ArrayList(2) yesNoSelections.Add(New ListItem("No", "0")) yesNoSelections.Add(New ListItem("Yes", "1")) 'configure the data source to get the data from the database 'NOTE: This code must be executed anytime the page is rendered ' including postbacks dSource.ConnectionString = ConfigurationManager. _ ConnectionStrings("sqlConnectionString").ConnectionString dSource.DataSourceMode = SqlDataSourceMode.DataSet dSource.ProviderName = "System.Data.SqlClient" dSource.SelectCommand = "SELECT EditProblemID, SectionNumber" & _ ", SectionHeading, HasVBExample" & _ " FROM EditProblem" & _ " ORDER BY SectionNumber" dSource.InsertCommand = "INSERT INTO EditProblem " & _ " (SectionNumber, SectionHeading, HasVBExample)" & _ " VALUES" & _ " (@SectionNumber,@SectionHeading,@HasVBExample)" param = New Parameter("SectionNumber", _ TypeCode.Int32) dSource.InsertParameters.Add(param) param = New Parameter("SectionHeading", _ TypeCode.String) dSource.InsertParameters.Add(param) param = New Parameter("HasVBExample", _ TypeCode.Int32) dSource.InsertParameters.Add(param) 'set the source of the data for the gridview control gvProblems.DataSourceID = dSource.ID End Sub 'Page_Init '''*********************************************************************** ''' <summary> ''' This routine provides the event handler for the GridView row command. ''' It is responsible for processing the Add button click. ''' </summary> ''' ''' <param name="sender">Set to the sender of the event</param> ''' <param name="e">Set to the event arguments</param> Protected Sub gvProblems_RowCommand(ByVal sender As Object, _ ByVal e As System.Web.UI.WebControls.GridViewCommandEventArgs) Dim tBox As TextBox Dim ddList As DropDownList 'check to see if the command is for the Insert button If (e.CommandName.Equals("Insert")) Then 'get the section number and set the parameter value tBox = CType(gvProblems.FooterRow.FindControl("txtSectionNumber"), _ TextBox) dSource.InsertParameters("SectionNumber").DefaultValue = tBox.Text 'get the section heading and set the parameter value tBox = CType(gvProblems.FooterRow.FindControl("txtSectionHeading"), _ TextBox) dSource.InsertParameters("SectionHeading").DefaultValue = tBox.Text 'get the has sample selection and set the parameter value ddList = CType(gvProblems.FooterRow.FindControl("selHasVBSample"), _ DropDownList) dSource.InsertParameters("HasVBExample").DefaultValue = _ ddList.SelectedItem.Value 'insert the row in the database dSource.Insert() End If End Sub 'gvProblems_RowCommand End Class 'CH02GridViewWithInsertVB End Namespace
|
Example 2-47. GridView with row insert (.cs) using System; using System.Collections; using System.Configuration; using System.Data; using System.Web.UI.WebControls; namespace ASPNetCookbook.CSExamples { /// <summary> /// This class provides the code behind for /// CH02GridViewWithInsertCS.aspx /// </summary> public partial class CH02GridViewWithInsertCS : System.Web.UI.Page { // The following variable contains the list of yes/no selections used in // the dropdown lists and is declared protected to provide access to the // data from the aspx page protected ArrayList yesNoSelections; ///*********************************************************************** /// <summary> /// This routine provides the event handler for the page init event. It /// is responsible for initializing the data source and the grid view /// on the page. /// </summary> /// /// <param name="sender">Set to the sender of the event</param> /// <param name="e">Set to the event arguments</param> protected void Page_Init(object sender, EventArgs e) { Parameter param; // build the arraylist with the acceptable responses to the // "Has C# Sample" field yesNoSelections = new ArrayList(2); yesNoSelections.Add(new ListItem("No", "0")); yesNoSelections.Add(new ListItem("Yes", "1")); // configure the data source to get the data from the database // NOTE: This code must be executed anytime the page is rendered // including postbacks dSource.ConnectionString = ConfigurationManager. ConnectionStrings["sqlConnectionString"].ConnectionString; dSource.DataSourceMode = SqlDataSourceMode.DataSet; dSource.ProviderName = "System.Data.SqlClient"; dSource.SelectCommand = "SELECT EditProblemID, SectionNumber" + ", SectionHeading, HasCSExample" + " FROM EditProblem" + " ORDER BY SectionNumber"; dSource.InsertCommand = "INSERT INTO EditProblem " + " (SectionNumber, SectionHeading, HasCSExample)" + " VALUES" + " (@SectionNumber,@SectionHeading,@HasCSExample)"; param = new Parameter("SectionNumber", TypeCode.Int32); dSource.InsertParameters.Add(param); param = new Parameter("SectionHeading", TypeCode.String); dSource.InsertParameters.Add(param); param = new Parameter("HasCSExample", TypeCode.Int32); dSource.InsertParameters.Add(param); // set the source of the data for the gridview control gvProblems.DataSourceID = dSource.ID; } // Page_Init ///*********************************************************************** /// <summary> /// This routine provides the event handler for the GridView row command. /// It is responsible for processing the Add button click. /// </summary> /// /// <param name="sender">Set to the sender of the event</param> /// <param name="e">Set to the event arguments</param> protected void gvProblems_RowCommand(Object sender, System.Web.UI.WebControls.GridViewCommandEventArgs e) { TextBox tBox; DropDownList ddList; // check to see if the command is for the Insert button if (e.CommandName.Equals("Insert")) { // get the section number and set the parameter value tBox = (TextBox)(gvProblems.FooterRow.FindControl("txtSectionNumber")); dSource.InsertParameters["SectionNumber"].DefaultValue = tBox.Text; // get the section heading and set the parameter value tBox = (TextBox)(gvProblems.FooterRow.FindControl("txtSectionHeading")); dSource.InsertParameters["SectionHeading"].DefaultValue = tBox.Text; // get the has sample selection and set the parameter value ddList = (DropDownList) (gvProblems.FooterRow.FindControl("selHasCSSample")); dSource.InsertParameters["HasCSExample"].DefaultValue = ddList.SelectedItem.Value; // insert the row in the database dSource.Insert(); } } // gvProblems_RowCommand } // CH02GridViewWithInsertCS }
|
|
