I have a problem that I'm guessing has at least something to do with the ASP.NET control life-cycle. I have a GridView control to allow editing of certain records in an SQLite db. I have gotten what I thought to be pretty much the same thing to work on a different page on my site that accesses a different SQLite db. I setup an SqlDataSource control for the GridView to make use of. The SelectCommand of the SqlDataSource selects all columns of 2 tables that are joined in a typical manner (where a column in the first table points to the primary key in the second table). I have the primary key from the first table specified to the GridView with the DataKeyNames property.
So when I actually attempt to make an update through the GridView, I get an "Insufficient parameters supplied to the command" error. I tried changing the SelectCommand to "WHERE C_ID = 1", and that makes things work (although, obviously, for just one record). I'm pretty sure I'm not supposed to list the primary key (for the first table) in the list of UpdateParameters, since the GridView control should add the primary key (specified in DataKeyNames) to its list of DataKeys (which I've seen that it does when debugging).
I started up the debugger, set a breakpoint in my Page_Load, and attempted my update again. I looked at some things when the program execution paused, and then I let execution continue. And the update worked, without complaining about the insufficient parameters. But if I run the update without breaking, it fails.
Here's my page (with some hopefully irrelevant parts left out, but I can post more if needed):
<%@ Page Language="C#" CodeFile="monthlyupdate.aspx.cs" Inherits="my_monthlyupdate" Title="Blah blah" %>
<asp:Content ContentPlaceHolderID="MainContent" runat="server">
<h1>Blah blah</h1>
<asp:Panel ID="Admin_Authenticated" Visible="False" runat="server">
<asp:Label ID="AdminInfo" ForeColor="#0000ff" runat="server" />
<h2>Reports</h2>
<p style="font-size:1.2em">Month filter – Begin: <asp:DropDownList ID="FilterBeginMonth" runat="server" />
<asp:DropDownList ID="FilterBeginYear" runat="server" />
<span style="margin-left:2em">End:</span> <asp:DropDownList ID="FilterEndMonth" runat="server" />
<asp:DropDownList ID="FilterEndYear" runat="server" />
<asp:Button Text="Update" runat="server" /></p>
<div style="overflow:auto">
<asp:GridView ID="UpdatesGridView" AllowSorting="True" AutoGenerateColumns="False" DataKeyNames="ID"
DataSourceID="UpdatesSqlDataSource" OnSorting="UpdatesGridView_Sorting" OnRowCancelingEdit="UpdatesGridView_RowCancelingEdit"
OnRowEditing="UpdatesGridView_RowEditing" OnRowUpdated="UpdatesGridView_RowUpdated" runat="server">
<Columns>
<asp:CommandField ButtonType="Button" ShowEditButton="True">
</asp:CommandField>
<asp:TemplateField HeaderText="Campus" SortExpression="Name1">
<EditItemTemplate>
<asp:DropDownList ID="EditCampus" DataSourceID="EditCampusDataSource" DataTextField="Name"
DataValueField="ID" SelectedValue='<%# Bind("ID1") %>' runat="server" />
</EditItemTemplate>
<ItemTemplate>
<asp:Label Text='<%# Server.HtmlEncode(Eval("Name1").ToString()) %>' runat="server" />
</ItemTemplate>
</asp:TemplateField>
<asp:BoundField DataField="Name" HeaderText="Name" SortExpression="Name">
</asp:BoundField>
<asp:TemplateField HeaderText="Date" SortExpression="Date">
<EditItemTemplate>
<asp:DropDownList ID="DateMonthDDL" OnLoad="DateMonthDDL_Load" SelectedValue='<%# Bind("Month") %>' runat="server" />
<asp:DropDownList ID="DateYearDDL" OnLoad="DateYearDDL_Load" SelectedValue='<%# Bind("Year") %>' runat="server" />
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="DateMonth" Text='<%# Eval("Month") %>' runat="server" />/<asp:Label ID="DateYear"
Text='<%# Eval("Year") %>' runat="server" />
</ItemTemplate>
</asp:TemplateField>
<asp:BoundField DataField="Highlight" HeaderText="Highlight" SortExpression="Highlight" Visible="False">
</asp:BoundField>
<asp:BoundField DataField="Event1" HeaderText="Event 1" SortExpression="Event1" Visible="False">
</asp:BoundField>
<asp:BoundField DataField="Average1" HeaderText="Average 1" SortExpression="Average1" Visible="False">
</asp:BoundField>
<asp:BoundField DataField="Event2" HeaderText="Event 2" SortExpression="Event2" Visible="False">
</asp:BoundField>
<asp:BoundField DataField="Average2" HeaderText="Average 2" SortExpression="Average2" Visible="False">
</asp:BoundField>
<asp:BoundField DataField="Highlight2" HeaderText="Highlight2" SortExpression="Highlight2" Visible="False">
</asp:BoundField>
<asp:BoundField DataField="Update" HeaderText="Update" SortExpression="Update" Visible="False">
</asp:BoundField>
<asp:BoundField DataField="NoTeams" HeaderText="# Teams" SortExpression="NoTeams" Visible="False">
</asp:BoundField>
<asp:BoundField DataField="NoContacted" HeaderText="# Contacted" SortExpression="NoContacted" Visible="False">
</asp:BoundField>
<asp:BoundField DataField="NoContacted2" HeaderText="# Contacted2" SortExpression="NoContacted2" Visible="False">
</asp:BoundField>
<asp:BoundField DataField="NoAttend" HeaderText="# Attended" SortExpression="NoAttend" Visible="False">
</asp:BoundField>
<asp:BoundField DataField="NoArticles" HeaderText="# Articles" SortExpression="NoArticles" Visible="False">
</asp:BoundField>
<asp:BoundField DataField="News" HeaderText="News" SortExpression="News" Visible="False">
</asp:BoundField>
<asp:BoundField DataField="VacHours" HeaderText="Vacation Hours" SortExpression="VacHours" Visible="False">
</asp:BoundField>
<asp:BoundField DataField="SickHours" HeaderText="Sick Hours" SortExpression="SickHours" Visible="False">
</asp:BoundField>
<asp:BoundField DataField="TimeEntered" HeaderText="Time Entered" SortExpression="TimeEntered">
</asp:BoundField>
</Columns>
</asp:GridView>
</div>
<asp:SqlDataSource ID="UpdatesSqlDataSource" CacheDuration="60" ConnectionString="<%$ ConnectionStrings:Blah %>"
EnableCaching="True"
FilterExpression="(Year > '{0}' OR (Year = '{0}' AND Month - 1 >= '{1}')) AND (Year < '{2}' OR (Year = '{2}' AND Month - 1 <= '{3}'))"
ProviderName="System.Data.SQLite" SelectCommand="SELECT * FROM CampusUpdates JOIN Campuses ON C_ID = Campuses.ID"
UpdateCommand="UPDATE CampusUpdates SET C_ID = @ID1, Name = @Name, Month = @Month, Year = @Year, Highlight = @Highlight, Event1 = @Event1, Average1 = @Average1, Event2 = @Event2, Average2 = @Average2, Highlight2 = @Highlight2, Update = @Update, NoTeams = @NoTeams, NoContacted = @NoContacted, NoContacted2 = @NoContacted2, NoAttend = @NoAttend, NoArticles = @NoArticles, News = @News, VacHours = @VacHours, SickHours = @SickHours, TimeEntered = @TimeEntered WHERE ID = @ID; VACUUM;"
runat="server">
<FilterParameters>
<asp:ControlParameter Name="Year" ControlID="FilterBeginYear" PropertyName="SelectedValue" />
<asp:ControlParameter Name="Month" ControlID="FilterBeginMonth" PropertyName="SelectedIndex" />
<asp:ControlParameter Name="Year" ControlID="FilterEndYear" PropertyName="SelectedValue" />
<asp:ControlParameter Name="Month" ControlID="FilterEndMonth" PropertyName="SelectedIndex" />
</FilterParameters>
<UpdateParameters>
<asp:Parameter Name="ID1" Type="Int16" />
<asp:Parameter Name="Name" Type="String" />
<asp:Parameter Name="Month" Type="Int16" />
<asp:Parameter Name="Year" Type="Int16" />
<asp:Parameter Name="Highlight" Type="String" />
<asp:Parameter Name="Event1" Type="String" />
<asp:Parameter Name="Average1" Type="Int16" />
<asp:Parameter Name="Event2" Type="String" />
<asp:Parameter Name="Average2" Type="Int16" />
<asp:Parameter Name="Highlight2" Type="String" />
<asp:Parameter Name="Update" Type="String" />
<asp:Parameter Name="NoTeams" Type="Int16" />
<asp:Parameter Name="NoContacted" Type="Int16" />
<asp:Parameter Name="NoContacted2" Type="Int16" />
<asp:Parameter Name="NoAttend" Type="Int16" />
<asp:Parameter Name="NoArticles" Type="Int16" />
<asp:Parameter Name="News" Type="String" />
<asp:Parameter Name="VacHours" Type="Int16" />
<asp:Parameter Name="SickHours" Type="Int16" />
<asp:Parameter Name="TimeEntered" Type="DateTime" />
</UpdateParameters>
</asp:SqlDataSource>
<asp:SqlDataSource ID="EditCampusDataSource" ConnectionString="<%$ ConnectionStrings:CLGM %>" ProviderName="System.Data.SQLite"
SelectCommand="SELECT * FROM Campuses" runat="server" />
</asp:Panel>
</asp:Content>
And here's the code-behind file (again, with some hopefully irrelevant parts left out, but I can post more if
needed):
using System;
using System.Data;
using System.Data.SQLite; // http://sqlite.phxsoftware.com/
using System.IO;
using System.Web.Security;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.Configuration;
public partial class my_monthlyupdate : System.Web.UI.Page
{
string connString = WebConfigurationManager.ConnectionStrings["Blah"].ConnectionString;
protected enum Months { Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec };
protected string[ headersArray = { "Highlight", "Event 1", "Average 1", "Event 2", "Average 2", "Highlight2", "Update", "# Teams", "# Contacted",
"# Contacted2", "# Attended", "# Articles", "News", "Vacation Hours", "Sick Hours" };
protected void Page_Load()
{
// If admin mode
if (blah)
{
// If logged in
if (Context.User.Identity.IsAuthenticated)
{
Admin_Authenticated.Visible = true;
if (!IsPostBack)
{
for (Months i = Months.Jan; i <= Months.Dec; i++)
{
FilterBeginMonth.Items.Add(new ListItem(i.ToString(), ((int)i + 1).ToString()));
FilterEndMonth.Items.Add(new ListItem(i.ToString(), ((int)i + 1).ToString()));
}
for (int i = DateTime.Now.Year; i >= DateTime.Now.Year - 10; i--)
{
FilterBeginYear.Items.Add(i.ToString());
FilterEndYear.Items.Add(i.ToString());
}
PopulateCampusList(ReportsCheckBoxList);
}
// SortDirection only applies to second column here, but can workaround by adding "DESC" after first column
UpdatesGridView.Sort("Year DESC, Month", SortDirection.Descending);
}
}
}
protected void Avg1_Validate(object source, ServerValidateEventArgs e)
{
// Only worry if something is specified in Event1
if (Event1.Text != "")
{
e.IsValid = false;
int outVal;
if (Int32.TryParse(e.Value, out outVal))
e.IsValid = Int32.Parse(e.Value) > 0;
}
}
protected void Avg2_Validate(object source, ServerValidateEventArgs e)
{
// Only worry if something is specified in Event2
if (Event2.Text != "")
{
e.IsValid = false;
int outVal;
if (Int32.TryParse(e.Value, out outVal))
e.IsValid = Int32.Parse(e.Value) > 0;
}
}
private void Ddl_DataBound(Object src, EventArgs e)
{
if (src.GetType() == typeof(CheckBoxList))
foreach (ListItem li in ((ListControl)src).Items)
li.Text = Server.HtmlEncode(li.Text);
}
protected void MonthYear_Validate(object source, ServerValidateEventArgs e)
{
int outVal;
if (Int32.TryParse(Year.SelectedValue, out outVal) && Int32.TryParse(Month.SelectedValue, out outVal))
{
if (Int32.Parse(Year.SelectedValue) == DateTime.Now.Year && Int32.Parse(Month.SelectedValue) > DateTime.Now.Month)
e.IsValid = false;
}
}
private void PopulateCampusList(ListControl ddl)
{
using (SQLiteConnection conn = new SQLiteConnection(connString))
{
SQLiteCommand command = new SQLiteCommand("SELECT * FROM Campuses", conn);
conn.Open();
SQLiteDataReader dr = command.ExecuteReader();
ddl.DataSource = dr;
ddl.DataTextField = "Name";
ddl.DataValueField = "ID";
ddl.DataBound += new EventHandler(Ddl_DataBound);
ddl.DataBind();
}
}
protected void DateMonthDDL_Load(Object src, EventArgs e)
{
for (Months i = Months.Jan; i <= Months.Dec; i++)
((DropDownList)src).Items.Add(new ListItem(i.ToString(), ((int)i + 1).ToString()));
}
protected void DateYearDDL_Load(Object src, EventArgs e)
{
for (int i = DateTime.Now.Year - 1; i <= DateTime.Now.Year; i++)
((DropDownList)src).Items.Add(i.ToString());
}
protected void UpdatesGridView_RowCancelingEdit(Object src, GridViewCancelEditEventArgs e)
{
ToggleColumns((GridView)src, headersArray);
}
protected void UpdatesGridView_RowEditing(Object src, GridViewEditEventArgs e)
{
ToggleColumns((GridView)src, headersArray);
}
protected void UpdatesGridView_RowUpdated(Object src, GridViewUpdatedEventArgs e)
{
ToggleColumns((GridView)src, headersArray);
}
protected void UpdatesGridView_Sorting(Object src, GridViewSortEventArgs e)
{
// Custom sort based on multiple columns
if (e.SortExpression == "Date")
e.SortExpression = "Year,Month";
}
private static void ToggleColumns(GridView g, string[ headers)
{
foreach (DataControlField col in g.Columns)
if (Array.IndexOf(headers, col.HeaderText) > -1)
col.Visible = (col.Visible ? false : true);
}
}