I have 3 tables 1>disease_table [columns=] (disease_id,disease_name)2>symptom_table [columns=] (symptom_id,symptom_name)3>disease_symptom [columns=] (disease_id,symptom_id) there is a checkboxlist on my web page which has symptoms where text=fever, value='fever'..and so on but the problem is that I get no output when I pass parameter to the sql stmt., it doesn't give any output.Also if I pass static parameter (if I choose to pass all the available symptoms) it gives me repeated disease names.
Here is my code:
protected void Button1_Click(object sender, EventArgs e)
{
MySqlConnection connection = new MySqlConnection(ConfigurationManager.ConnectionStrings["myconstring"].ConnectionString);
connection.Open();
symptons = String.Join(", ", CheckBoxList1.Items.Cast<ListItem>().Where(i => i.Selected).Select(i => i.Value).ToArray());
Label3.Text = symptoms;
MySqlCommand cmd = new MySqlCommand("select d.dname from disease d inner join diseasesymptom ds on ds.did = d.did inner join symptom s on s.sid = ds.sid where s.sname in (@pSymptoms)", connection);
cmd.Parameters.AddWithValue("@pSymptoms", symptoms);
using (MySqlDataAdapter sda = new MySqlDataAdapter())
{
cmd.Connection = connection;
sda.SelectCommand = cmd;
using (DataTable dt = new DataTable())
{
sda.Fill(dt);
GridView1.DataSource = dt;
GridView1.DataBind();
}
}
}
sql stmt :
selectd.name, d.disease_id
from Disease d
inner join DiseaseSymptom ds on ds.disease_id = d.disease_id
inner join Symptom s on s.symptom_id = ds.symptom_id
where s.name in ('Fever');
output: if I pass all the parameters(static) then I get all available diseases multiple times (e.g :if disease 'd1' has 2 symptoms and I choose 2 of those symptoms that disease 'd1' is displayed twice)
