2
Contributors
1
Reply
2
Views
7 Years
Discussion Span
Last Post by rohand
0

Why you have created this many functions ? I don't have more idea about your requirement but you have made your code more difficult.

I say as you are selecting data based on criteria from only one table. So you can create filter criteria dynamically based on your dropdown and date selection, then pass it in your DataView.RowFilter.

that's all it will work for you..

Hello,

I'm trying to create an app where the user can search the database using different controls. For example let's say the user can search by date (calendar control), country (DropDownList), states (DropDownList) and cities (DropDownList). So far I have the DropDown controls working. The user selects whatever they want from the DropDown lists, hits the search button, and the result is displayed in the GridView. Now how can I add the calendar control to my code so it displays in the GridView only those results that match the date selected AND whatever was selected in the dropdown list. Any help would be appreciated.

/*###########################################################################################*/
    private void BindRepeaterCountry(string term)
    {

        DataSet ds = (DataSet)Cache["InfoToFilter"];
        if (ds == null)
        {
            ds = GetInfoFromDB2();
            Cache["InfoToFilter"] = ds;
        }
       
        DataView view = new DataView();
        view.Table = ds.Tables[3];
        string filter = String.Format("country LIKE '{0}'", EscapeLikeValue(term));
        view.RowFilter = filter;
        GridView1.DataSource = view;
        GridView1.DataBind();

    }

    /*###########################################################################################*/
    private void BindRepeaterStates(string term)
    {

        DataSet ds = (DataSet)Cache["InfoToFilter"];
        if (ds == null)
        {

            ds = GetInfoFromDB2();
            Cache["InfoToFilter"] = ds;
        }
        DataView view = new DataView();
        view.Table = ds.Tables[3];
        string filter = String.Format("states LIKE '{0}'", EscapeLikeValue(term));
        view.RowFilter = filter;
        GridView1.DataSource = view;
        GridView1.DataBind();
    }


    /*###########################################################################################*/
    private void BindRepeaterCities(string term)
    {

        DataSet ds = (DataSet)Cache["InfoToFilter"];
        if (ds == null)
        {

            ds = GetInfoFromDB2();
            Cache["InfoToFilter"] = ds;
        }
        DataView view = new DataView();
        view.Table = ds.Tables[3];
        string filter = String.Format("cities LIKE '{0}'", EscapeLikeValue(term));
        view.RowFilter = filter;
        GridView1.DataSource = view;
        GridView1.DataBind();

    }

    /*###########################################################################################*/



protected void Button1_Click(object sender, EventArgs e)
    {

        if (lstCities.SelectedIndex.ToString() == "-1")
        {
            BindRepeaterCountry(lstCountry.SelectedItem.Text);
            
        }

        if (lstCities.SelectedIndex.ToString() == "0")
        {
            BindRepeaterStates(lstStates.SelectedItem.Text);

        }

        if (lstCities.SelectedIndex.ToString() != "0" & lstCities.SelectedIndex.ToString() != "-1")
        {
            BindRepeaterCities(lstCities.SelectedItem.Text);
        }

       
        
    }

/*######################################################################################*/

private DataSet GetInfoFromDB2()
{
//Read the connection string from Web.config
DataSet ds = new DataSet();

DataTable dtCountry = new DataTable("Country");
DataTable dtStates = new DataTable("States");
DataTable dtCities = new DataTable("Cities");
DataTable dtBindCountry = new DataTable("BindCountry");


string connectionString = ConfigurationManager.ConnectionStrings["Application"].ConnectionString;
string firstSql = "SELECT DISTINCT country FROM myTable";
string secondSql = "SELECT DISTINCT states, country FROM myTable";
string thirdSql = "SELECT states, cities FROM myTable";
string fourthSql = "SELECT country, states, cities FROM myTable";

OleDbDataAdapter adp = new OleDbDataAdapter(firstSql, connectionString);
adp.Fill(ds, "First Table");

dtCountry = ds.Tables[0];
foreach (DataRow dr in dtCountry.Rows)
{
dr["country"] = AsciiToString(dr["country"].ToString());
}
dtCountry.AcceptChanges();



adp.SelectCommand.CommandText = secondSql;
adp.Fill(ds, "Second Table");

dtStates = ds.Tables[1];
foreach (DataRow dr in dtStates.Rows)
{
dr["states"] = AsciiToString(dr["states"].ToString());
dr["country"] = AsciiToString(dr["country"].ToString());

}
dtStates.AcceptChanges();


adp.SelectCommand.CommandText = thirdSql;
adp.Fill(ds, "Third Table");

dtCities = ds.Tables[2];
foreach (DataRow dr in dtCities.Rows)
{
dr["cities "] = AsciiToString(dr["cities "].ToString());
dr["states"] = AsciiToString(dr["states"].ToString());
}
dtCities.AcceptChanges();


 adp.SelectCommand.CommandText = fourthSql;
        adp.Fill(ds, "Fourth Table");
        dtBindCountry = ds.Tables[3];
        foreach (DataRow dr in dtBindCountry.Rows)
        {
            dr["country"] = AsciiToString(dr["country"].ToString());
            dr["cities "] = AsciiToString(dr["cities "].ToString());
        dr["states"] = AsciiToString(dr["states"].ToString());           
        }
        dtBindPublisher.AcceptChanges();


adp.Dispose();


return ds;
}


/*#######################################
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.