How to make DropDownList dynamic from filtered GridView

Question

enter image description here

Here is my Asp.net code:

My code-behind:

protected void Page_Load(object sender, EventArgs e)
{

    strMainQuery = @"SELECT
               CT.OBJECTID,
               'http://checkthis.com/d=' + CAST(CT.OBJECTID AS VARCHAR) + '&classid=1224' 'Task Detail'
               ,LTRIM(RTRIM(CT.ATTR2846)) 'Service'
               ,LTRIM(RTRIM(CT.ATTR2812)) 'Status'
               ,CONVERT(VARCHAR(14), CT.ATTR2752, 110) 'Due Date'
               ,LTRIM(RTRIM(CT.ATTR2739)) 'Task Name'
               ,LTRIM(RTRIM(UA.REALNAME)) 'Owner'
               ,LTRIM(RTRIM(C.ATTR2815)) 'Client'
               ,RTRIM(SI.ATTR2819) 'Site'
               ,RTRIM(PR.ATTR2817) 'Practice'
               ,RTRIM(P.ATTR2919) 'Provider'
        FROM HSI.RMOBJECTINSTANCE1224 CT 
               INNER JOIN HSI.RMOBJECTINSTANCE1232 S ON CT.ATTR2846 = S.ATTR2821 
               INNER JOIN HSI.USERACCOUNT UA ON S.FK2852 = (UA.USERNUM * -1)
               LEFT JOIN HSI.RMOBJECTINSTANCE1251 PXC ON PXC.FK2924 = CT.OBJECTID
               LEFT JOIN HSI.RMOBJECTINSTANCE1249 P ON PXC.FK2923 = P.OBJECTID
               LEFT JOIN HSI.RMOBJECTINSTANCE1248 PRXC ON PRXC.FK2912 = CT.OBJECTID
               LEFT JOIN HSI.RMOBJECTINSTANCE1230 PR ON PR.OBJECTID = PRXC.FK2911
               LEFT JOIN HSI.RMOBJECTINSTANCE1247 SXC ON SXC.FK2908 = CT.OBJECTID
               LEFT JOIN HSI.RMOBJECTINSTANCE1229 SI ON SI.OBJECTID = SXC.FK2907
                INNER JOIN HSI.RMOBJECTINSTANCE1231 C ON CT.FK2927 = C.OBJECTID";

    if (!Page.IsPostBack)
    {
        ViewState["sortOrder"] = "Asc";
        ViewState["sortExp"] = "Due Date";
        PullData("Due Date", "Asc"); //ASC: A (top) to Z (bottom) || # (low to high) || Date (oldest to newest)
    }
}
public void PullData(string sortExp, string sortDir)
{
    query = "";
    DataTable taskData = new DataTable();
    connString = @""; //connectionstring
    if (ddlTaskName.SelectedIndex > 0)
    {
        strClause += " AND CT.ATTR2739 = '" + ddlTaskName.SelectedItem.Text + "'";
    }
    else
    {
        strClause += " AND CT.ATTR2739 LIKE '%'";
    }
    if (ddlService.SelectedIndex > 0)
    {
        strClause += " AND CT.ATTR2846 = '" + ddlService.SelectedItem.Text + "'";
    }
    else
    {
        strClause += " AND CT.ATTR2846 LIKE '%'";
    }
    if (ddlStatus.SelectedIndex > 0)
    {
        strClause += " AND CT.ATTR2812 = '" + ddlStatus.SelectedItem.Text + "'";
    }
    else
    {
        strClause += " AND CT.ATTR2812 LIKE '%'";
    }
    if (ddlDueDate.SelectedIndex > 0)
    {
        strClause += " AND CONVERT(VARCHAR(14), CT.ATTR2752, 110) = '" + ddlDueDate.SelectedItem.Text + "'";
    }
    else
    {
        strClause += " AND CONVERT(VARCHAR(14), CT.ATTR2752, 110) LIKE '%'";
    }
    if (ddlOwner.SelectedIndex > 0)
    {
        strClause += " AND UA.REALNAME = '" + ddlOwner.SelectedItem.Text + "'";
    }
    else
    {
        strClause += " AND UA.REALNAME LIKE '%'";
    }
    if (ddlClient.SelectedIndex > 0)
    {
        strClause += " AND C.ATTR2815 = '" + ddlClient.SelectedItem.Text + "'";
    }
    else
    {
        strClause += " AND C.ATTR2815 LIKE '%'";
    }
    if (ddlSite.SelectedIndex > 0)
    {
        strClause += " AND SI.ATTR2819 = '" + ddlSite.SelectedItem.Text + "'";
    }
    else
    {
        strClause += " AND SI.ATTR2819 LIKE '%'";
    }
    if (ddlPractice.SelectedIndex > 0)
    {
        strClause += " AND PR.ATTR2817 = '" + ddlPractice.SelectedItem.Text + "'";
    }
    else
    {
        strClause += " AND PR.ATTR2817 LIKE '%'";
    }
    if (ddlProvider.SelectedIndex > 0)
    {
        strClause += " AND P.ATTR2919 = '" + ddlProvider.SelectedItem.Text + "'";
    }
    else
    {
        strClause += " AND P.ATTR2919 LIKE '%'";
    }


    if (ddlTaskName.SelectedIndex == 0 && ddlService.SelectedIndex == 0 && ddlStatus.SelectedIndex == 0 && ddlDueDate.SelectedIndex == 0 && ddlOwner.SelectedIndex == 0 && ddlClient.SelectedIndex == 0 && ddlSite.SelectedIndex == 0 && ddlPractice.SelectedIndex == 0 && ddlProvider.SelectedIndex == 0)
    {
        query = strMainQuery + " WHERE CT.ACTIVESTATUS = 0";
    }
    else
    {
        query = strMainQuery + " WHERE CT.ACTIVESTATUS = 0" + strClause;
    }

    using (SqlConnection conn = new SqlConnection(connString))
    {
        try
        {
            SqlCommand cmd = new SqlCommand(query, conn);

            // create data adapter
            SqlDataAdapter da = new SqlDataAdapter(query, conn);
            // this will query your database and return the result to your datatable

            DataSet myDataSet = new DataSet();
            da.Fill(myDataSet);

            myDataView = new DataView();
            myDataView = myDataSet.Tables[0].DefaultView;

            if (sortExp != string.Empty)
            {
                //MessageBox.Show(sortExp);
                //MessageBox.Show(sortDir);
                myDataView.Sort = string.Format("{0} {1}", sortExp, sortDir);
            }

            yourTasksGV.DataSource = myDataView;
            yourTasksGV.DataBind();

            TasksUpdatePanel.Update();

            conn.Close();
        }
        catch (Exception ex)
        {
            string error = ex.Message;
        }
    }
}
protected void ddlTaskName_onSelectIndexChanged(object sender, EventArgs e)
{
    PullData(ViewState["sortExp"].ToString(), ViewState["sortOrder"].ToString());
}
protected void ddlOwner_onSelectIndexChanged(object sender, EventArgs e)
{
    PullData(ViewState["sortExp"].ToString(), ViewState["sortOrder"].ToString());
}
protected void ddlService_onSelectIndexChanged(object sender, EventArgs e)
{
    PullData(ViewState["sortExp"].ToString(), ViewState["sortOrder"].ToString());
}
protected void ddlStatus_onSelectIndexChanged(object sender, EventArgs e)
{
    PullData(ViewState["sortExp"].ToString(), ViewState["sortOrder"].ToString());
}
protected void ddlDueDate_onSelectIndexChanged(object sender, EventArgs e)
{
    PullData(ViewState["sortExp"].ToString(), ViewState["sortOrder"].ToString());
}
protected void ddlClient_onSelectIndexChanged(object sender, EventArgs e)
{
    PullData(ViewState["sortExp"].ToString(), ViewState["sortOrder"].ToString());
}
protected void ddlSite_onSelectIndexChanged(object sender, EventArgs e)
{
    PullData(ViewState["sortExp"].ToString(), ViewState["sortOrder"].ToString());
}
protected void ddlPractice_onSelectIndexChanged(object sender, EventArgs e)
{
    PullData(ViewState["sortExp"].ToString(), ViewState["sortOrder"].ToString());
}
protected void ddlProvider_onSelectIndexChanged(object sender, EventArgs e)
{
    PullData(ViewState["sortExp"].ToString(), ViewState["sortOrder"].ToString());
}

public string sortOrder
{
    get
    {
        if (ViewState["sortOrder"].ToString() == "Desc")
        {
            ViewState["sortOrder"] = "Asc";
        }
        else
        {
            ViewState["sortOrder"] = "Desc";
        }

        return ViewState["sortOrder"].ToString();
    }
    set
    {
        ViewState["sortOrder"] = value;
    }
}

How can I achieve the Dynamic DropDownList from the filtered GridView?

Thanks in advance.

workabyte · Accepted Answer

one thing you could try

sore all drop down queries in a private var excluding the where clause
you could create a method that will build the where clause for all (drop down list and gridview)
when data binding the grid view also rebind the drop down.

NOTE: being that you are using append data bound items you would need to clear out the current options. An alternative to that would be something like

select 'All' as [Task Name], '%' as value
union
SELECT DISTINCT [ATTR2739] as 'Task Name', [ATTR2739] as 'Value' FROM .....

if you set you private select vars up like

var _selectForADropDown = "select distinct ...... {0}"

then where you apply the filter to the gridview you can also apply the filter to the data source for the drop down

dataSourceForDropDown.SelectStatement = string.Format(selectForADropDown, MethodForTheWareClasues());
dropDownList.DataBind();

one thing that may help

using private const vars for each part of the query so you can reuse it

break up the query so the where and from clause can be used for both the gridview and the drop downs

private const string StrMainQuery = @"SELECT
               CT.OBJECTID,
               'http://checkthis.com/d=' + CAST(CT.OBJECTID AS VARCHAR) + '&classid=1224' 'Task Detail'
               ,LTRIM(RTRIM(CT.ATTR2846)) 'Service'
               ,LTRIM(RTRIM(CT.ATTR2812)) 'Status'
               ,CONVERT(VARCHAR(14), CT.ATTR2752, 110) 'Due Date'
               ,LTRIM(RTRIM(CT.ATTR2739)) 'Task Name'
               ,LTRIM(RTRIM(UA.REALNAME)) 'Owner'
               ,LTRIM(RTRIM(C.ATTR2815)) 'Client'
               ,RTRIM(SI.ATTR2819) 'Site'
               ,RTRIM(PR.ATTR2817) 'Practice'
               ,RTRIM(P.ATTR2919) 'Provider'";
        private const string _from = @"
        FROM HSI.RMOBJECTINSTANCE1224 CT 
               INNER JOIN HSI.RMOBJECTINSTANCE1232 S ON CT.ATTR2846 = S.ATTR2821 
               INNER JOIN HSI.USERACCOUNT UA ON S.FK2852 = (UA.USERNUM * -1)
               LEFT JOIN HSI.RMOBJECTINSTANCE1251 PXC ON PXC.FK2924 = CT.OBJECTID
               LEFT JOIN HSI.RMOBJECTINSTANCE1249 P ON PXC.FK2923 = P.OBJECTID
               LEFT JOIN HSI.RMOBJECTINSTANCE1248 PRXC ON PRXC.FK2912 = CT.OBJECTID
               LEFT JOIN HSI.RMOBJECTINSTANCE1230 PR ON PR.OBJECTID = PRXC.FK2911
               LEFT JOIN HSI.RMOBJECTINSTANCE1247 SXC ON SXC.FK2908 = CT.OBJECTID
               LEFT JOIN HSI.RMOBJECTINSTANCE1229 SI ON SI.OBJECTID = SXC.FK2907
                INNER JOIN HSI.RMOBJECTINSTANCE1231 C ON CT.FK2927 = C.OBJECTID
        ";
        private const string where = @"
                WHERE CT.ACTIVESTATUS = 0
                AND CT.ATTR2739 LIKE '{0}'
                AND CT.ATTR2846 LIKE '{1}'
                AND CT.ATTR2812 LIKE '{2}'
                AND AND CONVERT(VARCHAR(14), CT.ATTR2752, 110) LIKE '{3}'
                AND UA.REALNAME LIKE '{4}'
                AND C.ATTR2815 LIKE '{5}'
                AND SI.ATTR2819 LIKE '{6}'
                AND PR.ATTR2817 LIKE '{7}'
                AND P.ATTR2919 LIKE '{8}'
        ";

then build your query's something like:

private string GetWhere()
    {
        return string.Format(where,
            ddlTaskName.SelectedValue,
            ddlService.SelectedValue,
            ddlStatus.SelectedValue,
            ddlDueDate.SelectedValue,
            ddlOwner.SelectedValue,
            ddlClient.SelectedValue,
            ddlSite.SelectedValue,
            ddlPractice.SelectedValue,
            ddlProvider.SelectedValue
            );
    }

    private string GetGridSelect()
    {
        return string.Concat(StrMainQuery, _from, GetWhere());
    }

then you could build the query for each drop down usingthe union all method above to keep the "all"

    private void ReloadDropDown()
    {
        dsPopulateProvider.SelectCommand = GetDropDownGuery();
        ddlProvider.DataBind();
    }

you would benefit a lot by creating a view of this select then using entity framework. would make it really simple to get the value in one swoop for all item but my answer is based off what you currently are doing.

The steps I would take to make these changes

add the const vars for the select statement above the on load method
add method to get the where clause (example above)
add method to get complete select statement for the grid view(sample above)
pull all code building the where clause in the current data bind and method for the grid view and get the select statement from the method just added
add method for building the distinct select statements for each drop down and rebind them as shown above

let me know if you have any question, hope this helps

How to make DropDownList dynamic from filtered GridView

Answers (1)

Related Questions