none
show an access attachment in a vb.net picture box

    Question

  • I am trying to get a access picture attachment to show in an vb.net picture box. at start up, i add an attachment field to the database, and add pictures to the database in jpg form. I then connect the database to vb project using the general vb settings. The connection is sucessful, I then add a picture box control to the form along with changing the attachment field to a picture box setting at this point every thing should be good. However, when i go to run the program the picture box remains empty. Is there anyone who can give me advice or a workable code on my situation? Thanks
    Friday, December 02, 2011 6:59 PM

All replies

  • It is hard to give advice when you do not show your code.  Please post your code using the code insert tool.  It's the button that looks something like this [</>]. 

    Also you could search the forum for similar posts.  There has been at two that I've see recently. The end of the semester panic has set in again.

     

    Friday, December 02, 2011 7:20 PM
  • Thats the thing, I am not using a code to view the pics. I just conected the database to the project, and just used the database icons. there should be no coding to the database or form.
    Monday, December 05, 2011 9:42 PM
  • So the VS should help you to generate some code that read data from database. Please share your entire project, I think you just use the wizard to connect the DB, but we still need to write some code to get the pictures from DB.

    Or, have a look at this similar thread to see helps or not. http://social.msdn.microsoft.com/Forums/en-US/vbide/thread/351a0ef3-26bf-45ca-b707-aade01be65e7/

    Have a nice day,


    Kee Poppy [MSFT]
    MSDN Community Support | Feedback to us
    Tuesday, December 06, 2011 10:58 AM
    Moderator
  • I am trying to get a access picture attachment to show in an vb.net picture box. at start up,

    Are you using typed dataset?


     i add an attachment field to the database, and add&nbsp;pictures to the database in jpg form.

    What is the field type to store the jpg?

    when i go to run the program the picture box remains
     empty. Is there anyone who can give me advice or a workable code on my situation?

    Did you bind the picturebox to the correct field?
    Are you using bindingsource?


    Tuesday, December 06, 2011 1:05 PM
  • Multi-valued fields in Microsoft Access are relatively new and AFAIK there is no direct support in ADO or .NET for these data types. You can still use DAO (Microsoft.Office.interop.access.dao - version 12.0) to iterate through an Attachment field using a DAO Recordset, but it must be saved to a file first:

            Dim DatabasePath As String = "C:\Users\...\Documents\My Database\Access\Attachment.accdb"
            Dim AccessEngine As New Microsoft.Office.Interop.Access.Dao.DBEngine
            Dim SourceRecordset As Microsoft.Office.Interop.Access.Dao.Recordset
            Dim AttachmentRecordset As Microsoft.Office.Interop.Access.Dao.Recordset
    
            Dim db As Microsoft.Office.Interop.Access.Dao.Database = AccessEngine.OpenDatabase(DatabasePath)
    
            SourceRecordset = db.OpenRecordset("SELECT * FROM Table1 WHERE ID = 1")
            AttachmentRecordset = SourceRecordset.Fields("MyAttachments").Value
            While Not AttachmentRecordset.EOF
                Dim AttachmentFileName As String = AttachmentRecordset.Fields("FileName").Value.ToString
                Dim AttachmentPath As String = System.Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments) & "\" & AttachmentFileName
                AttachmentRecordset.Fields("FileData").SaveToFile(AttachmentPath)
                AttachmentRecordset.MoveNext()
            End While
    
            SourceRecordset.Close()
            SourceRecordset = Nothing
            AttachmentRecordset = Nothing
            db.Close()
            db = Nothing

     


    Paul ~~~~ Microsoft MVP (Visual Basic)
    Tuesday, December 06, 2011 1:51 PM
  • Multi-valued fields in Microsoft Access are relatively new and AFAIK there is no direct support in ADO or .NET for these data types.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Aha, so "attachment" is a field type in access2007 and above? nice!!

    That exist since access2007? why .net in vs2010 does not support it yet (3 years later)? are the new release of .net 4.5 will support it?

    Wednesday, December 07, 2011 3:02 PM
  • Multi-valued fields in Microsoft Access are relatively new and AFAIK there is no direct support in ADO or .NET for these data types.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Aha, so "attachment" is a field type in access2007 and above? nice!!

    That exist since access2007? why .net in vs2010 does not support it yet (3 years later)? are the new release of .net 4.5 will support it?


    There is no native .NET Provider for Microsoft Access databases. The only other way to add the capability would be to support it through the OLEDB/ODBC Provider/Driver. Since it's a database specific feature support isn't likely to be added to System.Data.OleDb or System.Data.Odbc.
    Paul ~~~~ Microsoft MVP (Visual Basic)
    Thursday, December 08, 2011 1:56 PM
  • Hi Paul

    I guess I made a discovery :)

    I manage to read the data from inside the attachment field in access.

    I try it with the dataset and it worked, I will try it with ADO later.

    Here is what I did

    From the access I added an image to the attachment field "desert.jpg" from the windows sample picture.

    I created a dataset and I add a table from the access database which contain attachment field, after the table being added to the dataset, I re-configure it and in the query builder it was coded SELECT ID, name, attach_fld FROM Table1 so I changed to SELECT ID, name, attach_fld,attach_fld.filedata FROM Table1

    My dataset contains 1 table and the table contain 1 row

    I dragged the table to a form to create typed-dataset then I coded this

    Dim x As Byte() = ds_test.Tables(0).Rows(0)("attach_fld_filedata")
    My.Computer.FileSystem.WriteAllBytes("d:\xxx2\myimage.jpg",x,False)  

    Then I opened the location, the created file was not recognized as image, so I did some Hex comparison and I found the new created file has extra 20 byte in it

    so I changed the code to

    Dim x As Byte() = ds_test.Tables(0).Rows(0)("attach_fld_filedata")
    Dim y = x.Skip(20).ToArray()
    My.Computer.FileSystem.WriteAllBytes("d:\xxx2\myimage.jpg",y,False)

    I looked to the location and the image was there :)

    I finding a little difficulty to convert the variable "y" to an image to display directly on a picturebox without the need to dump it on the HDD.

    I will see if I can do the same with command object ADO

     

    Saturday, December 10, 2011 7:43 AM
  • I test it on Command Object, it works also

    Dim oleconn As New OleDb.OleDbConnection("connection_string")
     oleconn.Open
    Dim olecomm As New OleDb.OleDbCommand("SELECT id, attach_fld.filedata as att_fld_filedata FROM Table1",oleconn)
     Dim olereader As OleDb.OleDbDataReader
    olereader = olecomm.ExecuteReader()
    olereader.Read
    Dim x As Byte() = olereader("att_fld_filedata")
    Dim y = x.Skip(20).ToArray()
    'http://windevblog.blogspot.com/2008/08/convert-image-to-byte-array-and-vice.html
    Dim imagestream = New MemoryStream(y)
    Dim newimage = Image.FromStream(imagestream)
    Me.PictureBox1.Image = newimage
    

    But there is still something weired

    If my select statement is "SELECT attach_fld.filedata as att_fld_filedata" it will not work unless I must include an extra field such as id

    SELECT ID, attach_fld.filedata as att_fld_filedata

    I will check later if the first 20 byte are fixed for all kind of attachment.

     

     

    Saturday, December 10, 2011 11:07 AM
  • Samir,

    For the offset that you discovered, you may want to look at this thread.

    http://social.msdn.microsoft.com/Forums/en-SG/csharpgeneral/thread/A7F0BF9F-58F5-4D91-993F-8B8A26D54B85

    Saturday, December 10, 2011 6:49 PM
  • Nice work! I will take a look at the code to see if it can be adapted for multiple attachments.
    Paul ~~~~ Microsoft MVP (Visual Basic)
    Saturday, December 10, 2011 8:59 PM
  • Hi All,

    I needed a distraction this afternoon that did not rot my brain so I decided to look into retrieving, adding to and deleting from the Access 2007/2010 attachment field.

    I don’t take credit for the techniques presented here as I only translated them into VB.Net from the work of other’s that I found on the internet.  The techniques for the DataBase Engine are primarily from Access 2007 VBA forums with some long thought forgotten memories of mine from working with record sets in VBA.

    The easy one was to retrieve the attachments using the OleDB class, as Mahmoud Zaben posted a good example at:  http://social.msdn.microsoft.com/Forums/en-SG/csharpgeneral/thread/A7F0BF9F-58F5-4D91-993F-8B8A26D54B85.

    However, I was not able to find a way to update the fields using OleDB.  There may be a way, but there is no need to as you can use the Microsoft Access Database Engine to accomplish all the needed tasks.  This engine is redistributable form Microsoft, so there is no need to have Access installed on the machine.   Just download and install the engine; there are links posted in the code.

    There are restrictions to what you can place in the Attachment field and my code does not do any checking for the resistrictions.  I suggest that you review:   “Attach files and graphics to the records in your database “ at:  http://office.microsoft.com/en-us/access-help/attach-files-and-graphics-to-the-records-in-your-database-HA001213852.aspx#BM9 for more information.

    The code is based on a Form with four button and one datagridview; all with the default names.  You will need to modify the “DBFile” and “PicToAdd” variables to match you system.

    This is meant as a quick overview of the techniques.

    Buttons 1 and to 2 display the content of the attachment field in the datagridview by OleDb and the Engine, respectively.  For this example, only place picture attachments as the Datagridview does not like other formats.  This does not mean that you cannot use other formats as I have successfully add/extracted both a Word document and a Zip file.  Button 3 adds a new picture and Button 4 allows deleting the picture added by Button 3.

    I realize that this does not provide a CAP solution for the original poster, but I would hope that retrieving the data is the difficult part and placing it in a picturebox versus a Datatable should be manageable.

    Imports Microsoft.Office.Interop.Access.Dao

    Public Class Form1
       Private DBFile As String = "D:\My Documents\Visual Studio 2008\Projects\Example Access 2007 with attachments\Example Access 2007 with attachments\Database2.accdb"
       Private PicToAdd As String = "D:\My Documents\Gifs\cartoon-boy.gif"
       Private AttachmentsTable As New DataTable

       Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
          With AttachmentsTable
             .Columns.Add(New DataColumn("FileData", GetType(Byte())))
             .Columns.Add(New DataColumn("FileFlags", GetType(String)))
             .Columns.Add(New DataColumn("FileName", GetType(String)))
             .Columns.Add(New DataColumn("FileType", GetType(String)))
             .Columns.Add(New DataColumn("FileURL", GetType(String)))
             .Columns.Add(New DataColumn("FileTimeStamp", GetType(Date)))
          End With
          DataGridView1.AllowUserToAddRows = False
       End Sub

       Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

          Dim conn As New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & DBFile & "'")
          Dim cmd As New OleDb.OleDbCommand()
          cmd.Connection = conn
          cmd.CommandText = "SELECT AttachmentField.FileData, " & _
                            "AttachmentField.FileFlags, " & _
                            "AttachmentField.FileName, " & _
                            "AttachmentField.FileType, " & _
                            "AttachmentField.FileURL, " & _
                            "AttachmentField.FileTimeStamp" & _
                            " FROM TESTATTACHMENTS " & _
                            "WHERE ID = @ID"

          cmd.Parameters.Add(New OleDb.OleDbParameter("@ID", 1))
          conn.Open()

          Try
             AttachmentsTable.Clear()
             Dim dr As OleDb.OleDbDataReader = cmd.ExecuteReader(CommandBehavior.Default)
             Dim row As DataRow
             Do While dr.Read
                row = AttachmentsTable.NewRow
                row("FileData") = ParseImageData(CType(dr(0), Byte()))
                row("FileFlags") = dr.Item(1)
                Dim s As String = dr.Item(2).ToString
                row("FileName") = dr.Item(2)
                row("FileType") = dr.Item(3)
                row("FileUrl") = dr.Item(4)
                row("FileTimeStamp") = dr.Item(5)
                AttachmentsTable.Rows.Add(row)
             Loop
          Finally
              conn.Close()
          End Try

          DataGridView1.DataSource = AttachmentsTable
       End Sub

       Private Function ParseImageData(ByVal data() As Byte) As Byte()
          'Original code in C# by: Mahmoud Zaben
          'http://social.msdn.microsoft.com/Forums/en-SG/csharpgeneral/thread/A7F0BF9F-58F5-4D91-993F-8B8A26D54B85
          Dim imageStartPos As Integer = BitConverter.ToInt32(data, 0)
          Dim unKnown As Integer = BitConverter.ToInt32(data, 4)
          Dim result(data.Length - imageStartPos - 1) As Byte
          Array.Copy(data, imageStartPos, result, 0, result.Length)
          Return result
       End Function

       Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
          'Install Microsoft Access Database Engine Redistributable
          '2007: http://download.microsoft.com/download/f/d/8/fd8c20d8-e38a-48b6-8691-542403b91da1/AccessDatabaseEngine.exe
          '2010 - 32: http://download.microsoft.com/download/2/4/3/24375141-E08D-4803-AB0E-10F2E3A07AAA/AccessDatabaseEngine.exe
          '2010 - 64:  http://download.microsoft.com/download/2/4/3/24375141-E08D-4803-AB0E-10F2E3A07AAA/AccessDatabaseEngine_x64.exe
          'Add Reference to "ACEDAO.DLL"

          Dim engine As New DBEngine
       'Reference Material: Microsoft Data Access Objects Reference
                            'http://msdn.microsoft.com/en-us/library/ff841598.aspx

       'Name:      File name.
       'Options:   True    Opens the database in exclusive mode.
                   'False    (Default) Opens the database in shared mode.

       'Connect:    Specifies various connection information, including passwords.

          Dim db As Database = engine.OpenDatabase(DBFile)

       'Name:        The source of the records for the new Recordset.
                   'The source can be a table name, a query name,
                   'or an SQL statement that returns records. For '
                   'table-type Recordset objects in Microsoft Access
                   'database engine databases, the source can only be a table name.

       'Type:      A RecordsetTypeEnum constant that indicates the type of Recordset to open.
                   'If you open a Recordset in a Microsoft Access workspace and you
                   'don't specify a type, OpenRecordset creates a table-type Recordset,
                   'if possible. If you specify a linked table or query, OpenRecordset
                   'creates a dynaset-type Recordset.

       'Options:   A combination of RecordsetOptionEnum constants that specify characteristics of the new Recordset.
                   'The constants dbConsistent and dbInconsistent are mutually exclusive,
                   'and using both causes an error. Supplying a lockedits argument when
                   'options uses the dbReadOnly constant also causes an error.
                   'see: http://msdn.microsoft.com/en-us/library/bb225802%28v=office.12%29.aspx

       'LockEdit:  A LockTypeEnum constant that determines the locking for the Recordset.
                   'You can use dbReadOnly in either the options argument or the lockedits argument,
                   'but not both. If you use it for both arguments, a run-time error occurs.
                   'see: http://msdn.microsoft.com/en-us/library/bb242663%28v=office.12%29.aspx

          Dim CommandText As String = "SELECT * FROM TESTATTACHMENTS WHERE ID=1"

          Dim rs As Recordset = db.OpenRecordset(CommandText, _
                                                 RecordsetTypeEnum.dbOpenDynaset, _
                                                 Nothing, _
                                                 LockTypeEnum.dbOptimistic)

          If rs IsNot Nothing Then 'Retrieve Attachments recordset
             Dim rs2 As Recordset2 = CType(rs.Fields("AttachmentField").Value, Recordset2)
             'A Recordset2 object contains all of the same properties and methods as the
             'Recordset object. The Recordset2 object contains a new property, ParentRecordset,
             'that support multi-valued field types.

             If rs2 IsNot Nothing Then 'Fill AttachmentsTable
                'You can attach a maximum of two gigabytes of data '
                '(the maximum size for an Access database). Individual
                'files cannot exceed 256 megabytes in size.
                AttachmentsTable.Clear()
                Dim offsetbytes() As Byte
                Dim offset As Int32
                Dim row As DataRow = Nothing
                Dim f2 As Field2
                Do While Not rs2.EOF
                   row = AttachmentsTable.NewRow
                   f2 = CType(rs2.Fields("FileData"), Field2)

                   'get offset to data
                   offsetbytes = CType(f2.GetChunk(0, 4), Byte())
                   offset = BitConverter.ToInt32(offsetbytes, 0)

                   'If numbytes is greater than the number of bytes in the field,
                   'GetChunk returns the actual number of remaining bytes in the field.
                   'there appears to be a resource limit for GetChuck
                   'so use Int16.MaxValue for bytes to return
                   'ref:  http://msdn.microsoft.com/en-us/library/ff194600.aspx

                   'I will use a memory stream for temporary storage
                   Dim ms As New IO.MemoryStream
                   Dim bw As New IO.BinaryWriter(ms)
                   Do
                      Dim returnBytes() As Byte = CType(f2.GetChunk(offset, Int16.MaxValue), Byte())
                      If returnBytes IsNot Nothing Then
                         bw.Write(returnBytes)
                         offset = offset + returnBytes.Length
                      Else
                         Exit Do
                      End If
                   Loop

                   row("FileData") = ms.ToArray
                   bw.Close() ' closes memory stream as well
                   row("FileFlags") = rs2.Fields("FileFlags").Value
                   row("FileName") = rs2.Fields("FileName").Value
                   row("FileType") = rs2.Fields("FileType").Value
                   row("FileUrl") = rs2.Fields("FileUrl").Value
                   row("FileTimeStamp") = rs2.Fields("FileTimeStamp").Value
                   AttachmentsTable.Rows.Add(row)
                   rs2.MoveNext() 'Get next attachment
                Loop
             End If 'rs2 IsNot Nothing

          End If 'rs IsNot Nothing
          db.Close()
          engine = Nothing

          DataGridView1.DataSource = AttachmentsTable
       End Sub

       Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
          Dim engine As New DBEngine
          Dim db As Database = engine.OpenDatabase(DBFile)
          Dim CommandText As String = "SELECT * FROM TESTATTACHMENTS WHERE ID=1"

          Dim rs As Recordset = db.OpenRecordset(CommandText, _
                                                 RecordsetTypeEnum.dbOpenDynaset, _
                                                 Nothing, _
                                                 LockTypeEnum.dbOptimistic)

          If rs IsNot Nothing Then
             Dim FileName As String = IO.Path.GetFileName(PicToAdd)

             'Retrieve Attachments recordset
             Dim rs2 As Recordset2 = CType(rs.Fields("AttachmentField").Value, Recordset2)

             'Check if the new attachment already exists
             rs2.FindFirst("Filename='" & FileName & "'")
             If rs2.NoMatch Then
                'Add new record
                rs.Edit()
                rs2.AddNew()

                'Load the fields; Only FileData and FileName can be changed
                Dim f2 As Field2 = CType(rs2.Fields("FileData"), Field2)
                f2.LoadFromFile(PicToAdd)
                f2 = CType(rs2.Fields("FileName"), Field2)
                f2.Value = FileName

                rs2.Update()
                rs2.Close()
                rs.Update()
                rs.Close()
                db.Close()
             Else
                MsgBox(FileName & " is already attached")
             End If

          End If 'rs IsNot Nothing
       End Sub

       Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
          Dim engine As New DBEngine
          Dim db As Database = engine.OpenDatabase(DBFile)
          Dim CommandText As String = "SELECT * FROM TESTATTACHMENTS WHERE ID=1"

          Dim rs As Recordset = db.OpenRecordset(CommandText, _
                                                 RecordsetTypeEnum.dbOpenDynaset, _
                                                 Nothing, _
                                                 LockTypeEnum.dbOptimistic)

          If rs IsNot Nothing Then
             Dim FileName As String = IO.Path.GetFileName(PicToAdd)

             'Retrieve Attachments recordset
             Dim rs2 As Recordset2 = CType(rs.Fields("AttachmentField").Value, Recordset2)

             'Check if the new attachment already exists
             rs2.FindFirst("Filename='" & FileName & "'")
             If rs2.NoMatch Then
                MsgBox(FileName & " is not attached")
             Else
                'delete attachment record
                rs.Edit()
                rs2.Edit()
                rs2.Delete()
                rs2.Close()
                rs.Update()
                rs.Close()
                db.Close()
             End If

          End If 'rs IsNot Nothing
       End Sub
    End Class

     

    Sunday, December 11, 2011 1:16 AM
  • Samir,

    For the offset that you discovered, you may want to look at this thread.

    http://social.msdn.microsoft.com/Forums/en-SG/csharpgeneral/thread/A7F0BF9F-58F5-4D91-993F-8B8A26D54B85

    Hello TnTinMN

    So you want to tell me that I will not be a candidate for getting a Nobel prize for my discovery? (lol)

    Thank you for the code conversion, I guess it took time from you, and it helped me to understand it better, especially the image part from c# to vb, although I did not test your code and I was going to test it and put it under microscope, but I decided not to do it, because my code works just fine ;) 

    Please check my next post.

    Sunday, December 11, 2011 9:31 AM
  • Hi all again

    I made some tests (not extensive) on the attachment field in Access2007, I want to tell my notes:

    - I test the attachment on "mp3,wmv,xlsx,jpg,m4a,flv"

    - I added multiple (6 attachment) to a single row to the attachment field.

    - The extra first 20 byte seems to be fixed for the file types i test.

    - I also Noted, that if you code something like "SELECT * FROM table1 WHERE ID = 1" and you have only 1 row that his ID is equal to 1, you will get 1 row, BUT, if your select query is "SELECT ID,attach.filename,attach.filedata FROM table1 WHERE ID = 1", you will get 6 rows (in my case 6 attachment is added), (the same number of attachment object exist in the field which match ID = 1"

    So simply, you can loop into them

    Here is my working code

    Dim conn As New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\test.accdb")
    conn.Open
    Dim cmd As New OleDb.OleDbCommand("select [id],attach.filedata as attach_filedata,attach.FileName as attach_filename from table1 where id = 1",conn)
    Dim dr As OleDb.OleDbDataReader
    dr = cmd.ExecuteReader()
    
    While dr.Read
        Dim x As Byte() =  dr("attach_filedata")
        Dim y = x.Skip(20).ToArray
        Dim filename = dr("attach_filename")
        My.Computer.FileSystem.WriteAllBytes("c:\xx\" + filename,y,False)
    End While
    


     

     

     

    Sunday, December 11, 2011 9:46 AM
  • Nice work! I will take a look at the code to see if it can be adapted for multiple attachments.
    Paul ~~~~ Microsoft MVP (Visual Basic)

    Thank you.

    I Manage to read the multiple attachments, I told my self, you may have too much work so I want to give you a hand :)

     

    Sunday, December 11, 2011 9:54 AM
  • Hi Samir,

    All that I meant by pointing you to the other thread was that there is apparently some meaning to those 20 bytes.  I apologize if I inadvertently offend you. To be honest, I was very impressed that at what you discovered and the intelligence displayed by your investigative method, even if you were not the first to do so.  Your tenacity in seeking a solution is trait that I find severely lacking by many who want to do programming.  This is not official or substantial, but here is your reward.

                                             

    The  information that I am still seeking is a definition for those remaining 16 bytes in the data file prefix.

    I'm still attempting to find a way to update using OleDB.   The following code fails with a "Dynamic SQL generation is not supported against multiple base tables." error on the update command.  From what I have observed is that a record is created in the Access System Table "MSysComplexColumns" for each attachment field defined and this record points to a table in "MSysObjects".  However, I do not know how to craft the SQL commands to update hidden/system tables.

          Dim conn As New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & DBFile & "'")
          Dim da As New OleDb.OleDbDataAdapter()

          da.SelectCommand = New OleDb.OleDbCommand("SELECT " & _
                            "ID, " & _
                            "AttachmentField.FileData, " & _
                            "AttachmentField.FileFlags, " & _
                            "AttachmentField.FileName, " & _
                            "AttachmentField.FileType, " & _
                            "AttachmentField.FileURL, " & _
                            "AttachmentField.FileTimeStamp" & _
                            " FROM TESTATTACHMENTS " & _
                            "WHERE ID = @ID", conn)

          da.SelectCommand.Parameters.Add("@ID", OleDb.OleDbType.Integer).Value = 1
          Dim cb As OleDb.OleDbCommandBuilder = New OleDb.OleDbCommandBuilder(da)
          conn.Open()

          Dim dt As New DataTable
          da.Fill(dt)

          Dim FileName As String = IO.Path.GetFileName(PicToAdd)
          Dim ft As String = IO.Path.GetExtension(PicToAdd).Replace(".", "").ToLowerInvariant

          Dim filter As String = "[AttachmentField.FileName]='" & FileName & "' And [AttachmentField.FileType]='" & ft & "'"
          Dim checkrow() As DataRow = dt.Select(filter)
          If checkrow.Length = 0 Then
             'add new attachment
             Dim row As DataRow = dt.NewRow
             row("AttachmentField.FileName") = FileName
             Dim strm As New IO.FileStream(PicToAdd, IO.FileMode.Open, IO.FileAccess.Read)
             Dim buffer(CInt(strm.Length)) As Byte
             strm.Read(buffer, 0, CInt(strm.Length))
             row("AttachmentField.FileData") = buffer
             row("ID") = dt.Rows(0)("ID")
             dt.Rows.Add(row)
             da.Update(dt)
          End If
          conn.Close()

    As always, have fun and keep moving forward.

     

    Sunday, December 11, 2011 4:02 PM
  • Hi TnTinMN

    >All that I meant by pointing you to the other thread was that there is apparently >some meaning to those 20 bytes. I apologize if I inadvertently offend you.

     

    Apology refused :), I did not have that thought neither felt any offend at any time in your post. I know what you meant by posting that thread and thankfully convert it to vb.net. When I said I did not look at it, I meant by "reading" the attachment, it seems I don't need to know anything more than skip 20 bytes. But I "may" need it to insert attachment or modify an existing one which is something I am goanna start working on it very soon.

    >To be honest, I was very impressed that at what you discovered and the intelligence displayed by your investigative method, even if you were not the first to do so. 

    Here is where I knew that my Nobel Prize evaporated. (lol)

    Thank you for the complement. I like programming challenges, and I am having fun also :)

    Sunday, December 11, 2011 6:30 PM
  • Hi Samir,

    All that I meant by pointing you to the other thread was that there is apparently some meaning to those 20 bytes.  I apologize if I inadvertently offend you. To be honest, I was very impressed that at what you discovered and the intelligence displayed by your investigative method, even if you were not the first to do so.  Your tenacity in seeking a solution is trait that I find severely lacking by many who want to do programming.  This is not official or substantial, but here is your reward.

                                             

    The  information that I am still seeking is a definition for those remaining 16 bytes in the data file prefix.

    I'm still attempting to find a way to update using OleDB.   The following code fails with a "Dynamic SQL generation is not supported against multiple base tables." error on the update command.  From what I have observed is that a record is created in the Access System Table "MSysComplexColumns" for each attachment field defined and this record points to a table in "MSysObjects".  However, I do not know how to craft the SQL commands to update hidden/system tables.

          Dim conn As New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & DBFile & "'")
          Dim da As New OleDb.OleDbDataAdapter()

          da.SelectCommand = New OleDb.OleDbCommand("SELECT " & _
                            "ID, " & _
                            "AttachmentField.FileData, " & _
                            "AttachmentField.FileFlags, " & _
                            "AttachmentField.FileName, " & _
                            "AttachmentField.FileType, " & _
                            "AttachmentField.FileURL, " & _
                            "AttachmentField.FileTimeStamp" & _
                            " FROM TESTATTACHMENTS " & _
                            "WHERE ID = @ID", conn)

          da.SelectCommand.Parameters.Add("@ID", OleDb.OleDbType.Integer).Value = 1
          Dim cb As OleDb.OleDbCommandBuilder = New OleDb.OleDbCommandBuilder(da)
          conn.Open()

          Dim dt As New DataTable
          da.Fill(dt)

          Dim FileName As String = IO.Path.GetFileName(PicToAdd)
          Dim ft As String = IO.Path.GetExtension(PicToAdd).Replace(".", "").ToLowerInvariant

          Dim filter As String = "[AttachmentField.FileName]='" & FileName & "' And [AttachmentField.FileType]='" & ft & "'"
          Dim checkrow() As DataRow = dt.Select(filter)
          If checkrow.Length = 0 Then
             'add new attachment
             Dim row As DataRow = dt.NewRow
             row("AttachmentField.FileName") = FileName
             Dim strm As New IO.FileStream(PicToAdd, IO.FileMode.Open, IO.FileAccess.Read)
             Dim buffer(CInt(strm.Length)) As Byte
             strm.Read(buffer, 0, CInt(strm.Length))
             row("AttachmentField.FileData") = buffer
             row("ID") = dt.Rows(0)("ID")
             dt.Rows.Add(row)
             da.Update(dt)
          End If
          conn.Close()

    As always, have fun and keep moving forward.

     

    Hi TnTinMN

    Upon my tests goes on, and while debugging your code, the error is on

    Dim cb As OleDb.OleDbCommandBuilder = New OleDb.OleDbCommandBuilder(da)
    

    I add this line

    debug.Print(cb.GetInsertCommand.CommandText.ToString)

    I get an error message
    Dynamic SQL generation is not supported against multiple base tables.

    I guess the command builder is unable to generate the correct insert statement and you had to assign it manually.

     

    Tuesday, December 13, 2011 6:53 AM
  • Hi TnTinMN

    I finally managed to make your code work. Here is the working code and my notes.

    - The first 20 byte of each file type are fixed, Actually, as you can see below, from byte 1 to 12, they are the same, only byte 13,15,17 are changing.

     

         01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20
         -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
    JPG  14 00 00 00 01 00 00 00 04 00 00 00 6A 00 70 00 67 00 00 00
    TXT  14 00 00 00 01 00 00 00 04 00 00 00 74 00 78 00 74 00 00 00
    XLS  14 00 00 00 01 00 00 00 04 00 00 00 78 00 6C 00 73 00 00 00

    Here is my modification on your code so it worked. In order to read the image, you had to skip 20 byte. In order to insert an image, you had to add the 20 byte to the beginning of the image.


    Dim conn As New OleDb.OleDbConnection("connection_string")
    Dim da As New OleDb.OleDbDataAdapter()
        
    da.SelectCommand = New OleDb.OleDbCommand("SELECT " & _
                    "[ID], " & _
                    "AttachmentField.FileData, " & _
                    "AttachmentField.FileName " & _
                    " FROM TESTATTACHMENTS " & _
                    "WHERE [ID] = @ID"conn)
          
    da.selectcommand.Connection = conn
    da.SelectCommand.Parameters.Add("@ID"OleDb.OleDbType.Integer).Value = 1
          
    Dim cb As OleDb.OleDbCommandBuilder = New OleDb.OleDbCommandBuilder(da)
          
    conn.Open
          
    Dim dt As New DataTable
    da.Fill(dt)
    Dim PicToAdd = "C:\Users\Public\Pictures\Sample Pictures\Jellyfish.jpg"
    Dim image_byte  = my.Computer.FileSystem.ReadAllBytes(PicToAdd)
    Dim FileName As String = IO.Path.GetFileName(PicToAdd)
    Dim ft As String = IO.Path.GetExtension(PicToAdd).Replace(".""").ToLowerInvariant
          
    Dim filter As String = "[AttachmentField.FileName]='" & FileName & "'"  ' And [AttachmentField.FileType]='" & ft & "'"
    Dim checkrow() As DataRow = dt.Select(filter)
    If checkrow.Length = 0 Then
        'add new attachment
        Dim row As DataRow = dt.NewRow
        row("AttachmentField.FileName") = FileName
        'COMMENT Dim strm As New IO.FileStream(PicToAdd, IO.FileMode.Open, IO.FileAccess.Read)
        'COMMENT Dim buffer(CInt(strm.Length)) As Byte
        'COMMENT strm.Read(buffer, 0, CInt(strm.Length))
             
    ' I COMMENT THE ABOVE 3 LINE BECAUSE STRM HAD TO BE IN BYTE FORMAT
            
        Dim jpg_20bytes = "14 00 00 00 01 00 00 00 04 00 00 00 6A 00 70 00 67 00 00 00"
        Dim hex_string = Replace(jpg_20bytes," ",""' remove the blank
             
        Dim byt_JPG20 = Convert_HexString_To_ByteArray(hex_string)
            
        row("AttachmentField.FileData") = byt_JPG20.Concat(image_byte).ToArray
        row("ID") = dt.Rows(0)("ID")
        dt.Rows.Add(row)
        'Cannot update 'FileFlag'; field not updateable.
        'Cannot update 'FileType'; field not updateable.
        'Cannot update 'FileUrl'; field not updateable.
        'Cannot update 'FileTimeStamp'; field not updateable.
        'I REMOVED THE ABOVE 4 FIELDS FROM THE SELECT IN ORDER FOR THE COMMAND BUILDER TO WORK
        da.Update(dt)  
    End If
    conn.Close()

        Function Convert_HexString_To_ByteArray(ByVal hex As StringAs Byte()         'hex = "A14152464C203230304232323020572F544947455234352E"         Dim raw As Byte() = New Byte((hex.Length / 2)  - 1) {}         Dim i As Integer         For i = 0 To raw.Length - 1             raw(i) = Convert.ToByte(hex.Substring((i * 2), 2), &H10)         Next i         Return raw     End Function
    Wednesday, December 14, 2011 3:32 PM
  • Hi Samir,

    I was ready to contact the Swedish Academy.  Unfortunately, I'm still getting that annoying multiple table update error with your code.  TYhe only thing I can think of that might be the cause is the connection string.  Did you have to add any extend properties or ad the read/write qualifiers?

    If you could could you share your connection string definition?

    Thanks

     

    Wednesday, December 14, 2011 8:49 PM
  • Hi Samir,

    I was ready to contact the Swedish Academy.  Unfortunately, I'm still getting that annoying multiple table update error with your code.  TYhe only thing I can think of that might be the cause is the connection string.  Did you have to add any extend properties or ad the read/write qualifiers?

    If you could could you share your connection string definition?

    Thanks

     

    LOL

    Here is my connection string

    Dim conn As New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\xxx2\test.accdb;Persist Security Info=True")

    I re-test my code given above, and it works well. Hurry up with your tests, change your pc if necessary, I want my prize. lol

    I have a question about the function you posted earlier.

       Private Function ParseImageData(ByVal data() As ByteAs Byte()
          'Original code in C# by: Mahmoud Zaben
          'http://social.msdn.microsoft.com/Forums/en-SG/csharpgeneral/thread/A7F0BF9F-58F5-4D91-993F-8B8A26D54B85
          Dim imageStartPos As Integer = BitConverter.ToInt32(data0)
          Dim unKnown As Integer = BitConverter.ToInt32(data4)
          Dim result(data.Length - imageStartPos - 1As Byte
          Array.Copy(dataimageStartPosresult0result.Length)
          Return result
       End Function

    When I debug, ImageStartPos get the value of 20

    Can you explain what BitConverter do exactly?

     

    Thursday, December 15, 2011 6:21 AM
  • Hi Samir,

    I still have not been successful in adding an attachment with OleDB.  But since you have had success, at least I know it can be done.  It is frustrating that I can not replicate your success though.

    --->Can you explain what BitConverter do exactly?<----

    The BitConverter.ToInt32(data,0) is reading four bytes (the byte length of Int32) starting at position 0 in the array and returning an Int32 value.   I hope this is what you meant by your question as I do not know by what means it is doing it. 

    I tend to accomplish the same thing with something like this:

    Public Function BytesToInt32(ByVal bytes() As Byte) As Int32
       Dim ms As New IO.MemoryStream(bytes)
       Dim br As New IO.BinaryReader(ms)
       Dim value As Int32 = br.ReadInt32
       br.Close()
       Return value
    End Function

    I know that its more code and a old technique (known as Internal Read/Write In FORTRAN)  that reflects my past  but I'm comfortable with it and its the first thing that pops into my head when I need to manipulate bytes.

    New Info:  20 bytes is not as static as you believe.

    It is 22 bytes for .docx and .xlsx files.  Also my JPG file has a slightly different signature than yours.

    "14 00 00 00 01 00 00 00 04 00 00 00 4A 00 50 00 47 00 00 00"

    -------------------

    You know, I'm starting to feel real bad about how we hi-jacked this thread.  I wonder how Mike Feng is going to responded to us when he reviews this in one of his janitorial sweeps.

    Thursday, December 15, 2011 4:35 PM
  • Hello TnTinMN

    Hi Samir,

    I still have not been successful in adding an attachment with OleDB.  But since you have had success, at least I know it can be done.  It is frustrating that I can not replicate your success though.

    I will upload my working project to my skydrive account as soon I finish it with the database, so you can try it

    --->Can you explain what BitConverter do exactly?<----

    The BitConverter.ToInt32(data,0) is reading four bytes (the byte length of Int32) starting at position 0 in the array and returning an Int32 value.   I hope this is what you meant by your question as I do not know by what means it is doing it. 

    What I mean with my question is this, I figure out the 20 byte difference between the original JPG and exported JPG by manually  making hex comparison, in this line Dim imageStartPos As Integer = BitConverter.ToInt32(data, 0) , imageStartPos value is 20 when I debug it, while the rest of the code is copying the bytes starting from byte 20 to new array. That mean, the BitConverter did some manipulation/calculation on the image. What calculation it made? How 20 is returned using BitConvertor?

    I will examine your function and reply later.

    btw: Can You upload your project so we can think on the same wave :)

     

    You know, I'm starting to feel real bad about how we hi-jacked this thread.  I wonder how Mike Feng is going to responded to us when he reviews this in one of his janitorial sweeps.

    LOL

    Man, I feel like I am writing my diary on this thread. LOOL

    Now, seriously. Is this wrong? Should I/we stop? because with my coming project which I am going to upload, and your project in case you decide to upload, and why your 20 bytes is different than my 20 bytes, we are not gonna finish soon :)

    In other forum, this is normal, but I don't know how thing is in here. So should we transfer this to a new discussion thread or continue here?

     

    Thursday, December 15, 2011 9:39 PM
  • Samir,

    I can upload later.  Just so that we are not making each others life more difficult, what version of VB are you using.

    I have 2005, 2008 and 2010 installed.  However I primarily work in 2008 as I  have the full VS2008.

    We probably should have broke this out into a discussion thread.  I'm not that well versed in forum etiquette.  I have see a lot worse than we are doing here and least this has been a productive and civil discussion unlike some I've seen lately.

    Please let me know what version to use and I will get to it later.

     

    Thursday, December 15, 2011 9:58 PM
  • Please let me know what version to use and I will get to it later.

    I am using vs2010.

    I am also not familiar with MSDN forum. So, you will start new discussion when you upload your project?

    I guess it would be a good idea if we finish it here with a link to the new created discussion thread. No?

    Thursday, December 15, 2011 10:48 PM