Subscribe to Nakor's Blog        RSS Feed
-----

Inserting with a GridView Control in ASP.Net

Icon Leave Comment
One of the more popular controls that come with ASP.Net is the GridView control. This control is great for displaying data from a database as well as editing and deleting that same data. One of the shortfalls of the GridView, however, is its lack of support for inserting new data into the database. The goal of this tutorial is to show you how to implement an insert row directly in the GridView's footer row. This tutorial will use a SqlDataSource as the datasource for the GridView.

The first thing we need to do after creating our project (Creating Your First ASP.Net Web Application) is to drag a GridView control from the ToolBox onto the .aspx page. This can be done in either design mode or source mode. For the purpose of this tutorial I'm going to assume you know how to do the initial set up of the GridView control with a SqlDataSource (If not, this page has a few examples towards the bottom). When setting up your SqlDataSource be sure to click the "Advanced" button and check the box for automatically generating insert, edit and delete methods. I am going to enable the Edit and Delete Buttons as that will allow me to use that same column for the Insert button in the footer.

At this point your code should looking similar to this:

        <asp:GridView ID="GridView1" runat="server" AllowPaging="True" 
            AllowSorting="True" AutoGenerateColumns="False" DataKeyNames="job_id" 
            DataSourceID="SqlDataSource1">
            <Columns>
                <asp:CommandField ShowDeleteButton="True" ShowEditButton="True" />
                <asp:BoundField DataField="job_id" HeaderText="job_id" InsertVisible="False" 
                    ReadOnly="True" SortExpression="job_id" />
                <asp:BoundField DataField="job_desc" HeaderText="job_desc" 
                    SortExpression="job_desc" />
                <asp:BoundField DataField="min_lvl" HeaderText="min_lvl" 
                    SortExpression="min_lvl" />
                <asp:BoundField DataField="max_lvl" HeaderText="max_lvl" 
                    SortExpression="max_lvl" />
            </Columns>
        </asp:GridView>

        <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
            ConflictDetection="CompareAllValues" 
            ConnectionString="<%$ ConnectionStrings:pubsConnectionString %>" 
            DeleteCommand="DELETE FROM [jobs] WHERE [job_id] = @original_job_id AND [job_desc] = @original_job_desc AND [min_lvl] = @original_min_lvl AND [max_lvl] = @original_max_lvl" 
            InsertCommand="INSERT INTO [jobs] ([job_desc], [min_lvl], [max_lvl]) VALUES (@job_desc, @min_lvl, @max_lvl)" 
            OldValuesParameterFormatString="original_{0}" 
            SelectCommand="SELECT * FROM [jobs]" 
            UpdateCommand="UPDATE [jobs] SET [job_desc] = @job_desc, [min_lvl] = @min_lvl, [max_lvl] = @max_lvl WHERE [job_id] = @original_job_id AND [job_desc] = @original_job_desc AND [min_lvl] = @original_min_lvl AND [max_lvl] = @original_max_lvl">
            <DeleteParameters>
                <asp:Parameter Name="original_job_id" Type="Int16" />
                <asp:Parameter Name="original_job_desc" Type="String" />
                <asp:Parameter Name="original_min_lvl" Type="Byte" />
                <asp:Parameter Name="original_max_lvl" Type="Byte" />
            </DeleteParameters>
            <InsertParameters>
                <asp:Parameter Name="job_desc" Type="String" />
                <asp:Parameter Name="min_lvl" Type="Byte" />
                <asp:Parameter Name="max_lvl" Type="Byte" />
            </InsertParameters>
            <UpdateParameters>
                <asp:Parameter Name="job_desc" Type="String" />
                <asp:Parameter Name="min_lvl" Type="Byte" />
                <asp:Parameter Name="max_lvl" Type="Byte" />
                <asp:Parameter Name="original_job_id" Type="Int16" />
                <asp:Parameter Name="original_job_desc" Type="String" />
                <asp:Parameter Name="original_min_lvl" Type="Byte" />
                <asp:Parameter Name="original_max_lvl" Type="Byte" />
            </UpdateParameters>
        </asp:SqlDataSource>



As you can see all of the columns were generated as BoundField controls. Howerver, we are going to have to convert these into template fields so we have access to the FooterTemplate. The easiest way to do this is to click on the smart tag (the small box with an arrow in it that appears to the upper right of the gridview), then select "Edit columns...". The smart tag is available when in Design Mode but not in Source Mode. In the selected fields portion on the lower left of the wizard that pops up just select a column then click on the "Convert This Field Into A Template Field" link that is under the Properties window. Repeat this for each of the columns including the CommandField. After completing this step your GridView should now look more like this:

        <asp:GridView ID="GridView1" runat="server" AllowPaging="True" 
            AllowSorting="True" AutoGenerateColumns="False" DataKeyNames="job_id" 
            DataSourceID="SqlDataSource1">
            <Columns>
                <asp:TemplateField ShowHeader="False">
                    <EditItemTemplate>
                        <asp:LinkButton ID="LinkButton1" runat="server" CausesValidation="True" 
                            CommandName="Update" Text="Update"></asp:LinkButton>
                        <asp:LinkButton ID="LinkButton2" runat="server" CausesValidation="False" 
                            CommandName="Cancel" Text="Cancel"></asp:LinkButton>
                    </EditItemTemplate>
                    <ItemTemplate>
                        <asp:LinkButton ID="LinkButton1" runat="server" CausesValidation="False" 
                            CommandName="Edit" Text="Edit"></asp:LinkButton>
                        <asp:LinkButton ID="LinkButton2" runat="server" CausesValidation="False" 
                            CommandName="Delete" Text="Delete"></asp:LinkButton>
                    </ItemTemplate>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="job_id" InsertVisible="False" 
                    SortExpression="job_id">
                    <EditItemTemplate>
                        <asp:Label ID="Label1" runat="server" Text='<%# Eval("job_id") %>'></asp:Label>
                    </EditItemTemplate>
                    <ItemTemplate>
                        <asp:Label ID="Label1" runat="server" Text='<%# Bind("job_id") %>'></asp:Label>
                    </ItemTemplate>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="job_desc" SortExpression="job_desc">
                    <EditItemTemplate>
                        <asp:TextBox ID="TextBox1" runat="server" Text='<%# Bind("job_desc") %>'></asp:TextBox>
                    </EditItemTemplate>
                    <ItemTemplate>
                        <asp:Label ID="Label2" runat="server" Text='<%# Bind("job_desc") %>'></asp:Label>
                    </ItemTemplate>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="min_lvl" SortExpression="min_lvl">
                    <EditItemTemplate>
                        <asp:TextBox ID="TextBox2" runat="server" Text='<%# Bind("min_lvl") %>'></asp:TextBox>
                    </EditItemTemplate>
                    <ItemTemplate>
                        <asp:Label ID="Label3" runat="server" Text='<%# Bind("min_lvl") %>'></asp:Label>
                    </ItemTemplate>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="max_lvl" SortExpression="max_lvl">
                    <EditItemTemplate>
                        <asp:TextBox ID="TextBox3" runat="server" Text='<%# Bind("max_lvl") %>'></asp:TextBox>
                    </EditItemTemplate>
                    <ItemTemplate>
                        <asp:Label ID="Label4" runat="server" Text='<%# Bind("max_lvl") %>'></asp:Label>
                    </ItemTemplate>
                </asp:TemplateField>
            </Columns>
        </asp:GridView>



Now that we are using TemplateFields for each of the columns we also have access to the FooterTemplate for each of the columns. One way to edit the FooterTemplate is to once again click on the smart tag and select "Edit Templates". Then just select the FooterTemplate for each column and insert a TextBox, except the first one which will need a HyperLink control instead. You could also just manually add the FooterTemplate and its controls to the GridView by hand. I like to rename the TextBoxes to something that will let me know which column they are going to be inserting data into as it helps keep me from using the wrong textbox once it's time to start working on the codebehind. In the HyperLink control we added for the first column I'm going to set its CommandName property to "Insert", then in the GridView control we're going to make use of the OnRowCommand event. We'll also need to set the ShowFooter property to true. So now my GridView looks like this and hopefully yours should be similar:

        <asp:GridView ID="GridView1" runat="server" AllowPaging="True" 
            AllowSorting="True" AutoGenerateColumns="False" DataKeyNames="job_id" 
            DataSourceID="SqlDataSource1" OnRowCommand="GridView1_RowCommand"
            ShowFooter="true">
            <Columns>
                <asp:TemplateField ShowHeader="False">
                    <EditItemTemplate>
                        <asp:LinkButton ID="LinkButton1" runat="server" CausesValidation="True" 
                            CommandName="Update" Text="Update"></asp:LinkButton>
                        <asp:LinkButton ID="LinkButton2" runat="server" CausesValidation="False" 
                            CommandName="Cancel" Text="Cancel"></asp:LinkButton>
                    </EditItemTemplate>
                    <FooterTemplate>
                        <asp:LinkButton ID="LinkButton3" runat="server" CommandName="Insert">Insert</asp:LinkButton>
                    </FooterTemplate>
                    <ItemTemplate>
                        <asp:LinkButton ID="LinkButton1" runat="server" CausesValidation="False" 
                            CommandName="Edit" Text="Edit"></asp:LinkButton>
                        <asp:LinkButton ID="LinkButton2" runat="server" CausesValidation="False" 
                            CommandName="Delete" Text="Delete"></asp:LinkButton>
                    </ItemTemplate>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="job_id" InsertVisible="False" 
                    SortExpression="job_id">
                    <EditItemTemplate>
                        <asp:Label ID="Label1" runat="server" Text='<%# Eval("job_id") %>'></asp:Label>
                    </EditItemTemplate>
                    <ItemTemplate>
                        <asp:Label ID="Label1" runat="server" Text='<%# Bind("job_id") %>'></asp:Label>
                    </ItemTemplate>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="job_desc" SortExpression="job_desc">
                    <EditItemTemplate>
                        <asp:TextBox ID="TextBox1" runat="server" Text='<%# Bind("job_desc") %>'></asp:TextBox>
                    </EditItemTemplate>
                    <FooterTemplate>
                        <asp:TextBox ID="txtJobDesc" runat="server"></asp:TextBox>
                    </FooterTemplate>
                    <ItemTemplate>
                        <asp:Label ID="Label2" runat="server" Text='<%# Bind("job_desc") %>'></asp:Label>
                    </ItemTemplate>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="min_lvl" SortExpression="min_lvl">
                    <EditItemTemplate>
                        <asp:TextBox ID="TextBox2" runat="server" Text='<%# Bind("min_lvl") %>'></asp:TextBox>
                    </EditItemTemplate>
                    <FooterTemplate>
                        <asp:TextBox ID="txtMinLvl" runat="server"></asp:TextBox>
                    </FooterTemplate>
                    <ItemTemplate>
                        <asp:Label ID="Label3" runat="server" Text='<%# Bind("min_lvl") %>'></asp:Label>
                    </ItemTemplate>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="max_lvl" SortExpression="max_lvl">
                    <EditItemTemplate>
                        <asp:TextBox ID="TextBox3" runat="server" Text='<%# Bind("max_lvl") %>'></asp:TextBox>
                    </EditItemTemplate>
                    <FooterTemplate>
                        <asp:TextBox ID="txtMaxLvl" runat="server"></asp:TextBox>
                    </FooterTemplate>
                    <ItemTemplate>
                        <asp:Label ID="Label4" runat="server" Text='<%# Bind("max_lvl") %>'></asp:Label>
                    </ItemTemplate>
                </asp:TemplateField>
            </Columns>
        </asp:GridView>



Now all that's left is to handle the actual code to insert the data into the database so let's switch over to our .cs file and find the OnRowCommand event we created for the GridView. The first thing to do in this method is to check which Command is being used, since we only want our code to execute on the "Insert" command. To do this we check the value of the RowCommandEventArgs CommandName property. The next thing we'll do is create our insert parameters and finally we'll execute the insert command. Now that all sounds good, but let's go ahead and see what all of that looks like in code.

        protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e)
        {
            if (e.CommandName.CompareTo("Insert").Equals(0))
            {
                // This just provides easier access to the Cells in the GridView
                var cells = GridView1.FooterRow.Cells;

                // Clear any values that may already be in the InsertParameters
                SqlDataSource1.InsertParameters.Clear();

                // Create the Insert Parameters
                // I don't not include the Job ID column since
                // that is an autogenerated field in my database
                SqlDataSource1.InsertParameters.Add("job_desc", (cells[2].FindControl("txtJobDesc") as TextBox).Text);
                SqlDataSource1.InsertParameters.Add("min_lvl", (cells[3].FindControl("txtMinLvl") as TextBox).Text);
                SqlDataSource1.InsertParameters.Add("max_lvl", (cells[4].FindControl("txtMaxLvl") as TextBox).Text);
                    
                // Now we make use of our SqlDataSource to perform the Insert operation for us.
                SqlDataSource1.Insert();
            }
        }



That's it, we now have a gridview that allows you to also insert data. The only real downside to this method is if you happen to have no data available in the database. This is because the gridview will display the EmptyData Template and the Footer Template will not be displayed. You'll also want to implement any validation/error checking that may be necessary depending on your database setup and/or project requirements.

0 Comments On This Entry

 

Trackbacks for this entry [ Trackback URL ]

There are no Trackbacks for this entry

August 2014

S M T W T F S
     12
3456789
10111213141516
17181920212223
242526 27 282930
31      

Tags

    Recent Entries

    Recent Comments

    Search My Blog

    0 user(s) viewing

    0 Guests
    0 member(s)
    0 anonymous member(s)

    Categories