1

I am trying to get a sum with a column from a ms access database but so not nothing has worked this is what I have

      Conn.Open();
            Command.CommandText = "Select SUM(c_qty) from COLLVAULT_COINS WHERE c_type!='US Currency' AND c_type!='World Currency' AND c_type!='World Coins' AND c_listName = '" + activeCollection + "'";
            int total_1 = Convert.ToInt32(Command.ExecuteScalar());
            //total sum - get how many match the query
            lblcollectedcount.Text = Convert.ToString(total_1);
            Conn.Close();

any help would be nice.

Improve this question
4
  • 1
    not equal is <> not != support.office.com/en-us/article/… Commented Jul 5, 2017 at 2:18
  • What is the definition of not working? Share some more code and tell us what error you are seeing? Commented Jul 5, 2017 at 2:19
  • Message=Syntax error (missing operator) in query expression 'c_type!='US Currency' AND c_type!='World Currency' AND c_type!='World Coins' AND c_listName = 'test-acc''. and it highlights the int total_1 = Convert.ToInt32(Command.ExecuteScalar()); Commented Jul 5, 2017 at 2:19
  • @Slai got it working. your answer helped. This is first time trying to do this in c# with access so I thought it was != Thanks. Commented Jul 5, 2017 at 2:22

2 Answers 2

Reset to default
1 
      Conn.Open();
            Command.CommandText = "Select SUM(c_qty) from COLLVAULT_COINS WHERE c_type <>'US Currency' AND c_type<>'World Currency' AND c_type<>'World Coins' AND c_listName = '" + activeCollection + "'";
            int total_1 = Convert.ToInt32(Command.ExecuteScalar());
            //total records
            lblcollectedcount.Text = Convert.ToString(total_1);
            Conn.Close();

I had to use <> as not equals to instead of !=

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

Comments

1

You could try something like this, assuming you want the number of records that were matched with your query:

Conn.Open();
Command.CommandText = "Select SUM(c_qty) as sum, Count(*) as count from COLLVAULT_COINS WHERE c_type <> 'US Currency' AND c_type <> 'World Currency' AND c_type <> 'World Coins' AND c_listName = '" + activeCollection + "'";    //

int total_matches = 0;

using (MySqlDataReader dataReader = Command.ExecuteReader())
{
    if (dataReader.Read()) 
    {
        lblcollectedcount.Text = Convert.ToString(dataReader["sum"]);
        total_matches = Convert.ToInt32(dataReader["count"]);
    } 
    else 
    {
        //nothing was read, handle that case
    }
}

Conn.Close();

EDIT:

Misunderstood what the OP was asking for, and I also didn't catch the '<>' error.

I'll still keep this answer here, just for reference code.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.