Saving Drawing on PictureBox to SQL Database - Page 2

[Teme64]

I made a mistake in previous post. The code should have been

' Loads the image to the picture box control from SQL Server
Dim ImageByteArr(0) As Byte ' An array to hold image (bytes)
Dim patientIC As String ' Patient's IC for Image

' Get ID from a text box control and remove spaces
patientIC = txtId.Text.Trim
' Load an array of bytes from SQL Server's field of type Image
LoadByteArray(ImageByteArr, patientIC)
If ImageByteArr.Length <= 1 Then
  MsgBox("Empty byte array! patientIC='" & patientIC & "' might not exist?")
End If
' Convert an array of bytes to image
Dim TempImage As Image = Nothing
Byte2Image(TempImage, ImageByteArr)
If TempImage IsNot Nothing Then
  pbBody.Image = TempImage
Else
  MsgBox("Image is nothing!")
End If

Code line 9 changes a bit. Do you get now "Empty byte array..." message?

Two possibilities left. The ID patientIC is still not found. Or the saving of the image fails in the first place.

If you view the DB data in SQL Server Management Studio, it shows <Binary data> in the Image type field, but it may not be valid data. You could try to execute following SQL statement in Management Studio

SELECT [ImageID], [BlobField]
  FROM [ImageTable]
WHERE [ImageID]='patient101'

(just replace correct field and table names and ID value). You should now see if there's actually data in Image field:

ImageID BlobField
patient101 0xFFD8FFE000104A46494600010101006...

Image data is dumped in hex format but you'll see that there's actually some data in the field.

Another (and possible a better) way is to execute query

SELECT DATALENGTH([BlobField]) AS DataLen
  FROM [ImageTable]
WHERE [ImageID]='patient101'

I got the result

DataLen
2319

for an image. This is a value you can compare to the length of data you're originally saving. You had (my variable names, sorry) Image2Byte(Form2.BitmapCanvas, ImageByteArr) in your code. Write after that line MsgBox(ImageByteArr.Length) and compare those two lengths, they should be the same.

I hope this didn't get too complicated :) There's some small bug left in the code, but it's hard to catch.

[DAWNIE]

If ImageByteArr.Length < 1 Then

to

If ImageByteArr.Length <= 1 Then

after editing the above code, i did receive the "Empty byte array..." message.

but if the id is not exist, then how could i retrieve the other data and not the image whereby they are in the same DB jus different table only?

Image2Byte(Form2.BitmapCanvas, ImageByteArr)

I didnt manage to find the above code in my codes... i only found

Public Sub Image2Byte(ByVal NewImage As Image, ByRef ByteArr() As Byte)

and i had try the excute SQL statement in SQL Studio and the result I get is:

patientIC image
S7654321W 0x00

and for datalen

DataLen
1

doesnt that means my image is an invalid 1?

yaya, the bug is real hard to solve but hopefully is not a bug that due to my careless if not i will gonna go bang the wall =)

[a.j.]

Can someone please tell me what program to use...i want to draw up logos for a club in school. i dont even know how i am going to go about this. please help!!!!

[Teme64]

and i had try the excute SQL statement in SQL Studio and the result I get is:
patientIC image
S7654321W 0x00
and for datalen
DataLen
1
doesnt that means my image is an invalid 1?

Yes, the image isn't saved correctly. Did you use adatapost's code (Post #9 in Saving Drawing on PictureBox to SQL Database) for saving the image?

I slightly modified the "save image to DB" code

'Memory stream        
Dim ms As New System.IO.MemoryStream
'Write an image data into memory stream        
DrawingForm.BitmapCanvas.Save(ms, System.Drawing.Imaging.ImageFormat.Jpeg)

'Byte array        
Dim b() As Byte = ms.ToArray
Dim cn As New System.Data.SqlClient.SqlConnection(m_CnStr) ' <-- your connection string goes here
'Parameterized Query        
Dim cmd As New SqlClient.SqlCommand("insert into image (patientIC,picture) values (@p1,@p2)", cn) ' <-- your table/field names go here
'Create/add parameters
Dim oVarCharParam As SqlParameter = New SqlParameter("p1", SqlDbType.VarChar, 50, ParameterDirection.Input.ToString)
oVarCharParam.Value = "image01"  ' lblId.Text, I use a hard coded ID here for debugging
cmd.Parameters.Add(oVarCharParam)
'Create/add parameters
Dim oBLOBParam As SqlParameter = New SqlParameter("@p2", SqlDbType.Binary, b.Length, ParameterDirection.Input.ToString)
oBLOBParam.Value = b
cmd.Parameters.Add(oBLOBParam)

'Execute command        
cn.Open()
cmd.ExecuteNonQuery()

cn.Close() ' <-- close the connection unless you need it

to ensure that ParameterDirection is correct.

You should also check all your code, what System.Drawing.Imaging.ImageFormat enumeration value you use with images. It shouldn't make any difference what image format you use, but you have to use the same format (like Jpeg) for both saving and retrieving the image from the DB. If you used the original posting with Png format and later tried to get image back with my code (Jpeg format), that didn't cause an empty image to be saved.

You could also put MsgBox(b.Length) to the code above ("between" lines 7 and 8) to check the original image size. You should get the same value with SQL statement (from my previous posting) containing DataLen function after you've saved a new image.

[DAWNIE]

i didnt put that codes cos i thought this codes already saving the image to DB

Public Sub Image2Byte(ByVal NewImage As Image, ByRef ByteArr() As Byte)
        '
        Dim ImageStream As MemoryStream

        Try
            ReDim ByteArr(0)
            If NewImage IsNot Nothing Then
                ImageStream = New MemoryStream
                NewImage.Save(ImageStream, ImageFormat.Jpeg)
                ReDim ByteArr(CInt(ImageStream.Length - 1))
                ImageStream.Position = 0
                ImageStream.Read(ByteArr, 0, CInt(ImageStream.Length))
            End If
        Catch ex As Exception

        End Try

    End Sub

    Public Sub SaveByteArray(ByVal ByteArr() As Byte, ByVal patientIC As String)
        '
        Dim mySQL As String
        Dim cmdAdd4 As SqlCommand
        Dim oVarCharParam As SqlParameter
        Dim oBLOBParam As SqlParameter

        Try
            ' Create and open connection object
            Dim connAdd4 As New SqlConnection _
                ("Data Source=DATASOURCE;" + "Initial Catalog=DBNAME;" + "User ID=UID;" + "Password=PW;")
            connAdd4.Open()
            ' Insert statement
            ' Notice that @BLOBValue is a placeholder for the actual data
            mySQL = "INSERT INTO image (patientIC, picture) VALUES (@IDValue, @BLOBValue)"
            ' Create a command object
            cmdAdd4 = connAdd4.CreateCommand()
            ' Set SQL statement
            cmdAdd4.CommandText = mySQL
            ' Create a command parameter
            oVarCharParam = New SqlParameter("@IDValue", SqlDbType.VarChar, _
               50, ParameterDirection.Input.ToString)
            ' Set the actual data
            oVarCharParam.Value = patientIC
            ' Add this parameter to the command
            cmdAdd4.Parameters.Add(oVarCharParam)
            ' Create a command parameter
            oBLOBParam = New SqlParameter("@BLOBValue", SqlDbType.Binary, _
              ByteArr.Length, ParameterDirection.Input.ToString)
            ' Finally, set the actual data
            oBLOBParam.Value = ByteArr
            ' Add this parameter to the command
            cmdAdd4.Parameters.Add(oBLOBParam)
            ' Execute SQL statement
            cmdAdd4.ExecuteNonQuery()
            ' Close the connection
            connAdd4.Close()
        Catch ex As Exception

        End Try

    End Sub

m i wrong?

[Teme64]

m i wrong?

No. Just more debugging needed.

Here's the same code with four messagebox "dumps" added

Public Sub Image2Byte(ByVal NewImage As Image, ByRef ByteArr() As Byte)
    '
    Dim ImageStream As MemoryStream

    Try
        ReDim ByteArr(0)
        If NewImage IsNot Nothing Then
            ImageStream = New MemoryStream
            NewImage.Save(ImageStream, ImageFormat.Jpeg)
            ReDim ByteArr(CInt(ImageStream.Length - 1))
            ImageStream.Position = 0
            ImageStream.Read(ByteArr, 0, CInt(ImageStream.Length))
            MsgBox(ByteArr.Length) ' DEBUG
        End If
    Catch ex As Exception
	MsgBox(ex.Message) ' DEBUG
    End Try

End Sub

Public Sub SaveByteArray(ByVal ByteArr() As Byte, ByVal patientIC As String)
    '
    Dim mySQL As String
    Dim cmdAdd4 As SqlCommand
    Dim oVarCharParam As SqlParameter
    Dim oBLOBParam As SqlParameter

    Try
        MsgBox(ByteArr.Length) ' DEBUG
        ' Create and open connection object
        Dim connAdd4 As New SqlConnection _
            ("Data Source=DATASOURCE;" + "Initial Catalog=DBNAME;" + "User ID=UID;" + "Password=PW;")
        connAdd4.Open()
        ' Insert statement
        ' Notice that @BLOBValue is a placeholder for the actual data
        mySQL = "INSERT INTO image (patientIC, picture) VALUES (@IDValue, @BLOBValue)"
        ' Create a command object
        cmdAdd4 = connAdd4.CreateCommand()
        ' Set SQL statement
        cmdAdd4.CommandText = mySQL
        ' Create a command parameter
        oVarCharParam = New SqlParameter("@IDValue", SqlDbType.VarChar, _
           50, ParameterDirection.Input.ToString)
        ' Set the actual data
        oVarCharParam.Value = patientIC
        ' Add this parameter to the command
        cmdAdd4.Parameters.Add(oVarCharParam)
        ' Create a command parameter
        oBLOBParam = New SqlParameter("@BLOBValue", SqlDbType.Binary, _
          ByteArr.Length, ParameterDirection.Input.ToString)
        ' Finally, set the actual data
        oBLOBParam.Value = ByteArr
        ' Add this parameter to the command
        cmdAdd4.Parameters.Add(oBLOBParam)
        ' Execute SQL statement
        cmdAdd4.ExecuteNonQuery()
        ' Close the connection
        connAdd4.Close()
    Catch ex As Exception
	MsgBox(ex.Message) ' DEBUG
    End Try

End Sub

When you save the image, you should get the image size twice. If you get an error message (from MsgBox(ex.Message)) then post the error message.

Image2Byte(Form2.BitmapCanvas, ImageByteArr)
I didnt manage to find the above code in my codes... i only found
Public Sub Image2Byte(ByVal NewImage As Image, ByRef ByteArr() As Byte)

You should have a call like Image2Byte(DrawingForm.BitmapCanvas, ImageByteArr) followed by SaveByteArray(ImageByteArr, patientIC) If you don't have (notice that the actual parameter names may differ a bit), check that you do call them after you return from your DrawingForm back to main form and you haven't disposed DrawingForm yet.

The subroutines themselves are ok and your drawing form's code should be ok. You either didn't save the image buffer (i.e. DrawingForm.BitmapCanvas) in the first place or something goes wrong when you try to save a byte array to image field :-/

My original sample code had "empty" exception handlers

Catch ex As Exception

End Try

but you should at least put a breakpoint or MsgBox

Catch ex As Exception
  MsgBox(ex.Message) ' DEBUG
End Try

to see if something does go wrong.

[DAWNIE]

after putting the four msgbox, i have a popup box. In the popup box, there is number "1" or "ok" button. does that means the datalen?

when i retrieve, it still prompt mi ""Empty byte array..." message and "image is nothing" kind of prompt. Other data appear except images.

You should have a call like
Image2Byte(DrawingForm.BitmapCanvas, ImageByteArr)
followed by
SaveByteArray(ImageByteArr, patientIC)

the above "ImageByteArr" is in another form so how can i input the codes to saving form whereby the ImageByteArr will not have a curly line under it?

[Teme64]

the above "ImageByteArr" is in another form so how can i input the codes to saving form whereby the ImageByteArr will not have a curly line under it?

Add declaration

Dim ImageByteArr(0) As Byte ' An array to hold image (bytes)

right before calling

Image2Byte(DrawingForm.BitmapCanvas, ImageByteArr)
' and after that
SaveByteArray(ImageByteArr, patientIC)

After declaring that byte array, could you change MsgBox(ByteArr.Length) ' DEBUG to MsgBox("Image2Byte " & ByteArr.Length) ' DEBUG in Image2Byte procedure and MsgBox(ByteArr.Length) ' DEBUG to MsgBox("SaveByteArray " & ByteArr.Length) ' DEBUG in SaveByteArray procedure, so that it would be clear at which point byte array remains or gets empty.

Also, change in Image2Byte procedure

If NewImage IsNot Nothing Then
  ImageStream = New MemoryStream
  NewImage.Save(ImageStream, ImageFormat.Jpeg)
  ReDim ByteArr(CInt(ImageStream.Length - 1))
  ImageStream.Position = 0
  ImageStream.Read(ByteArr, 0, CInt(ImageStream.Length))
  MsgBox(ByteArr.Length) ' DEBUG
Else ' <-- ADD THIS ELSE PART TO TRAP MISSING IMAGE PARAM
  MsgBox("Image is nothing") ' DEBUG
End If

this will prompt if you call it with a non-existing image. If you do get "Image is nothing" prompt, your DrawingForm.BitmapCanvas is empty (i.e. Nothing) at the point when you call this procedure.

In the popup box, there is number "1" or "ok" button. does that means the datalen?

Yes. Actually this is length of the byte array but it should be same as the final data length in the DB.

when i retrieve, it still prompt mi ""Empty byte array..." message and "image is nothing" kind of prompt. Other data appear except images.

There's still some problem in saving the image. Once that is solved, you'll get the image too (I keep my fingers crossed).

[DAWNIE]

i've attached my printscreen for my error...

the codes that i hav edit according to ur instruction is so far like that:

''' <summary>
    ''' Convert an image to array of bytes
    ''' </summary>
    ''' <param name="NewImage">Image to be converted</param>
    ''' <param name="ByteArr">Returns bytes</param>
    ''' <remarks></remarks>
    ''' 
    Public Sub Image2Byte(ByVal NewImage As Image, ByRef ByteArr() As Byte)

        Dim ImageByteArr(0) As Byte ' An array to hold image (bytes)
        Dim patientIC As String ' ID for the image

        Image2Byte(Drawing.BitmapCanvas, ImageByteArr)

        SaveByteArray(ImageByteArr, patientIC)

        Dim ImageStream As MemoryStream

        Try
            If NewImage IsNot Nothing Then
                ImageStream = New MemoryStream
                NewImage.Save(ImageStream, ImageFormat.Jpeg)
                ReDim ByteArr(CInt(ImageStream.Length - 1))
                ImageStream.Position = 0
                ImageStream.Read(ByteArr, 0, CInt(ImageStream.Length))
                MsgBox("Image2Byte " & ByteArr.Length) ' DEBUG
            Else
                MsgBox("Image is nothing") 'DEBUG
            End If
        Catch ex As Exception
            MsgBox(ex.Message) ' DEBUG
        End Try

    End Sub

    ''' <summary>
    ''' Save a byte array to database
    ''' </summary>
    ''' <param name="ByteArr">Contains bytes to be saved</param>
    ''' <param name="patientIC">ID for the image</param>
    ''' <remarks></remarks>
    Public Sub SaveByteArray(ByVal ByteArr() As Byte, ByVal patientIC As String)
        '
        Dim mySQL As String
        Dim cmdAdd4 As SqlCommand
        Dim oVarCharParam As SqlParameter
        Dim oBLOBParam As SqlParameter

        Try
            MsgBox("SaveByteArray " & ByteArr.Length) ' DEBUG
            ' Create and open connection object
            Dim connAdd4 As New SqlConnection _
                ("Data Source=DATASOURCE;" + "Initial Catalog=DBNAME;" + "User ID=UID;" + "Password=PW;")
            connAdd4.Open()
            ' Insert statement
            ' Notice that @BLOBValue is a placeholder for the actual data
            mySQL = "INSERT INTO image (patientIC, picture) VALUES (@IDValue, @BLOBValue)"
            ' Create a command object
            cmdAdd4 = connAdd4.CreateCommand()
            ' Set SQL statement
            cmdAdd4.CommandText = mySQL
            ' Create a command parameter
            oVarCharParam = New SqlParameter("@IDValue", SqlDbType.VarChar, _
               50, ParameterDirection.Input.ToString)
            ' Set the actual data
            oVarCharParam.Value = patientIC
            ' Add this parameter to the command
            cmdAdd4.Parameters.Add(oVarCharParam)
            ' Create a command parameter
            oBLOBParam = New SqlParameter("@BLOBValue", SqlDbType.Binary, _
              ByteArr.Length, ParameterDirection.Input.ToString)
            ' Finally, set the actual data
            oBLOBParam.Value = ByteArr
            ' Add this parameter to the command
            cmdAdd4.Parameters.Add(oBLOBParam)
            ' Execute SQL statement
            cmdAdd4.ExecuteNonQuery()
            ' Close the connection
            connAdd4.Close()
        Catch ex As Exception
            MsgBox(ex.Message) ' DEBUG
        End Try

    End Sub

    Private Sub btnOpen_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnOpen.Click

        Dim ImageByteArr(0) As Byte ' An array to hold image (bytes)
        Dim patientIC As String ' ID for the image

        Drawing.ShowDialog() ' Draw the image first

        ' Get ID from a text box control and remove spaces
        patientIC = txtId.Text.Trim
        ' Convert image to an array of bytes
        Image2Byte(pbBody.Image, ImageByteArr)
        ' Save an array of bytes to SQL Server's field of type Image
        SaveByteArray(ImageByteArr, patientIC)

    End Sub

but y cant i dim patientIC As String on the Image2Byte procedure? whereby i'm able to dim it as String in OPEN to drawingform prodecure?

the error i received for dimming patientIC as String is "Variable 'patientIC' is used before it has been assigned a value. A null reference exception could result at runtime"

should i dim it as integer instead?

[DAWNIE]

Teme64, I have solve the problem regarding my codes... it becos of 1 typo error...

Private Sub btnOpen_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnOpen.Click

        ' Saves the image from the bitmap buffer to SQL Server
        Dim ImageByteArr(0) As Byte ' An array to hold image (bytes)
        Dim patientIC As String ' ID for the image

        Drawing.ShowDialog() ' Draw the image first

        ' Get ID from a text box control and remove spaces
        patientIC = lblId.Text.Trim
        ' Convert image to an array of bytes
        ' USE FORM2 BITMAP, NOT PICTURE BOX CONTROL
        Image2Byte(Drawing.BitmapCanvas, ImageByteArr)
        ' Save an array of bytes to SQL Server's field of type Image
        SaveByteArray(ImageByteArr, patientIC)

    End Sub

the sentence that is red in colour is what it shld be... but that time i went to type [ Image2Byte(pbBody.Image, ImageByteArr)]..

sry for my typo error that time =)