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

Question

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

On which line are you receiving the exception? Also, make sure you close your cn instance with the .Close() method. You should also always use parameterized queries. — Drew Kennedy
– Drew Kennedy, Commented Jul 25, 2016 at 15:54
I can use parameterized queries. Does it Gives expectation result ???@DrewKennedy — user5549099
– user5549099, Commented Jul 25, 2016 at 15:57
To repeat @DrewKennedy, which line is throwing the exception? — mxmissile
– mxmissile, Commented Jul 25, 2016 at 16:08
MoneyIn += Convert.ToInt32(DataBinder.Eval(e.Row.DataItem, "Money In")); MoneyOut += Convert.ToInt32(DataBinder.Eval(e.Row.DataItem, "Money Out"));; This two line is throwing exception @DrewKennedy — user5549099
– user5549099, Commented Jul 25, 2016 at 16:11

mxmissile · Accepted Answer · 2016-07-25 16:02:20Z

1

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"));
}

answered Jul 25, 2016 at 16:02

mxmissile

11.7k6 gold badges57 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

15 Comments

user5549099 Over a year ago

Yes But still same result @mxmissile

mxmissile Over a year ago

@MAHossan are you comparing "Money Out" also?

user5549099 Over a year ago

Yes both @mxmissile

mxmissile Over a year ago

Can you update your question to show the code you are checking against DBNull with?

user5549099 Over a year ago

OKK i added the image without using RowDataBound . Please find the image@mxmissile

|

Hugo Yates · Accepted Answer · 2016-07-25 16:24:04Z

0

So if you're getting empty fields back do something with IsNull()? Like this:

SELECT  Account_Number AS [Account Number], IsNull(Amount,0) AS[Money In] from   Deposit  where Deposit.Account_Number='" + TextBox1.Text + "'"

You may also want to link your two DataTables together using Linq instead as this will give you more control. That or write a Stored Procedure to give you the correct output in one query/DataTable (the better option).

Also stop with the = '"+ unsantitised input +"'! You're making your page extremely easy to hack with SQL Injection. You need to use Parameters.

answered Jul 25, 2016 at 16:24

Hugo Yates

2,1112 gold badges27 silver badges25 bronze badges

4 Comments

user5549099 Over a year ago

What will be answer form this line

user5549099 Over a year ago

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);

user5549099 Over a year ago

I am afraid say that same result @Hugo Yates

user5549099 Over a year ago

I changed the SqlQueries according your suggestion but same result

José Algarra · Accepted Answer · 2016-07-25 19:24:38Z

0

You think you need to validate DataBinder.Eval(e.Row.DataItem, "Money In") before using it, but actually if you are aware that there will be exceptions and the data in these exceptions should just be ignored you don't need to validate the data, you can just ignore it.

Here is a quick, dirty fix that may help you:

Change:

    MoneyIn += Convert.ToInt32(DataBinder.Eval(e.Row.DataItem, "Money In"));

To

    Try
    {
    MoneyIn += Convert.ToInt32(DataBinder.Eval(e.Row.DataItem, "Money In"));
    }
    catch {};

Change also:

    MoneyOut += Convert.ToInt32(DataBinder.Eval(e.Row.DataItem, "Money Out"));

To

    Try
    {
    MoneyOut += Convert.ToInt32(DataBinder.Eval(e.Row.DataItem, "Money Out"));
    }
    catch {};

Please notice: this won't stop you exception from happening, it will just catch the exception and ignore the data on it. You program will just ignore the data that cannot be converted to INT, and this is what you were trying to do by validating it.

This can be used when you are aware that exceptions will be thrown and you are willing to ignore these exception because they won't change your program functionality, altough it is not exactly a "good standards" solution it works.

edited Jul 25, 2016 at 19:24

answered Jul 25, 2016 at 19:21

José Algarra

7311 gold badge6 silver badges10 bronze badges

1 Comment

user5549099 Over a year ago

Thanks for suggestion . I will try

Collectives™ on Stack Overflow

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

3 Answers 3

15 Comments

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

15 Comments

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related