Storing and Retrieving Image Using Ms Access

[androidz]

good day is there a way in storing image and retrieving images using ms access and display in picture box randomly or just like a slideshow?which slides form left to right?or vice versa..i have some codes here but i does not display...hope you could help me and correct me..thanks more power daniweb.

str = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Program Files\chuloader\Chu'sLoadCompute\Chu'sLoadCompute\bin\Release\Imagebank.accdb;User Id=admin;Password=;"
            con = New OleDbConnection(str)
            sql = "select image_name from picture order by id"
            cmd = New OleDbCommand(sql, con)
            con.Open()
            dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
            'Dim c = ds.Tables("Picture").Rows.Count
            'Dim c As Byte() = CType(dr.Item("image_name"), Byte())

            If dr.Read Then
                Dim flote(dr.GetBytes(1, 0, Nothing, 0, Integer.MaxValue) - 1) As Byte
                dr.GetBytes(1, 0, flote, 0, flote.Length)
                Dim stmBLOBData As New MemoryStream(flote)
                picture_box.Image = Image.FromStream(stmBLOBData)
            End If
            dr.Close()
            'If c > 0 Then
            'Dim flote As Byte() = ds.Tables("Picture").Rows(c - 1)("image_name")
            'Dim stream As New MemoryStream(flote)
            'picture_box.Image = Image.FromStream(stream)
            'stream.Close()

            'End If
            con.Close()
        Catch ex As OleDbException
            MsgBox(ex.Message, MsgBoxStyle.Critical, "oledb Error")
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Critical, "General Error")
        End Try

[androidz]

i got an error saying index was outside the bounds of array =(

[Unhnd_Exception]

You should set the byte array straight from the reader.

Dim flote As Byte() = ctype(dr("YourPicStream"),byte())
 Dim stmBLOBData As New MemoryStream(flote)

As far as your slide show, This should give you plenty of example.

Public Class Form1
    Private TimerSlide As New Timer

    Private fpImageIDs As List(Of String)
    Private fpImageIndex As Integer
    Private fpSlideDirection As SlideDirection
    Private fpAnimationSpeed As Integer

    Enum SlideDirection
        LeftToRight
        RightToLeft
    End Enum

    Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        LoadIds()

        fpSlideDirection = SlideDirection.RightToLeft
        fpAnimationSpeed = 1

        'Set the intial image to the first image id in the list.
        'This should be validated that the list is not nothing 
        'and there are ids in the list
        PictureBox1.Image = GetImage(fpImageIDs(0))

        AddHandler TimerSlide.Tick, AddressOf TimerSlide_Tick
        TimerSlide.Interval = 5000
        TimerSlide.Start()

        'This has an animation speed of 1 second and a timer interval at 5 seconds.
        'That means the image will be full displayed for 4 seconds.
    End Sub

    Private Sub TimerSlide_Tick(ByVal sender As Object, ByVal e As System.EventArgs)
        Dim CurrentIndex As Integer
        Dim NextIndex As Integer

        'Get the next index.  If the current index + 1 is greater than
        'the total ids then wrap the next index to 0
        CurrentIndex = fpImageIndex
        If CurrentIndex = fpImageIDs.Count Then
            NextIndex = 0
        Else
            NextIndex = CurrentIndex + 1
        End If

        'Set the global index to the next index so it can be checked
        'the next time this fires.
        fpImageIndex = NextIndex

        'Set up the current image and the next image so they
        'can be animated.
        Dim CurrentImage As Bitmap
        Dim NextImage As Bitmap

        'Picturebox image should be the last image from this
        'sub or the load event.  If it is not then 
        'get the image that mathces the current id
        If PictureBox1.Image Is Nothing Then
            CurrentImage = GetImage(fpImageIDs(CurrentIndex))
        Else
            CurrentImage = PictureBox1.Image.Clone
        End If

        'Get the next image.
        NextImage = GetImage(fpImageIDs(NextIndex))

        If CurrentImage Is Nothing OrElse NextImage Is Nothing Then
            Exit Sub
        End If

        'Some time and image offset variables.
        Dim Shifted, ShiftAmount As Double
        Dim StartTime, TotalTime As Double

        'Create the graphics object and a double buffered graphics object
        'so the paint is smooth.
        Dim g As Graphics = Graphics.FromImage(PictureBox1.Image)
        Dim bc As BufferedGraphicsContext = BufferedGraphicsManager.Current
        Dim bg As BufferedGraphics = bc.Allocate(g, PictureBox1.ClientRectangle)


        Select Case fpSlideDirection

            Case SlideDirection.LeftToRight

                StartTime = DateAndTime.Timer

                Do While TotalTime < fpAnimationSpeed

                    ShiftAmount = PictureBox1.Width * (TotalTime / fpAnimationSpeed) - Shifted
                    Shifted += ShiftAmount

                    TotalTime = DateAndTime.Timer - StartTime

                    If TotalTime >= fpAnimationSpeed Then
                        Shifted = PictureBox1.Width
                    End If

                    bg.Graphics.DrawImageUnscaledAndClipped(CurrentImage, New Rectangle(CInt(Shifted), 0, CurrentImage.Width, CurrentImage.Height))
                    bg.Graphics.DrawImageUnscaledAndClipped(NextImage, New Rectangle(CInt(Shifted) - NextImage.Width, 0, NextImage.Width, NextImage.Height))
                    bg.Render()

                    PictureBox1.Refresh()

                Loop

            Case SlideDirection.RightToLeft

                StartTime = DateAndTime.Timer

                Do While TotalTime < fpAnimationSpeed

                    ShiftAmount = PictureBox1.Width * (TotalTime / fpAnimationSpeed) - Shifted
                    Shifted += ShiftAmount

                    TotalTime = DateAndTime.Timer - StartTime

                    If TotalTime >= fpAnimationSpeed Then
                        Shifted = PictureBox1.Width
                    End If

                    bg.Graphics.DrawImageUnscaledAndClipped(CurrentImage, New Rectangle(CInt(-Shifted), 0, CurrentImage.Width, CurrentImage.Height))
                    bg.Graphics.DrawImageUnscaledAndClipped(NextImage, New Rectangle(NextImage.Width - CInt(Shifted), 0, NextImage.Width, NextImage.Height))
                    bg.Render()

                    PictureBox1.Refresh()

                Loop

        End Select

        'Dispose
        NextImage.Dispose()
        CurrentImage.Dispose()
        bg.Dispose()
        bc.Dispose()
        g.Dispose()
    End Sub

    Private Sub LoadIds()

        'Connect to your database and fill all the ids into the fpImageIDs.
        'if your ids are integer then change the fpImageIds to list of integer

        'Using the file names in mypictures for the ids.
        fpImageIDs = My.Computer.FileSystem.GetFiles(My.Computer.FileSystem.SpecialDirectories.MyPictures, FileIO.SearchOption.SearchTopLevelOnly, "*.bmp", "*.png", "*.jpg").ToList

    End Sub

    Private Function GetImage(ByVal id As String)
        'Connect to your database and get the image that matches the 
        'id.  If your id's are integers then change as string to
        'as integer. and fpImageIndex to list of integer

        'returning the mathcing file name in my pictures
        Return ResizeBitmap(New Bitmap(id), PictureBox1.Size, PictureBox1.BackColor)
    End Function

    Private Function ResizeBitmap(ByVal bmp As Bitmap, ByVal maxSize As Size, ByVal backColor As Color) As Bitmap
        If bmp Is Nothing Then Return Nothing

        'If the image is to big to fit into the picture box this will scale it
        'down keeping aspect ratio.

        'If it fits the size will remain the same.

        'The new image returned will be the same size as the picture box size with
        'the image drawn in the center.


        Dim NewSize As Size = bmp.Size
        Dim scale As Double = 1

        If bmp.Width > maxSize.Width Then
            'Image is wider than the maximum width. Scale it down.
            scale = maxSize.Width / bmp.Width
            NewSize.Width = CInt(NewSize.Width * scale)
            NewSize.Height = CInt(NewSize.Height * scale)
        End If

        If (bmp.Height * scale) > maxSize.Height Then
            'Image Height is still taller than the maximum height. Scale it again.
            scale = maxSize.Height / (bmp.Height * scale)
            NewSize.Width = CInt(NewSize.Width * scale)
            NewSize.Height = CInt(NewSize.Height * scale)
        End If

        Dim ResizedBitmap As New Bitmap(maxSize.Width, maxSize.Height)
        Dim ResizedGraphics As Graphics = Graphics.FromImage(ResizedBitmap)
        ResizedGraphics.Clear(backColor)
        ResizedGraphics.SmoothingMode = Drawing2D.SmoothingMode.HighQuality

        'Draw the image at the new size centered.
        ResizedGraphics.DrawImage(bmp, _
                    CInt((PictureBox1.Width - NewSize.Width) / 2), _
                    CInt((PictureBox1.Height - NewSize.Height) / 2), _
                    NewSize.Width, NewSize.Height)

        bmp.Dispose()
        ResizedGraphics.Dispose()

        Return ResizedBitmap

    End Function

End Class

[Unhnd_Exception]

In the timer event where it checks if current index = fpimageids.count . should be if currentindex = fpimageids.count - 1

[androidz]

sorry i did not used timer. i will try using it. should i remove the code and place it on the timer?

[androidz]

im sorry was not able to read the your first post..anyways should i remove the code inside dr

If dr.Read Then
Dim flote(dr.GetBytes(1, 0, Nothing, 0, Integer.MaxValue) - 1) As Byte
dr.GetBytes(1, 0, flote, 0, flote.Length)
Dim stmBLOBData As New MemoryStream(flote)
picture_box.Image = Image.FromStream(stmBLOBData)

and replace it with only this one?

dr.read then     Dim flote As Byte() = ctype(dr("YourPicStream"),byte())
    Dim stmBLOBData As New MemoryStream(flote)

[androidz]

what is meant by "YourPicstream" is it the column where i put my image in the database?

[androidz]

when i tried changing it to integer i got an error on this line

fpImageIDs = My.Computer.FileSystem.GetFiles(My.Computer.FileSystem.SpecialDirectories.MyPictures, FileIO.SearchOption.SearchTopLevelOnly, "*.bmp", "*.png", "*.jpg").ToList

Value of type 'System.Collections.Generic.List(Of String)' cannot be converted to 'System.Collections.Generic.List(Of Integer)'.

[androidz]

i already got it. it display images now and also slideshow but it only get images from the directory of mypicture, but what how about trying to get the images from the ms access which i stored the image? thanks i have some clue on how this code works but if you can share some idea how to get it from the database i appreciate much..thanks a lot.

[androidz]

on this line here:

Private Sub LoadIds()

        'Connect to your database and fill all the ids into the fpImageIDs.
        'if your ids are integer then change the fpImageIds to list of integer

        'Using the file names in mypictures for the ids.
        fpImageIDs = My.Computer.FileSystem.GetFiles(My.Computer.FileSystem.SpecialDirectories.MyPictures, FileIO.SearchOption.SearchTopLevelOnly, "*.bmp", "*.png", "*.jpg").ToList

    End Sub

it works for mypicture and tried it for the database but im still stuck..i got an headache already ^_^

[androidz]

any ideas that could help me in getting image through the database?

[Unhnd_Exception]

Figured you would be able to get it worked out. Your original code trying to create the image from the db was pretty much there.

For the slide show example you needed to modify the LoadIds sub and the GetImage function. This will do it.

Private Sub LoadIds()

        'Connect to your database and fill all the ids into the fpImageIDs.
        'if your ids are integer then change the fpImageIds to list of integer

        fpImageIDs = New List(Of String)

        Dim Connection As New SqlCeConnection(ConnectionString)
        Dim Command As SqlCeCommand = Connection.CreateCommand
        Dim Reader As SqlCeDataReader

        Command.CommandText = "Select YourPictureIDColumn From YourPictureTable"
      
        Try
            Connection.Open()
            Reader = Command.ExecuteReader
            Do While Reader.Read
                fpImageIDs.Add(CStr(Reader("YouPictureIDColumn")))
            Loop
            Connection.Close()
        Catch ex As Exception

        End Try
        'The ImageId List now contains the ids for every picture in your
        'picture table.  These ids will be used to get the correct image
        'in the Get image function.

    End Sub

    Private Function GetImage(ByVal id As String) As Bitmap
        'Connect to your database and get the image that matches the 
        'id.

        Dim Connection As New SqlCeConnection(ConnectionString)
        Dim Command As SqlCeCommand = Connection.CreateCommand
        Dim Reader As SqlCeDataReader
        Dim BitmapFromDB As Bitmap = Nothing

        Command.CommandText = "Select YourPictureStreamColumn From YourPictureTable" & vbCrLf & _
                              "Where YourPictureIDColumn = @ID"
          
        Command.Parameters.AddWithValue("@ID", id)

        Try
            Connection.Open()
            Reader = Command.ExecuteReader
            If Reader.Read Then
                Dim Bytes() As Byte = CType(Reader("YourPictureStreamColumn"), Byte())
                Dim MemoryStream As New IO.MemoryStream(Bytes)

                BitmapFromDB = New Bitmap(MemoryStream)

                Return ResizeBitmap(BitmapFromDB, PictureBox1.Size, PictureBox1.BackColor)

            Else

            End If
            Connection.Close()
        Catch ex As Exception
             Return Nothing
        Finally
            Connection.Dispose()
            Command.Dispose()
        End Try

 End Function

[androidz]

thanks for that great answer..im still newbie im still learning about this one..but thanks ^_^

[rhea jane]

can u help me how to Selecting, Exporting, and Retrieving Image in Ms Access using Vb 10
im just a new begginner here. pleaee