Store Images in SQL Server Using [Link] and OleDB I've wasted most of my day trying to get my VB.
Net program to store pictures in a SQL Server database and have finally got it working. Most of the information and examples on the Internet refer to [Link] or C# or are not using OleDB. And as usual, I had to wade through all the infuriating spam pages to find the info I needed. So here it is. 1. Add Image Field
First, add your field to your table with the Management Studio, or use a statement something like this:
ALTER TABLE myTable ADD myPicture image;
I also had this working using VarBinary(Max) as the data type. I'm not sure what the difference is, whether both types are available in different versions of SQL Server, or why you'd use one over the other. 2. Load Image Into PictureBox
Load an image into a PictureBox on the screen, so we can see what we're working with. Obviously you don't need to do this as two stages but it might help you to get it working. I am also scaling and resizing at this stage to ensure the pictures are nice and small as we're storing the images directly in the database (not included here).
Private Sub btnSetPic_Click(ByVal sender As [Link], _ ByVal e As [Link]) Handles [Link] Dim dlg As OpenFileDialog Dim img As Image Try dlg = New OpenFileDialog [Link] = "All Pictures|*.bmp;*.gif;*.jpg;*.png|" & _ "Bitmaps|*.bmp|GIFs|*.gif|JPEGs|*.jpg|PNGs|*.png" If [Link] = [Link] Then [Link] = [Link] [Link] = New Bitmap([Link]) End If Catch ex As Exception ' My error handler m_errors.handle(ex) Finally
[Link]
img = Nothing dlg = Nothing [Link] = [Link] End Try End Sub
3.
Save the image from the PictureBox into the database
sKey is the primary key of the row that we want to contain the picture. myImage is the image we want to store. So call the function like: savePicture("FRED", [Link])
Private Sub savePicture(ByVal sKey As String, ByVal myImage As Image) Dim Dim Dim Dim Dim Dim Dim Try sql = _ "SELECT rowId, myPicture " & _ "FROM myTable " & _ "WHERE rowId = '" & Replace(sKey, "'", "''") & "';" cm = New [Link](sql, m_dbConnection) da = New [Link](cm) [Link](dt) If [Link] = 0 Then Throw New CWarningException("Row " & sKey & " not found") End If If myImage Is Nothing Then [Link](0)("myPicture") = [Link] Else mstr = New [Link] [Link](mstr, [Link]) arrImage = [Link] [Link](0)("myPicture") = arrImage End If cb = New [Link](da) [Link](dt) Catch ex As Exception Throw Finally arrImage = Nothing mstr = Nothing dt = Nothing cb = Nothing sql As String da As [Link] cm As [Link] cb As [Link] dt As New DataTable mstr As [Link] arrImage() As Byte
[Link]
cm = Nothing da = Nothing End Try End Sub
4.
Retrieve the picture
Load the image data from the database and read into an Image data type. First, get the DataTable containing the Row with the Image data we want to load into a DataTable called dt. Then use something like the following:
Dim myImage As Image Dim barr() As Byte Dim imgstr As [Link] If IsDBNull(dr("myPicture")) Then ' No picture stored myImage = Nothing Else barr = CType(dr("myPicture"), Byte()) If [Link](0) > 0 Then imgstr = New [Link](barr) myImage = [Link](imgstr) Else myImage = Nothing End If End If
You can then set myImage into your PictureBox or whatever. Hopefully that should put you on the right track. I'm sure you'll have to fiddle about with this code a bit to get it working, but the method works.
[Link]