Although Wes correctly says you should use parametrized queries, that will not solve you problem. But it might well save your database from SQL Injection attack which can easily damage or destory your database. Do not concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Use Parametrized queries instead:
SqlCommand cmd = new SqlCommand("select Accountno,Acct_Name,Dr_amount,Cr_amount from Gl_Transaction where Value_Date between @FD AND @TD order by Accountno ASC" , con);
cmd.Parameters.AddWithValue("@FD", fromdate);
cmd.Parameters.AddWithValue("@TD", todate);
But that won't solve you immediate problem, whinch is pretty simple: one or more of your Dr_amount or Cr_amount values is not specified in your DB (it's a
null
value) so the database returns a special result
DBNull.Value
to indicate that.
You need to test for it before you use the actual value.
decimal debit = 0;
if (dr[2] != DBNull.Value)
{
debit = Convert.ToDecimal(dr[2]);
}
decimal credit = 0;
if (dr[3] != DBNull.Value)
{
credit = Convert.ToDecimal(dr[3]);
}
if (debit > 0)
{
balance = balance + debit;
drsum += debit;
}
else
{
balance = balance - credit;
crsum += credit;
}