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

0
906

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
  1. CREATE TABLE <code>tblstudent</code> (
  2. <code>studID</code> int(11) NOT NULL,
  3. <code>FNAME</code> varchar(30) NOT NULL,
  4. <code>LNAME</code> varchar(30) NOT NULL,
  5. <code>COURSE</code> varchar(30) NOT NULL,
  6. <code>YR</code> varchar(30) NOT NULL,
  7. <code>AGE</code> varchar(30) NOT NULL,
  8. <code>EMAIL</code> varchar(30) NOT NULL,
  9. <code>STUDIMG</code> longblob NOT NULL
  10. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

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”
  1. Dim conn As New MySqlConnection
  2. Dim Myconnection As String = "server=localhost;user id=root;password=;database=studentdb"
  3. Dim cmd As New MySqlCommand
  4. Dim da As New MySqlDataAdapter
  5. Dim result As Integer
  6. Dim imgpath As String
  7. Dim arrImage() As Byte
  8. Dim sql As String

 

For “Browse Image” Button, Under Click Event:

 

  1. Try
  2. Dim OFD As FileDialog = New OpenFileDialog()
  3.  
  4. OFD.Filter = "Image File (*.jpg;*.bmp;*.gif)|*.jpg;*.bmp;*.gif"
  5.  
  6. If OFD.ShowDialog() = DialogResult.OK Then
  7. imgpath = OFD.FileName
  8. Pic1.ImageLocation = imgpath
  9.  
  10. End If
  11.  
  12. OFD = Nothing
  13.  
  14. Catch ex As Exception
  15. MsgBox(ex.Message.ToString())
  16. End Try

 

For “Save” Button, Under Click Event:

  1. Try
  2.  
  3. Dim mstream As New System.IO.MemoryStream()
  4. Pic1.Image.Save(mstream, System.Drawing.Imaging.ImageFormat.Jpeg)
  5. arrImage = mstream.GetBuffer()
  6. Dim FileSize As UInt32
  7. FileSize = mstream.Length
  8.  
  9. mstream.Close()
  10. conn.ConnectionString = Myconnection
  11. conn.Open()
  12. sql = "INSERT INTO tblstudent(FNAME,LNAME, COURSE, YR, AGE, EMAIL,studimg) VALUES (@FNAME, @LNAME, @COURSE, @YR, @AGE, @EMAIL,@studimg)"
  13. cmd.Connection = conn
  14. cmd.CommandText = sql
  15. cmd.Parameters.AddWithValue("@FNAME", TXTFIRSTNAME.Text)
  16. cmd.Parameters.AddWithValue("@LNAME", TXTLNAME.Text)
  17. cmd.Parameters.AddWithValue("@COURSE", TXTCOURSE.Text)
  18. cmd.Parameters.AddWithValue("@YR", TXTYR.Text)
  19. cmd.Parameters.AddWithValue("@AGE", TXTAGE.Text)
  20. cmd.Parameters.AddWithValue("@EMAIL", TXTEMAIL.Text)
  21. cmd.Parameters.AddWithValue("@studimg", arrImage)
  22. Dim r As Integer
  23. r = cmd.ExecuteNonQuery()
  24. If r &gt; 0 Then
  25. MsgBox("Student Record hass been Saved!")
  26. Else
  27. MsgBox("No record has been saved!")
  28. End If
  29. cmd.Parameters.Clear()
  30. conn.Close()
  31. Catch ex As Exception
  32. MessageBox.Show(ex.Message)
  33. End Try

For “Search” Button, Under Click Event:

  1. sql = "Select * from tblstudent where studID=" &amp; Val(TextBox1.Text)
  2. conn.ConnectionString = Myconnection
  3. conn.Open()
  4. With cmd
  5. .Connection = conn
  6. .CommandText = sql
  7. End With
  8. Dim arrImage() As Byte
  9. Dim publictable As New DataTable
  10. Try
  11. da.SelectCommand = cmd
  12. da.Fill(publictable)
  13. TXTLNAME.Text = publictable.Rows(0).Item(2)
  14. TXTFIRSTNAME.Text = publictable.Rows(0).Item(1)
  15. TXTCOURSE.Text = publictable.Rows(0).Item(3)
  16. TXTYR.Text = publictable.Rows(0).Item(4)
  17. TXTAGE.Text = publictable.Rows(0).Item(5)
  18. TXTEMAIL.Text = publictable.Rows(0).Item(6)
  19. arrImage = publictable.Rows(0).Item(7)
  20. Dim mstream As New System.IO.MemoryStream(arrImage)
  21. Pic1.Image = Image.FromStream(mstream)
  22. Catch ex As Exception
  23. MsgBox(ex.Message)
  24. Finally
  25. da.Dispose()
  26.  
  27. conn.Close()
  28.  
  29. End Try

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.

 

Facebook Comments

LEAVE A REPLY

Please enter your comment!
Please enter your name here