SQL-VB2010 DataType Problems with Images

[TsadokBlok]

Dear Experts,

I am building a program which needs to store an Image (any image type) in SQL Express 2008.

I am able to read, and insert images into SQL Datatable, but I can't seem to update my images.

The SQL DataTable contains columnname "picture" - Varbinary(Max)
I am using a Class that holds Public Values (like FirstName).
In this class 'IdentityPicture' and 'PictureDlgFileName' are declared as Image Type.

I am using a Class that contains all Db-Connectivity in code (Select, Insert and Update Commands) called 'PersonDb'.

In my presentation layer, I placed the retrieved image in a Picturebox as BackgroundImage and I use a .png from my 'My.Resources' when the cell in SQL Column 'Picture' is DBNull.

What I am looking for is the proper SQL Update Command.

Please find below the code that I use:

ClassDb (Database Connectivity Class)
Retrieving and Handling the image after SQL Select Command

If drPerson("Picture") Is DBNull.Value Then
                Dim img As Image = My.Resources.anonymous
                person.IdentityPicture = img.GetThumbnailImage(150, 150, Nothing, Nothing)
            Else
                Dim PictureDatatable As New DataTable()
                PictureDatatable.Load(drPerson)
                Dim row As DataRow = PictureDatatable.Rows(0)
                Using ms As New IO.MemoryStream(CType(row("Picture"), Byte()))
                    Dim img As Image = Image.FromStream(ms)
                    person.IdentityPicture = img.GetThumbnailImage(150, 150, Nothing, Nothing)
                End Using
            End If

SQL InsertCommand and transforming of Picture
Reads the Image (PictureDlgFileName) from class 'Person' after a FileOK from the OpenFileDialog then uses .addwithvalue parameter to insert the image into SQL Data column named 'Picture'. This works fine too.

Dim cmdPerson As New SqlCommand(sInsertCommand, conDb)
        With cmdPerson.Parameters
            .AddWithValue("@Picture", IO.File.ReadAllBytes(Person.PictureDlgFileName))
        End With
        conDb.Open()
        cmdPerson.ExecuteNonQuery()
        cmdPerson.CommandText = "SELECT @@IDENTITY"

SQL Update Command (not working)!

Dim sUpdateCommand As String = "UPDATE Persons SET Picture = @Picture WHERE PersonId = @PersonId AND Picture = @OldPicture
        Dim cmdPerson As New SqlCommand(sUpdateCommand, conDb)
        With cmdPerson.Parameters
            'Code ommited for other fields	
            .AddWithValue("Picture", IO.File.ReadAllBytes(NewPerson.IdentityPicture))
            .AddWithValue("OldPicture", IO.File.ReadAllBytes(OldPerson.IdentityPicture))
        End With

The IO.File.ReadAllBytes(OldPerson.IdentityPicture) is firing up an error telling me that:

An unhandled exception of type 'System.InvalidCastException' occurred in Microsoft.VisualBasic.dll
Additional information: Conversion from type 'Bitmap' to type 'String' is not valid.

PresentationLayer Code launching Validation and UPDATE

Form Data Validation and Launching Update or Insert Commands:

If ValidData() Then 'Validates and saves all form Controls.
            If Me.NewPerson Then
                Me.SetPersonFields(Person)
                Person.PersonId = PersonDb.InsertPerson(Person)
                Me.Close()
            Else
                Dim NewPerson As New Person()
                Me.SetPersonFields(NewPerson)
                If PersonDb.UpdatePerson(NewPerson, Person) Then
                    Person = NewPerson
                    Me.Close()
                End If
            End If
        End If

Private Sub SetPersonFields(ByVal Pers As Person) 'Note: Save Person
        If Person Is Nothing Then
            Dim AddPers As New Person
           If pbCusPicture.BackgroundImage Is Nothing Then
                AddPers.IdentityPicture = My.Resources.anonymous
            Else
                AddPers.IdentityPicture = pbCusPicture.BackgroundImage
            End If
            Pers = AddPers
            AddPers = Nothing
        Else
            If pbCusPicture.BackgroundImage Is Nothing Then
                Pers.IdentityPicture = My.Resources.anonymous
            Else : Pers.IdentityPicture = pbCusPicture.BackgroundImage
            End If
   End Sub

Please help me - I am staring myself blind on this.
Thank you in advance.

[Teme64]

The SQL DataTable contains columnname "picture" - Varbinary(Max)
I am using a Class that holds Public Values (like FirstName).
In this class 'IdentityPicture' and 'PictureDlgFileName' are declared as Image Type.

I am staring myself blind on this

I can see that.

In Persons-table you have following fields:

PersonId INT IDENTITY(1,1)
Picture Varbinary(Max)

where the PersonId field is primary key. In your code you have Person class which has PersonId property to hold this unique identifier. When you update the Picture field, you need only this PersonId field to identify correct record to update.

Here's the code

Dim sUpdateCommand As String = "UPDATE Persons SET Picture = @Picture WHERE PersonId = @PersonId"
' AND Picture = @OldPicture <- drop this

Dim cmdPerson As New SqlCommand(sUpdateCommand, conDb)

With cmdPerson.Parameters
  'Code ommited for other fields
  .AddWithValue("@Picture", IO.File.ReadAllBytes(NewPerson.IdentityPicture))
  .AddWithValue("@PersonId", OldPerson.PersonId) ' <- PersonId value identifies correct record (OldPerson) and updates Picture (from NewPerson)
End With

HTH

[TsadokBlok]

Thanks Teme64 for your quick reply...

Normally my program works fine (all SQL Statements are executed properly), and I never have a problem with dataconversions etc. Yesterday I added all coding for a single picture and that's when the dataconversion problem started.

PersonId is not the problem it is properly handled as a parameter to locate the exact record and since the executenonquery is set to single row behaviour there is no confusion with other records either.

I just can't seem to locate where the system.drawing.image is being converted into string, and why it is at all attempting to do such a conversion. The exact line where the program hicks-up is at the command parameters line of the update statement:

.AddWithValue("Picture", IO.File.ReadAllBytes(NewPerson.IdentityPicture))

ALso removing the IO.File.ReadAllBytes is resulting in a mapping error (thus the problem is indeed here)
An unhandled exception of type 'System.ArgumentException' occurred in System.Data.dll

Additional information: No mapping exists from object type System.Drawing.Bitmap to a known managed provider native type.

Also replacing the code with:

.AddWithValue("Picture", SqlDbType.VarBinary).Value = (NewPerson.IdentityPicture)

give me the same mapping error.

[Teme64]

...and you have checked from the database schema that Picture field is Varbinary? And you're connecting to the right DB?

Here's a line from my snippet: .AddWithValue("@Picture", IO.File.ReadAllBytes(NewPerson.IdentityPicture)) IO.File.ReadAllBytes() always returns Byte() (or Nothing). Have you checked Person class's IdentityPicture property? That should be string i.e. name and path to a file? I guess you have something like:

Private _IdentityPicture As String
  Private _PictureData As Byte()

  Property IdentityPicture() As String
    '
    Get
      Return _IdentityPicture
    End Get
    Set(ByVal value As String)
      _IdentityPicture = value
    End Set

  End Property

  Property PictureData() As Byte()
    '
    Get
      Return _PictureData
    End Get
    Set(ByVal value As Byte())
      _PictureData = value
    End Set

  End Property

to store filename (a string!) and image data. Could it be possible that you have coded:

Private _IdentityPicture As Byte()
  Private _PictureData As Byte()

  Property IdentityPicture() As Byte()
    '
    Get
      Return _IdentityPicture
    End Get
    Set(ByVal value As Byte())
      _IdentityPicture = value
    End Set

  End Property

that would throw the exception.

Finally, try more debugging (catch that unhandled exception) to see what actually goes wrong:

Try
  .AddWithValue("@Picture", IO.File.ReadAllBytes(NewPerson.IdentityPicture))
Catch ex As Exception
  Dim ErrMsg As String
  ErrMsg = "Message: " & ex.Message & Environment.NewLine & _
           "StackTrace: " & ex.StackTrace & Environment.NewLine & _
           "InnerException: " & ex.InnerException.Message & Environment.NewLine
  ' Put breakpoint in here and dump (? ErrMsg) to Immediate Window and copy/paste from there the error message
  MessageBox.Show(ErrMsg)
End Try

and post that message here please.

[TsadokBlok]

...and you have checked from the database schema that Picture field is Varbinary? And you're connecting to the right DB?

Here's a line from my snippet: .AddWithValue("@Picture", IO.File.ReadAllBytes(NewPerson.IdentityPicture)) IO.File.ReadAllBytes() always returns Byte() (or Nothing). Have you checked Person class's IdentityPicture property? That should be string i.e. name and path to a file? I guess you have something like:

Private _IdentityPicture As String
  Private _PictureData As Byte()

  Property IdentityPicture() As String
    '
    Get
      Return _IdentityPicture
    End Get
    Set(ByVal value As String)
      _IdentityPicture = value
    End Set

  End Property

  Property PictureData() As Byte()
    '
    Get
      Return _PictureData
    End Get
    Set(ByVal value As Byte())
      _PictureData = value
    End Set

  End Property

to store filename (a string!) and image data. Could it be possible that you have coded:

Private _IdentityPicture As Byte()
  Private _PictureData As Byte()

  Property IdentityPicture() As Byte()
    '
    Get
      Return _IdentityPicture
    End Get
    Set(ByVal value As Byte())
      _IdentityPicture = value
    End Set

  End Property

that would throw the exception.

Finally, try more debugging (catch that unhandled exception) to see what actually goes wrong:

Try
  .AddWithValue("@Picture", IO.File.ReadAllBytes(NewPerson.IdentityPicture))
Catch ex As Exception
  Dim ErrMsg As String
  ErrMsg = "Message: " & ex.Message & Environment.NewLine & _
           "StackTrace: " & ex.StackTrace & Environment.NewLine & _
           "InnerException: " & ex.InnerException.Message & Environment.NewLine
  ' Put breakpoint in here and dump (? ErrMsg) to Immediate Window and copy/paste from there the error message
  MessageBox.Show(ErrMsg)
End Try

and post that message here please.

Hey,
I found the solution - (I changed the update handling and select handling).

Select Handling (after Select)

If drPerson("Picture") Is DBNull.Value Then
                Dim img As Image = My.Resources.anonymous
                person.IdentityPicture = img.GetThumbnailImage(150, 150, Nothing, Nothing)
            Else
                Dim btTempval As Byte() = CType(drPerson("picture"), Byte())
                Dim ms As New IO.MemoryStream(btTempval)
                Dim bitmap As New Bitmap(ms)
                person.IdentityPicture = bitmap
            End If

Update Handling
Before Parameters are added to the CmdStatement of the Dataadapter

Dim ms1 As IO.MemoryStream = New IO.MemoryStream()
        NewPerson.IdentityPicture.Save(ms1, System.Drawing.Imaging.ImageFormat.Jpeg)
        Dim OldData(ms1.Length - 1) As Byte
        ms1.Position = 0
        ms1.Read(OldData, 0, ms1.Length)

        Dim ms As IO.MemoryStream = New IO.MemoryStream()
        NewPerson.IdentityPicture.Save(ms, System.Drawing.Imaging.ImageFormat.Jpeg)
        Dim NewData(ms.Length - 1) As Byte
        ms.Position = 0
        ms.Read(NewData, 0, ms.Length)

Then the .addwithvalue parameters are using the NewData and Olddata

.AddWithValue("Picture", SqlDbType.VarBinary).Value = (NewData)
.AddWithValue("OldPicture", SqlDbType.VarBinary).Value = (OldData)

[ghdfans2010]

This is a great post. I like this topic.This site has lots of advantage. I found many interesting things from this site. It helps me many away..So i want some information for sharing this side with some of my friend. Thanks