How to count number of records set under a condition?

Question

I'm basically new in coding, and I'm in uni, we have a small project to complete and I'm using OleDb to do it, as I have used it before, I'm trying to count the number of rows where a field is equal to a specific value, but I always get 0. To be more clear, I want statistics showing percentage of males/females who completed the survey(the program I'm making), currently I have 1 male and 2 females, so my string for all rows shows 3, which is correct, but when I want to count all the rows that contain females, it shows 0, so the total percentage is 0, and the textbox that's supposed to show the stat shows 0, here is the code:

private void Form2_Load(object sender, EventArgs e)
    {
        String connectionString;
        connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source='C:\Users\kubas\Desktop\MyProject\ProjectDB.accdb'";
        using (OleDbConnection myConnection = new OleDbConnection(connectionString))
        {
            string Gender1 = "Female";
            OleDbConnection con;
            OleDbCommand cmd = new OleDbCommand();
            OleDbCommand cmd1 = new OleDbCommand();
            con = new OleDbConnection(connectionString);
            con.Open();
            cmd.CommandText = "SELECT Count(Gender) FROM Survey WHERE Gender = '" + Gender1 + "'";
            cmd1.CommandText = "SELECT Count(Gender) FROM Survey";
            cmd.Connection = con;
            cmd1.Connection = con;
            con.Close();
            try
            {
                con.Open();
                int total = (Int32)cmd.ExecuteScalar();
                int total1 = (Int32)cmd1.ExecuteScalar();
                int tot;
                tot = total / total1 * 100;
                string ttal = Convert.ToString(tot);
                textBox1.Text = ttal;
                con.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                if (con.State == ConnectionState.Open)
                {
                    con.Close();
                }

            }

sorry if there is something you can't understand, English isn't my first language.

_tot = total / total1 * 100;_ you are using integer division that can only produce integer values. You need a floating point division. — Steve, Dec 11 '19 at 17:11
So converting the numbers to double, and the dividing it like dat: tot = (double)total/(double)total1? That would be for the result, thanks for that, but the first value is always 0, and it's not supposed to be — YourBestSenpai, Dec 11 '19 at 17:13
Yeah, it shows nothing on the access query as well, but I'm not sure why, Female with capital F everywhere, no spaces, no special characters UPDATE: Actually, whenever I try to run any query on access, no records show up, tried age right now, and name UPDATE2: Instead of writing gender = "female" etc i just entered female and it worked properly, in access that is. — YourBestSenpai, Dec 11 '19 at 17:23

score 0 · Answer 1 · edited Dec 11 '19 at 18:23

0

Maybe try this command query: "SELECT Count(Gender) FROM Survey WHERE Gender = \"" + Gender1 + "\"";

From what I can remember. access uses quotation marks for strings.

edited Dec 11 '19 at 18:23

Eray Balkanli

7,752
11
48
82

answered Dec 11 '19 at 18:00

Han Alister

21
4

Umm, I don't know how to say this, but I asked my friend for help in the meanwhile, he told me few things and nothing worked, so I went back to the same code and it worked, I don't know how or why, but it does work with the slashes indeed, thanks for that – YourBestSenpai Dec 11 '19 at 18:08

How to count number of records set under a condition?

1 Answers1