Read images from access database and storing the images into a folder.

huBelial
huBelial used Ask the Experts™
on
I have an access database that has a column with OLE datatype. I use this column to store jpeg images.  Well, I'm trying to write a program in VB.NET that would read the images and store the images into a folder.  My program runs fine but the images that are being saved are blank and I don't know why.  Can someone please take a look?

Note: The way I save the images is by dragging the images into the column in Access.


Option Explicit On
Imports System.IO

Public Class Form1
    Private cn As ADODB.Connection
    Private rs As ADODB.Recordset
    Dim test As OleDb.OleDbDataReader
    Dim RecCount As Integer
    Dim image As String = "C:\Image\"
    Dim format As String = ".Jpeg"
    'Dim c As New cDibSection

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        cn = New ADODB.Connection
        cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                              "Data Source=C:\Database3.accdb"
        cn.Open()
        rs = New ADODB.Recordset
        rs.Open("Select * FROM Table1", cn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockBatchOptimistic, 1)

        Do While Not rs.EOF
            'MsgBox(rs.Fields("firstname").Value)
            'MsgBox(rs.Fields("lastname").Value)
            Dim saveFile As FileStream = File.Create("C:\Image\image1.Jpg")
            Dim data As Byte() = DirectCast(rs.Fields("Field1").Value, Byte())
            saveFile.Write(data, 0, data.Length)
            saveFile.Close()

            rs.MoveNext()
        Loop

        cn.Close()
    End Sub
End Class

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Top Expert 2014
Commented:
You are overwriting the same file in loop. Try using this code. Also step through the code to make sure the bytes are being retrieved



Do While Not rs.EOF
            Dim data As Byte() = DirectCast(rs.Fields("Field1").Value, Byte())
            IO.File.WriteAllBytes("C:\Images\" & GUID.NewGuid.ToString & ".jpg", data)
            rs.MoveNext()
        Loop

Open in new window

Author

Commented:
I'm still getting blank images.  How would I step through to see the bytes are received? I'm really new to VB.NET

 Blank images
Most Valuable Expert 2012
Top Expert 2014

Commented:
Add this line

msgbox data.Length()

after the

Dim data As Byte() = DirectCast(rs.Fields("Field1").Value, Byte())
Angular Fundamentals

Learn the fundamentals of Angular 2, a JavaScript framework for developing dynamic single page applications.

Author

Commented:
It displays the bytes.  

Author

Commented:
This is what it displays.
byte1.png
Most Valuable Expert 2012
Top Expert 2014

Commented:
So it is retrieving the image as expected. The problem seems to be that you are using Win 7/vista which does not allow programs to access many folders such as C drive and its subfolders. If you have a D/E drive, try using that for saving and see if it works.

Author

Commented:
I still got blank images when I saved the images to my D drive. Do you think it has anything to do with my Access database?

Here is my example database.


Database3.accdb
Most Valuable Expert 2012
Top Expert 2014

Commented:
The messagebox above indicates that the bytes are being read correctly. Its the saving of the images which is not working.

Author

Commented:
Do you think I have to do any conversion before I can write it to file?
Most Valuable Expert 2012
Top Expert 2014

Commented:
I dont think. If you are saving bytes then reading bytes should work.

Let's try this.

Add a picture box on the form and then add this inplace of msgbox

Dim ms As New MemorySteam(data)
Dim img As Image = Image.FromStream(ms)
picturebox1.Image = img


see if it loads.

Author

Commented:
I got an error message at "Dim img As Image = image.FromStream(ms)"
Saying: 'FromStream' is not a member of 'String'

Option Explicit On
Imports System.IO

Public Class Form1
    Private cn As ADODB.Connection
    Private rs As ADODB.Recordset
    Dim test As OleDb.OleDbDataReader
    Dim RecCount As Integer
    Dim image As String = "C:\Image\"
    Dim format As String = ".Jpeg"
    'Dim c As New cDibSection

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        cn = New ADODB.Connection
        cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                              "Data Source=C:\Database3.accdb"
        cn.Open()
        rs = New ADODB.Recordset
        rs.Open("Select * FROM Table1", cn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockBatchOptimistic, 1)

        Do While Not rs.EOF
            'MsgBox(rs.Fields("firstname").Value)
            'MsgBox(rs.Fields("lastname").Value)
            'Dim test = rs.fields
            'Dim saveFile As FileStream = File.Create("C:\Image\image1.Jpg")
            Dim data As Byte() = DirectCast(rs.Fields("Field1").Value, Byte())
            Dim ms As New MemoryStream(data)
            Dim img As Image = image.FromStream(ms)
            PictureBox1.Image = img

            IO.File.WriteAllBytes("D:\Image\" & Guid.NewGuid.ToString & ".jpg", data)
            'saveFile.Write(data, 0, data.Length)
            'saveFile.Close()

            rs.MoveNext()
        Loop

        cn.Close()
    End Sub

    Private Sub PictureBox1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles PictureBox1.Click

    End Sub
End Class

Open in new window

Most Valuable Expert 2012
Top Expert 2014

Commented:
Change this

Dim image As String = "C:\Image\"

to

Dim imageDir As String = "C:\Image\"

and update any reference to it. This is being confused with the Image namespace.

Author

Commented:
When I executed the program, it did not display the images on the picture box.  Also, I couldn't move the form.. I think it froze.

Author

Commented:
Oh, now its displaying an error message at line "Dim img As Image = image.FromStream(ms)"
stating that "Parameter is not valid."
Most Valuable Expert 2012
Top Expert 2014

Commented:
Looks like the byte stream is not a valid image. Show the code you use to save images.

Author

Commented:
I saved the images into my access database manually by creating an OLE column and then dragging the images into the column.

db1.png
db2.png
Most Valuable Expert 2012
Top Expert 2014

Commented:
Set the image column data type to Binary and try this tutorial

http://www.dreamincode.net/code/snippet2846.htm

Author

Commented:
There is not a binary option.

But according to :http://www.w3schools.com/SQL/sql_datatypes.asp

"Ole Object      Can store pictures, audio, video, or other BLOBs (Binary Large OBjects)"

db1.png
Most Valuable Expert 2012
Top Expert 2014

Commented:
But its not working so we have to try other things. Also try following that tutorial and use OLEDB providers instead of antique DAO.

Author

Commented:
Alright.  I'll try that tutorial.

Thank you very much for helping me CodeCruiser. :p
Most Valuable Expert 2012
Top Expert 2014

Commented:
Glad to help :-)

Author

Commented:
How would I call these funtions for testing?

I used his code and made some minor changes to the connection string and the insert and select statements.


Imports System.IO
Imports System.Data.OleDb
Imports System.Drawing
Imports System.Drawing.Imaging 

Public Class Form1
    'First save the image to the table
    Private Function SaveImageToDB(ByRef name As String) As Boolean
        Try
            Dim conn As New OleDbConnection
            Dim cmd As OleDbCommand

            conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0 Source=D:\ImageTable.accdb"
            conn.Open()

            cmd = conn.CreateCommand()
            cmd.CommandText = "INSERT INTO ImageTable(Image, Image) VALUES (@Name, @Image)"
            Dim imgByteArray() As Byte
            Try
                Dim stream As New MemoryStream
                Dim bmp As New Bitmap(stream)

                bmp.Save(stream, ImageFormat.Jpeg)
                imgByteArray = stream.ToArray()
                stream.Close()

                cmd.Parameters.AddWithValue("@Name", name)
                cmd.Parameters.AddWithValue("@Image", imgByteArray)

                If DirectCast(cmd.ExecuteNonQuery(), Integer) > 0 Then
                    Return True
                End If
            Catch ex As Exception
                MessageBox.Show(ex.Message)
                Return False
            End Try

            conn.Close()
            cmd.Dispose()
            conn.Dispose()
        Catch ex As Exception
            MessageBox.Show(ex.Message)
            Return False
        End Try
    End Function

    'Retrieve image from table
    Public Function GetImageFromDB(ByRef imageName As String) As Bitmap
        Try
            Dim conn As New OleDbConnection
            Dim cmd As OleDbCommand
            Dim reader As OleDbDataReader

            conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0 Source=D:\ImageTable.accdb"
            conn.Open()

            cmd = conn.CreateCommand()
            cmd.CommandText = "SELECT Image FROM ImageTable WHERE Name = '" & imageName & "'"

            reader = cmd.ExecuteReader

            If reader.Read Then
                Dim imgByteArray() As Byte

                Try
                    imgByteArray = CType(reader(0), Byte())
                    Dim stream As New MemoryStream(imgByteArray)
                    Dim bmp As New Bitmap(stream)
                    stream.Close()
                    Return bmp
                Catch ex As Exception
                    MessageBox.Show(ex.Message)
                    Return Nothing
                End Try
            End If

            reader.Close()
            conn.Close()

            cmd.Dispose()
            conn.Dispose()
        Catch ex As Exception
            MessageBox.Show(ex.Message)
            Return Nothing
        End Try
    End Function
End Class

Open in new window

Most Valuable Expert 2012
Top Expert 2014

Commented:
Call the Save sub by passing it the full image path and call the retrieve sub similarly.

picturebox1.Image = GetImageFromDB("C:\Images\1.jpg")

Author

Commented:
I got "Parameter is not valid" when I tried to call the SaveImageToDB function.

Dim runningVB As Boolean = SaveImageToDB("D:\Bao1.jpg"


I think its failing at this line "bmp.Save(stream, ImageFormat.Jpeg)"  because I used MsgBox to display random messages and right after that line, the program did not display my message.  

Imports System.IO
Imports System.Data.OleDb
Imports System.Drawing
Imports System.Drawing.Imaging 

Public Class Form1
    'First save the image to the table
    Dim runningVB As Boolean = SaveImageToDB("D:\Bao1.jpg")
    Private Function SaveImageToDB(ByRef name As String) As Boolean
        Try
            Dim conn As New OleDbConnection
            Dim cmd As OleDbCommand

            conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=D:\ImageTable.accdb;Persist Security Info=False;"
            conn.Open()

            cmd = conn.CreateCommand()
            cmd.CommandText = "INSERT INTO ImageTable(Name, Image) VALUES (@Name, @Image)"
            Dim imgByteArray() As Byte
            Try
                Dim stream As New MemoryStream
                Dim bmp As New Bitmap(stream)

                bmp.Save(stream, ImageFormat.Jpeg)
                imgByteArray = stream.ToArray()
                stream.Close()

                cmd.Parameters.AddWithValue("@Name", name)
                cmd.Parameters.AddWithValue("@Image", imgByteArray)

                If DirectCast(cmd.ExecuteNonQuery(), Integer) > 0 Then
                    Return True
                End If
            Catch ex As Exception
                MessageBox.Show(ex.Message)
                Return False
            End Try

            conn.Close()
            cmd.Dispose()
            conn.Dispose()
        Catch ex As Exception
            MessageBox.Show(ex.Message)
            Return False
        End Try
    End Function

    'Retrieve image from table
    Public Function GetImageFromDB(ByRef imageName As String) As Bitmap
        Try
            Dim conn As New OleDbConnection
            Dim cmd As OleDbCommand
            Dim reader As OleDbDataReader

            conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0 Source=D:\ImageTable.accdb"
            conn.Open()

            cmd = conn.CreateCommand()
            cmd.CommandText = "SELECT Image FROM ImageTable WHERE Name = '" & imageName & "'"

            reader = cmd.ExecuteReader

            If reader.Read Then
                Dim imgByteArray() As Byte

                Try
                    imgByteArray = CType(reader(0), Byte())
                    Dim stream As New MemoryStream(imgByteArray)
                    Dim bmp As New Bitmap(stream)
                    stream.Close()
                    Return bmp
                Catch ex As Exception
                    MessageBox.Show(ex.Message)
                    Return Nothing
                End Try
            End If

            reader.Close()
            conn.Close()

            cmd.Dispose()
            conn.Dispose()
        Catch ex As Exception
            MessageBox.Show(ex.Message)
            Return Nothing
        End Try
    End Function
End Class

Open in new window

image1.png
Most Valuable Expert 2012
Top Expert 2014

Commented:
Change this section


                Dim stream As New MemoryStream
                Dim bmp As New Bitmap(stream)

                bmp.Save(stream, ImageFormat.Jpeg)
                imgByteArray = stream.ToArray()
                stream.Close()


to


                imgByteArray = IO.File.ReadAllBytes(name)

Author

Commented:
It fails at line : "If DirectCast(cmd.ExecuteNonQuery(), Integer) > 0 Then"
with the error message: "Syntax error in Insert Into Statement."

It looks right to me..
                   

Author

Commented:
I think it has to do with my column datatype... because I tried adding a value to the name column and it worked.

Author

Commented:
I tried using a different method to insert a sample image into my database and I got the same error.
Imports System.IO
Imports System.Data.OleDb
Imports System.Drawing
Imports System.Drawing.Imaging

Public Class Form1

    Dim runningVB As Boolean = SaveImage("D:\Bao1.jpg")

    Private Function SaveImage(ByVal MyPathToImageFile As String) As Boolean

        Dim o As System.IO.FileStream
        Dim r As StreamReader
        Dim jpgFile As String = MyPathToImageFile

        o = New FileStream(jpgFile, FileMode.Open, FileAccess.Read, FileShare.Read)
        r = New StreamReader(o)

        Try
            Dim FileByteArray(o.Length - 1) As Byte

            o.Read(FileByteArray, 0, o.Length)

            Dim Con As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0; Data Source=D:\ImageTable.accdb")
            Dim Sql As String = "INSERT INTO ImageTable (Image) VALUES (?)"
            Dim CmdObj As New System.Data.OleDb.OleDbCommand(Sql, Con)

            CmdObj.Parameters.Add("@Image", System.Data.OleDb.OleDbType.Binary, o.Length).Value = FileByteArray

            Con.Open()
            CmdObj.ExecuteNonQuery()

            Con.Close()

            Return True

        Catch ex As Exception

            MsgBox(ex.ToString)

            Return False

        End Try

    End Function

End Class

Open in new window

Most Valuable Expert 2012
Top Expert 2014

Commented:
Do you have SQL Server installed?
Commented:
I finally got it done.  This tutorial helped me insert images into the database:

http://www.vbforums.com/showthread.php?t=469562
Most Valuable Expert 2012
Top Expert 2014

Commented:
What's different in that tutorial?

Author

Commented:
CodeCruiser helped me a lot.  He is the man!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial