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 gridview
s 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
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 databind
s 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
- 30th August, 2009: Initial post