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;
}
/*#######################################