Save and Retrieve Image From Database Using VB.Net and MySQL

0
5172

This article Save and Retrieve Image From Database Using VB.Net and MySQL is a continuation of our last topic called Save and Retrieve an Image in a Database Using Vb.Net, but this time we will be using MySQL Database to store our data.  To do this application, follow the steps below:

Step 1: Create a Database in PHPMyAdmin

  • Database Name:  studentdb
  • then execute this query to create a new table tblstudent
[sql]

CREATE TABLE tblstudent (
studID int(11) NOT NULL,
FNAME varchar(30) NOT NULL,
LNAME varchar(30) NOT NULL,
COURSE varchar(30) NOT NULL,
YR varchar(30) NOT NULL,
AGE varchar(30) NOT NULL,
EMAIL varchar(30) NOT NULL,
STUDIMG longblob NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

[/sql]

Step 2: Create Visual Basic Project:

  • Create and Save it as “Saveandrettriveimg”
  • Design the form like as shown below.

Step 3: Adding Code:

  • Double the form, then Add the following code under “Public Class”
[vbnet]

Dim conn As New MySqlConnection
Dim Myconnection As String = “server=localhost;user id=root;password=;database=studentdb”
Dim cmd As New MySqlCommand
Dim da As New MySqlDataAdapter
Dim result As Integer
Dim imgpath As String
Dim arrImage() As Byte
Dim sql As String

[/vbnet]

 

For “Browse Image” Button, Under Click Event:

 

[vbnet]

Try
Dim OFD As FileDialog = New OpenFileDialog()

OFD.Filter = “Image File (*.jpg;*.bmp;*.gif)|*.jpg;*.bmp;*.gif”

If OFD.ShowDialog() = DialogResult.OK Then
imgpath = OFD.FileName
Pic1.ImageLocation = imgpath

End If

OFD = Nothing

Catch ex As Exception
MsgBox(ex.Message.ToString())
End Try

[/vbnet]

 

For “Save” Button, Under Click Event:

[vbnet]

Try

Dim mstream As New System.IO.MemoryStream()
Pic1.Image.Save(mstream, System.Drawing.Imaging.ImageFormat.Jpeg)
arrImage = mstream.GetBuffer()
Dim FileSize As UInt32
FileSize = mstream.Length

mstream.Close()
conn.ConnectionString = Myconnection
conn.Open()
sql = “INSERT INTO tblstudent(FNAME,LNAME, COURSE, YR, AGE, EMAIL,studimg) VALUES (@FNAME, @LNAME, @COURSE, @YR, @AGE, @EMAIL,@studimg)”
cmd.Connection = conn
cmd.CommandText = sql
cmd.Parameters.AddWithValue(“@FNAME”, TXTFIRSTNAME.Text)
cmd.Parameters.AddWithValue(“@LNAME”, TXTLNAME.Text)
cmd.Parameters.AddWithValue(“@COURSE”, TXTCOURSE.Text)
cmd.Parameters.AddWithValue(“@YR”, TXTYR.Text)
cmd.Parameters.AddWithValue(“@AGE”, TXTAGE.Text)
cmd.Parameters.AddWithValue(“@EMAIL”, TXTEMAIL.Text)
cmd.Parameters.AddWithValue(“@studimg”, arrImage)
Dim r As Integer
r = cmd.ExecuteNonQuery()
If r > 0 Then
MsgBox(“Student Record hass been Saved!”)
Else
MsgBox(“No record has been saved!”)
End If
cmd.Parameters.Clear()
conn.Close()
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try

[/vbnet]

For “Search” Button, Under Click Event:

[vbnet]

sql = “Select * from tblstudent where studID=” & Val(TextBox1.Text)
conn.ConnectionString = Myconnection
conn.Open()
With cmd
.Connection = conn
.CommandText = sql
End With
Dim arrImage() As Byte
Dim publictable As New DataTable
Try
da.SelectCommand = cmd
da.Fill(publictable)
TXTLNAME.Text = publictable.Rows(0).Item(2)
TXTFIRSTNAME.Text = publictable.Rows(0).Item(1)
TXTCOURSE.Text = publictable.Rows(0).Item(3)
TXTYR.Text = publictable.Rows(0).Item(4)
TXTAGE.Text = publictable.Rows(0).Item(5)
TXTEMAIL.Text = publictable.Rows(0).Item(6)
arrImage = publictable.Rows(0).Item(7)
Dim mstream As New System.IO.MemoryStream(arrImage)
Pic1.Image = Image.FromStream(mstream)
Catch ex As Exception
MsgBox(ex.Message)
Finally
da.Dispose()

conn.Close()

End Try

[/vbnet]

Step 4: Run Project

  • Press “F5”

You can Download the Save and Retrieve Image From Database Using VB.Net and MySQL full Source Codesaveandretriveimagemysql

 

If you find this tutorial helpful please share this to your friends and to help us grow please hit the LIKE button in our Facebook Page. Thank You.

 

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.