Introduction

This little code tidbit makes it easy to insert a row into a gridview bound to an sqldatasource.

Background

I hate... I mean hate placing and naming controls in the footer row of my gridview so that I can do an insert. What I hate even more is looking up with findcontrol("controlname") all of the controls in the footer row, clearing the sqlDatasources insert parameters, and then adding them back one at a time with the value of the control in the footer that I just created. It just seems ridiculous. So I'm working on a project this week that literally has 27 gridviews that need to allow an insert. Ok so I could use a detailsview control but that's just way too time consuming and it does not look nice, and all I want to do is let the user type, select, or check mark a few values, click an insert link, and have the job done.

Yes I could put the fields in a table below the grid view, I know but I don't like that either. So here is the solution I came up with and I'm betting some of you will be just thrilled to have it. It took about 6 hours to figure out, but it takes about 5 minutes to implement now on each gridview that I want to allow an insert for.

Using the Code

So let's start with our gridview. This is a big nasty one to demonstrate the point, though a little two field one will work just as well. The first really important thing to notice is that every field has the SortExpression set to the corresponding name of the filed in the datasource. If you have to do some other kind of sorting or you don't want the user to be able to sort by all of the fields, this isn't going to work for you and you'll have to do it the long way... sorry.

Also notice that the OnRowCommad is set, ShowFooter is true and that the ID column is set to InsertVisible=False.

<asp:GridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True"
 AutoGenerateColumns="False" CellPadding="4" DataKeyNames="id" DataSourceID="UsersDS"
 Caption="Manage Users" CaptionAlign="Left" ForeColor="#333333" GridLines="None"
 ShowFooter="True" Font-Names="Arial" Font-Size="11px" 
HorizontalAlign="Center" OnRowCommand="GridView1_RowCommand">
  <AlternatingRowStyle BackColor="White" ForeColor="#284775" />
  <Columns>
  <asp:TemplateField ShowHeader="False" SortExpression="none">
    <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="ftrInsert" runat="server" 
		Text="Insert" CommandName="ftrInsert">
   </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="id" InsertVisible="False" SortExpression="id" 
Visible="False">
    <EditItemTemplate>
      <asp:Label ID="Label1" runat="server" Text='<%# Eval("id") %>'></asp:Label>
    </EditItemTemplate>
    <ItemTemplate>
      <asp:Label ID="Label13" runat="server" Text='<%# Bind("id") %>'></asp:Label>
    </ItemTemplate>
  </asp:TemplateField>
  <asp:TemplateField HeaderText="Company" SortExpression="Company">
    <EditItemTemplate>
      <asp:TextBox ID="TextBox2" runat="server" Text='<%# Bind("Company") %>'>
</asp:TextBox>
    </EditItemTemplate>
    <FooterTemplate>
      <asp:DropDownList ID="ftrCompany" runat="server" DataSourceID="CompaniesDS" 
DataTextField="Company_Name" DataValueField="id"> </asp:DropDownList>
    </FooterTemplate>
    <ItemTemplate>
      <asp:Label ID="Label2" runat="server" Text='<%# Bind("Company") %>'></asp:Label>
    </ItemTemplate>
  </asp:TemplateField>
  <asp:TemplateField HeaderText="Access Level" SortExpression="Access_Level">
    <EditItemTemplate>
      <asp:TextBox ID="TextBox1" runat="server" Text='<%# Bind("Access_Level") %>'>
</asp:TextBox>
    </EditItemTemplate>
    <FooterTemplate>
      <asp:DropDownList ID="ftrAccessLevel" runat="server" DataSourceID="AccessLevelsDS"
      DataTextField="Access_Name" DataValueField="id"> </asp:DropDownList>
    </FooterTemplate>
    <ItemTemplate>
      <asp:Label ID="Label1" runat="server" Text='<%# Bind("Access_Level") %>'>
</asp:Label>
    </ItemTemplate>
  </asp:TemplateField>
  <asp:TemplateField HeaderText="Employee ID" SortExpression="Employee_ID">
    <EditItemTemplate>
      <asp:TextBox ID="TextBox3" runat="server" Text='<%# Bind("Employee_ID") %>'>
</asp:TextBox>
    </EditItemTemplate>
    <FooterTemplate>
      <asp:TextBox ID="ftrEmpID" runat="server" Columns="5"></asp:TextBox>
    </FooterTemplate>
    <ItemTemplate>
      <asp:Label ID="Label3" runat="server" Text='<%# Bind("Employee_ID") %>'>
	</asp:Label>
    </ItemTemplate>
  </asp:TemplateField>
  <asp:TemplateField HeaderText="Department" SortExpression="Department">
    <EditItemTemplate>
      <asp:TextBox ID="TextBox4" runat="server" Text='<%# Bind("Department") %>'>
</asp:TextBox>
    </EditItemTemplate>
    <FooterTemplate>
      <asp:DropDownList ID="DropDownList1" runat="server" DataSourceID="DepartmentsDS"
       DataTextField="Department_Name" DataValueField="id"> </asp:DropDownList>
    </FooterTemplate>
    <ItemTemplate>
      <asp:Label ID="Label4" runat="server" Text='<%# Bind("Department") %>'>
	</asp:Label>
    </ItemTemplate>
  </asp:TemplateField>
  <asp:TemplateField HeaderText="Email" SortExpression="Email">
    <EditItemTemplate>
      <asp:TextBox ID="TextBox5" runat="server" Text='<%# Bind("Email") %>'>
	</asp:TextBox>
    </EditItemTemplate>
    <FooterTemplate>
      <asp:TextBox ID="ftrEmail" runat="server" Columns="10"></asp:TextBox>
    </FooterTemplate>
    <ItemTemplate>
      <asp:Label ID="Label5" runat="server" Text='<%# Bind("Email") %>'></asp:Label>
    </ItemTemplate>
  </asp:TemplateField>
  <asp:TemplateField HeaderText="Password" SortExpression="Password">
    <EditItemTemplate>
      <asp:TextBox ID="TextBox6" runat="server" Text='<%# Bind("Password") %>'>
</asp:TextBox>
    </EditItemTemplate>
    <FooterTemplate>
      <asp:TextBox ID="ftrPassword" runat="server" Columns="10" TextMode="Password">
</asp:TextBox>
    </FooterTemplate>
    <ItemTemplate>
      <asp:Label ID="Label6" runat="server" Text='<%# Bind("Password") %>'></asp:Label>
    </ItemTemplate>
  </asp:TemplateField>
  <asp:TemplateField HeaderText="First Name" SortExpression="First_Name">
    <EditItemTemplate>
      <asp:TextBox ID="TextBox7" runat="server" Text='<%# Bind("First_Name") %>'>
</asp:TextBox>
    </EditItemTemplate>
    <FooterTemplate>
      <asp:TextBox ID="ftrFName" runat="server" Columns="10"></asp:TextBox>
    </FooterTemplate>
    <ItemTemplate>
      <asp:Label ID="Label7" runat="server" Text='<%# Bind("First_Name") %>'>
	</asp:Label>
    </ItemTemplate>
  </asp:TemplateField>
  <asp:TemplateField HeaderText="MI" SortExpression="MI">
    <EditItemTemplate>
      <asp:TextBox ID="TextBox8" runat="server" Text='<%# Bind("MI") %>'></asp:TextBox>
    </EditItemTemplate>
    <FooterTemplate>
      <asp:TextBox ID="ftrMI" runat="server" Columns="2"></asp:TextBox>
    </FooterTemplate>
    <ItemTemplate>
      <asp:Label ID="Label8" runat="server" Text='<%# Bind("MI") %>'></asp:Label>
    </ItemTemplate>
  </asp:TemplateField>
  <asp:TemplateField HeaderText="Last Name" SortExpression="Last_Name">
    <EditItemTemplate>
      <asp:TextBox ID="TextBox9" runat="server" Text='<%# Bind("Last_Name") %>'>
</asp:TextBox>
    </EditItemTemplate>
    <FooterTemplate>
      <asp:TextBox ID="ftrLName" runat="server" Columns="10"></asp:TextBox>
    </FooterTemplate>
    <ItemTemplate>
      <asp:Label ID="Label9" runat="server" Text='<%# Bind("Last_Name") %>'>
	</asp:Label>
    </ItemTemplate>
  </asp:TemplateField>
  <asp:TemplateField HeaderText="Phone/Extention" SortExpression="Phone_Extention">
    <EditItemTemplate>
      <asp:TextBox ID="TextBox10" runat="server" Text='<%# Bind("Phone_Extention") %>'>
</asp:TextBox>
    </EditItemTemplate>
    <FooterTemplate>
      <asp:TextBox ID="ftrPhone" runat="server" Columns="10"></asp:TextBox>
    </FooterTemplate>
    <ItemTemplate>
      <asp:Label ID="Label10" runat="server" Text='<%# Bind("Phone_Extention") %>'>
</asp:Label>
    </ItemTemplate>
  </asp:TemplateField>
  <asp:TemplateField HeaderText="Notes" InsertVisible="False" SortExpression="Notes">
    <EditItemTemplate>
      <asp:TextBox ID="TextBox11" runat="server" Text='<%# Bind("Notes") %>'>
</asp:TextBox>
    </EditItemTemplate>
    <ItemTemplate>
      <asp:Label ID="Label11" runat="server" Text='<%# Bind("Notes") %>'></asp:Label>
    </ItemTemplate>
  </asp:TemplateField>
  <asp:TemplateField HeaderText="Admin Notes" InsertVisible="False" 
SortExpression="Admin_Notes">
    <EditItemTemplate>
      <asp:TextBox ID="TextBox12" runat="server" Text='<%# Bind("Admin_Notes") %>'>
</asp:TextBox>
    </EditItemTemplate>
    <ItemTemplate>
      <asp:Label ID="Label12" runat="server" Text='<%# Bind("Admin_Notes") %>'>
</asp:Label>
    </ItemTemplate>
  </asp:TemplateField>
  </Columns>
  <EditRowStyle BackColor="#999999" />
  <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
  <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" Wrap="False" />
  <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
  <RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
  <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
</asp:GridView>

Next we have the sqldatasource. Notice that it has an insert command and that it is not using optimistic concurrency.

<asp:SqlDataSource ID="UsersDS" runat="server"
ConnectionString="<%$ ConnectionStrings:EstimatorConnectionString %>" 
DeleteCommand="DELETE FROM [Users] WHERE [id] = @original_id"
InsertCommand="INSERT INTO [Users] ([Company], [Access_Level], [Employee_ID], 
[Department], [Email], [Password], [First_Name], [MI], [Last_Name], [Phone_Extention], 
[Notes], [Admin_Notes]) VALUES (@Company, @Access_Level, @Employee_ID, @Department, 
@Email, @Password, @First_Name, @MI, @Last_Name, 
@Phone_Extention, @Notes, @Admin_Notes)"
OldValuesParameterFormatString="original_{0}" 
SelectCommand="SELECT [id], [Company], [Access_Level], [Employee_ID], [Department], 
[Email], [Password], [First_Name], [MI], [Last_Name], [Phone_Extention], [Notes], 
[Admin_Notes] FROM [Users]"
UpdateCommand="UPDATE [Users] SET [Company] = @Company, [Access_Level] = @Access_Level, 
[Employee_ID] = @Employee_ID, [Department] = @Department, [Email] = @Email, 
[Password] = @Password, [First_Name] = @First_Name, 
[MI] = @MI, [Last_Name] = @Last_Name, 
[Phone_Extention] = @Phone_Extention, [Notes] = @Notes, [Admin_Notes] = @Admin_Notes 
WHERE [id] = @original_id">
  <DeleteParameters>
    <asp:Parameter Name="original_id" Type="Int32"></asp:Parameter>
  </DeleteParameters>
  <InsertParameters>
    <asp:Parameter Name="Company" Type="Int32"></asp:Parameter>
    <asp:Parameter Name="Access_Level" Type="Int32"></asp:Parameter>
    <asp:Parameter Name="Employee_ID" Type="String"></asp:Parameter>
    <asp:Parameter Name="Department" Type="String"></asp:Parameter>
    <asp:Parameter Name="Email" Type="String"></asp:Parameter>
    <asp:Parameter Name="Password" Type="String"></asp:Parameter>
    <asp:Parameter Name="First_Name" Type="String"></asp:Parameter>
    <asp:Parameter Name="MI" Type="String"></asp:Parameter>
    <asp:Parameter Name="Last_Name" Type="String"></asp:Parameter>
    <asp:Parameter Name="Phone_Extention" Type="String"></asp:Parameter>
    <asp:Parameter Name="Notes" Type="String"></asp:Parameter>
    <asp:Parameter Name="Admin_Notes" Type="String"></asp:Parameter>
  </InsertParameters>
  <UpdateParameters>
    <asp:Parameter Name="Company" Type="Int32"></asp:Parameter>
    <asp:Parameter Name="Access_Level" Type="Int32"></asp:Parameter>
    <asp:Parameter Name="Employee_ID" Type="String"></asp:Parameter>
    <asp:Parameter Name="Department" Type="String"></asp:Parameter>
    <asp:Parameter Name="Email" Type="String"></asp:Parameter>
    <asp:Parameter Name="Password" Type="String"></asp:Parameter>
    <asp:Parameter Name="First_Name" Type="String"></asp:Parameter>
    <asp:Parameter Name="MI" Type="String"></asp:Parameter>
    <asp:Parameter Name="Last_Name" Type="String"></asp:Parameter>
    <asp:Parameter Name="Phone_Extention" Type="String"></asp:Parameter>
    <asp:Parameter Name="Notes" Type="String"></asp:Parameter>
    <asp:Parameter Name="Admin_Notes" Type="String"></asp:Parameter>
    <asp:Parameter Name="original_id" Type="Int32"></asp:Parameter>
  </UpdateParameters>
</asp:SqlDataSource>

Now in the code behind, we have two functions. The first one is the standard OnRowCommand sub we check for the command name which in this case is "ftrInsert" which is the commandname we assigned to the insert linkbutton in the footer row of our gridview, and then we call our little insert sub.

Protected Sub GridView1_RowCommand(ByVal sender As System.Object, 
ByVal e As System.Web.UI.WebControls.GridViewCommandEventArgs)
  If e.CommandName = "ftrInsert" Then
  'call the insert sub (sender is the gridview that called the rowCommand, 
  'UsersDS is the name of our sqlDataSource on the page
    gvQuickInsert(sender, UsersDS)
  End If
End Sub

Ready? Here is our insert routine. This little sub finds all of the column names from the SortExpression of each field and then finds the control in the footer row that corresponds to each row. Then it iterates and adds each column as an sqlDataSource insert parameter and appends the value of the control that corresponds to it. Last it inserts to the datasource and databinds the gridview. I only defined textbox, dropdownlist, and checkbox because that is all I am using but you can define your own if you want.

Protected Sub gvQuickInsert(ByRef gv As GridView, ByRef ds As SqlDataSource)
Dim paramsArr As ArrayList = New ArrayList
Dim controlsArr As ArrayList = New ArrayList
Dim col As Integer = 0
Dim footrow As GridViewRow = gv.FooterRow 

Try
Dim ix As Integer = 0
For ix = 0 To gv.Columns.Count
Dim d As DataControlField = gv.Columns(ix)
   If d.InsertVisible = True Then
      If footrow.Cells(ix).Controls(1) IsNot Nothing Then
         paramsArr.Add(d.SortExpression.ToString)
         controlsArr.Add(footrow.Cells(ix).Controls(1))
      End If
   End If
Next
Catch ex As Exception
End Try

ds.InsertParameters.Clear()

Dim ii As Integer = 0
For Each contr As Object In controlsArr
   If contr.GetType.Name.ToLower.Contains("textbox") Then
      ds.InsertParameters.Add(paramsArr(ii), TryCast(contr, TextBox).Text)
   ElseIf contr.GetType.Name.ToLower.Contains("dropdownlist") Then
      ds.InsertParameters.Add(paramsArr(ii), _
	TryCast(contr, DropDownList).SelectedValue)
   ElseIf contr.GetType.Name.ToLower.Contains("checkbox") Then
      If TryCast(contr, CheckBox).Checked Then
        ds.InsertParameters.Add(paramsArr(ii), 1)
      Else
        ds.InsertParameters.Add(paramsArr(ii), 0)
      End If
   End If
ii = ii + 1
Next
ds.Insert()
gv.DataBind()
End Sub

Points of Interest

This could save you hours, but it's not perfect. You must allow all the fields to be sorted and they must be sorted by their default field names. Other than that, there is probably no end to how you can use this.

History

30^th August, 2009: Initial post

Quickly Insert Rows Using Your Gridview With No Control Naming