Displaying Access 2007 Attachment type

[JimStewart]

I have an access 2007 database that my client has provided me. The products table contains the Attachment type for the product image. I can read the product data no problem, but I have yet to figure out how to display the image for the product in my picturebox control in VB.Net

Any Suggestions?

Thanks in advance.

 

[jmcilhinney]

So, you're saying that this attachment column contains images? Exactly what type is the data that you get from ADO.NET? I would assume that it's a Byte array. In that case, create a MemoryStream, write the data to it, then call Image.FromStream to create an Image object, e.g.

Saving Images in Databases

That code is for SQL Server but the pattern should be the same.

 

[JimStewart]

Ok, in ADO.NET it shows the column as a string value. When I open the access database, the attachment field has three sub-fields (.FileName, .FileType, .FileData) . The FileData sub-field actually contains the Binary Data. So I tried your recommendation above with the following code:

Dim command As New OleDbCommand("SELECT Products.ProductPicture FROM Products WHERE ProductID = '" & Me.ListBox1.SelectedValue & "'", con1)
Dim PictureData As Byte() = DirectCast(command.ExecuteScalar(), Byte())

And I get Error: "Cannot cast type System.String() to System.Byte()"

So I modified the code to try and access the sub-field information:

Dim command As New OleDbCommand("SELECT Products.ProductPicture.FileData FROM Products WHERE ProductID = '" & Me.ListBox1.SelectedValue & "'", con1)
Dim PictureData As Byte() = DirectCast(command.ExecuteScalar(), Byte())

And I get an error telling me "Parameter is not Valid"

Any thoughts on accessing the sub-field data?

 

[jmcilhinney]

I've never actually used the Attachment type so this is new to me. If the error message says that then the actual data is a String array, not a Byte array. You need to examine that String array to see exactly what it contains. I'm guessing that it will have three elements: the first will be the name of the file, the second will be the file type and the third will be the file contents, possibly in base-64 form. That would be enough data to re-create the original file if required.

So, your next step is to examine the data and see what it actually is. Once we know that, we can decide how to handle it. For that test, use this:

Dim command As New OleDbCommand("SELECT Products.ProductPicture FROM Products WHERE ProductID = '" & Me.ListBox1.SelectedValue & "'", con1)
Dim PictureData As String() = DirectCast(command.ExecuteScalar(), String())

For Each element In PictureData
    MessageBox.Show(element)
Next

If my theory is correct that will show you three messages, the first two containing a file name and a file type and the third some gibberish. Let me know if that's correct and, if not, exactly what you do get.

 

[JimStewart]

Now I get the error: "Unable to cast System.String to System.String()"

To be honest, I have been searching all night, and it seems that no one has been able to come up with a solution to this. Maybe this is something we need to contact Microsoft about..

 

[jmcilhinney]

No, this is not something we have to contact Microsoft about. This is very simple. You just have to work out what you've got first, and then you can work out what to do with.

You need to provide us with accurate information first. In your previous post you said that the error message was:

Cannot cast type System.String() to System.Byte()

Now you're saying that the error message is:

Unable to cast System.String to System.String()

Before you had a String array and now you have a String. It wouldn't have changed by itself. Either your data has changed, your code has changed or you provided inaccurate information the first time. Which is it?

Just like before though, you just need to cast the data as the correct type and then examine it as that type to see what it contains. If it's a String array then cast it as a String array and examine each element. If it's actually a String then cast it as a String and examine it, e.g. display it using MessageBox.Show. As I've said more than once, once you know what it is, then you can work out how to use it.

 

[JimStewart]

Ok, I re-ran to confirm the other error message, you were correct, I did provide the wrong error it was a System.String. So I Cast the Variable as a String. Once I did this and ran the project I am getting the Files Name for the return value.

 

[JimStewart]

I think I am almost there..

I added a reference to Microsoft.Office.Interop.Access.DAO

Then I created a function:

Public Function GetAttachment(ByVal ProductID As String)

        Dim dbEngine As Microsoft.Office.Interop.Access.Dao.DBEngine
        Dim db As Microsoft.Office.Interop.Access.Dao.Database
        dbEngine = New Microsoft.Office.Interop.Access.Dao.DBEngine()
        db = dbEngine.OpenDatabase("RegionalCatalog.accdb")
        Dim rstRecords As Microsoft.Office.Interop.Access.Dao.Recordset
        Dim SQL As String

        SQL = "SELECT Products.ProductPicture.FileData FROM Products WHERE ProductID = '" & ProductID & "'"


        Try

            rstRecords = db.OpenRecordset(SQL)

            Dim FieldData As Byte()

            FieldData = DirectCast(rstRecords.Fields(0).Value, Byte())

            Dim picture As Image = Nothing


            Using strm As New IO.MemoryStream(FieldData)

                picture = Image.FromStream(strm)

            End Using

            Return picture

            db.Close()


        Catch ex As Exception

            MsgBox(ex.Message.ToString)

            Return Nothing

            db.Close()


        End Try

     End Function

Everything works fine until I get to the

picture = Image.FromStream(strm)

Then I get a "Parameter is not Valid" Exception. Any Ideas?

 

[JimStewart]

[SOLVED] Displaying Access 2007 Attachment type picture in VB.net Project

After many hours of searching different websites and piecing together code, and with jmcilhinney's (forum moderator) guidance I have finally come up with a function that returns a Image from an MS Access 2007 Attachment-type Column.

Here is the working version:

[COLOR="seagreen"]' Imports[/COLOR]
Imports System.IO
Imports Microsoft.Office.Interop.Access.DAO




Public Function GetAttachment(ByVal ProductID As String)

        Dim dbEngine As Microsoft.Office.Interop.Access.Dao.DBEngine
        Dim db As Microsoft.Office.Interop.Access.Dao.Database
        dbEngine = New Microsoft.Office.Interop.Access.Dao.DBEngine()
        db = dbEngine.OpenDatabase("RegionalCatalog.accdb")
        Dim rstRecords As Microsoft.Office.Interop.Access.Dao.Recordset2
        Dim rstChild As Microsoft.Office.Interop.Access.Dao.Recordset2
        Dim fldAttach As Microsoft.Office.Interop.Access.Dao.Field2
        Dim strTempDir As String
        Dim strFilePath As String

        Dim SQL As String

        SQL = "SELECT Products.ProductPicture FROM Products WHERE ProductID = '" & ProductID & "'"


        Try
            rstRecords = db.OpenRecordset(SQL)
            rstChild = rstRecords.Fields("ProductPicture").Value  [COLOR="seagreen"]'Sub-Field Data[/COLOR]

           [COLOR="seagreen"] ' Get the Temp directory from the environment variable.[/COLOR]
            strTempDir = Environ("Temp")

           [COLOR="seagreen"] ' Make sure the path always ends with a backslash.[/COLOR]
            If Right(strTempDir, 1) <> "\" Then strTempDir = strTempDir & "\"

           [COLOR="seagreen"] ' Append the name of the first (and only) attached file to temp dir.[/COLOR]
            strFilePath = strTempDir & rstChild.Fields("FileName").Value

           [COLOR="seagreen"] 'If file exists then delete the file[/COLOR]
            If Dir(strFilePath) <> "" Then
                Dim fi As New FileInfo(strFilePath)
                fi.Delete()
            End If

            fldAttach = rstChild.Fields("FileData") [COLOR="seagreen"]' The binary data of the file.[/COLOR]

            fldAttach.SaveToFile(strFilePath)

            rstChild.Close() [COLOR="seagreen"]' cleanup[/COLOR]
            rstRecords.Close() [COLOR="seagreen"]'cleanup[/COLOR]



            Dim picture As Image [COLOR="seagreen"] 'create image object [/COLOR]

            picture = Image.FromFile(strFilePath) [COLOR="seagreen"]'Build the image[/COLOR]

            Return picture   [COLOR="seagreen"]'Return the image[/COLOR]
            db.Close() [COLOR="seagreen"]'Close database[/COLOR]




        Catch ex As Exception

            MsgBox(ex.Message.ToString)

            Return Nothing

            db.Close()


        End Try






    End Function

 

[jmcilhinney]

That's nice work. From what you've posted and from what I've seen when searching, it seems that maybe ADO.NET just doesn't support the Attachment data type. More specifically, the ACE OLE DB provider doesn't support it. The only issue with your solution is that it requires Access to be installed. I wonder whether just installing ACE would be enough to get that support.

 

[JimStewart]

Office Primary Interop Assemblies in .Net 4.0

Primary Interop Assemblies in the Global Assembly Cache

To perform certain development tasks, the PIAs must be installed and registered in the global assembly cache on the development computer. Typically, the PIAs are installed automatically when you install Office on the development computer. For more information, see Configuring a Computer to Develop Office Solutions.

End-user computers must also have the PIAs installed and registered in the global assembly cache to run Office solutions that target the .NET Framework 3.5. However, the Office PIAs are not required on end-user computers to run Office solutions that target the .NET Framework 4. For more information, see Designing and Creating Office Solutions.

Can I require the .NET 4.0 Framework at install time?


Thanks

Jim

 

[jmcilhinney]

Having Interop assemblies, whether PIAs or those from .NET 4.0, are not enough. That just provides the interop between your app and the appropriate COM component. You have to have the COM component, i.e. Office, to interop with.