Question How to Insert Null Value to Image column?

[Mazhar]

Asalm o alikum
I m using vb.net 2010 with sql server 2014, In my product table I have an image column to save product picture. In case i don't have picture for a product i want to insert a null value. i tried to insert with dbnull.value but faild. can any body help me in this regard.

thanks

 

[jmcilhinney]

Maybe if you were to show us what you did then we could tell you what's wrong with it. You absolutely do use DBNull.Value to insert a NULL value into a database regardless of the data type but exactly how you do it depends on the specifics of the situation. There are a number of variations, most notably whether you're using a typed DataSet or not and whether you're using a DataTable or not.

 

[Mazhar]

Maybe if you were to show us what you did then we could tell you what's wrong with it. You absolutely do use DBNull.Value to insert a NULL value into a database regardless of the data type but exactly how you do it depends on the specifics of the situation. There are a number of variations, most notably whether you're using a typed DataSet or not and whether you're using a DataTable or not.

Thanks for u reply. I am using dataTable below is the vb.net code through which i tried to to insert the null value.
Data type of column is Image (sql Server 2014)

cmd.Parameters.AddWithValue("@Picture", ProductPicture.Image).Value = System.DBNull.Value

 

[jmcilhinney]

That code has a number of issues. You call AddWithValue and then set the Value. How does that make sense. It's an either/or choice: either call AdWithValue and provide the value as an argument or call Add and set the Value explicitly. You don't do both.

Also, you can't call AddWithValue if you have no value to add - NULL is specifically no value - so if you want to use NULL then you have to use Add, not AddWithValue. AddWithValue will infer the data type from the value but, if there's no value, then no type can be inferred.

Finally, you can't provide an actual Image as a value (as far as I'm aware, unless something's changed). Images are stored as binary data so you need to convert your Image to a Byte array first and then use that array as the value. Overall, your code might look like this:

Dim value As Object = If(ProductPicture.Image Is Nothing, CObj(DBNull.Value), GetImageData(ProductPicture.Image))

cmd.Parameters.Add("@Picture", SqlDbType.VarBinary, -1).Value = value

In that code, the -1 argument passed to Add indicates that the column's data type is varbinary(max). If you have created that column with a specific size rather than MAX, use that number instead of -1. The GetImageData method, which takes an Image and returns a Byte array, could be implemented like this:

Private Function GetImageData(image As Image) As Byte()
    Dim data As Byte()

    'Create an empty stream in memory.
    Using stream As New IO.MemoryStream
        'Fill the stream with the binary data from the Image.
        image.Save(stream, Imaging.ImageFormat.Jpeg)

        'Get an array of Bytes from the stream.
        data = stream.GetBuffer()
    End Using

    Return data
End Function

 

[Mazhar]

That code has a number of issues. You call AddWithValue and then set the Value. How does that make sense. It's an either/or choice: either call AdWithValue and provide the value as an argument or call Add and set the Value explicitly. You don't do both.

Also, you can't call AddWithValue if you have no value to add - NULL is specifically no value - so if you want to use NULL then you have to use Add, not AddWithValue. AddWithValue will infer the data type from the value but, if there's no value, then no type can be inferred.

Finally, you can't provide an actual Image as a value (as far as I'm aware, unless something's changed). Images are stored as binary data so you need to convert your Image to a Byte array first and then use that array as the value. Overall, your code might look like this:

Dim value As Object = If(ProductPicture.Image Is Nothing, CObj(DBNull.Value), GetImageData(ProductPicture.Image))

cmd.Parameters.Add("@Picture", SqlDbType.VarBinary, -1).Value = value

In that code, the -1 argument passed to Add indicates that the column's data type is varbinary(max). If you have created that column with a specific size rather than MAX, use that number instead of -1. The GetImageData method, which takes an Image and returns a Byte array, could be implemented like this:

Private Function GetImageData(image As Image) As Byte()
    Dim data As Byte()

    'Create an empty stream in memory.
    Using stream As New IO.MemoryStream
        'Fill the stream with the binary data from the Image.
        image.Save(stream, Imaging.ImageFormat.Jpeg)

        'Get an array of Bytes from the stream.
        data = stream.GetBuffer()
    End Using

    Return data
End Function

Dear Thank you very much for corrections and information as well. You code works very fine and as desired. Once again thank u very much for ur help.

 

[jmcilhinney]

One point of correction, this:

data = stream.GetBuffer()

should be this:

data = stream.ToArray()