how to store binary data in mysql using vb.net?

Question

am building a simple program that helps one browse for an image and save it in a mysql database. Am having big trouble in saving a picture in vb.net, kindly appreciate your assistance...

Imports System.IO
Public Class frmSalesManager

Private Sub frmSalesManager_Load(sender As Object, e As EventArgs) Handles MyBase.Load

End Sub

Private Sub btnBrowse_Click(sender As Object, e As EventArgs) Handles btnBrowse.Click
    OpenFileDialog1.Filter = "image file (*.jpg, *.bmp, *.png) | *.jpg; *.bmp; *.png| all files (*.*) | *.* "
    If OpenFileDialog1.ShowDialog <> Windows.Forms.DialogResult.Cancel Then
        PictureBox1.Image = Image.FromFile(OpenFileDialog1.FileName)
    End If
End Sub

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    Dim fsreader As New FileStream(OpenFileDialog1.FileName, FileMode.Open, FileAccess.Read)
    Dim breader As New BinaryReader(fsreader)
    Dim imgbuffer(fsreader.Length) As Byte
    breader.Read(imgbuffer, 0, fsreader.Length)
    fsreader.Close()
    strsql = "Insert into Test (name, picture) VALUES (@name, @picture)"
    acsmd.CommandText = strsql

    acsmd.Connection = con
    acsmd.Parameters.AddWithValue("@name", txtname.Text)
    acsmd.Parameters.AddWithValue("@picture", imgbuffer)
    'acsmd.ExecuteNonQuery()
    acsmd.Dispose()
    MsgBox("Saved")


End Sub

Private Sub OpenFileDialog1_FileOk(sender As Object, e As System.ComponentModel.CancelEventArgs) Handles OpenFileDialog1.FileOk
    If OpenFileDialog1.FileName <> Nothing Or OpenFileDialog1.FileName <> "" Then
        txtname.Text = OpenFileDialog1.FileName.Substring( _
OpenFileDialog1.FileName.LastIndexOf("\") + 1, _
(OpenFileDialog1.FileName.IndexOf(".", 0) - (OpenFileDialog1.FileName.LastIndexOf("\") + 1)))
        End If
    End Sub
End Class

possible duplicate of [How to store binary data into mysql](http://stackoverflow.com/questions/10979100/how-to-store-binary-data-into-mysql) — Andy, Aug 15 '14 at 03:00
possible duplicate of [Binary Data in MySQL](http://stackoverflow.com/questions/17/binary-data-in-mysql) — Peter O., Aug 18 '14 at 13:22

score 0 · Answer 1 · answered Feb 05 '14 at 05:47

I hope con is an Open connection...try this code for Button1_Click

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim imgbuffer() As Byte
        Using fsreader As New FileStream(OpenFileDialog1.FileName, FileMode.Open, FileAccess.Read)
            ReDim imgbuffer(fsreader.Length - 1)
            Using breader As New BinaryReader(fsreader)
                breader.Read(imgbuffer, 0, fsreader.Length)
            End Using
        End Using

        strsql = "Insert into Test (name, picture) VALUES (@name, @picture)"
        acsmd.CommandText = strsql

        acsmd.Connection = con
        acsmd.Parameters.AddWithValue("@name", txtname.Text)
        acsmd.Parameters.AddWithValue("@picture", imgbuffer)
        acsmd.ExecuteNonQuery()
        acsmd.Dispose()
        MsgBox("Saved")
    End Sub

score 0 · Answer 2 · edited Jun 20 '20 at 09:12

Binary data can be stored in a MySQL database in a BLOB field. A BLOB is a binary large object that can hold a variable amount of data.

Short pieces of binary data, such as password hashes, work very well by simply base64-encoding them and storing the resulting string as a VARCHAR
"Not-quite-binary" data, such as document snipplets with the occasional non-printable can be escaped and sored as a string
The BLOB datatype allows you to store arbitrary chunks of binary data, but I strongly recommend against using it: Store the data in a file, then store the path to the file in a String type. You gain nothing from storing binary data, that the DB doesn't "understand" in the DB.

Source

how to store binary data in mysql using vb.net?

2 Answers2