M A Hossan M A Hossan - 4 months ago 80
SQL Question

Object cannot be cast from DBNull to other types in Grid view Controll

I am trying to achieve from this code is printing Grand Total in gridview footer by using RowDataBound method. I declared two variables inside the RowDataBound method . One is Money In and another is Money Out.Gridview has empty rows.I have two table in database one call Deposit and another call withdraw. I am merging two table by using SqlDataAdapter.I declared two Data Table dt and dt1.Inside the SqlDataAdapter of each data table I passed two SQL Query to retrieve the data from database by using one key which is coming from textbox. Finally I am merging this two Data table by using merge method and displaying in grid view. It works perfectly but when I am trying to get Grand Total of the columns I am having this error Object cannot be cast from DBNull to other types.
Here is my Sql code.

SqlConnection cn = new SqlConnection(@"Data Source=KHUNDOKARNIRJOR\KHUNDOKERNIRJOR;Initial Catalog=Login;Integrated Security=True");

SqlDataAdapter sdr = new SqlDataAdapter(@"SELECT Account_Number AS [Account Number],Amount AS[Money In] from Deposit where Deposit.Account_Number='" + TextBox1.Text + "'", cn);
DataTable dt = new DataTable();
sdr.Fill(dt);
SqlDataAdapter sdr1 = new SqlDataAdapter(@"SELECT WithDraw.Amount AS[Money Out], ACCOUNT.statementamount AS [Balance] FROM ACCOUNT
INNER JOIN WithDraw
ON ACCOUNT.Account_Number = WithDraw.Account_Number
where WithDraw.Account_Number='" + TextBox1.Text + "'", cn);
DataTable dt1 = new DataTable();
sdr1.Fill(dt1);
dt.Merge(dt1);
GridView2.DataSource = dt;
GridView2.DataBind();
}

int MoneyIn=0;
int MoneyOut=0;


// Loop thru each data row and compute total unit price and quantity sold
if (e.Row.RowType == DataControlRowType.DataRow)
{
if (DataBinder.Eval(e.Row.DataItem, "Money In") != DBNull.Value)
{
MoneyIn += Convert.ToInt32(DataBinder.Eval(e.Row.DataItem, "Money In"));
}
if (DataBinder.Eval(e.Row.DataItem, "Money Out") != DBNull.Value)
{
MoneyOut += Convert.ToInt32(DataBinder.Eval(e.Row.DataItem, "Money Out"));
}
} //// Display totals in the gridview footer
else if (e.Row.RowType == DataControlRowType.Footer)
{
e.Row.Cells[0].Text = "Grand Total";
e.Row.Cells[0].Font.Bold = true;
e.Row.Cells[1].Text = MoneyIn.ToString();
e.Row.Cells[1].Font.Bold = true;
e.Row.Cells[2].Text = MoneyOut.ToString();
e.Row.Cells[2].Font.Bold = true;
}
}


}

Please anyone can help me to solve this problem. Thanks

here is the out put after editing and cheeking the Db null referance but still can not add the grand total enter image description here

Answer

Have you tried comparing with DBNull before converting?

if(DataBinder.Eval(e.Row.DataItem, "Money In") != DBNull.Value)
{
  MoneyIn += Convert.ToInt32(DataBinder.Eval(e.Row.DataItem, "Money In"));
}