Store And Retrieve Image In MS Access

[ryklon]

Hi!

I'm having a bad time on learning how to store and retrieve image files on MS Access.

This is some sort of uploading profile picture thing in my software.

The open file dialog stuff and the storing of image file in the MS Access Database are taken care of but I don't seem to be able retrieve it. I'd like to retrieve the image and load it into a picture box.

By the way, if you guys can also give me some sort of good references that explains briefly how do you store and retrieve files in MS Access, especially the functions that are used, I'd be really happy for that.

Honestly, the code below are just rip offs that I gathered all over the web and I don't understand it that much. I'm having a bad time learning because there is no brief explanation about the algorithm flow. So, basically I don't have any clue in what I'm doing, save for the SQL commands. LOL. But I'd like to learn from you guys, so please help.

Thanks in advance!

Here is my code.

Private Sub btnProfilePicture_Upload_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnProfilePicture_Upload.Click

        '----------------------
        'START: Picture upload.
        '----------------------
        Dim fsreader As New FileStream(OpenFileDialog.FileName, FileMode.Open, FileAccess.Read)

        Dim breader As New BinaryReader(fsreader)

        Dim imgbuffer(fsreader.Length) As Byte

        breader.Read(imgbuffer, 0, fsreader.Length)

        fsreader.Close()

        cmdUPDATE = New OleDbCommand("UPDATE [Employee Information] SET [Profile Picture] = '" & txtFileLocation.Text & "'" &
                                     " WHERE ID = " & frmLogin.txtEmployeeID.Text & "", conOLEDB)

        cmdUPDATE.Parameters.AddWithValue("@dtapic", imgbuffer)

        cmdUPDATE.ExecuteNonQuery()

        cmdUPDATE.Dispose()

        MessageBox.Show("Uploaded Successfully!", "Message", MessageBoxButtons.OK, MessageBoxIcon.Information)

        '--------------------
        'END: Picture upload.
        '--------------------

        '------------------------
        'START: Picture retrieve.
        '------------------------
        cmdSELECT = New OleDbCommand("SELECT [Profile Picture] FROM [Employee Information] WHERE ID =" &
                                     " '" & frmLogin.txtEmployeeID.Text & "'", conOLEDB)

        drDataReader = cmdSELECT.ExecuteReader

        If drDataReader.Read Then

            Dim imgByteArray() As Byte

            imgByteArray = CType(drDataReader.Item(8), Byte())

            Dim stream As New MemoryStream(imgByteArray)

            Dim bmp As New Bitmap(stream)

            stream.Close()

            frmMain.picProfilePicture.Image = bmp

            Me.Close()

        End If

        drDataReader.Close()

        conOLEDB.Close()

        cmdSELECT.Dispose()

        conOLEDB.Dispose()
        '----------------------
        'END: Picture retrieve.
        '----------------------

[M.Waqas Aslam]

hello !
i have no idea how to deal with ms access , but here is a code to insert image in mssql , may be this will give you any idea .

'use this code where you are assigning the values in you command
SqlParameter("@picture", SqlDbType.Image)).Value = arrimage)
   Dim myFile As String = picture
            If picture.ToString.Length <= 1 Then
                Me.myCmd.Parameters.Add("@Picture", SqlDbType.Image).Value = Convert.DBNull
            Else
                Dim myStream As FileStream = New FileStream(myFile, FileMode.Open)
                Dim myImageBuffer() As Byte = New Byte(myStream.Length) {}
                myStream.Read(myImageBuffer, 0, myStream.Length)

                Me.myCmd.Parameters.Add("@Picture", SqlDbType.Image).Value = myImageBuffer
                myStream.Close()
            End If
'and this code is for binding picture box with you sql db
dim con as new sqlconnection("string")
con.open()
dim da as new sqldataadapter("select mypic from table1",con)
dim dt as datatable
da.fill(dt)
picturebox1.databinding.add("image",dt,"mypic",true)
'this code will show your saved pic from mssql to your picture box ,

i know this is not what you are looking for , but this will give you an idea to solve your prob:) .

Regards

[ryklon]

Thanks! Much appreciated! :)

[ryklon]

Guys, I badly need some good references about this one ASAP. It would be much appreciated.

[jbennet]

In real life, no one really stores images as BLOBs in databses anyway, its better to have them in the filesystem, and just store the path to them as text in the database.

[ryklon]

@jbennet

Yep, that's true. But the thing is, what if the software will be transferred to another location? How can I preserve the paths that I coded inside?

Can you please teach me how can I actually do that? I'm open for anything aside from BLOBS, as long as I can store any files in my database and it can be transferred anywhere I like.

Thanks in advance!