protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
//POPULATE COURSE TABLE
DataTable dt = new DataTable();
String connString = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();
SqlConnection conn = new SqlConnection(connString);
conn.Open();
string sql = "SELECT CourseID, CourseName, University, State FROM COURSE";
SqlCommand cmd = new SqlCommand(sql, conn);
SqlDataAdapter da = new SqlDataAdapter(sql, conn);
da.SelectCommand = cmd;
da.Fill(dt);
tblCourse.DataSource = dt;
tblCourse.DataBind();
//POPULATE STATE DROPDOWN
sql = "SELECT DISTINCT State FROM Course ORDER BY State ASC";
cmd = new SqlCommand(sql, conn);
da = new SqlDataAdapter(sql, conn);
da.SelectCommand = cmd;
dt.Clear();
da.Fill(dt);
StateSelect.DataSource = dt;
StateSelect.DataTextField = "State";
StateSelect.DataValueField = "State";
StateSelect.DataBind();
//POPULATE SCHOOL DROPDOWN
sql = "SELECT DISTINCT University FROM Course ORDER BY University ASC";
cmd = new SqlCommand(sql, conn);
da = new SqlDataAdapter(sql, conn);
da.SelectCommand = cmd;
dt.Clear();
da.Fill(dt);
SchoolSelect.DataSource = dt;
SchoolSelect.DataTextField = "University";
SchoolSelect.DataValueField = "University";
SchoolSelect.DataBind();
conn.Close();
}
}
protected void StateSelect_SelectedIndexChanged(object sender, EventArgs e)
{
string state = StateSelect.SelectedValue.ToString();
//POPULATE COURSE TABLE
DataTable dt = new DataTable();
String connString = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();
SqlConnection conn = new SqlConnection(connString);
conn.Open();
string sql = "SELECT CourseID, CourseName, University, State FROM COURSE";
if (!state.Equals("All States"))
{
sql += " WHERE State = @state";
}
SqlCommand cmd = new SqlCommand(sql, conn);
if (!state.Equals("All States"))
{
cmd.Parameters.AddWithValue("@state", state);
}
SqlDataAdapter da = new SqlDataAdapter(sql, conn);
da.SelectCommand = cmd;
da.Fill(dt);
tblCourse.DataSource = dt;
tblCourse.DataBind();
//POULATE SCHOOL DROPDOWN
SchoolSelect.Items.Clear();
SchoolSelect.Items.Add("All Schools");
sql = "SELECT DISTINCT University FROM Course";
if (!state.Equals("All States"))
{
sql += " WHERE State = @state ORDER BY University ASC";
}
cmd = new SqlCommand(sql, conn);
if (!state.Equals("All States"))
{
cmd.Parameters.AddWithValue("@state", state);
}
da = new SqlDataAdapter(sql, conn);
da.SelectCommand = cmd;
dt.Clear();
da.Fill(dt);
SchoolSelect.DataSource = dt;
SchoolSelect.DataTextField = "University";
SchoolSelect.DataValueField = "University";
SchoolSelect.DataBind();
SchoolSelect.SelectedValue = "All Schools";
conn.Close();
}
private void Search_Btn_Click(object sender, RoutedEventArgs e)
{
// Main filters
GlobalVar.SelectedTypes = new List <string>()
{
"" + GPSelect.getCheckedString(), "" + DentistSelect.getCheckedString(), "" + OpticianSelect.getCheckedString(), "" + SchoolSelect.getCheckedString(), "" + NurserySelect.getCheckedString()
};
// Private filter
GlobalVar.showPrivate = PrivateSelect.getChecked();
// Selected postcode
GlobalVar.postcodeSelected = PostCode_TextBox.Text;
// Display results
DatabaseResults.DisplayResults();
}
