how to save a byte() to my SQLDatabase using VB.net

Question

I know that there are already a couple of questions which seems like my problem but I tried to copy a lot of it and tried but can’t get the resold I want. Actually is my target to save an image from a Picture Box as Byte() into mySQL Database. At first I tried the simple way like

    Dim acCust As Image = pgbox.image
    Dim picBytes() As Byte
    Using ms As New MemoryStream
        cust.Save(ms, cust.RawFormat)
        picBytes = ms.ToArray()
    End Using

but with this I run into a general GUI error so I copied the Picture at first like this

    Dim rec As New Rectangle(0, 0, acCust.Width, acCust.Height)
    Dim mus As New ImageFormatConverter
    Dim muster As New Bitmap(rec.Width, rec.Height)
    Dim cust As Image
    Using grp = Graphics.FromImage(muster)
        grp.DrawImage(acCust, New Rectangle(0, 0, rec.Width, rec.Height), rec, GraphicsUnit.Pixel)
        cust = muster
    End Using

If I use after that again the code

Dim picBytes() As Byte
    Using ms As New MemoryStream
        cust.Save(ms, cust.RawFormat)
        picBytes = ms.ToArray()
    End Using

sqlconection.Open()
    Dim query As String = "INSERT INTO tblimg(img) VALUES ('@image');"

    Dim com As New MySqlCommand(query, sqlconection)
    com.Parameters.AddWithValue("image", picBytes)

    com.ExecuteNonQuery()
    sqlconection.Close()

everything looks sofa ok but if I read it like this

Dim imgData As Byte()
    Dim sql As String = "SELECT img FROM tblimg"
    sqlconection.Open()
    Dim cmd As New MySqlCommand(sql, sqlconection)
    Using rdr As MySqlDataReader = cmd.ExecuteReader
        If rdr.HasRows Then
            rdr.Read()
            imgData = TryCast(rdr.Item("img"), Byte())
            ' in case this record has no image
            If imgData IsNot Nothing Then
                ' ToDo: dispose of any previous Image

                ' create memstream from bytes
                Using ms As New MemoryStream(imgData)
                    ' create image from stream, assign to PicBox
                    pbbox.Image = CType(Image.FromStream(ms), Image)

                End Using
            End If
        End If
    End Using

I got an alarm message

Eine nicht behandelte Ausnahme des Typs "System.ArgumentException" ist in System.Drawing.dll aufgetreten.

Zusätzliche Informationen: Ungültiger Parameter. (Sorry it's in German)

Has anybody an idea what and were it’s going wrong? If I use the the picByts () in a new memory stream and convert it back to an image then I got the correct result. It seems like there is a problem with the up or download query in my Server I use the variable type LongBlob

Does this answer your question? [how to insert image in mysql database using vb.net and adodb connection](https://stackoverflow.com/questions/24924982/how-to-insert-image-in-mysql-database-using-vb-net-and-adodb-connection) — TnTinMn, Jun 09 '20 at 17:39

score 1 · Accepted Answer · answered Jun 09 '20 at 15:36

1

You save a Byte array to a database in exactly the same way as you save any other data. You write your SQL, you add a parameter to a command and you set its value. This:

Dim query As String = "INSERT INTO tblimg(img) VALUES ('@image');"

Dim com As New MySqlCommand(query, sqlconection)
com.Parameters.AddWithValue("image", picBytes)

is wrong. You only wrap literal strings in single quotes in SQL code. That should be like this:

Dim query As String = "INSERT INTO tblimg(img) VALUES (@image);"

Dim com As New MySqlCommand(query, sqlconection)
com.Parameters.AddWithValue("@image", picBytes)

No single quotes around the parameter in the SQL and use the actual parameter name when adding it to the command. I'd also recommend using Add rather than AddWithValue but that's a discussion for another time.

answered Jun 09 '20 at 15:36

jmcilhinney

50,448
5
26
46

First of all thank you so much for your Help! I tried so much different ways but the only line which I copied every time was the String :-( . I finally got now data to my server and it looks like it’s ok because I use “Save value to file” after that I opened the file with notepad and it looks ok. Now after I got the data back and convert it to an Image, I thougt I only get a white Image until I made a break after pb.image = Image.fromStream() and its there but after I continue the image is removed. Why? Any Idea? – Park Jun 10 '20 at 12:24
Ok I found my problem I had a mistake in the way how I call the function – Park Jun 10 '20 at 12:43

how to save a byte() to my SQLDatabase using VB.net

1 Answers1