How to Save an Image in MySQL Database Using VB.Net

Submitted by janobe on Wednesday, August 22, 2018 - 18:04.
In my tutorial, this time I will teach you how to save an Image in the MySQL Database using VB.Net. This method will help you store the image itself into the MySQL Database. This is a secure way of saving your image so that it will not be deleted once a virus affects your system. This can also reduce the size of the file of your system. Lets get started: First, create a database and named it "test"
  1.  
Second, add the table in the database that you have created.
  1. CREATE TABLE `test`.`tblimageblob` ( `ImageID` INT NOT NULL AUTO_INCREMENT ,`Caption` VARCHAR(255) NOT NULL , `ImageFile` LONGBLOB NOT NULL , PRIMARY KEY(`ImageID`)) ENGINE = InnoDB;
Third, open Microsoft Visual Studio 2015 and create a new project. ps1 Fourth, add two buttons, picturebox, opendialogbox and do the form just like shown below. ps2 Fifth, double click the "Browse" button to open the code editor and do the following code to get an image in the computer and place it in the the picture box.
  1. Private Sub btnBrowse_Click(sender As Object, e As EventArgs) Handles btnBrowse.Click
  2.         Try
  3.             With OpenFileDialog1
  4.  
  5.                 'CHECK THE SELECTED FILE IF IT EXIST OTHERWISE THE DIALOG BOX WILL DISPLAY A WARNING.
  6.                 .CheckFileExists = True
  7.  
  8.                 'CHECK THE SELECTED PATH IF IT EXIST OTHERWISE THE DIALOG BOX WILL DISPLAY A WARNING.
  9.                 .CheckPathExists = True
  10.  
  11.                 'GET AND SET THE DEFAULT EXTENSION
  12.                 .DefaultExt = "jpg"
  13.  
  14.                 'RETURN THE FILE LINKED TO THE LNK FILE
  15.                 .DereferenceLinks = True
  16.  
  17.                 'SET THE FILE NAME TO EMPTY
  18.                 .FileName = ""
  19.  
  20.                 'FILTERING THE FILES
  21.                 .Filter = "(*.jpg)|*.jpg|(*.png)|*.png|(*.jpg)|*.jpg|All files|*.*"
  22.                 'SET THIS FOR ONE FILE SELECTION ONLY.
  23.                 .Multiselect = False
  24.  
  25.  
  26.  
  27.                 'SET THIS TO PUT THE CURRENT FOLDER BACK TO WHERE IT HAS STARTED.
  28.                 .RestoreDirectory = True
  29.  
  30.                 'SET THE TITLE OF THE DIALOG BOX.
  31.                 .Title = "Select a file to open"
  32.  
  33.                 'ACCEPT ONLY THE VALID WIN32 FILE NAMES.
  34.                 .ValidateNames = True
  35.  
  36.                 If .ShowDialog = DialogResult.OK Then
  37.                     Try
  38.                         PictureBox1.Image = Image.FromFile(OpenFileDialog1.FileName)
  39.                     Catch fileException As Exception
  40.                         Throw fileException
  41.                     End Try
  42.                 End If
  43.  
  44.             End With
  45.         Catch ex As Exception
  46.             MsgBox(ex.Message, MsgBoxStyle.Exclamation, Me.Text)
  47.         End Try
  48.     End Sub
Sixth, go back to the design view and double click the "Save" button to open the code editor. After that, do the following code for saving an image in the MySQL database.
  1.     Private Sub btnSave_Click(sender As Object, e As EventArgs) Handles btnSave.Click
  2.         Dim con As MySqlConnection = New MySqlConnection("server=localhost;user id=root;password=janobe;database=test;sslMode=none")
  3.         Dim cmd As MySqlCommand
  4.         Dim sql As String
  5.         Dim result As Integer
  6.         Dim caption As String
  7.         Dim arrImage() As Byte
  8.         Dim mstream As New System.IO.MemoryStream()
  9.  
  10.         caption = System.IO.Path.GetFileName(OpenFileDialog1.FileName)
  11.         'SPECIFIES THE FILE FORMAT OF THE IMAGE
  12.         PictureBox1.Image.Save(mstream, System.Drawing.Imaging.ImageFormat.Jpeg)
  13.  
  14.         'RETURNS THE ARRAY OF UNSIGNED BYTES FROM WHICH THIS STREAM WAS CREATED
  15.         arrImage = mstream.GetBuffer()
  16.  
  17.         'GET THE SIZE OF THE STREAM IN BYTES
  18.         Dim FileSize As UInt32
  19.         FileSize = mstream.Length
  20.         'CLOSES THE CURRENT STREAM AND RELEASE ANY RESOURCES ASSOCIATED WITH THE CURRENT STREAM
  21.         mstream.Close()
  22.         Try
  23.             con.Open()
  24.             sql = "INSERT INTO  `tblimageblob` (`Caption`, `ImageFile`) VALUES (@Caption, @ImageFile)"
  25.             cmd = New MySqlCommand
  26.             With cmd
  27.                 .Connection = con
  28.                 .CommandText = sql
  29.                 .Parameters.AddWithValue("@Caption", caption)
  30.                 .Parameters.AddWithValue("@ImageFile", arrImage)
  31.                 result = .ExecuteNonQuery()
  32.             End With
  33.             If result > 0 Then
  34.                 MsgBox("Picture has been save in the database")
  35.             Else
  36.                 MsgBox("Error query", MsgBoxStyle.Exclamation)
  37.             End If
  38.         Catch ex As Exception
  39.             MsgBox(ex.Message)
  40.         Finally
  41.             con.Close()
  42.         End Try
  43.  
  44.     End Sub
Note : add Imports MySql.Data.MySqlClient and Imports System.IO above the public class form1 for your imports. Without this your mysql class will not work. Output: ps3 ps3 If you find an error, please download mysql-connector latest version and install it on your machine. Add the MySQL.Data.dll as a reference for the project that you have created. For more question about this article. You can contact me @ Email – [email protected] Mobile No. – 09305235027 – TNT FB Account – https://www.facebook.com/onnaj.soicalap
Tags
insert an image in mysql database
vb.net
MySQL Database

Comments

Add new comment

About text formats