.net - VB.net - insert/retrieve picture from mysql Database directly to/from a Picturebox

Question

I'm am having a heck of a time finding a code snippet that works for this. I have got to the point where it appears the picture is stored as a blob (perhaps incorrectly) by using this code.

Dim filename As String = txtName.Text + ".jpg"
Dim FileSize As UInt32
Dim ImageStream As System.IO.MemoryStream

ImageStream = New System.IO.MemoryStream
PbPicture.Image.Save(ImageStream, System.Drawing.Imaging.ImageFormat.Jpeg)
ReDim rawdata(CInt(ImageStream.Length - 1))
ImageStream.Position = 0
ImageStream.Read(rawdata, 0, CInt(ImageStream.Length))
FileSize = ImageStream.Length

Dim query As String = ("insert into actors (actor_pic, filename, filesize) VALUES    (?File, ?FileName, ?FileSize)")
cmd = New MySqlCommand(query, conn)
cmd.Parameters.AddWithValue("?FileName", filename)
cmd.Parameters.AddWithValue("?FileSize", FileSize)
cmd.Parameters.AddWithValue("?File", rawData)

cmd.ExecuteNonQuery()

MessageBox.Show("File Inserted into database successfully!", _
"Success!", MessageBoxButtons.OK, MessageBoxIcon.Asterisk)

but on retrieving to the picturebox using the following code:

Private Sub GetPicture()
    'This retrieves the pictures from a mysql DB and buffers the rawdata into a memorystream 

    Dim FileSize As UInt32
    Dim rawData() As Byte
    Dim conn As New MySqlConnection(connStr)

    conn.Open()
    conn.ChangeDatabase("psdb")

    Dim cmd As New MySqlCommand("SELECT actor_pic, filesize, filename FROM actors WHERE actor_name = ?autoid", conn)
    Cmd.Parameters.AddWithValue("?autoid", Actor1Box.Text)

    Reader = cmd.ExecuteReader
    Reader.Read()

    'data is in memory 

    FileSize = Reader.GetUInt32(Reader.GetOrdinal("filesize"))
    rawData = New Byte(FileSize) {}

    'get the bytes and filesize 
    Reader.GetBytes(Reader.GetOrdinal("actor_pic"), 0, rawData, 0, FileSize)

    Dim ad As New System.IO.MemoryStream(100000)
    ' Dim bm As New Bitmap

    ad.Write(rawData, 0, FileSize)

    Dim im As Image = Image.FromStream(ad) * "error occurs here" (see below)
    Actor1Pic.Image = im

    Reader.Close()
    conn.Close()
    conn.Dispose()
    ad.Dispose()

I get the error "parameter not valid" in the area noted. FYI If anyone even has some better (working) code examples than this that I can plug in versus debugging this mess that would be great too.

Answer 1

Well since getting no help i bashed away at the problem and got it to work finally. Here is my working code.

SAVE TO MySQL out of Picturebox (pbPicture)

    Dim filename As String = txtName.Text + ".jpg"
    Dim FileSize As UInt32

    conn.Close()

    Dim mstream As New System.IO.MemoryStream()
    PbPicture.Image.Save(mstream, System.Drawing.Imaging.ImageFormat.Jpeg)
    Dim arrImage() As Byte = mstream.GetBuffer()

    FileSize = mstream.Length
    Dim sqlcmd As New MySqlCommand
    Dim sql As String
    mstream.Close()

    sql = "insert into [your table]  (picture, filename, filesize) 
                               VALUES(@File, @FileName, @FileSize)"

    Try
        conn.Open()
        With sqlcmd
            .CommandText = sql
            .Connection = conn
            .Parameters.AddWithValue("@FileName", filename)
            .Parameters.AddWithValue("@FileSize", FileSize)
            .Parameters.AddWithValue("@File", arrImage)

            .ExecuteNonQuery()
        End With
    Catch ex As Exception
        MsgBox(ex.Message)
    Finally
        conn.Close()
    End Try

LOAD from MySQL db Back to Picturebox

   Dim adapter As New MySqlDataAdapter
    adapter.SelectCommand = Cmd

    data = New DataTable

    adapter = New MySqlDataAdapter("select picture from [yourtable]", conn)

NOTE!! can only put once picture in picturebox so obvoiusly this query can only return one record for you

    commandbuild = New MySqlCommandBuilder(adapter)
    adapter.Fill(data)

    Dim lb() As Byte = data.Rows(0).Item("picture")
    Dim lstr As New System.IO.MemoryStream(lb)
    PbPicture.Image = Image.FromStream(lstr)
    PbPicture.SizeMode = PictureBoxSizeMode.StretchImage
    lstr.Close()