Saving Images in Databases-VBForums
Page 1 of 2 12 LastLast
Results 1 to 40 of 64

Thread: Saving Images in Databases

Hybrid View

  1. #1

    Thread Starter
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    88,601

    Saving Images in Databases

    C# version here.

    This question gets asked all the time so I thought an example was in order.

    Loading an image from a database field:
    VB.NET Code:
    1. Dim connection As New SqlConnection("connection string here")
    2. Dim command As New SqlCommand("SELECT Picture FROM MyTable WHERE ID = 1", connection)
    3.  
    4. connection.Open()
    5.  
    6. Dim pictureData As Byte() = DirectCast(command.ExecuteScalar(), Byte())
    7.  
    8. connection.Close()
    9.  
    10. Dim picture As Image = Nothing
    11.  
    12. 'Create a stream in memory containing the bytes that comprise the image.
    13. Using stream As New IO.MemoryStream(pictureData)
    14.     'Read the stream and create an Image object from the data.
    15.     picture = Image.FromStream(stream)
    16. End Using
    Saving an image to a database field:
    VB.NET Code:
    1. Dim connection As New SqlConnection("connection string here")
    2. Dim command As New SqlCommand("UPDATE MyTable SET Picture = @Picture WHERE ID = 1", connection)
    3.  
    4. 'Create an Image object.
    5. Using picture As Image = Image.FromFile("file path here")
    6.     'Create an empty stream in memory.
    7.     Using stream As New IO.MemoryStream
    8.         'Fill the stream with the binary data from the Image.
    9.         picture.Save(stream, Imaging.ImageFormat.Jpeg)
    10.  
    11.         'Get an array of Bytes from the stream and assign to the parameter.
    12.         command.Parameters.Add("@Picture", SqlDbType.VarBinary).Value = stream.GetBuffer()
    13.     End Using
    14. End Using
    15.  
    16. connection.Open()
    17. command.ExecuteNonQuery()
    18. connection.Close()
    Please take note: this is example code only. Please don't paste it into your project and then ask why it doesn't work. Note also that while this code uses ExecuteScalar to get a single image's worth of data and ExecuteNonQuery to update a single existing record, the principles are the same no matter how your retrieving and saving your data. The important part is the MemoryStream. That is the link between the Byte array that gets stored in the database and the binary Image object in your VB app. For example, if you have 100 rows in a DataTable then you just use a MemoryStream for each row and assign the Byte array to the appropriate field of each row. You then just call the Update method of your DataAdapter as normal.
    Last edited by jmcilhinney; Dec 18th, 2008 at 09:48 PM.

    2007-2014

    Why is my data not saved to my database? | MSDN Data Walkthroughs
    MSDN "How Do I?" Videos: VB | C#
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts) | WP8 Turnstile Feather Transition with Pivot Control
    Beginner Tutorials: VB | C# | SQL

  2. #2
    Hyperactive Member
    Join Date
    Mar 2007
    Location
    Hong Kong
    Posts
    384

    Re: Saving Images in Databases

    what is the mean of "file path here"?
    The pic is come from database, how come the file path?
    What is the datatype of this pic in the database?
    Last edited by newpat; Mar 6th, 2008 at 11:16 AM.

  3. #3

    Thread Starter
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    88,601

    Re: Saving Images in Databases

    Quote Originally Posted by newpat
    what is the mean of "file path here"?
    The pic is come from database, how come the file path?
    What is the datatype of this pic in the database?
    No, the pic is GOING TO the database. It has to come from somewhere in the first place, and the most likely place for it to come from is a file.

    The data type of the column containing the images would depend on your database. It will be stored as binary data, so use whatever data type is appropriate for such data. In SQL Server you could use the old 'image' data type but, as that's been deprecated, you should use a 'varbinary' column.

    2007-2014

    Why is my data not saved to my database? | MSDN Data Walkthroughs
    MSDN "How Do I?" Videos: VB | C#
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts) | WP8 Turnstile Feather Transition with Pivot Control
    Beginner Tutorials: VB | C# | SQL

  4. #4
    Hyperactive Member
    Join Date
    Mar 2007
    Location
    Hong Kong
    Posts
    384

    Re: Saving Images in Databases

    Quote Originally Posted by jmcilhinney
    No, the pic is GOING TO the database. It has to come from somewhere in the first place, and the most likely place for it to come from is a file.

    The data type of the column containing the images would depend on your database. It will be stored as binary data, so use whatever data type is appropriate for such data. In SQL Server you could use the old 'image' data type but, as that's been deprecated, you should use a 'varbinary' column.
    how about MySQL?
    There are no image, varbinary or binary type, but bit.Should I declare it to be bit as well?

  5. #5

    Thread Starter
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    88,601

    Re: Saving Images in Databases

    Quote Originally Posted by newpat
    how about MySQL?
    There are no image, varbinary or binary type, but bit.Should I declare it to be bit as well?
    I've never used MySQL but I'd imagine that a bit column is the same as a SQL Server bit column. If so then it can only store the values 1 and 0 and is intended to represent boolean values. You could post in the database Development forum if you need to ask how to use MySQL.

    2007-2014

    Why is my data not saved to my database? | MSDN Data Walkthroughs
    MSDN "How Do I?" Videos: VB | C#
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts) | WP8 Turnstile Feather Transition with Pivot Control
    Beginner Tutorials: VB | C# | SQL

  6. #6
    Member
    Join Date
    Mar 2005
    Posts
    47

    Re: Saving Images in Databases

    Thanks JM it is really a great help

  7. #7
    Fanatic Member
    Join Date
    Aug 2005
    Location
    Wisconsin
    Posts
    788

    Re: Saving Images in Databases

    Code:
    Dim connection As New SqlConnection("connection string here")
    Dim command As New SqlCommand("UPDATE MyTable SET Picture = @Picture WHERE ID = 1", connection) 
    
    Using picture As Image = Image.FromFile("file path here")
        Using stream As New IO.MemoryStream
            picture.Save(stream, Imaging.ImageFormat.Jpeg)
            command.Parameters.Add("@Picture", SqlDbType.Image).Value =stream.GetBuffer()
        End Using
    End Using
    
    connection.Open()
    command.ExecuteNonQuery()
    connection.Close()

    In the code above what would the code be for an OleDb connection to an Access database?

    I am most curios about the line
    command.Parameters.Add("@Picture",SqlDBType.Image).Value=stream.GetBuffer()

    Nothing urgent about this post just would like to see the alternatives to SQL Db.

    Thanks.

  8. #8

    Thread Starter
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    88,601

    Re: Saving Images in Databases

    Quote Originally Posted by mojo69
    Code:
    Dim connection As New SqlConnection("connection string here")
    Dim command As New SqlCommand("UPDATE MyTable SET Picture = @Picture WHERE ID = 1", connection) 
    
    Using picture As Image = Image.FromFile("file path here")
        Using stream As New IO.MemoryStream
            picture.Save(stream, Imaging.ImageFormat.Jpeg)
            command.Parameters.Add("@Picture", SqlDbType.Image).Value =stream.GetBuffer()
        End Using
    End Using
    
    connection.Open()
    command.ExecuteNonQuery()
    connection.Close()

    In the code above what would the code be for an OleDb connection to an Access database?

    I am most curios about the line
    command.Parameters.Add("@Picture",SqlDBType.Image).Value=stream.GetBuffer()

    Nothing urgent about this post just would like to see the alternatives to SQL Db.

    Thanks.
    While parameter names are ignored by OleDb and parameters are accessed by position only, I still prefer to use descriptive names where they are supported. If you're using Access then you can use parameter names but some other OLEDB data sources only support the use of "?" as a place-holder for parameters. So, if you were using Access you'd change the code to this:
    Code:
    Dim connection As New OleDbConnection("connection string here")
    Dim command As New OleDbCommand("UPDATE MyTable SET Picture = @Picture WHERE ID = 1", connection) 
    
    Using picture As Image = Image.FromFile("file path here")
        Using stream As New IO.MemoryStream
            picture.Save(stream, Imaging.ImageFormat.Jpeg)
            command.Parameters.Add("@Picture", OleDbType.VarBinary).Value =stream.GetBuffer()
        End Using
    End Using
    
    connection.Open()
    command.ExecuteNonQuery()
    connection.Close()
    You can see that, as tg said, only the types have changed to protect the innocent. Everything else is exactly the same.

    Now, if you were using an OLEDB data source that didn't support named parameter place-holders then your SQL code would change:
    Code:
    Dim connection As New OleDbConnection("connection string here")
    Dim command As New OleDbCommand("UPDATE MyTable SET Picture = ? WHERE ID = 1", connection) 
    
    Using picture As Image = Image.FromFile("file path here")
        Using stream As New IO.MemoryStream
            picture.Save(stream, Imaging.ImageFormat.Jpeg)
            command.Parameters.Add("Picture", OleDbType.VarBinary).Value =stream.GetBuffer()
        End Using
    End Using
    
    connection.Open()
    command.ExecuteNonQuery()
    connection.Close()
    Note the "?" place-holder in the SQL code, which is standard for OleDb. You still specify a name when you add the parameter to the command so a descriptive name should be used there for clarity at least. You can imagine that if you had a lot of parameters it would be much easier to make a mistake and add them out of order without the names in the SQL code. That's why I prefer to use named place-holders if possible.

    As to this line:
    Code:
    command.Parameters.Add("@Picture",SqlDBType.Image).Value=stream.GetBuffer()
    it is getting the binary contents of the stream, which is returned as a Byte array, and assigning that to the parameter's Value property. It is the Value of the parameter that eventually replaces the place-holder in the SQL code when it gets executed on the database. As such, the binary data that was read from your Image into the stream and then from the stream into the parameter will eventually get written into the database.
    Last edited by jmcilhinney; Jun 27th, 2008 at 08:51 PM.

    2007-2014

    Why is my data not saved to my database? | MSDN Data Walkthroughs
    MSDN "How Do I?" Videos: VB | C#
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts) | WP8 Turnstile Feather Transition with Pivot Control
    Beginner Tutorials: VB | C# | SQL

  9. #9
    PowerPoster
    Join Date
    May 2002
    Posts
    25,478

    Re: Saving Images in Databases

    strangely enough, it should be nearly identical... the parameter name would be irelevant, and you would change the type from SQLdbType.Image to the correct OLEDB type (I think it too would be image, but it's in a different namespace). Other than that, it's the same.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  10. #10
    Fanatic Member
    Join Date
    Aug 2005
    Location
    Wisconsin
    Posts
    788

    Re: Saving Images in Databases

    This is what I did
    OleDBType.Image

    But it gives me error. I will post the error a little later as I am no longer at the PC with the project on it.

  11. #11
    Fanatic Member
    Join Date
    Aug 2005
    Location
    Wisconsin
    Posts
    788

    Re: Saving Images in Databases

    Excellent! Thank you, that helped me make progress.

  12. #12
    Lively Member
    Join Date
    Nov 2007
    Location
    Doo Keep Doo !
    Posts
    108

    Re: Saving Images in Databases

    So this method is what they are talking about converting the image to a binary before it will be save in a database. And then once the image had been converted into a binary format no need to find the path of the image ? Wherever i go, if i get the database it will still recognize the image even though the image is not exist in a computer?

  13. #13

    Thread Starter
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    88,601

    Re: Saving Images in Databases

    Quote Originally Posted by VBLegend
    So this method is what they are talking about converting the image to a binary before it will be save in a database. And then once the image had been converted into a binary format no need to find the path of the image ? Wherever i go, if i get the database it will still recognize the image even though the image is not exist in a computer?
    All files are just a collection of bytes. Image files are no different. They are just a collection of bytes stored on the hard disk. You can store that same collection of bytes in a database, which is what this example does. When you create an Image object in VB.NET you read the collection of bytes that constitute the image from a Stream object. If you create an Image from a file then it's a FileStream. In this example it's a MemoryStream. The underlying implementation of the stream doesn't matter though. You can create an Image object from any type of stream at all, as long as the bytes you read from it represent a valid image.

    2007-2014

    Why is my data not saved to my database? | MSDN Data Walkthroughs
    MSDN "How Do I?" Videos: VB | C#
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts) | WP8 Turnstile Feather Transition with Pivot Control
    Beginner Tutorials: VB | C# | SQL

  14. #14
    Pro Grammar chris128's Avatar
    Join Date
    Jun 2007
    Location
    England
    Posts
    7,595

    Re: Saving Images in Databases

    Does storing images in a database like this take up a large amount of space in the DB? Is it the exact same amount of space as the JPG file (or whatever you select to save to the DB) takes up on your hard disk or does SQL compress it in any way?
    My free .NET Windows API library (Version 2.2 Released 12/06/2011)

    Blog: cjwdev.wordpress.com
    Web: www.cjwdev.co.uk


  15. #15

    Thread Starter
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    88,601

    Re: Saving Images in Databases

    I can't say definitively but I've never heard of databases compressing binary data. I guess you could always read the documentation for the binary data type(s) of your database of choice because I'm sure it would be mentioned there if compression is, or can be, performed.

    2007-2014

    Why is my data not saved to my database? | MSDN Data Walkthroughs
    MSDN "How Do I?" Videos: VB | C#
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts) | WP8 Turnstile Feather Transition with Pivot Control
    Beginner Tutorials: VB | C# | SQL

  16. #16
    Pro Grammar chris128's Avatar
    Join Date
    Jun 2007
    Location
    England
    Posts
    7,595

    Re: Saving Images in Databases

    So if there's no compression within SQL Server then it will just make the database bigger by however large the file displays as in windows?

    E.g: I select a 2 MB JPG image to upload into my database, so it makes the database 2 MB larger in size
    My free .NET Windows API library (Version 2.2 Released 12/06/2011)

    Blog: cjwdev.wordpress.com
    Web: www.cjwdev.co.uk


  17. #17
    PowerPoster
    Join Date
    May 2002
    Posts
    25,478

    Re: Saving Images in Databases

    yup, that's pretty much the case. I don't know of any DBMS that does compression on any data at all...

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  18. #18
    Pro Grammar chris128's Avatar
    Join Date
    Jun 2007
    Location
    England
    Posts
    7,595

    Re: Saving Images in Databases

    OK cool, its not a problem I was just wondering. Thanks for confirming
    My free .NET Windows API library (Version 2.2 Released 12/06/2011)

    Blog: cjwdev.wordpress.com
    Web: www.cjwdev.co.uk


  19. #19
    Pro Grammar chris128's Avatar
    Join Date
    Jun 2007
    Location
    England
    Posts
    7,595

    Re: Saving Images in Databases

    OK so I'm trying to implement this and just wondering why your Image is saved as database type Binary instead of Image ... is this just because it was a generic example and Image isnt a supported type in all databases or is there a reason why I shouldn't be using the Image type in SQL server for this?

    Thanks
    My free .NET Windows API library (Version 2.2 Released 12/06/2011)

    Blog: cjwdev.wordpress.com
    Web: www.cjwdev.co.uk


  20. #20
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    15,817

    Re: Saving Images in Databases

    What version of MS SQL Server are you using?

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  21. #21

    Thread Starter
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    88,601

    Re: Saving Images in Databases

    Quote Originally Posted by chris128
    OK so I'm trying to implement this and just wondering why your Image is saved as database type Binary instead of Image ... is this just because it was a generic example and Image isnt a supported type in all databases or is there a reason why I shouldn't be using the Image type in SQL server for this?

    Thanks
    Read post #3, then read the MSDN documentation for the SQL Server image data type.

    2007-2014

    Why is my data not saved to my database? | MSDN Data Walkthroughs
    MSDN "How Do I?" Videos: VB | C#
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts) | WP8 Turnstile Feather Transition with Pivot Control
    Beginner Tutorials: VB | C# | SQL

  22. #22
    Pro Grammar chris128's Avatar
    Join Date
    Jun 2007
    Location
    England
    Posts
    7,595

    Re: Saving Images in Databases

    2000 and 2005 - I've got it all working fine using the Image type but I just wondered if using Byte may be better for some reason?
    My free .NET Windows API library (Version 2.2 Released 12/06/2011)

    Blog: cjwdev.wordpress.com
    Web: www.cjwdev.co.uk


  23. #23
    New Member
    Join Date
    Feb 2009
    Posts
    9

    Re: Saving Images in Databases

    Quote Originally Posted by jmcilhinney

    Loading an image from a database field:
    VB.NET Code:
    1. Dim connection As New SqlConnection("connection string here")
    2. Dim command As New SqlCommand("SELECT Picture FROM MyTable WHERE ID = 1", connection)
    3.  
    4. connection.Open()
    5.  
    6. Dim pictureData As Byte() = DirectCast(command.ExecuteScalar(), Byte())
    7.  
    8. connection.Close()
    9.  
    10. Dim picture As Image = Nothing
    11.  
    12. 'Create a stream in memory containing the bytes that comprise the image.
    13. Using stream As New IO.MemoryStream(pictureData)
    14.     'Read the stream and create an Image object from the data.
    15.     picture = Image.FromStream(stream)
    16. End Using

    Saving an image to a database field:
    VB.NET Code:
    1. Dim connection As New SqlConnection("connection string here")
    2. Dim command As New SqlCommand("UPDATE MyTable SET Picture = @Picture WHERE ID = 1", connection)
    3.  
    4. 'Create an Image object.
    5. Using picture As Image = Image.FromFile("file path here")
    6.     'Create an empty stream in memory.
    7.     Using stream As New IO.MemoryStream
    8.         'Fill the stream with the binary data from the Image.
    9.         picture.Save(stream, Imaging.ImageFormat.Jpeg)
    10.  
    11.         'Get an array of Bytes from the stream and assign to the parameter.
    12.         command.Parameters.Add("@Picture", SqlDbType.VarBinary).Value = stream.GetBuffer()
    13.     End Using
    14. End Using
    15.  
    16. connection.Open()
    17. command.ExecuteNonQuery()
    18. connection.Close()
    Hi jmcilhinney,

    Thanks for this code you posted to help novices like me get around some problems.

    As I said, I'm a novice to programming. I'm presently developping a database (db) programme for my cooperative society. I'm using Visual Basic 2008 express edition connected to a MS Access database.

    One of the forms (MemberProfile) is meant to display the profile of each member with a picture of the member. I'm actually having problem with the picture. I got this code to load the picture into the picturebox:

    Code:
        Private Sub BrowseButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BrowseButton.Click
    
            Dim MyRow As Stream = Nothing
            Dim fo As New OpenFileDialog
    
            If fo.ShowDialog = DialogResult.OK Then
    
                Try
                    MyRow = fo.OpenFile()
    
                    If (MyRow IsNot Nothing) Then
                        M_photoPictureBox.Load(fo.FileName)
    
                        fo.OpenFile()
    
                    End If
                Catch ex As Exception
                    MessageBox.Show("Cannot read file from disk. Original error: " & ex.Message)
                Finally
                    ' Check this again, since we need to make sure we didn't throw an exception on open.
                    If (MyRow IsNot Nothing) Then
                        MyRow.Close()
                    End If
                End Try
    
    
            End If
        End Sub
    After loading the picture, I need to save it into the db and then be able to load each member's picture in the picturebox whenever the member's profile is displayed. With your sample code I tried to work something out but with no success. This is the code I have made from your sample code:

    - this is to save the picture
    Code:
        Private Sub UploadButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles UploadButton.Click
    
    
            Dim connection As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
                "Data Source=C:\Users\client\Documents\Visual Studio 2008\Projects\SavingsLoansCalc.mdb")
    
            Dim command As New OleDbCommand("UPDATE Member SET m_photo = @m_photo WHERE m_accnumber = ?", connection)
            'Create an Image object.
            Using picture As Image = Image.FromFile("C:\Users\client\Pictures\pass2.jpg")
    
                'Create an empty stream in memory.    
                Using stream As New IO.MemoryStream
                    'Fill the stream with the binary data from the Image.        
                    picture.Save(stream, Imaging.ImageFormat.Jpeg)
                    'Get an array of Bytes from the stream and assign to the parameter.        
                    command.Parameters.Add("@m_photo", OleDbType.VarBinary).Value = stream.GetBuffer()
                End Using
            End Using
    
            connection.Open()
            command.ExecuteNonQuery()
            connection.Close()
    
    
    
        End Sub
    - and this is to load it back for preview
    Code:
        Private Sub MemberProfileForm_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
            'TODO: This line of code loads data into the 'SavingsLoansDataSet.Liste_of_Banks' table. You can move, or remove it, as needed.
            Me.Liste_of_BanksTableAdapter.Fill(Me.SavingsLoansDataSet.Liste_of_Banks)
            'TODO: This line of code loads data into the 'SavingsLoansDataSet.Liste_of_Banks' table. You can move, or remove it, as needed.
            Me.MemberTableAdapter.Fill(Me.SavingsLoansDataSet.Member)
    
            Dim connection As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=C:\Users\client\Documents\Visual Studio 2008\Projects\SavingsLoansCalc.mdb")
    
            Dim command As New OleDbCommand("SELECT m_photo FROM Member WHERE m_accnumber = ?", connection)
    
            connection.Open()
    
            Dim pictureData As Byte() = DirectCast(command.ExecuteScalar(), Byte())
    
            connection.Close()
    
            Dim picture As Image = Nothing
            'Create a stream in memory containing the bytes that comprise the image.
    
            Using stream As New IO.MemoryStream(pictureData)
    
                'Read the stream and create an Image object from the data. 
    
                picture = Image.FromStream(stream)
    
            End Using
    
        End Sub
    Please, what am I missing out? I need to be able to load the picture from a specific or any drive, have each member's picture saved and displayed during the preview of the profile.

    Thanks in advance. Waiting impatiently to hear from you.

  24. #24

    Thread Starter
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    88,601

    Re: Saving Images in Databases

    Quote Originally Posted by Jbenzin
    Hi jmcilhinney,

    Thanks for this code you posted to help novices like me get around some problems.

    As I said, I'm a novice to programming. I'm presently developping a database (db) programme for my cooperative society. I'm using Visual Basic 2008 express edition connected to a MS Access database.

    One of the forms (MemberProfile) is meant to display the profile of each member with a picture of the member. I'm actually having problem with the picture. I got this code to load the picture into the picturebox:

    Code:
        Private Sub BrowseButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BrowseButton.Click
    
            Dim MyRow As Stream = Nothing
            Dim fo As New OpenFileDialog
    
            If fo.ShowDialog = DialogResult.OK Then
    
                Try
                    MyRow = fo.OpenFile()
    
                    If (MyRow IsNot Nothing) Then
                        M_photoPictureBox.Load(fo.FileName)
    
                        fo.OpenFile()
    
                    End If
                Catch ex As Exception
                    MessageBox.Show("Cannot read file from disk. Original error: " & ex.Message)
                Finally
                    ' Check this again, since we need to make sure we didn't throw an exception on open.
                    If (MyRow IsNot Nothing) Then
                        MyRow.Close()
                    End If
                End Try
    
    
            End If
        End Sub
    After loading the picture, I need to save it into the db and then be able to load each member's picture in the picturebox whenever the member's profile is displayed. With your sample code I tried to work something out but with no success. This is the code I have made from your sample code:

    - this is to save the picture
    Code:
        Private Sub UploadButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles UploadButton.Click
    
    
            Dim connection As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
                "Data Source=C:\Users\client\Documents\Visual Studio 2008\Projects\SavingsLoansCalc.mdb")
    
            Dim command As New OleDbCommand("UPDATE Member SET m_photo = @m_photo WHERE m_accnumber = ?", connection)
            'Create an Image object.
            Using picture As Image = Image.FromFile("C:\Users\client\Pictures\pass2.jpg")
    
                'Create an empty stream in memory.    
                Using stream As New IO.MemoryStream
                    'Fill the stream with the binary data from the Image.        
                    picture.Save(stream, Imaging.ImageFormat.Jpeg)
                    'Get an array of Bytes from the stream and assign to the parameter.        
                    command.Parameters.Add("@m_photo", OleDbType.VarBinary).Value = stream.GetBuffer()
                End Using
            End Using
    
            connection.Open()
            command.ExecuteNonQuery()
            connection.Close()
    
    
    
        End Sub
    - and this is to load it back for preview
    Code:
        Private Sub MemberProfileForm_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
            'TODO: This line of code loads data into the 'SavingsLoansDataSet.Liste_of_Banks' table. You can move, or remove it, as needed.
            Me.Liste_of_BanksTableAdapter.Fill(Me.SavingsLoansDataSet.Liste_of_Banks)
            'TODO: This line of code loads data into the 'SavingsLoansDataSet.Liste_of_Banks' table. You can move, or remove it, as needed.
            Me.MemberTableAdapter.Fill(Me.SavingsLoansDataSet.Member)
    
            Dim connection As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=C:\Users\client\Documents\Visual Studio 2008\Projects\SavingsLoansCalc.mdb")
    
            Dim command As New OleDbCommand("SELECT m_photo FROM Member WHERE m_accnumber = ?", connection)
    
            connection.Open()
    
            Dim pictureData As Byte() = DirectCast(command.ExecuteScalar(), Byte())
    
            connection.Close()
    
            Dim picture As Image = Nothing
            'Create a stream in memory containing the bytes that comprise the image.
    
            Using stream As New IO.MemoryStream(pictureData)
    
                'Read the stream and create an Image object from the data. 
    
                picture = Image.FromStream(stream)
    
            End Using
    
        End Sub
    Please, what am I missing out? I need to be able to load the picture from a specific or any drive, have each member's picture saved and displayed during the preview of the profile.

    Thanks in advance. Waiting impatiently to hear from you.
    You say you're having a problem. What problem? What actually happens that shouldn't, or was doesn't happen that should? Are you getting any error messages? You need to provide us with all the relevant information you can.

    2007-2014

    Why is my data not saved to my database? | MSDN Data Walkthroughs
    MSDN "How Do I?" Videos: VB | C#
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts) | WP8 Turnstile Feather Transition with Pivot Control
    Beginner Tutorials: VB | C# | SQL

  25. #25
    New Member
    Join Date
    Feb 2009
    Posts
    9

    Re: Saving Images in Databases

    Quote Originally Posted by jmcilhinney
    You say you're having a problem. What problem? What actually happens that shouldn't, or was doesn't happen that should? Are you getting any error messages? You need to provide us with all the relevant information you can.

    Thanks for your quick response and I'm sorry for not including those details.

    The code for the Browse button works fine; I'm able to load picture with it into the picturebox successfully.

    But I'm having problem with the code for saving the picture into the db. When I use the code like this:

    Code:
        Private Sub UploadButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles UploadButton.Click
    
    
            Dim connection As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
                "Data Source=C:\Users\client\Documents\Visual Studio 2008\Projects\SavingsLoansCalc.mdb")
    
            Dim command As New OleDbCommand("UPDATE Member SET m_photo = @m_photo WHERE m_accnumber = 'AGS/CCSS/0001'", connection)
            'Create an Image object.
            Using picture As Image = Image.FromFile("C:\Users\client\Pictures\pass2.jpg")
    
                'Create an empty stream in memory.    
                Using stream As New IO.MemoryStream
                    'Fill the stream with the binary data from the Image.        
                    picture.Save(stream, Imaging.ImageFormat.Jpeg)
                    'Get an array of Bytes from the stream and assign to the parameter.        
                    command.Parameters.Add("@m_photo", OleDbType.VarBinary).Value = stream.GetBuffer()
                End Using
            End Using
    
            connection.Open()
            command.ExecuteNonQuery()
            connection.Close()
    
    
    
        End Sub
    I get this error:

    "OleDbException was unhandled
    No value given for one or some required parameters."

    on this line:
    Code:
            command.ExecuteNonQuery()
    (Note: 'AGS/CCSS/0001' is a member's ID. That is my db is filled with five sample data for five members.)

    I then replace the "WHERE m_accnumber = 'AGS/CCSS/0001'" clause with this "WHERE m_accnumber = ?" that is using a place-holder. I get the same error as above. Following your suggestion to mojo69, I also tried using a place-holder for the picture and I get the same message.

    I now decided to remove the WHERE clause completely. When I loaded the picture into the picturebox and clicked on the Upload button, no error was given. I opened the db to see what has happened and I noticed that the five picture fields have been field with "Binary Data".

    With this development, I suppect that the problem is with the WHERE clause. I don't know what is the problem or how to put the clause because I think I'll need it to save the picture in the picture field for a specific member.

    The problem is how to get the picture of each member saved into the db. If I'm able to solve this, I'll then try the code for loading the picture for preview.

    My aim is to enable the user add members' pictures to their profiles and to be able to change the picture if a member wants it changed.

    Thanks in advance. I wait eagerly for your reply.
    Jbenzin

  26. #26
    Hyperactive Member jazFunk's Avatar
    Join Date
    Dec 2008
    Location
    Flint, MI
    Posts
    343

    Re: Saving Images in Databases

    I must me missing an important step. I've used your code and have no problem creating the bitmap and viewing it in a PictureBox on my form.

    I'm attempting to insert that bitmap into my table. I have an ole object field where the bitmap gets inserted. When viewing the table, that field reads "Long binary data".

    I'm now attempting to create a simple Access Report (for barcode labels) however the image does not display on the report. I have a bound object frame tied to that field but no image shows.

    I've posted in a database forum http://forums.databasejournal.com/sh...019#post125019

    ...but the help I received there seems to suggest I'm missing something, but who knows what.

    Thought I'd shoot this at you and see if anyone here can assist.

  27. #27

    Thread Starter
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    88,601

    Re: Saving Images in Databases

    Quote Originally Posted by jazFunk View Post
    I must me missing an important step. I've used your code and have no problem creating the bitmap and viewing it in a PictureBox on my form.

    I'm attempting to insert that bitmap into my table. I have an ole object field where the bitmap gets inserted. When viewing the table, that field reads "Long binary data".

    I'm now attempting to create a simple Access Report (for barcode labels) however the image does not display on the report. I have a bound object frame tied to that field but no image shows.

    I've posted in a database forum http://forums.databasejournal.com/sh...019#post125019

    ...but the help I received there seems to suggest I'm missing something, but who knows what.

    Thought I'd shoot this at you and see if anyone here can assist.
    If you can retrieve the data from the database again using ADO.NET and display it in a PictureBox then that's proof that you're using this code correctly. In that case the issue must be somewhere with the Access report, which is beyond the scope of this trhead and beyond the scope of my experience.

    2007-2014

    Why is my data not saved to my database? | MSDN Data Walkthroughs
    MSDN "How Do I?" Videos: VB | C#
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts) | WP8 Turnstile Feather Transition with Pivot Control
    Beginner Tutorials: VB | C# | SQL

  28. #28
    Addicted Member
    Join Date
    Sep 2008
    Posts
    183

    Re: Saving Images in Databases

    I just wanted to point out that storing images directly in an Access database is not advised by Microsoft for performance issues. Instead they recommend creating a text field with a path to the image file (which is accessable by the DB). On your reports you can then update the image location field of the image box using the string. I've found my reports open much quicker using this method as opposed to reading them from a table.

    http://office.microsoft.com/en-us/ac...802251033.aspx

    However, embedding images can rapidly inflate the size of your database and cause it to run slowly. This is especially true if you store GIF and JPEG files, because OLE creates additional bitmap files that contain display information for each of your image files, and those additional files can be larger than your original images. In addition, this method only supports the Windows Bitmap (.bmp) and Device Independent Bitmap (.dib) graphic file formats. If you want to display other common types of image files, such as GIF and JPEG images, you have to install additional software.

  29. #29
    PowerPoster
    Join Date
    May 2002
    Posts
    25,478

    Re: Saving Images in Databases

    Two points to note: 1) that recommendation is for Access only. SQL Server, Oracle, MySQL, and some of the larger DBMS can handle the data just fine without issues. 2) There are problems with path storage as well, permissions, things get moved, servers get renamed, and as sure as I'm sitting here, things get deleted by accident. I'm not saying it's a bad idea, I'm simply saying that one needs to look at all of the risks before deciding on a particular path.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  30. #30
    Member
    Join Date
    Nov 2009
    Posts
    55

    Re: Saving Images in Databases

    I get the below error at
    Code:
    picture.Save(stream, Imaging.ImageFormat.Jpeg)
    Code:
    System.Runtime.InteropServices.ExternalException was unhandled
      ErrorCode=-2147467259
      Message="A generic error occurred in GDI+."
      Source="System.Drawing"
      StackTrace:
           at System.Drawing.Image.Save(Stream stream, ImageCodecInfo encoder, EncoderParameters encoderParams)
           at System.Drawing.Image.Save(Stream stream, ImageFormat format)
           at WindowsApplication1.frmCustomers.Button1_Click(Object sender, EventArgs e) in Z:\My Projects\Salon Sanda\Salon Sanda\Form1.vb:line 117
           at System.Windows.Forms.Control.OnClick(EventArgs e)
           at System.Windows.Forms.Button.OnClick(EventArgs e)
           at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
           at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
           at System.Windows.Forms.Control.WndProc(Message& m)
           at System.Windows.Forms.ButtonBase.WndProc(Message& m)
           at System.Windows.Forms.Button.WndProc(Message& m)
           at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
           at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
           at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
           at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
           at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData)
           at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
           at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
           at System.Windows.Forms.Application.Run(ApplicationContext context)
           at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun()
           at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel()
           at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[] commandLine)
           at WindowsApplication1.My.MyApplication.Main(String[] Args) in 17d14f5c-a337-4978-8281-53493378c1071.vb:line 81
           at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)
           at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
           at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
           at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
           at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
           at System.Threading.ThreadHelper.ThreadStart()
      InnerException:
    My exact code is

    Code:
     Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
            Dim command As New SqlCeCommand("UPDATE Customers SET Picture = @Picture WHERE CustomerID = 'C1001'", conn)
            'Create an Image object.
            Using picture As Image = Me.imgCustomerImage.Image
                'Create an empty stream in memory.
                Using stream As New IO.MemoryStream
                    'Fill the stream with the binary data from the Image.
                    picture.Save(stream, Imaging.ImageFormat.Jpeg)
                    'Get an array of Bytes from the stream and assign to the parameter.
                    command.Parameters.Add("@Picture", SqlDbType.Image).Value = stream.GetBuffer()
                    'command.Parameters.Add("@CustomerID", SqlDbType.NChar, 5).Value = InputBox("")
                End Using
            End Using
            conn.Open()
            command.ExecuteNonQuery()
            conn.Close()
        End Sub
    Could you please help??

  31. #31
    Member
    Join Date
    Nov 2009
    Posts
    55

    Re: Saving Images in Databases

    I could resolve the above by just changing
    Code:
    Using picture As Image = Me.imgCustomerImage.Image
    to
    Code:
    Using picture As Image = Image.FromFile(Me.imgCustomerImage.ImageLocation)
    But is there a way to use a Image from a Picturebox without using the ImageLocaion Property?

    Reezan
    Last edited by reezan; Nov 25th, 2009 at 01:08 PM.

  32. #32

    Thread Starter
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    88,601

    Re: Saving Images in Databases

    Quote Originally Posted by reezan View Post
    I could resolve the above by just changing
    Code:
    Using picture As Image = Me.imgCustomerImage.Image
    to
    Code:
    Using picture As Image = Image.FromFile(Me.imgCustomerImage.ImageLocation)
    But is there a way to use a Image from a Picturebox without using the ImageLocaion Property?

    Reezan
    You can't Save an Image that has been loaded into a PictureBox using either the Load method or ImageLocation property. If the Image has been created by calling Image.FromFile in the first place, then you can save it. Otherwise, you'd have to create a new Bitmap, draw your existing Image onto that, then save the copy. You may lose something in the process though.

    2007-2014

    Why is my data not saved to my database? | MSDN Data Walkthroughs
    MSDN "How Do I?" Videos: VB | C#
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts) | WP8 Turnstile Feather Transition with Pivot Control
    Beginner Tutorials: VB | C# | SQL

  33. #33
    Fanatic Member bharanidharanit's Avatar
    Join Date
    Oct 2008
    Location
    India
    Posts
    673

    Re: Saving Images in Databases

    Quote Originally Posted by jmcilhinney View Post
    #
    Dim picture As Image = Nothing
    #

    #
    'Create a stream in memory containing the bytes that comprise the image.
    #
    Using stream As New IO.MemoryStream(pictureData)
    #
    'Read the stream and create an Image object from the data.
    #
    picture = Image.FromStream(stream)
    Hi with this code how to show the picture in ImageBox in asp.net?

  34. #34
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    15,817

    Re: Saving Images in Databases

    Look at this thread - it was an early web app I did but shows how I got images from a database onto a webpage

    http://www.vbforums.com/showthread.p...es#post3117257

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  35. #35
    Fanatic Member bharanidharanit's Avatar
    Join Date
    Oct 2008
    Location
    India
    Posts
    673

    Re: Saving Images in Databases

    With this coding, i can able to display image in imagebox, but it writing binary datas above the page as text.
    Code:
    Protected Sub imgUpload_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles imgUpload.Load
            Dim cmd As New OleDbCommand("select ImageData from ImageTable where ImageName='menu.JPG'", DataConn)
            DataConn.Open()
            Dim dr As OleDbDataReader
            cmd.ExecuteNonQuery()
            dr = cmd.ExecuteReader
            dr.Read()
            Response.BinaryWrite(dr("ImageData"))
            dr.Close()
        End Sub

  36. #36
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    15,817

    Re: Saving Images in Databases

    I don't recall the details of this - as I did it a couple of years ago - but the thread I posted a link to shows this code

    Code:
        Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
            Dim dcn As New SqlConnection
            dcn.ConnectionString = "Server=fps-lap-sz\sqlexpress; Initial Catalog=Stufiles; Integrated Security=SSPI"
            Try
                dcn.Open()
                Dim drc As New SqlCommand
                drc.Connection = dcn
                drc.CommandType = Data.CommandType.StoredProcedure
                drc.CommandText = "GetStuPhoto_P"
                drc.Parameters.Add(New SqlParameter("@StuId", Data.SqlDbType.VarChar, 255, Data.ParameterDirection.Input _
                                            , False, 0, 0, "", Data.DataRowVersion.Default, Request.QueryString("imageid")))
                Dim bytArrayContent As Byte() = DirectCast(drc.ExecuteScalar, Byte())
                'bytArrayContent = CType(dr.Item("ImageField"), Byte())
                Response.ContentType = "image/jpeg"
                Response.OutputStream.Write(bytArrayContent, 0, bytArrayContent.Length)
                Response.End()
                txtMessage.Text = "Student Displayed!"
            Catch ex As Exception
                txtMessage.Text = "Student Not Found!"
            End Try

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  37. #37
    Addicted Member yolandre's Avatar
    Join Date
    Oct 2004
    Posts
    167

    Re: Saving Images in Databases

    Dear all,
    Thanks to all those who viewed this post, and possibly gave it some thought. Found a work-around and managed to get bulk volume of images into MS Access 2003.

    Thanks!
    Last edited by yolandre; Jan 11th, 2010 at 05:19 AM.

  38. #38
    New Member
    Join Date
    Mar 2011
    Posts
    10

    Re: Saving Images in Databases

    what if the image was imported into a form and was modified during runtime... and wanted to be saved into a database??? what will be its filepath??

  39. #39
    PowerPoster
    Join Date
    May 2002
    Posts
    25,478

    Re: Saving Images in Databases

    If you are saving the image itself in the database, (and are using .NET) then all you need to do is convert the image to a stream and red rover red rover, send it on over.
    If you are storing filenames as strings instead, well then you need to generate a filename, save your image to it, then save the file name to the database.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  40. #40
    Fanatic Member
    Join Date
    Mar 2009
    Posts
    760

    Re: Saving Images in Databases

    jmcilhinney what about the delete and update of the pictures
    time to amp up the beef

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.