CODEWITHSUNDEEP
asp.netsql-server
Tim Vavra
Tim Vavra

Reputation: 537

Populate gridview based on two dropdown lists

I have an asp.net web page with two drop down lists and a gridview.

The two drop down lists are based on queries to a SQL database (2008R2). The drop downs are populating just fine.

What I need to do is create a grid view based on the selections in the drop downs.

The first drop down allows the user to select a job name - while this is helpful to the user, what I need is the job # that is associated with that job name. The second drop down is a stage (three selections) very straight forward.

Right now I have the grid view based on the master table in SQL, but I need to display only the results that are pertinent. I could do this based on a filter, but the user usually does not know the job number.

Here is the datasource for the gridview:

SELECT a.[Annotation Number], a.Page_ID, a.[Annotation By], a.[Annotation Type], 
a.[Business Unit], a.[Actual Agency Error], a.[Error Type], a.[Annotation Comments], 
a.[Team Comments], a.sgkComments 
FROM MasterAnnotation AS a 
INNER JOIN ActiveWorkPages AS b 
ON a.Page_ID = b.WorkPage INNER JOIN ActiveJob AS c ON c.Job = b.Job 
ORDER BY a. [Annotation Number]

What I thought I could do is add two variable into the datasource to limit it by where b.Job = @Job and Stage = @Stage

I understand that I need to autopostback in order to update the gridview.
I also will need to update the gridview with certain columns based on client input (next phase of the project)

Thanks in advance for any help.

Upvotes: 1

Views: 2663

Answers (2)

sealz
sealz

Reputation: 5408

You can call something like this on dropdownlist change, or via a button click. (Not sure how you have that configured) 

//import and add the System.Data.SqlClient Namespace
using System.Data.SqlClient;



 try //Try block for opening, querying and displaying pulled data
            {   
            sqlConn.Open();
            cmdstring = "Select col1, col2, col3 from tablename where field 1 = '" + ddllist1.selecteditem.value+ "' and field2 = '" + ddllist2.selecteditem.value + "'";
            sqlcmd01 = new SqlCommand(cmdstring, sqlConn);
            reader01 = sqlcmd01.ExecuteReader();
            //set the datasource and bind it to the gridview
            gridview01.DataSource = reader01;
            gridview01.DataBind();
            sqlConn.Close();
        }
        catch (Exception ex)
        {
           //handle Errors
        } //End Try Catch for SQL Operations

This should selected the values form the table and assingn the column names automatically unless your gridview is configured differently already.

String appending for the SQL Query is probally not ideal but that is just how I have my sample block set up :)

EDIT You will need to make your declarations as follows

//Declare Globally or where you need
        SqlConnection sqlConn = new SqlConnection("ConnString"); //Connection String
        SqlDataReader reader01 = null; //DataReader
                SqlCommand sqlcmd01 = null; 
                String cmdstring = "";

EDIT 2 My gridview is declared as follows(mostly default values). The example I am using uses a .ascx file for formatting the page. Your case is probally something similar.

<asp:GridView ID="gridview01" runat="server" CellPadding="4" 
    EnableModelValidation="True" ForeColor="#333333" GridLines="None" 
    Height="179px" Width="496px" style="margin-right: 0px">
    <AlternatingRowStyle BackColor="White" />
    <EditRowStyle BackColor="#2461BF" />
    <FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
    <HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
    <PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
    <RowStyle BackColor="#EFF3FB" HorizontalAlign="Center" />
    <SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />
</asp:GridView>

Upvotes: 1

Chaz Gardyner
Chaz Gardyner

Reputation: 166

Here are couple details that will get you in the right direction.

Use job ID as option value when populating drop down for jobs. This will show job name but it will hold job id in the background

Here is how to do this from code but you can also do this from the designer if you’re using SQLDataSource or something like that.

string sql = "select job_name, job_id from dbo.jobs";

using (SqlConnection conn = new SqlConnection("your conneciton string"))
{
     using (SqlCommand cmd = new SqlCommand(sql, conn))
     {
       SqlDataAdapter adapter = new SqlDataAdapter(cmd);
       DataSet data = new DataSet();
       adapter.Fill(data);

       ddlJobs.DataSource = data;
       ddlJobs.DataTextField = "job_name";
       ddlJobs.DataValueField = "job_id";
       ddlJobs.DataBind();
    }
}

Once you have this you can pass the parameter to the query used to fill the gridview and then just refresh. Idea is to use SelectedValue property in your grid view.

string sql = "select * from table where job_id = @job_id";

using (SqlConnection conn = new SqlConnection("your conneciton string"))
{
      using (SqlCommand cmd = new SqlCommand(sql, conn))
     {
           cmd.Parameters.Add(new SqlParameter("@job_id", ddlJobs.SelectedValue));
           SqlDataAdapter adapter = new SqlDataAdapter(cmd);
           DataSet data = new DataSet();
           adapter.Fill(data);

           gridView.DataSource = data;
           gridView.DataBind();
     }
}

Also make sure to set AutoPostback property in drop down lists to True and an event handler when selected index changes 

<asp:DropDownList ID="ddlJobs" runat="server" AutoPostBack="true" OnSelectedIndexChanged="ddlJobs_OnSelectedIndexChanged">
        </asp:DropDownList>

Upvotes: 2

Related Questions