-
May 18th, 2007, 05:06 AM
#1
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:
Dim connection As New SqlConnection("connection string here") Dim command As New SqlCommand("SELECT Picture FROM MyTable WHERE ID = 1", 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
Saving an image to a database field:
VB.NET Code:
Dim connection As New SqlConnection("connection string here") Dim command As New SqlCommand("UPDATE MyTable SET Picture = @Picture WHERE ID = 1", connection) 'Create an Image object. Using picture As Image = Image.FromFile("file path here") '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.VarBinary).Value = stream.ToArray() End Using End Using connection.Open() command.ExecuteNonQuery() 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; Jan 22nd, 2020 at 11:18 PM.
-
Mar 6th, 2008, 10:56 AM
#2
Hyperactive Member
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 12:16 PM.
-
Mar 6th, 2008, 07:48 PM
#3
Re: Saving Images in Databases
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.
-
Mar 6th, 2008, 08:42 PM
#4
Hyperactive Member
Re: Saving Images in Databases
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?
-
Mar 6th, 2008, 10:36 PM
#5
Re: Saving Images in Databases
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.
-
Jun 18th, 2008, 03:35 AM
#6
Member
Re: Saving Images in Databases
Thanks JM it is really a great help
-
Jun 27th, 2008, 06:28 PM
#7
Fanatic Member
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.
-
Jun 27th, 2008, 07:45 PM
#8
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
-
Jun 27th, 2008, 08:01 PM
#9
Fanatic Member
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.
-
Jun 27th, 2008, 08:48 PM
#10
Re: Saving Images in Databases
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.
-
Jun 28th, 2008, 01:23 PM
#11
Fanatic Member
Re: Saving Images in Databases
Excellent! Thank you, that helped me make progress.
-
Jul 20th, 2008, 02:16 AM
#12
Lively Member
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?
-
Jul 20th, 2008, 03:26 AM
#13
Re: Saving Images in Databases
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.
-
Dec 8th, 2008, 08:49 AM
#14
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?
-
Dec 8th, 2008, 09:14 AM
#15
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.
-
Dec 8th, 2008, 09:20 AM
#16
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
-
Dec 8th, 2008, 10:49 AM
#17
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
-
Dec 8th, 2008, 11:54 AM
#18
Re: Saving Images in Databases
OK cool, its not a problem I was just wondering. Thanks for confirming
-
Dec 22nd, 2008, 06:34 AM
#19
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
-
Dec 22nd, 2008, 07:26 AM
#20
Re: Saving Images in Databases
What version of MS SQL Server are you using?
-
Dec 22nd, 2008, 07:29 AM
#21
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?
-
Dec 22nd, 2008, 07:29 AM
#22
Re: Saving Images in Databases
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.
-
Feb 5th, 2009, 05:37 AM
#23
New Member
Re: Saving Images in Databases
Originally Posted by jmcilhinney
Loading an image from a database field:
VB.NET Code:
Dim connection As New SqlConnection("connection string here")
Dim command As New SqlCommand("SELECT Picture FROM MyTable WHERE ID = 1", 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
Saving an image to a database field:
VB.NET Code:
Dim connection As New SqlConnection("connection string here")
Dim command As New SqlCommand("UPDATE MyTable SET Picture = @Picture WHERE ID = 1", connection)
'Create an Image object.
Using picture As Image = Image.FromFile("file path here")
'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.VarBinary).Value = stream.GetBuffer()
End Using
End Using
connection.Open()
command.ExecuteNonQuery()
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.
-
Feb 5th, 2009, 06:26 AM
#24
Re: Saving Images in Databases
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.
-
Feb 6th, 2009, 06:40 AM
#25
New Member
Re: Saving Images in Databases
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
-
May 19th, 2009, 10:43 AM
#26
Hyperactive Member
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.
-
May 19th, 2009, 06:39 PM
#27
Re: Saving Images in Databases
Originally Posted by jazFunk
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.
-
Aug 20th, 2009, 08:12 AM
#28
Addicted Member
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.
-
Aug 20th, 2009, 08:27 AM
#29
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
-
Nov 25th, 2009, 10:59 AM
#30
Member
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??
-
Nov 25th, 2009, 01:29 PM
#31
Member
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 02:08 PM.
-
Nov 25th, 2009, 06:28 PM
#32
Re: Saving Images in Databases
Originally Posted by reezan
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.
-
Dec 13th, 2009, 10:23 AM
#33
Fanatic Member
Re: Saving Images in Databases
Originally Posted by jmcilhinney
#
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?
-
Dec 13th, 2009, 11:17 AM
#34
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
-
Dec 13th, 2009, 11:33 AM
#35
Fanatic Member
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
-
Dec 13th, 2009, 11:42 AM
#36
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
-
Jan 8th, 2010, 12:27 AM
#37
Addicted Member
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 06:19 AM.
-
Mar 4th, 2011, 08:06 AM
#38
New Member
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??
-
Mar 4th, 2011, 09:56 AM
#39
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
-
Mar 4th, 2011, 02:43 PM
#40
Banned
Re: Saving Images in Databases
jmcilhinney what about the delete and update of the pictures
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|