How to fix exception:Unable to cast object of type 'System.DBNull' to type 'System.Byte[]'

Question

I have a table named tblStaff of which the column named staffImage has Image datatype which is allowed to store Null.If the user supply his photo then this column will store image as Binary data and if he does not supply his image then it will store Null value.If this column has null value then the image from Resource folder should be displayed in pictureBox1 and if this column has Binary data then the image stored in this column as a binary data should be shown in pictureBox1.

CREATE TABLE tblStaff
(
    staffId int not null identity Primary Key,
    staffName varchar(50) not null,
    staffUserName varchar(25) not null,
    staffPassword varchar(30) not null,
    staffPhone varchar(15) not null,
    staffRole int not null,
    staffStatus tinyint not null,
    **staffImage image**
)

    ALTER PROC [dbo].[sp_GetStaffImage]
    @staffId varchar(150)
    as
    SELECT Stf.staffImage as 'Image' FROM tblStaff Stf WHERE 
    staffID=@staffId


.
.
.
.

string staffID = Convert.ToString(dataGridViewStaff.Rows[e.RowIndex].Cells["Staff Id"].Value);
..............
.............
..........
...........

SqlConnection con1 = new SqlConnection(cs);
con.Open();
SqlCommand cmd1 = new SqlCommand("sp_GetStaffImage", con);
cmd1.CommandType = CommandType.StoredProcedure;
cmd1.Parameters.AddWithValue("@staffId", staffID);
SqlDataAdapter sda1 = new SqlDataAdapter(cmd1);
DataSet ds = new DataSet();
sda1.Fill(ds);

if(ds.Tables[0].Rows.Count>0)
{
   var img = (byte[])ds.Tables[0].Rows[0][0];

   if (img != Null) //code if the data in column named staffImage is 
                    Binary data then show the image                        
                    in PictureBox1 from the database.
   {

         MemoryStream ms = new MemoryStream(img);
         pictureBox1.Image = new Bitmap(ms);
   }
   else  //code if the data in column named staffImage is Null then show the image in PictureBox1 
           from Resource folder .
   {
       pictureBox1.ImageLocation = "Resources/human.png";
   }

}
con.Close();

By running the above code I got Exception as follows: Unable to cast object of type 'System.DBNull' to type 'System.Byte[]'.

Answer 1

That exception of "Unable to cast object of type 'System.DBNull' to type xxx" is correct and it's also quite straightforward, because the column value you are trying to convert from DataRow is DbNull.

If you are reading rows from DataTable, you should always check for DbNull if the column is typed as nullable column.

For example, the code should be like this:

 var img = (byte[])(ds.Tables[0].Rows[0][0] == DbNull.Value ? null : ds.Tables[0].Rows[0][0]);