Insert records with GridView and SqlDataSource using the FooterRow

If you want to add records to your database using a gridview (the gridview should already have at least one record with this method), you can use the GridView FooterRow as a container to hold the controls for the user to enter new data.

You will then use the InsertParameters collection and InsertCommand of the SqlDataSource, which you populate with FindControl methods in your codebehind in the SqlDataSource Inserting event which is initally fired by something like a LinkButton command.

Summary over, here’s the meat and potatoes..

Code for a GridView and an SqlDataSource

[codesyntax lang=”csharp”]

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
  DataKeyNames="Code" DataSourceID="SqlDataSource1" EnableModelValidation="True"
    ShowFooter="True" AllowPaging="True" AllowSorting="True"
    onrowcommand="GridView1_RowCommand" >
  <Columns>
    <asp:TemplateField ShowHeader="False">
        <ItemTemplate>
            <asp:LinkButton ID="lbEdit" runat="server" CausesValidation="False" CommandName="Edit"
            Text="Edit" />
        </ItemTemplate>

        <EditItemTemplate>
            <asp:LinkButton ID="lbUpdate" runat="server" CausesValidation="True" CommandName="Update" Text="Update" />
        </EditItemTemplate>

        <FooterTemplate>
            <asp:LinkButton ID="lbInsert" runat="server" CommandName="Insert">Insert</asp:LinkButton>
        </FooterTemplate>
    </asp:TemplateField>

    <asp:BoundField DataField="Code" HeaderText="Code" InsertVisible="False"
      ReadOnly="True" SortExpression="Code" />
    <asp:TemplateField HeaderText="Description" SortExpression="Description">
      <EditItemTemplate>
        <asp:TextBox ID="txtDescripton" runat="server" Text='<%# Bind("Description") %>'></asp:TextBox>
      </EditItemTemplate>
      <ItemTemplate>
        <asp:Label ID="lblDescription" runat="server" Text='<%# Bind("Description") %>'></asp:Label>
      </ItemTemplate>
        <FooterTemplate>
          <asp:TextBox ID="txtDescription" runat="server" />
        </FooterTemplate>
    </asp:TemplateField>
    <asp:TemplateField HeaderText="Active" SortExpression="Active">
      <EditItemTemplate>
        <asp:CheckBox ID="chkActive" runat="server" Checked='<%# Bind("Active") %>' />
      </EditItemTemplate>
      <ItemTemplate>
        <asp:CheckBox ID="chkActive" runat="server" Checked='<%# Convert.ToBoolean(Eval("Active")) %>'
          Enabled="false" />
      </ItemTemplate>
      <FooterTemplate>

      </FooterTemplate>
    </asp:TemplateField>
  </Columns>
</asp:GridView>

[/codesyntax]

You then need your SqlDataSource control

[codesyntax lang=”csharp”]

<asp:SqlDataSource ID="SqlDataSource1" runat="server" OnInserting="onInserting"
  ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
  InsertCommand="INSERT INTO [TABLENAME] ([Description]) VALUES (?)"
  ProviderName="<%$ ConnectionStrings:AdminConnectionString.ProviderName %>"
  SelectCommand="SELECT * FROM [TABLENAME]"
  UpdateCommand="UPDATE [TABLENAME] SET [Description] = ?, [Active] = ? WHERE [Code] = ?">

  <InsertParameters>
    <asp:Parameter Name="Description" Type="String" />
  </InsertParameters>
  <UpdateParameters>
    <asp:Parameter Name="Description" Type="String" />
    <asp:Parameter Name="Active" Type="Boolean" />
    <asp:Parameter Name="Code" Type="Int32" />
  </UpdateParameters>
</asp:SqlDataSource>

[/codesyntax]

And finally the codebehind should be something like this where you use the events within the page lifecycle to set parameter values and do the insert.

[codesyntax lang=”csharp”]

using System;
using System.Collections.Generic;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class Admin : System.Web.UI.Page
{
    protected void onInserting(object sender, SqlDataSourceCommandEventArgs e)
    {
      string _Description = ((TextBox)(GridView1.FooterRow.FindControl("txtDescription"))).Text;

      if (_Description != null)
      {
        e.Command.Parameters["Description"].Value = _Description;
      }
    }
    protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e)
    {
      if (e.CommandName.Equals("Insert"))
      {    
        SqlDataSource1.Insert();
      }
    }
}

[/codesyntax]