passing parameter to mysql command dynamically and column relating

Question

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)

I want it to display disease name based on symptoms entered by user through checkboxes which can be any number of symptoms. So I know that disease_id will be primary key in disease_table, but I don't understand how to relate disease_id,symptom_id in disease_symptom table.Also I have something like this in mind

select disease_name,disease_id 
from disease_symptom ds
InnerJoin symptom_table s on ds.symptom_id=s.symptom_id
InnerJoin disease_table d on d.disease_id=s.disease_id
where s.symptom_name in('checkbox1.text','checkbox2.text',.... and so on)

So basically what I want to know is how to relate disease_id,symptom_id in disease_symptom table and how to pass dynamically adjusted number of parameter (user could select 3 checkboxes , sometimes 4 ,5 etc)...Thanks

Are you using Entity Framework?

Andrew Paes
– Andrew Paes

2016-02-10 19:54:16 +00:00
Commented Feb 10, 2016 at 19:54 — Andrew Paes
– Andrew Paes, Commented Feb 10, 2016 at 19:54
@AndrewPaes no its in asp.net basic not fancy

panman
– panman

2016-02-11 04:51:23 +00:00
Commented Feb 11, 2016 at 4:51 — panman
– panman, Commented Feb 11, 2016 at 4:51

Andrew Paes · Accepted Answer · 2016-02-10 20:45:12Z

1

The correct SQL:

select
  d.name
  , d.disease_id 
from Disease d
inner join DiseaseSympton ds on ds.disease_id = d.disease_id
inner join Sympton s on s.sympton_id = ds.sympton_id
where s.name in ('Fever')

SQL Fiddle

Getting checkbox values dynamically ( I couldn't test MySQL Conns ):

using System;
using System.Text;
using System.Web.UI.WebControls;
using System.Linq;


public class Program
{
    public static void Main()
    {
        var chBoxList= new CheckBoxList();
        chBoxList.Items.Add(new ListItem("Headache", "1"));
        chBoxList.Items.Add(new ListItem("Fever", "2"));
        chBoxList.Items.Add(new ListItem("Nause", "3"));

        chBoxList.Items[1].Selected = true;
        chBoxList.Items[2].Selected = true;

        string symptons = String.Join(", ", chBoxList.Items.Cast<ListItem>()
                                      .Where(i => i.Selected)
                                      .Select(i => i.Text));

        Console.WriteLine(symptons);


        using (MySqlConnection connection = new MySqlConnection(...))
        {
            connection.Open();

            using (MySqlCommand cmd = new MySqlCommand("select d.name , d.disease_id from Disease d inner join DiseaseSympton ds on ds.disease_id = d.disease_id inner join Sympton s on s.sympton_id = ds.sympton_id where s.name in ('@pSymptons');", connection))
            {
                cmd.Parameters.AddWithValue("@pSymptons", symptons);

                using (MySqlDataReader reader = cmd.ExecuteReader())
                {
                    StringBuilder sb = new StringBuilder();

                    while (reader.Read())
                    {
                        ...
                    }
                }
            }
        }
    }
}

Net Fiddle

answered Feb 10, 2016 at 20:45

Andrew Paes

2,0221 gold badge16 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

panman Over a year ago

thanks for replying to my question. The mysql query works fine but the string symptons = String.Join(", ", chBoxList.Items.Cast<ListItem>() .Where(i => i.Selected) .Select(i => i.Text)); has error it says Error 5 The best overloaded method match for 'string.Join(string, string[])' has some invalid arguments tried using .ToArray() at end but it still doesn't produce any result

Andrew Paes Over a year ago

Did you declare "using System.Linq;"?

panman Over a year ago

can you explain me that statement ("string symptons...")..what does it do..even after adding reference , it doesn't produce any result. but sql query works absolutely fine

panman Over a year ago

Hello, i got it now ..the output of that statement would be a comma seperated selected values from checkbox list. so now the question is how to pass these varying values into sql statement in place of @pSymptons

Andrew Paes Over a year ago

Well, I'll explain in a very simple way. In this snip it is captured all items within the checkbox list, where they are casted for the ListItem type whose checkbox is selected. So it's taken the "text" value of the selected checkbox and they are "lined up" and attributed to a string being separated by a comma. See the example in the link I put in response "Net Fiddle"

|

Collectives™ on Stack Overflow

passing parameter to mysql command dynamically and column relating

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related