lundi 6 avril 2020

How to write a query based on results of checked checkBoxes C#

I've been trying to write a program that looks as following:

enter image description here

The point is to be able to select more than one value in the first Group box that represents whether person is Mother, Father or the Tutor, second group box lets us choose if the kids of the person are male or female and the numericUpDown lets us tell the number of kids that person haves. Button click should give all records from Database that match the criteria. I wrote a program that functions only when some of the checkboxes are checked, but not if we chose more than one checkbox in each criteria:

        cmd.CommandText = "Select Roditelj.Ime, Roditelj.Prezime, Adresa, Svojstvo From Roditelj, Svojstvo_Roditelja, Dete, Pol Where Roditelj.SvojstvoID = Svojstvo_Roditelja.SvojstvoID And Pol.PolID = Dete.PolID And Roditelj.RoditeljID = Dete.RoditeljID ";
        if (checkBox1.Checked)
            cmd.CommandText += " And Svojstvo='Otac'";
        if (checkBox2.Checked)
            cmd.CommandText += " And Svojstvo='Majka'";
        if (checkBox3.Checked)
            cmd.CommandText += " And Svojstvo='Staratelj'";
        if (checkBox4.Checked)
            cmd.CommandText += " And Pol='Muški'";
        if (checkBox5.Checked)
            cmd.CommandText += " And Pol='Ženski'";
        cmd.CommandText += " Group By Roditelj.Ime, Roditelj.Prezime, Adresa, Svojstvo Having Count(Dete.RoditeljID) = " + numericUpDown1.Value.ToString();

        DataSet ds = new DataSet();
        OleDbDataAdapter da = new OleDbDataAdapter();
        da.SelectCommand = cmd;
        da.Fill(ds);

Then i tried to expand my command and wrote this:

        cmd.CommandText = string.Format("Select Roditelj.Ime, Roditelj.Prezime, Adresa, Svojstvo From Roditelj, Svojstvo_Roditelja, Dete, Pol Where Roditelj.SvojstvoID = Svojstvo_Roditelja.SvojstvoID And Pol.PolID = Dete.PolID And Roditelj.RoditeljID = Dete.RoditeljID And (Svojstvo_Roditelja.Svojstvo='{0}' Or Svojstvo_Roditelja.Svojstvo='{1}' Or Svojstvo_Roditelja.Svojstvo='{2}') And (Pol.Pol='{3}' Or Pol.Pol='{4}')", checkBox1.Checked.ToString(), checkBox2.Checked.ToString(), checkBox3.Checked.ToString(), checkBox4.Checked.ToString(), checkBox5.Checked.ToString());

As result this query always writes only a blank line into gridview. Can someone please help me. Thanks!




Aucun commentaire:

Enregistrer un commentaire