46 comments Tutorial, Visual Basic Tutorial

Saving images in MySQL Database from Visual Basic .NET 2010

Posted by
Total Read 1,245

Last updated on June 13th, 2020 at 08:50 pm

I am just not sure enough about if this is a good or a bad idea to save images in DB. The most cases I have heard about said that it’s a bad idea and in very fewer articles I have read that “No, it’s same expensive.” But my suggestion would be that, if you have to pull only a single image from DB at a time, then you can use this method. But not in the else cases. In case of web development, there is not need to do this, because PHP is already having very easy procedure to save the image or any file in server’s directory and pulling them back and so as other server side languages.

Target: 

Target of this tutorial is to Store or Save images in MySQL Server Database from Visual Basic .NET 2010.

Assumption: 

I am assuming that you are familiar with the following
  1. Creating Database, Table in MySQL
  2. Adding data to MySQL database.
  3. Creating projects, saving projects, writing codes in VB.net.

Prerequisites: 

To achieve the target, you will have to have the following things,
  1. The MySQL Server
  2. The MySQL .NET Connector
  3. An IDE for DB Management. Such as SQLyog, PHPmyadmin etc. If you don’t have any, then you can download a free tool.
  4. Finally, Visual Basic .NET 2010 [ 2008 will also work ]
Once you have the above things, install all of them.
If you are not familiar with using MySQL Server Database with Visual Basic .NET then I recommend the blog post at the following link

Read Also: Using MySQL Database with Visual Basic .NET 2010

The Database: 

We have named our database as “my_test_projects” and the table that will have the image as “image_in_db”. The structure of the table “image_in_db” is as follows.

Column Name
Type
Description
id
int
This is not necessary, I have just used it, and I think you should to.
Image_data
mediumblob
This field actually holds the image. I am taking “mediumblob” as data type as it can hold nearly 4 MB of data, that is an image with nearly 4 MB of file size.

Okay, now open VB.net and create a new project and name it whatever you like and save it. Then, as described in this post “Using MySQL Database with Visual Basic .NET 2010” Add Reference of MySQL Connector to your project.

The Form: 

Design the form as follow,

Here, we have 4 controls. Description of the controls are given below.

Serial
Control
Name
Value
Usage
1
Picture Box
pic_box_save

It holds the image that will be saved in the DB. You can set an image by default as I did.
2
Button
btn_save
Save
The saving procedure is underneath this button.
3
Picture Box
pic_box_get

It holds the image after pulling that from the DB.
4
Button
btn_get
Get
The procedure to pull the image from the DB lies with this button.
Okay, so now we have the database and form ready, lets start coding in the next part.

First Phase – Creating connection and declaring variables:

First of all we have to import the MySQL Connector and need to declare some variables that we will use through the project.

Imports MySql.Data
Public Class Form1
     Dim sql As String
     Dim sql_connection As MySqlClient.MySqlConnection
     Dim sql_command As MySqlClient.MySqlCommand
     Dim sql_reader As MySqlClient.MySqlDataReader

     Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
          sql_connection = New MySqlClient.MySqlConnection("Data Source=localhost;user id=root;database=my_test_projects;")
          Try
               sql_connection.Open()
          Catch ex As Exception
               MsgBox("Error Creating DB Connection")
          End Try
     End Sub
End Class

Explanation of the above code is already given in this post “Using MySQL Database with Visual Basic .NET 2010”, please read that post if you need.
Great, so the connection is made, lets move on to the next part about saving the image in DB.

Second Phase – Saving the image in DB:

Double-Click the “btn_save” button to open the code editor and write the following code.

Private Sub btn_save_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_save.Click
 Dim FileSize As UInt32

 Dim mstream As New System.IO.MemoryStream()
 pic_box_save.Image.Save(mstream, System.Drawing.Imaging.ImageFormat.Jpeg)
 Dim arrImage() As Byte = mstream.GetBuffer()
 FileSize = mstream.Length
 mstream.Close()
 MsgBox(FileSize)
 Try
  sql = "INSERT INTO image_in_db(id, image_data) VALUES(@image_id, @image_data)"
  sql_command = New MySqlClient.MySqlCommand(sql, sql_connection)
  sql_command.Parameters.AddWithValue("@image_id", Nothing)
  sql_command.Parameters.AddWithValue("@image_data", arrImage)
  sql_command.ExecuteNonQuery()
 Catch ex As Exception
  MsgBox(ex.Message)
  Exit Sub
 End Try
 MsgBox("Image has been saved.")
End Sub

We will convert the image file to Byte type data. But before that, we will need to place the image file in memory-stream. That’s why in line 1 we have taken mstream as an instance of memory-stream. Then in the second line, we have saved the image of pic_box_save in mstream. Right after that, we have taken the stream in a Byte type array named as arrImage and then closed the stream on line 4.

That’s it and we are ready to save the image. In the Try…Catch block we have writen our SQL statement and then we made the SQL Command. After that, in line 8 and 9, we passes our data to the command. In line 8, we passed Nothing as the value of @image_id and then in line 9 we passed arrImage as the value of @image_data. Then in line 10 we have executed our SQL Command which actually saves the image in the DB. If this process fails for any reason then the code in line 12 and 13 will be executed, where we show the reason for the failure as a message and then exit the sub, respectively. But if this process is succeded then the code of line 15 will be executed which will show a message regarding the success of the saving process.
So far so good, the image has been saved to DB, but of no use unless we are able to pull it back for processing, right? Lets see how to do that in the next part.

Third Phase – Pulling the image back from the DB:

Double-Click the “btn_get” button to open the code editor and write the following code.

Private Sub btn_get_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_get.Click
 Dim arrImage() As Byte
 Try
  sql = "SELECT * FROM image_in_db WHERE id = '1'"
  sql_command = New MySqlClient.MySqlCommand(sql, sql_connection)
  sql_reader = sql_command.ExecuteReader()
  sql_reader.Read()
  arrImage = sql_reader.Item("image_data")
  Dim mstream As New System.IO.MemoryStream(arrImage)
  pic_box_get.Image = Image.FromStream(mstream)
  sql_reader.Close()
 Catch ex As Exception
  MsgBox(ex.Message)
  Exit Sub
 End Try
End Sub
To get the image back we just revert the image-saving procedure. In case of saving the image what we did is, Image in Picture Box > Memory Stream > Byte > Database. Here what we have done is the reverse process.
Line 4 to 7 : Pulling the image from the DB
Line 8: Getting the image in a Byte array named as arrImage.
Line 9: Getting the Byte array in memory-stream named as mstream.
Line 10: Putting the data from the memory-stream mstream in a picture box named as pic_box_get.
And that’s it. We are done. In the next part, we get the full source code along with the sample project to download to see things in action.

The Full Source Code:

Download Full Project using the following download link:

Saving Images in MySQL DB

Next Read:

46 comments

  1. Boss, vb.net setup করতে পারছি না। কিভাবে রান করতে হয় তাও জানি না। একদম new আমি। আমাকে একটু ডিটেইল ভাবে হেল্প করবেন প্লিজ। আমার মেইল [email protected]

  2. আপনি ভিবি.নেট এর কোন ভার্সন সেটআপ দিতে চাচ্ছেন? ২০০৮ নাকি ২০১০ ? সেট আপ দেবার ব্যাপারটা খুব সিম্পল, তবু প্রথমবার হবার জন্য আপনি কারো কাছে থেকে সরাসরি একটু হেল্প নিলেই আপনার জন্য ভালো হবে। আমি পুরো টিউটোরিয়াল দিতে পারবো কিন্তু প্রবলেম হলো আমি ভয়ানক বিজি শিডিউলে আছি এই গোটা মাস, এটলিস্ট মিনিমাম 25 তারিখ পর্যন্ত, তাই এর মধ্যে কোনই হেল্প করতে পারবো না, সরি।

  7. Hello Tanmay,

    Your tutorial is very easy to follow.
    But I have a problem. i encountered an error as i run this.
    The error is this:

    "column image_data cannot be null"

    it did not save.

    your reply is very much appreciated and thanks in advance!

  8. It just can't be that there is an error with this code. However, check your solution as follows,

    1. Do you have the DB created yet? (I am sure you have it.)
    2. Do you have the image in the picture while trying save the image image?(I am quite sure thats the problem, you don't have any image in it.)

    I have attached the download link for the whole project at the bottom of the post, try to download the project and run it. If you need, then change DB parameters, like DB name, host name, user name, user password, table name etc.

  10. When this error is appearing it must be pointing to a line of code, I need to know that line to find out which object has not been initialized.

    For now, check out if you have created reference to MySQL connector or not. Everything else is dependent on it, so make it sure, then let me have screenshot when the debugger generates the error.

  12. Try
    Dim arrImage() As Byte
    sqlconn.Open()
    sqlstr = "SELECT * FROM patron,pcategory,college,course WHERE patron.plname = '" & frmLibraryAccount.dgpatron.Item(0, i).Value & "' " & _
    "AND patron.pfname = '" & frmLibraryAccount.dgpatron.Item(1, i).Value & "' AND patron.pmname = '" & frmLibraryAccount.dgpatron.Item(2, i).Value & "' " & _
    "AND patron.pcategoryno = pcategory.pcategoryno AND patron.pcourseno = course.courseno AND " & _
    "patron.pcollegeno = college.collegeno"
    sqlcmd = New MySqlCommand(sqlstr, sqlconn)
    sqldr = sqlcmd.ExecuteReader

    'Dim pictureData As Byte() = DirectCast(sqlcmd.ExecuteScalar(), Byte())

    While sqldr.Read
    txtFNameUpdatePatron.Text = sqldr("pfname").ToString
    txtMNameUpdatePatron.Text = sqldr("pmname").ToString
    txtLNameUpdatePatron.Text = sqldr("plname").ToString
    cmbGenderUpdatePatron.Text = sqldr("pgender").ToString
    txtAddressUpdatePatron.Text = sqldr("paddress").ToString
    txtEMailUpdatePatron.Text = sqldr("pemail").ToString
    txtCNumUpdatePatron.Text = sqldr("pcnumber").ToString
    cmbCategoryUpdatePatron.Text = sqldr("pcategory").ToString
    txtIDNumUpdatePatron.Text = sqldr("pidno").ToString
    cmbCollegeUpdatePatron.Text = sqldr("collegename").ToString
    cmbCourseUpdatePatron.Text = sqldr("coursename").ToString

    arrImage = sqldr.Item("pimage")
    Dim mstream As New System.IO.MemoryStream(arrImage)
    pbUpdatePatron.Image = Image.FromStream(mstream)

    sqlconn.Close()
    sqldr.Close()
    End While

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

    Sir, Your tutorial about saving blob on database really helped me a lot but when it comes to retrieving the image from the database I have a bit of problem. It said that the "Parameter is not valid" in part of "pbUpdatePatron.Image = Image.FromStream(mstream)". Could you help me in this? Thanks.

  13. Currently at office. I will try it when at home. In the mean time, try simplify you sql by only selecting the image from the DB and putting in on a picture box as I told. Also, check out if your image saving procedure was accurate. Remove Try…Catch and find out exactly on which line the error is occurring.

    I think you will find the problem and the solution both.

  14. Sir,I'm sorry about the long sql strings. I'm just a newbiew on VB.Net. I will try it out. But your resolve is very much appreciated.
    AJ here. Thanks.

  16. Hi Tanmay, great tutorial, your work helping others is fantastic. Your program runs fine as per your tutorial. I need some help on how to UPDATE the image. I have tried various UPDATE strings but cannot get this to work. I have "googled" this but not much information available. Thanks for you assistance, Norm

  26. Very Awesome Tutorial sir. .Thumbs Up for you. .Keep up the good work. .But I have A Question Sir, In my Table I have the ff. Fields, Name,Picture , address. How can I save the other fields using your code?

  27. sql = "INSERT INTO image_in_db(id, image_data) VALUES(@image_id, @image_data, @name, @address)"
    sql_command = New MySqlClient.MySqlCommand(sql, sql_connection)
    sql_command.Parameters.AddWithValue("@image_id", Nothing)
    sql_command.Parameters.AddWithValue("@image_data", arrImage)
    sql_command.Parameters.AddWithValue("@name", the_name)
    sql_command.Parameters.AddWithValue("@address", the_address)

    Have you tried this way?

  28. Great post. I Like to read your post. I gonna to bookmark your post. Thanks for sharing all links I am sure they are useful.

  37. Sir, Thank You very much for this.. it worked fine to me..
    now I'll try saving from OFD to picture box to dbase.. and this'll be my basis ..
    Thank you again 🙂

  39. Sir I encountered A problem when retrieving the image from the dbase
    It says "Parameter is not valid" when I tried viewing it from another vb form rather than they are in the same form

    Thank You in advance for the response ^^

  40. I like that code, because my database server is always being transferred, so if it does, so are the images. Thanks!

Comments are closed.