Data type mismatch using execute scalar

Question

I'm looking to fetch sum of ALLOCATED_DEPOSIT from Allocations using Invoice_No. I'm getting all the details through datareader but only execute scalar is being a problem for me. I tried everything but using the following code it gives "data type mismatch error" always.

connection.Close();

        try
        {
            connection.Open();
            string cstrQuery = "SELECT SUM (ALLOCATED_DEPOSIT)FROM ALLOCATIONS WHERE INVOICE_NO= " + int.Parse(txt_Invoiceno.Text);
            OleDbCommand cmd = new OleDbCommand(cstrQuery, connection);
            int sum = Convert.ToInt32(cmd.ExecuteScalar());
            //long sum = (long)dt.Compute("Sum(ALLOCATED_DEPOSIT)", "True");

            textBox2.Text = sum.ToString();
        }
        catch (Exception ex)
        {

            MessageBox.Show(ex.Message);
        }
        connection.Close();

Answer 1

Be aware that the ExecuteScalar() can return null.

You are also using the result as a string so no need to convert to int then to string.

Do this instead:

var sumObject = cmd.ExecuteScalar();
if(sumObject != null)
{
    textBox1.Text = sumObject.ToString();
}
else
{
    Console.WriteLine("Something went wrong!");
}

Inferring from you query I would guess the values in the field ALLOCATED_DEPOSIT are floating point and not integer, thus the sum of which would be floating point. That might be the "data type mismatch error" you are seeing.