• September 23, 2021

PHSARIT

Bring Technology Ideas with you

 How to insert image/picture into MySql Server Database using VB.net Programing

Here is the project, on MySQL Server Database which you can insert insert images easily. In order to know how to connect from VB.net Programming to MySQL Server Database, please visit our previous post how-to-connect-vb-net-with-mysql-server-database. In order to work with image or photo, in MySQL Database, you need to create this field with the datatype as A BLOB (binary large object) is a varying-length binary string to store the image.

    In MySQL Server Database, please create the table Name: staff_info in your own database as below image:

      Open Visual Studio, start design form as the image:

        There some controls: There are 9 TextBox Controls, 4 Combobox, 1 picture box, 1 OpenFileDialoque, 4 Buttons, 1 Datagridview ( you can design as you wishes). In order to work, you need to rename those controls as you want.

        In Button Clear, please double click and use this code below:

        Private Sub bntClear_Click(sender As Object, e As EventArgs) Handles bntClear.Click
                Me.txtstaffid.Text = ""
                Me.txtstaffname.Text = ""
                Me.cbGender.Text = ""
                Me.txtphone.Text = ""
                Me.txtbirth.Text = ""
                Me.txtexperience.Text = ""
                Me.txtplaceofbirth.Text = ""
                Me.txtcurrentad.Text = ""
                Me.txtsalary.Text = ""
                Me.txtsearchbyid.Text = ""
                Me.txtposition.Text = ""
                Me.cbcontract.Text = ""
                Me.cboEducation.Text = ""
                Me.cbstatus.Text = ""
                staff_image.Image = Nothing
            End Sub

        Over the public class of your Form project, please imports:

        Imports System.IO
        Imports System.Drawing.Image
        Imports System.Drawing.Imaging
        Imports MySql.Data.MySqlClient

        Then under the public class of your Form project, please declare:

        Dim cn As MySqlConnection
        Dim arraimage() As Byte

        To connect to MySQL Database, please use this code below:

        Sub ConnectionTomySQL()
                Dim conString As String = "server=127.0.0.1;userid=root;password=;database=staffdb"
                cn = New MySqlConnection(conString)
                Try
                    cn.Open()
                    Me.lbconnect.Text = "Connected"
                Catch ex As Exception
                    MessageBox.Show(ex.Message)
                End Try
            End Sub

        On the Save Button, please use this code:

        Private Sub bntSave_Click(sender As Object, e As EventArgs) Handles bntSave.Click
                Try
                    Dim msPhoto As New MemoryStream
                    staff_image.Image.Save(msPhoto, System.Drawing.Imaging.ImageFormat.Jpeg)
                    arraimage = msPhoto.GetBuffer
                    Dim fileSize As UInt32
                    fileSize = arraimage.Length
                    msPhoto.Close()
                    Dim InsertData As String = "Insert into staff_info(staffid,staffname,gender,dob,dop,phone,caddress,education,experience,mstatus,tcontract,position,bsalary,photo)VALUES(@staffid,@staffname,@gender,@dob,@dop,@phone,@caddress,@education,@experience,@mstatus,@tcontract,@position,@bsalary,@photo)"
                    Dim cmInsert As New MySqlCommand(InsertData, cn)
                    cmInsert.Parameters.AddWithValue("@staffid", Me.txtstaffid.Text)
                    cmInsert.Parameters.AddWithValue("@staffname", Me.txtstaffname.Text)
                    cmInsert.Parameters.AddWithValue("@gender", Me.cbGender.Text)
                    cmInsert.Parameters.AddWithValue("@dob", Me.txtbirth.Text)
                    cmInsert.Parameters.AddWithValue("@dop", Me.txtplaceofbirth.Text)
                    cmInsert.Parameters.AddWithValue("@phone", Me.txtphone.Text)
                    cmInsert.Parameters.AddWithValue("@caddress", Me.txtcurrentad.Text)
                    cmInsert.Parameters.AddWithValue("@education", Me.cboEducation.Text)
                    cmInsert.Parameters.AddWithValue("@experience", Me.txtexperience.Text)
                    cmInsert.Parameters.AddWithValue("@mstatus", Me.cbstatus.Text)
                    cmInsert.Parameters.AddWithValue("@tcontract", Me.cbcontract.Text)
                    cmInsert.Parameters.AddWithValue("@position", Me.txtposition.Text)
                    cmInsert.Parameters.AddWithValue("@bsalary", Me.txtsalary.Text)
                    cmInsert.Parameters.AddWithValue("@photo", arraimage)
                    cmInsert.ExecuteNonQuery()
                    MsgBox("Save Successfully")
                Catch ex As Exception
                    MessageBox.Show(ex.Message)
                End Try
            End Sub

        To brows the image in your local machine, please visit our previous post: Display image in Picturebox

        Run your project to try then see the image below:

          Go to MySQL Database Admin, then Click on Brows you will see the result as below:

            Related post