Sum values from a column in the database via date filter

Asked

Viewed 351 times

3

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 typed in the textBox in the act of registration, and I need to sum all the values of the column quantidadeVagas. So when the user enters a date in the program, it returns the sum of that column with the data containing the date typed.

I did it that way but it didn’t 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:

Código de erro

c# mysql winforms

  • What is not working? An error occurs, it does not bring the expected result? Please give more details so we can help you. If you can leave an example of the expected result and the template of your table (name of fields and example of a 2 records). Thank you.

  • I edited in more detail

2 answers

2


Why not add a MySqlParameter at the MySqlCommand passing the value of the date?

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 give problems.

  • Gave error: "Invalid Attempt to access a field before Calling Read()" in snippet: quantidade = myReader.GetInt32("Vagas");

  • You’re right, the myReader.Read()! Edited response.

  • 1

    Edited response. Probably the error is due to the parameter’s attribution. EDIT: how did you overcome the error?

  • I had switched the MySqlDbType.DateTime for MySqlDbType.VarChar, but persisted if the value is null

  • How I deal when typing a date that is not in the database so that it does not return the error: "Data are Null. Cannot call this method or this property at Null values."?

  • 1

    Edited response to avoid null error. Basically add a IFNULL in query SQL.

Show 1 more comment

1

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

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

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

Browser other questions tagged c# mysql winforms

You are not signed in. Login or sign up in order to post.