Adding values from a column in the database using date filter

Question

Adding values from a column in the database using date filter

Navigation

#1 by (2 votes)
#2 by (1 votes)

4

I need to sum all the values of a column containing in each row a specific date, that is, in the database, the values are saved with the date entered in textBox in the act of the cadastre, and I must add all the values column quantidadeVagas . So when the user types a date into the program, it returns the sum of that column with the data containing the date entered.

I did that, but it did not work:

string query = "Select sum (quantidadeVagas) from vagas where data like '%" + mTxtDataVagas.Text + "%'";
MySqlConnection conexao = new MySqlConnection();

conexao.ConnectionString = DadosDaConexao.StringDeConexao;
conexao.Open();

MySqlCommand cmdDataBase = new MySqlCommand(query, conexao);
MySqlDataReader myReader;

myReader = cmdDataBase.ExecuteReader();

while (myReader.Read())
{
    int quantidade = Convert.ToInt32(myReader.GetString("count"));
}

The following error appears:

mysql c# winforms

asked by anonymous 28.12.2018 / 13:00

2 answers

1

The problem occurs because MYSQL does not accept whitespace between the function name and the parentheses, in which case it is sum (quantidadeVagas) .

Change your query by removing white space between SUM and parentheses:

string query = "Select SUM(quantidadeVagas) from vagas where data like '%" + mTxtDataVagas.Text + "%'";

28.12.2018 / 13:13

Does the last statement on a switch need to 'break'? Function refactoring to collect constants with a specific prefix

score 2 · Accepted Answer

Why not add a MySqlParameter to MySqlCommand by passing the date value?

string query = "SELECT IFNULL(SUM(quantidadeVagas), 0) AS Vagas FROM vagas WHERE data = @Data";

using(MySqlConnection conexao = new MySqlConnection(DadosDaConexao.StringDeConexao))
{
    conexao.Open();

    using(MySqlCommand cmdDataBase = new MySqlCommand(query, conexao))
    {
        cmdDataBase.Parameters.AddWithValue("@Data", mTxtDataVagas.Text);

        using(MySqlDataReader myReader = cmdDataBase.ExecuteReader())
        {
            int quantidade = 0;

            if(myReader.HasRows)
            {
                myReader.Read();
                quantidade = myReader.GetInt32("Vagas");
            }
        }
    }
}

The way I was passing the date parameter would almost certainly cause problems.