0

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();
June7
  • 19,874
  • 8
  • 24
  • 34
Sheharyar Khan Durrani
  • 1
  • Can you share the exact error message you are getting? Which line in the code throws the error? – Chetan May 19 '20 at 02:55
  • If Invoice_No is text type, use apostrophe delimiters for the parameter. Or instead of parameter concatenation, review https://stackoverflow.com/questions/11905185/executing-query-with-parameters – June7 May 19 '20 at 03:30
  • @ChetanRanpariya It says "Data type mismatch in criteria expression." – Sheharyar Khan Durrani May 20 '20 at 00:19

1 Answers1

0

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.

Barns
  • 4,850
  • 3
  • 17
  • 31