I have two dropdown lists that I am using to filter a gridview. I gridview is populating based on a query
select [AnnotationNumber],[AnnotationBy],[AnnotationType],[BusinessUnit] as Unit,[ErrorType],[ActualAgencyError],AnnotationComments,[sgkComments],[ActualAgencyError],Cust from vw_GridviewSource where Name = '"+@Name+"' and AnnotationDate = '"+@Stage+"'";
SqlCommand cmd = new SqlCommand(query);
gvSummary.DataSource = GetData(cmd);
The @Name and @Stage variables are the selected values of the dropdownlist.
When I start I get the two drop down lists on my webform. When I select the values from the
dropdownlists, nothing happens. I get no gridview.
I added onselecedindexchange event both dropdownlists and even after both are selected nothing. I debugged the
code and found that there is a point where the value is correct, but I still don't see the gridview. I have also
checked the query associated with the databind and it works perfectly when I check it in SQL. I am enclosing all the code from the aspx.cs and aspx.
Any assistance would be greatly appreciated.
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Trakker.aspx.cs" Inherits="AnnoTracker.WebForm1" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:DropDownList ID="dlJobName" runat="server" AutoPostBack="True"
DataSourceID="JobName" DataTextField="Name" DataValueField="Name" Height="65px"
style="z-index: 1; left: 20px; top: 55px; position: absolute; bottom: 252px"
onselectedindexchanged="dlJobName_SelectedIndexChanged" >
</asp:DropDownList>
<asp:SqlDataSource ID="JobName" runat="server"
ConnectionString="<%$ ConnectionStrings:SRM_MetricConnectionString %>"
SelectCommand="SELECT DISTINCT Name FROM vw_GridviewSource WHERE (DueDt > sysdatetime()) ORDER BY Name">
</asp:SqlDataSource>
<asp:GridView ID="gvSummary" runat="server" AutoGenerateColumns = "False" AllowSorting = "true"
OnRowCancelingEdit = "CancelEdit" OnRowEditing = "EditSummary" OnRowDataBound = "RowDataBound"
OnRowUpdating = "UpdateSummary" CellPadding="4" ForeColor="#333333"
GridLines="None" DataKeyNames="AnnotationNumber, Cust"
style="z-index: 1; left: 10px; top: 130px; position: absolute; height: 133px; width: 257px" >
<RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
<columns>
<asp:CommandField ShowEditButton="true" />
<asp:TemplateField HeaderText = "Annotation Number">
<ItemTemplate>
<asp:Label ID = "lblAnno" runat="server" Text='<%# Eval("AnnotationNumber") %>'> </asp:Label>
</ItemTemplate>
<HeaderStyle Width="300px" />
<ItemStyle Width="300px" HorizontalAlign="Center" />
</asp:TemplateField>
<asp:TemplateField HeaderText = "Annotation By" HeaderStyle-Width = "250">
<ItemTemplate>
<asp:Label ID = "lblAnnoBy" runat="server" Text='<%# Eval("AnnotationBy") %>' Width = "150"> </asp:Label>
</ItemTemplate>
<HeaderStyle Width="450" />
<ItemStyle Width="450" HorizontalAlign="Center" />
</asp:TemplateField>
<asp:TemplateField HeaderText = "Annotation Type">
<ItemTemplate>
<asp:Label ID = "lblAnnoType" runat="server" Text='<%# Eval("AnnotationType") %>' Width = "150"> </asp:Label>
</ItemTemplate>
<HeaderStyle Width="450px" />
<ItemStyle Width="450px" HorizontalAlign="Center" />
</asp:TemplateField>
<asp:TemplateField HeaderText = "Business Unit" HeaderStyle-Width = "250">
<ItemTemplate>
<asp:Label ID = "lblBU" runat="server" Text='<%# Eval("Unit") %>' Width = "250" ></asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:Label ID="lblBU" runat="server" Text='<%# Eval("Unit") %>' Visible ="false"></asp:Label>
<asp:DropDownList ID = "dlBU" runat="server">
</asp:DropDownList>
</EditItemTemplate>
<HeaderStyle Width="300px" HorizontalAlign="Center" />
<ItemStyle Width="300px" HorizontalAlign="Center" />
</asp:TemplateField>
<asp:TemplateField HeaderText = "Error Type" HeaderStyle-Width = "250">
<ItemTemplate>
<asp:Label ID = "lblET" runat="server" Text='<%# Eval("ErrorType") %>' Width = "250" ></asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:Label ID="lblET" runat="server" Text='<%# Eval("ErrorType") %>' Visible ="false"></asp:Label>
<asp:DropDownList ID = "dlET" runat="server">
</asp:DropDownList>
</EditItemTemplate>
<HeaderStyle Width="300px" HorizontalAlign="Center" />
<ItemStyle Width="300px" HorizontalAlign="Center" />
</asp:TemplateField>
<asp:TemplateField HeaderText = "Actual Agency Error" HeaderStyle-Width = "250">
<ItemTemplate>
<asp:Label ID = "lblAA" runat="server" Text='<%# Eval("ActualAgencyError") %>' Width = "250" ></asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:Label ID="lblAA" runat="server" Text='<%# Eval("ActualAgencyError") %>' Visible ="false"></asp:Label>
<asp:DropDownList ID = "dlAA" runat="server">
</asp:DropDownList>
</EditItemTemplate>
<HeaderStyle Width="300px" HorizontalAlign="Center" />
<ItemStyle Width="300px" HorizontalAlign="Center" />
</asp:TemplateField>
<asp:TemplateField HeaderText = "AnnotationComments">
<ItemTemplate>
<asp:Label ID = "lblAnnoComm" runat ="server" Text = '<%# Eval("AnnotationComments") %>' Width = "400"></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText = "sgk Comments">
<ItemTemplate>
<asp:Label ID = "lblsgkComm" runat ="server" Text = '<%# Eval("sgkComments") %>' Width = "400"></asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:Textbox ID="tbsgkComm" runat="server" Text='<%# Eval("sgkComments") %>' TextMode = "MultiLine" Columns = "40" ></asp:Textbox>
</EditItemTemplate>
<HeaderStyle HorizontalAlign="Center" Width="400px" />
<ItemStyle HorizontalAlign="Justify" Width="400px" />
</asp:TemplateField>
<asp:TemplateField HeaderText = "Cust" >
<ItemTemplate>
<asp:Label ID = "lblCust" runat="server" Text='<%# Eval("Cust") %>'> </asp:Label>
</ItemTemplate>
<HeaderStyle Width="300px" />
<ItemStyle Width="300px" HorizontalAlign="Center" />
</asp:TemplateField>
</columns>
<FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
<PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
<SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
<HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
<EditRowStyle BackColor="#999999" />
<AlternatingRowStyle BackColor="White" ForeColor="#284775" />
</asp:GridView>
<asp:DropDownList ID="dlStage" runat="server" AutoPostBack="True"
DataSourceID="Stage" DataTextField="Annotation_Date"
DataValueField="Annotation_Date" Height="65px"
style="z-index: 1; left: 360px; top: 50px; position: absolute" Width="240px"
onselectedindexchanged="dlStage_SelectedIndexChanged">
</asp:DropDownList>
<asp:SqlDataSource ID="Stage" runat="server"
ConnectionString="<%$ ConnectionStrings:SRM_MetricConnectionString %>"
SelectCommand="SELECT DISTINCT [AnnotationDate] AS Annotation_Date FROM [vw_GridviewSource] WHERE ([Name] = @Name)" >
<SelectParameters>
<asp:ControlParameter ControlID="dlJobName" Name="Name"
PropertyName="SelectedValue" Type="String" />
</SelectParameters>
</asp:SqlDataSource>
</div>
</form>
</body>
</html>
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Web.Security;
using System.Configuration;
namespace AnnoTracker
{
public partial class WebForm1 : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
this.BindData();
}
}
protected void EditSummary(object sender, GridViewEditEventArgs e)
{
gvSummary.EditIndex = e.NewEditIndex;
string _custName = gvSummary.DataKeys[e.NewEditIndex].Value.ToString();
BindData();
}
protected void CancelEdit(object sender, GridViewCancelEditEventArgs e)
{
gvSummary.EditIndex = -1;
BindData();
}
protected void RowDataBound(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.DataRow && gvSummary.EditIndex == e.Row.RowIndex)
{
DropDownList dlBU = (DropDownList)e.Row.FindControl("dlBU");
string _custName = gvSummary.DataKeys[e.Row.RowIndex].Values[1].ToString();
string BUquery = "select distinct Unit from vw_BU where Business='" + _custName + "'";
SqlCommand BUcmd = new SqlCommand(BUquery);
dlBU.DataSource = GetData(BUcmd);
dlBU.DataTextField = "Unit";
dlBU.DataValueField = "Unit";
dlBU.DataBind();
dlBU.Items.FindByValue((e.Row.FindControl("lblBU") as Label).Text).Selected = true;
DropDownList dlET = (DropDownList)e.Row.FindControl("dlET");
string ETquery = "select distinct ErrorType from ErrorType";
SqlCommand ETcmd = new SqlCommand(ETquery);
dlET.DataSource = GetData(ETcmd);
dlET.DataTextField = "ErrorType";
dlET.DataValueField = "ErrorType";
dlET.DataBind();
dlET.Items.FindByValue((e.Row.FindControl("lblET") as Label).Text).Selected = true;
DropDownList dlAA = (DropDownList)e.Row.FindControl("dlAA");
string AAquery = "select distinct AAA from ActualAgencyError";
SqlCommand AAcmd = new SqlCommand(AAquery);
dlAA.DataSource = GetData(AAcmd);
dlAA.DataTextField = "AAA";
dlAA.DataValueField = "AAA";
dlAA.DataBind();
dlAA.Items.FindByValue((e.Row.FindControl("lblAA") as Label).Text).Selected = true;
}
}
protected void UpdateSummary(object sender, GridViewUpdateEventArgs e)
{
string BU = (gvSummary.Rows[e.RowIndex].FindControl("dlBU") as DropDownList).SelectedItem.Value;
string ET = (gvSummary.Rows[e.RowIndex].FindControl("dlET") as DropDownList).SelectedItem.Value;
string AA = (gvSummary.Rows[e.RowIndex].FindControl("dlAA") as DropDownList).SelectedItem.Value;
string AnnotationNumber = gvSummary.DataKeys[e.RowIndex].Value.ToString();
string sgkComments = (gvSummary.Rows[e.RowIndex].FindControl("tbsgkComm") as TextBox).Text;
string strConnString = ConfigurationManager.ConnectionStrings["SRM_MetricConnectionString"].ConnectionString;
using (SqlConnection con = new SqlConnection(strConnString))
{
string query = "update vw_GridviewSource set [BusinessUnit] = @BU, [ErrorType] = @ET, [sgkComments] = @sgk, [ActualAgencyError] = @AA where [AnnotationNumber] = @AnnoNum";
using (SqlCommand cmd = new SqlCommand(query))
{
cmd.Connection = con;
cmd.Parameters.AddWithValue("@BU", BU);
cmd.Parameters.AddWithValue("@AnnoNum", AnnotationNumber);
cmd.Parameters.AddWithValue("@ET", ET);
cmd.Parameters.AddWithValue("@AA", AA);
cmd.Parameters.AddWithValue("@sgk", sgkComments);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
Response.Redirect(Request.Url.AbsoluteUri);
}
}
}
private void BindData()
{
string @Name;
string @Stage;
@Name = dlJobName.SelectedValue;
@Stage = dlStage.SelectedValue;
string query = "select [AnnotationNumber],[AnnotationBy],[AnnotationType],[BusinessUnit] as Unit,[ErrorType],[ActualAgencyError],AnnotationComments,[sgkComments],[ActualAgencyError],Cust from vw_GridviewSource where Name = '"+@Name+"' and AnnotationDate = '"+@Stage+"'";
SqlCommand cmd = new SqlCommand(query);
gvSummary.DataSource = GetData(cmd);
gvSummary.DataBind();
}
private DataTable GetData(SqlCommand cmd)
{
string strConnString = ConfigurationManager.ConnectionStrings["SRM_MetricConnectionString"].ConnectionString;
using (SqlConnection con = new SqlConnection(strConnString))
{
using (SqlDataAdapter sda = new SqlDataAdapter())
{
cmd.Connection = con;
sda.SelectCommand = cmd;
using (DataTable dt = new DataTable())
{
sda.Fill(dt);
return dt;
}
}
}
}
protected void dlJobName_SelectedIndexChanged(object sender, EventArgs e)
{
string @Name;
@Name = dlJobName.SelectedValue;
BindData();
gvSummary.DataBind();
}
protected void dlStage_SelectedIndexChanged(object sender, EventArgs e)
{
string @Stage;
@Stage = dlStage.SelectedValue;
BindData();
gvSummary.DataBind();
}
}
}
