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

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

huBelialAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

CodeCruiserCommented:
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

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
huBelialAuthor 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
0
CodeCruiserCommented:
Add this line

msgbox data.Length()

after the

Dim data As Byte() = DirectCast(rs.Fields("Field1").Value, Byte())
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

huBelialAuthor Commented:
It displays the bytes.  
0
huBelialAuthor Commented:
This is what it displays.
byte1.png
0
CodeCruiserCommented:
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.
0
huBelialAuthor 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
0
CodeCruiserCommented:
The messagebox above indicates that the bytes are being read correctly. Its the saving of the images which is not working.
0
huBelialAuthor Commented:
Do you think I have to do any conversion before I can write it to file?
0
CodeCruiserCommented:
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.
0
huBelialAuthor 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

0
CodeCruiserCommented:
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.
0
huBelialAuthor 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.
0
huBelialAuthor Commented:
Oh, now its displaying an error message at line "Dim img As Image = image.FromStream(ms)"
stating that "Parameter is not valid."
0
CodeCruiserCommented:
Looks like the byte stream is not a valid image. Show the code you use to save images.
0
huBelialAuthor 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
0
CodeCruiserCommented:
Set the image column data type to Binary and try this tutorial

http://www.dreamincode.net/code/snippet2846.htm
0
huBelialAuthor 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
0
CodeCruiserCommented:
But its not working so we have to try other things. Also try following that tutorial and use OLEDB providers instead of antique DAO.
0
huBelialAuthor Commented:
Alright.  I'll try that tutorial.

Thank you very much for helping me CodeCruiser. :p
0
CodeCruiserCommented:
Glad to help :-)
0
huBelialAuthor 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

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

picturebox1.Image = GetImageFromDB("C:\Images\1.jpg")
0
huBelialAuthor 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
0
CodeCruiserCommented:
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)
0
huBelialAuthor 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..
                   
0
huBelialAuthor Commented:
I think it has to do with my column datatype... because I tried adding a value to the name column and it worked.
0
huBelialAuthor 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

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

http://www.vbforums.com/showthread.php?t=469562
0
CodeCruiserCommented:
What's different in that tutorial?
0
huBelialAuthor Commented:
CodeCruiser helped me a lot.  He is the man!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.