VB.NET Update BLOB in MySQL

Question

The code below that reads an image from the pic box into a memorystream and inserts it into a MySQL BLOB in the database, works perfectly. Can retrieve from the database and display in the pic box works perfectly (not shown). Just a side note, I didn’t write that code, it is from a tutorial on the web.

The bit I wrote to UPDATE the database does not work. I have tried many combinations of the brackets, single quotes, double quotes but no luck yet. I get varies error messages from the CATCH, sometimes in plain English referring to syntax and sometimes in a binary dump. When I do get a successful update message, all that is written into the BLOB is the name of what I am trying to update "VALUES(@image_data)". I have tried to UPDATE from both a memorystream and a file but no luck yet.

The “rem’d” code works perfectly for INSERT and I can manually update the BLOB but not practical. The BLOB would be updated for example when I upgrade a SD-DVD to BD-DVD, I will change the small logo.

Before I get flamed, I know not good practice to store the images but in this case, it is more practical I believe. The images are tiny 24x11 DVD logo’s that are read into a datagridview table, all my other images are stored as files on the server (1000’s of them) however, the test example is just reading a cover image.

Can anyone help me correct the code or suggest a better method? Thanks….

  Private Sub btnUpdate_Click(sender As System.Object, e As System.EventArgs) Handles btnUpdate.Click

    Dim FileSize As UInt32
    'temp for testing
    Dim carjackedfront As String = "8f17cd4a-8dd6-4ec1-9e7b-7f4d50460693"

    'get picture from database
    Dim nvcCover As String = carjackedfront

    'Dim original As Image = Image.FromFile("D:\Pics\ae.jpg")
    Dim original As Image = Image.FromFile(mediastorageCovers & nvcCover & pictureformat)

    Dim mstream As New System.IO.MemoryStream()

    '   -----this line saves image from picture box
    'pic_box_save.Image.Save(mstream, System.Drawing.Imaging.ImageFormat.Jpeg)

    '   -----This line saves image from file into memory stream
    original.Save(mstream, System.Drawing.Imaging.ImageFormat.Jpeg)


    Dim arrImage() As Byte = mstream.GetBuffer()

    FileSize = mstream.Length
    pic_box_get.Image = Image.FromStream(mstream)
    mstream.Close()
    MsgBox("File Size = " & FileSize)

    Try

        sql = "UPDATE image_in_db SET  Test = VALUES(@image_Text)  WHERE id = '1'"

        'sql = "INSERT INTO image_in_db(id, image_data) VALUES(@image_id, @image_data)"

        sql_command = New MySqlClient.MySqlCommand(sql, sql_connection)
        ' sql_command.Parameters.AddWithValue("@image_id", Nothing)
       sql_command.Parameters.AddWithValue("@image_data", arrImage)
        sql_command.ExecuteNonQuery()

    Catch ex As Exception
        MsgBox(ex.Message)
        Exit Sub
    End Try

    MsgBox("Image has been UPDATED.")


End Sub

" I get varies [various] error messages from the CATCH" - that's not helpful. — Mitch Wheat
– Mitch Wheat, Commented Sep 16, 2013 at 1:24

ron tornambe · Accepted Answer · 2013-09-16 03:12:29Z

1

UPDATE statements do not utilize a VALUES clause like INSERTa do. Your UPDATE statement should look something like the following:

...

sql = "UPDATE image_in_db SET image_data = @image_data WHERE id = @imaage_id"
sql_command.Parameters.AddWithValue("@image_id", 1)
sql_command.Parameters.AddWithValue("@image_data", arrImage)
sql_command.ExecuteNonQuery()

...

answered Sep 16, 2013 at 3:12

ron tornambe

10.8k8 gold badges38 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

user2760389 Over a year ago

Thanks for that but it returns error "Fatal error encountered during command execution"

Bala Over a year ago

@user2760389 Specify the datatype before adding the parameters

user2760389 Over a year ago

I amended to this but still fatal error:sql_command.Parameters.AddWithValue("@image_id", 1).MySqlDbType = MySqlClient.MySqlDbType.Int16 sql_command.Parameters.AddWithValue("@image_data", arrImage).MySqlDbType = MySqlClient.MySqlDbType.Blob

user2760389 Over a year ago

I tried this also but still fatal error:- sql_command.Parameters.Add("@image_data", MySqlClient.MySqlDbType.Blob, FileSize).Value = arrImage

user2760389 Over a year ago

My apologies gents...I copied Ron's fix verbatim and there was a slight error I didn't pick up (@imaage_id) should be @image_id. This works now with and without adding the data types. Thank you very much for you valued assistance.

Collectives™ on Stack Overflow

VB.NET Update BLOB in MySQL

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related