Null value in sum

Question

I’ve got a problem with mine query, when there are values in the table the sum normally happens as programmed:

string nomeProd = item.SubItems[0].Text;
                double Quantidade = Convert.ToDouble(item.SubItems[2].Text);
                cmdQry.CommandText = "SELECT SUM(quantidadeMat) FROM tbl_EstoqueMat WHERE reservadoMat=False AND nomeMat=@nome";

                cmdQry.Parameters.Clear();
                cmdQry.Parameters.Add(new OleDbParameter("@nome", nomeProd));

                try
                {
                    //SOMA AQUI, OCORRE NORMALMENTE QUANDO HÁ DADOS NA TABELA
                    double soma = Convert.ToDouble(cmdQry.ExecuteScalar());

When no data is in the table, or not available for summation, the following error occurs:

The value is returned null. How to deal with this type of problem?

I use Oledb, that is, Access and C#.

Answer 1

First of all you have two problems in your code. The first is the use of double, this is not appropriate. The second is that you are converting something that you cannot guarantee can be converted, it should be something like this:

if (!decimal.TryParse(item.SubItems[2].Text, out var quantidade)) //faz alguma coisa se deu erro e encerra a execução

But the specific problem is precisely what is indicated in your question, there is situation that the returned value is null, so you have to work with this option and treat this appropriate value. What you shouldn’t do is let her make an exception and catch her to do something. Almost every exception capture in code is wrong and is gambiarra of those who do not want to fix the error in their code.

Just as I fixed the above checked problem if it went all right in this case I would also have to do something similar:

var resultado = cmdQry.ExecuteScalar();
if (resultado == DBNull.Value) //faz alguma coisa aqui para tratar o erro e encerra
var soma = ToDecimal(resultado);

I put in the Github for future reference.

In case I put one using static System.Convert; at the beginning of the code.

This way the conversion will only be made if it is not null.

Use a rule: if you don’t know how to use a language mechanism like the exception or type double, among others, do not use. When you use something you do not understand how it works you end up entering this situation:

Answer 2

You can use "catch" to handle the error received.

Ex:

            string nomeProd = item.SubItems[0].Text;
            double Quantidade = Convert.ToDouble(item.SubItems[2].Text);
            cmdQry.CommandText = "SELECT SUM(quantidadeMat) FROM tbl_EstoqueMat WHERE reservadoMat=False AND nomeMat=@nome";

            cmdQry.Parameters.Clear();
            cmdQry.Parameters.Add(new OleDbParameter("@nome", nomeProd));

            try
            {
                //SOMA AQUI, OCORRE NORMALMENTE QUANDO HÁ DADOS NA TABELA
                double soma = Convert.ToDouble(cmdQry.ExecuteScalar()); 

            }
            catch 
            {
              MessageBox.Show("O valor recebido é nulo");
            }