Filtering DataGrid using DropDown

I am trying to filter my databound datagridview by CompanyID using an updatepanel. For some reason it's not working. Here's my code. Thanks for your help!

C#

string company_id;

protected void Page_Load(object sender, EventArgs e)
{

        SqlCommand cmd = new SqlCommand("SELECT CompanyName, CompanyID FROM Company ORDER BY CompanyName", conn);
        SqlCommand cmd2 = new SqlCommand("SELECT p.ProjectName AS ProjectName, p.ProjectID, p.CompanyID, p.Status AS Status FROM Project p, Company c WHERE p.CompanyID = c.CompanyID AND c.CompanyID = '" + company_id + "' ORDER BY ProjectName", conn);

        cmd.CommandType = CommandType.Text;

        SqlDataAdapter da = new SqlDataAdapter(cmd);

        DataSet ds = new DataSet();

        da.Fill(ds);

        if (!Page.IsPostBack)
        {
            company_list.DataSource = ds;
            company_list.DataTextField = "CompanyName";
            company_list.DataValueField = "CompanyID";
            company_list.DataBind();

            company_list.Items.Insert(0, new System.Web.UI.WebControls.ListItem("-- Please Select Company --"));


        //cmd2.Connection.Open();

            cmd2.CommandType = CommandType.Text;

            SqlDataAdapter sqlAdapter = new SqlDataAdapter(cmd2);
            DataSet ds2 = new开发者_运维百科 DataSet();
            sqlAdapter.Fill(ds2);

            Gridview1.DataSource = ds2;
            Gridview1.DataBind();

            conn.Close();
        }

        //cmd2.Connection.Close();
        //cmd2.Connection.Dispose();
    }

}
protected void company_list_SelectedIndexChanged(object sender, EventArgs e)
{

    company_id = company_list.SelectedValue;

}

ASP.NET:

<asp:DropDownList ID="company_list" runat="server" 
                             onselectedindexchanged="company_list_SelectedIndexChanged" width="175" AutoPostBack="true" />

...

<asp:UpdatePanel ID="UpdateGrid" runat="server">
                <ContentTemplate>
                           <asp:gridview ID="Gridview1" runat="server" ShowFooter="True"
                       AutoGenerateColumns="False" GridLines="None">
....

                </ContentTemplate>
                <Triggers>
                    <asp:AsyncPostBackTrigger ControlID="company_list" />
                </Triggers>
 </asp:UpdatePanel>

You should bind your grid in page_load only if !IsPostback and rebind it after the user selected a company from DropDownlist in the SelectedIndexChanged-Eventhandler and not from Page_Load. All event-handlers are called after page_load and therefore your database query is too early.

Page Lifecycle

Your code behind needs to follow this pattern (used local datasources instead of db calls):

public partial class _default : System.Web.UI.Page
{
    ICollection<Company> companies = new List<Company>()
        {
            new Company(1, "company1"),
            new Company(2, "company2"),
            new Company(3, "company3")
        };

    ICollection<Project> projects = new List<Project>()
        {
            new Project(1, "project1a", 1),
            new Project(2, "project2a", 2),
            new Project(3, "project3a", 3),
            new Project(4, "project1b", 1),
            new Project(5, "project2b", 2),
            new Project(6, "project3b", 3),
        };

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!Page.IsPostBack)
        {
            company_list.DataSource = companies;
            company_list.DataTextField = "Name";
            company_list.DataValueField = "Id";
            company_list.DataBind();
            company_list.Items.Insert(0, new System.Web.UI.WebControls.ListItem("-- Please Select Company --"));
        }
    }

    protected void company_list_SelectedIndexChanged(object sender, EventArgs e)
    {
        DropDownList ddl = sender as DropDownList;
        Gridview1.DataSource = projects.Where(x => x.CompanyId == Int32.Parse(ddl.SelectedValue));
        Gridview1.DataBind();
    }
}

Something to note:

• Your current code is vulnerable to SQL injection attacks - you should parameterise your SQL queries rather than concatenating strings.