locked
How to insert image on MySQL Database using vb.net 2005? RRS feed

  • Question

  • Good Day! Can anyone help me? I am doing a simple registration form in visual basic.net 2005, this form contains the general information about the person, in this form it includes uploading/inserting images for the record. Here is my project, on MS SQL, i can insert and retrieve images easily but when i switch my database into MySQL  i encounter the problem that i can't insert image to my database. Can anyone provide me a sample code for inserting and retrieving image from a table on MySQL database? Thanks guys!
    Tuesday, September 27, 2011 3:45 AM

Answers

All replies

  • Hi Daryl.

    Some years ago I made a sample which in which was removed all culprit we see often in samples around Images on internet and databases.

    It fits for all databases as long as it uses serialized arrays as object. It is made around datasets which you can use on MySQL with normal code.

    I have recreated it a short while ago

    http://www.vb-tips.com/datasetimage.aspx


    Success
    Cor
    • Proposed as answer by WITWEW Wednesday, September 28, 2011 7:30 AM
    Tuesday, September 27, 2011 4:41 AM
    1. 'Save an Image to a memory stream so you can get the bytes
    2.         Dim sampleImage As Bitmap = New Bitmap(100, 100)
    3.         Dim mStream As New System.IO.MemoryStream
    4.         Dim ImageBytes As Byte()
    5.  
    6.         sampleImage.Save(mStream, Imaging.ImageFormat.Png)
    7.         ImageBytes = mStream.ToArray
    8.  
    9.         'Sample Insert image command
    10.         'Save the bytes from the image into a image or varbinary column
    11.         Dim com As New SqlClient.SqlCommand("Insert Into MyTable" & vbCrLf & _
    12.                                             "(MyImageColumn)" & vbCrLf & _
    13.                                             "Values(@MyImage)")
    14.  
    15.         'an image column or varbinary column
    16.         com.Parameters.Add("@MyImage", SqlDbType.Image)
    17.         com.Parameters("@MyImage").Value = ImageBytes
    18.  
    19.  
    20.         'Sample Read Image Command
    21.         'Read the bytes from the table and create a new memory stream from them
    22.         com.CommandText = "Select MyImage From MyTable"
    23.         Dim rdr As System.Data.SqlClient.SqlDataReader
    24.  
    25.         rdr = com.ExecuteReader
    26.         If rdr.Read Then
    27.             Dim newMstream As New System.IO.MemoryStream(CType(rdr.Item("MyImage"), Byte()))
    28.             'Create a new image from the bytes from the memory
    29.             Dim ImageFromDB As New Bitmap(newMstream)
    30.  
    31.         End If

       

    http://stackoverflow.com/questions/5033634/vb-net-insert-retreive-picture-from-mysql-database-directly-to-from-a-picturebo

    http://forums.mysql.com/read.php?38,6172,6172

    http://www.codeproject.com/Questions/115005/regarding-insert-image-in-mysql-from-vb-net

    Kindly have a look at the above links it will be useful.

    Regards,

    Narendran Ponpandiyan

     

    Tuesday, September 27, 2011 5:19 AM
  • Thanks Cor for your immediate response but I think the sample you've given to me is not saving the image to the database right? I want to save the image on my database and then retrieve and display the value came from the database.
    Tuesday, September 27, 2011 5:42 AM
  • You can simply use that bytearray created even in a executenonquery, using the code I gave earlier to day to callum

    Module Module1
        Sub Main()
            Using connection As New SqlClient.SqlConnection("connectionString")
                Using command As New SqlClient.SqlCommand("Update YourTable set YourImageColum=@image Where ID = 12", connection)
                    command.Connection.Open()
                    command.parameters.addwithValue("@image",byteArray)
                    command.ExecuteNonQuery()
                End Using
            End Using
        End Sub
    End Module
    
    
    Where byteArray is the code you see on our website under button 4 in the code sample around the images and databases.


    Success
    Cor
    Tuesday, September 27, 2011 8:07 AM
  • Are you using SQL Server? What I'm looking for is for MySQL, i did it right in SQL Server, i can store and retrieve image without any error. But when I switch to MySQL, that's the time I encounter a problem. I thought the code/query used in ms sql can be use also in mysql. I am forced to switch to mysql because that's the requirements of my client.
    Tuesday, September 27, 2011 10:51 AM
  • Why not, that code can be done with my Sql and with whatever DataBase.

    As long as you take the rigth provider.

    http://www.mysql.com/products/connector/

    http://www.mysql.com/downloads/connector/net/

    Although you don't have to use the prefix sqlclient of course but the mySQL one.

     


    Success
    Cor
    Tuesday, September 27, 2011 12:37 PM
  • The below link should address your issue:

    http://dev.mysql.com/doc/refman/5.0/en/connector-net-programming-blob.html

     


    Paul ~~~~ Microsoft MVP (Visual Basic)
    • Marked as answer by Kee Poppy Monday, October 3, 2011 6:01 AM
    Tuesday, September 27, 2011 12:52 PM
  • To make it easy, can you guys convert this code from sql server into mysql query? I am having a hard time looking for a solution to my problem.

     

    To create table:

    CREATE TABLE Employees
    (
        Id int,
        Name varchar(50) not null,
        Photo varbinary(max) not null
    )
    


    To insert values:

    INSERT INTO Employees (Id, Name, Photo) 
    SELECT 10, 'John', BulkColumn 
    FROM Openrowset( Bulk 'C:\photo.bmp', Single_Blob) as EmployeePicture
    

    I used this code/query on MS SQL Server 2008 abd it works fine. I want the equivalent query of this in MySQL. Thanks guys for your help.

    Wednesday, September 28, 2011 5:25 AM
  • Daryll,

    I gave that code already. 

    The first part is to create an employee table, it is very uncommon to create for every image a new table in the database.

    I gave you this code before, however now used a diffent sql string.

    Be aware this code is very uncommon to use, it insert images endless times and you are unable to change it. Better is to use a dataset. 

     

    Module Module1
        Sub Main()
            Using connection As New SqlClient.SqlConnection("connectionString")
                Using command As New SqlClient.SqlCommand("Insert into Employees (Id, Name, Photo) from (@Employee, @Name, @image)", connection)
                    command.Connection.Open()
                    command.parameters.addwithValue("@ID",10)
                    command.parameters.addwithValue("@Name","John")
                    command.parameters.addwithValue("@Photo",byteArray)
                    command.ExecuteNonQuery()
                End Using
            End Using
        End Sub
    End Module
    

     


    Success
    Cor
    • Edited by Cor Ligthert Wednesday, September 28, 2011 6:24 AM
    Wednesday, September 28, 2011 6:23 AM
  • Sorry Cor, I didn't use module with my projects. How can I insert the image using the code above? I am sorry i can't understand the code above, I'm not that familiar with modules. Be patient, i have a lot of questions. I know you can help me that's why i keep on asking.
    Wednesday, September 28, 2011 7:42 AM
  • Sorry Cor, I didn't use module with my projects. How can I insert the image using the code above? I am sorry i can't understand the code above, I'm not that familiar with modules. Be patient, i have a lot of questions. I know you can help me that's why i keep on asking.

     Sub InsertButeArray(byval byteArray as byte())
            Using connection As New SqlClient.SqlConnection("connectionString")
                Using command As New SqlClient.SqlCommand("Insert into Employees (Id, Name, Photo) from (@Employee, @Name, @image)", connection)
                    command.Connection.Open()
                    command.parameters.addwithValue("@ID",10)
                    command.parameters.addwithValue("@Name","John")
                    command.parameters.addwithValue("@Photo",byteArray)
                    command.ExecuteNonQuery()
                End Using
            End Using
    End sub
    Be aware all the code provided in these forums are meant as sample it is not actual software. You have to check it yourself and to tailored it to your needs. They all can contain errors because of typos or what other mistake

    Success
    Cor
    • Edited by Cor Ligthert Wednesday, September 28, 2011 7:48 AM
    Wednesday, September 28, 2011 7:47 AM
  • Hi Joe,

    MySQL relevant forums would have more resources for your question. You can consider posting there if you need.

    Thanks,


    Kee Poppy [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, September 28, 2011 10:32 AM
  • He's using MySQL Cor. It's in the link to the documentation I posted.


    Paul ~~~~ Microsoft MVP (Visual Basic)
    Wednesday, September 28, 2011 3:24 PM
  • To make it easy, can you guys convert this code from sql server into mysql query? I am having a hard time looking for a solution to my problem.

     


    I would suggested looking at the MySQL examples. There is no point attempting to convert SQL Server examples to MySQL when it isn't necessary.


    Paul ~~~~ Microsoft MVP (Visual Basic)
    Wednesday, September 28, 2011 3:31 PM