"Argued tofrangeexception" error when performing database query to return quantity of records

Question

I have a query in ADO . NET with the aim of return the amount of records I have in the bank based on last month and year. For example, I pass the date "27/01/2021" and receive the amount of records I have in the bank for "01/2021". The problem happens if I have no record recorded in the bank, then I get the error:

Tofrangeexception argued: Index was out of range. Must be non-negative and Less than the size of the Collection. (Parameter 'index') System.Collections.Generic.list.get_Item(int index)

If I already have at least one record in the bank I don’t get this error, I tried a few things, but I couldn’t get any solution for this error. Follow my query:

public int RetornaQuantidadeCuponsMes(string numDocumento, string idProduto, string dataImportacao)
        {
            var conexao = AbrirConexao();
            var comando = conexao.CreateCommand();
            comando.CommandText =
                "SELECT DATE_FORMAT(@dataEfetivacao,'%Y-%m') AS data, " +
                "(SELECT COUNT(cupom) FROM campanhaCupons AS c2 " +
                "WHERE DATE_FORMAT(c2.dataEfetivacao,'%Y-%m') = data AND numDocumento = @numDocumento  AND idProduto = @idProduto) " +
                "AS qtdCupons " +
                "FROM campanhaCupons AS c1";
            comando.Parameters.AddWithValue("numDocumento", numDocumento);
            comando.Parameters.AddWithValue("idProduto", idProduto);
            comando.Parameters.AddWithValue("dataEfetivacao",
                Convert.ToDateTime(dataImportacao).ToString("yyyy/MM/dd"));
            var cooperado = ExecutaComandoComRetorno(comando);

            var cuponsNoMes = cooperado[0]["qtdCupons"];

            return Convert.ToInt32(cuponsNoMes);
        }

Answer 1

The cooperado is coming empty because there is no record. One simple way to resolve this is with a if:

var cooperado = ExecutaComandoComRetorno(comando);
if (cooperado is null) // Se não tiver dados, retorna 0
    return 0;

var cuponsNoMes = cooperado[0]["qtdCupons"];
return Convert.ToInt32(cuponsNoMes);

Answer 2

As quoted by @Ricardopunctual in the question comment, the correct is check if there are items in your list before, to avoid exceptions of the type Argued tofrangeexception, when trying to access an index outside the values accepted in a list, something like:

if (cooperado != null && cooperado.Count > 0) {

Depending on the implementation of the data structure that is returning, it may be that the property that provides the list size is Length, Count or Count() (the method Count is a namespace extension method System.Linq).

Check whether cooperado is different from null is not mandatory, but is recommended to also avoid a Nullreferenceexception (when trying to access a property of a value null).

I hope I’ve helped in some way.