With so many solutions, how can you tell which solutions are most likely to help you and which ones are not? To provide you with a tool to use, we rate our solutions based on various elements that most accurately determine if a solution is a quality solution. To explain what factors affect the solution rating, here are the elements we take into consideration when formulating our solution rating.
The Grade of the Solution
The Zone Rank of the Expert Providing the Solution
The Number of Author and Expert Comments
The Number of Experts Contributing
The Feedback of the Community
Your Input Matters Because of the way the system is set up, the most important variable in this equation is you. As a member of Experts Exchange, you are able to cast your vote on the quality of the solutions in regard to how complete, accurate, helpful and easy to understand each solution is. When you provide your feedback, each rating is adjusted accordingly. So, if you see a solution that has a poor rating that you think is a good solution, let us know by rating it. As you do, the rating will be adjusted and will become more accurate for other members of our site.
If you have any suggestions that you would like to make for our rating system, please ask a question in the Suggestions Zone of Community Support.
I have an access 2000 database with several thousand pictures and Microsoft documents (doc, xls, etc) in an OLE Object field. I have a vb application that will read the access database and return the OLE Object to the vb.net code. My problem is that when I attempt to write the bytes out from column that is returned, the original software (Word, Excel, and Photo Editor) does not recognize the file.
I use this same code to get data from a SQL Server image column and it works fine. The code is as follows:
Public Sub BinImageWrite(ByVal theOutput As Object, ByVal theFile_Location As String, ByVal Append As Boolean, ByVal AddToOffset As Integer)
Dim fs As FileStream Dim offset As Long = 0 + AddToOffset
Try If Append Then If File.Exists(_userFilePath) Then fs = New FileStream(theFile_Location, FileMode.Append, FileAccess.ReadWrite) offset = fs.Length + 1 Else fs = New FileStream(theFile_Location, FileMode.CreateNew, FileAccess.Write) End If Else If File.Exists(_userFilePath) Then File.Delete(_userFilePath) End If
fs = New FileStream(theFile_Location, FileMode.CreateNew, FileAccess.Write) End If
WriteBinaryData(fs, theOutput, offset) Catch ex As SystemException Throw New ApplicationException("The date to be written was empty or null.") Catch ex As Exception Throw ex End Try
fs.Close() End Sub
Private Sub WriteBinaryData(ByVal fs As FileStream, ByVal theOutput As Object, ByVal offset As Long) Try Dim theData() As Byte = theOutput Dim dataLen As Long = UBound(theData)
fs.Write(theData, offset, dataLen) Catch ex As Exception Throw ex End Try End Sub
The above code worked perfectly for the SQL Server side, but the Access DB side gives me junk. I attempted a binary writer routine for the OLE Object data. It is as follows:
Public Sub OLEObjectWrite(ByVal theOutput As Object, ByVal theFile_Location As String, ByVal Append As Boolean)
Dim offset As Integer = 0 PathFileLocation = theFile_Location
Try Dim fs As FileStream Dim bw As BinaryWriter Dim outBytes() As Byte = theOutput
If Append Then If File.Exists(_userFilePath) Then fs = New FileStream(_userFilePath, FileMode.Append, FileAccess.ReadWrite) offset = fs.Length + 1 Else fs = New FileStream(_userFilePath, FileMode.CreateNew, FileAccess.Write) End If Else If File.Exists(_userFilePath) Then File.Delete(_userFilePath) End If
fs = New FileStream(_userFilePath, FileMode.CreateNew, FileAccess.Write) End If
bw = New BinaryWriter(fs)
bw.Write(outBytes, 0, outBytes.Length - 1)
bw.Flush() bw.Close() fs.Close() Catch ex As SystemException Throw New ApplicationException("The date to be written was empty or null.") Catch ex As Exception Throw ex End Try End Sub
This code does not get an error, but if I go into the Access Database, and right-click on the image. Tell it to place it into a Photo Editor Photo Object, the mage appears and when I save it, its size is about 7 KB for a jpg. Using the above routine, the image size is 303 KB. I've been searching for weeks on the net attempting to get this to work without success.
I need to avoid using OLE reads from the database, because this is a generic utility to connect and read databases. Do the data coming back will be an OLE Object for Access, which is converted to a byte array.
I would appreciate any insight, or better yet, solution, to this issue.
This definitely works. In fact, I've put together a VS 2005 solution with an Access Database for you. Just open it up and run the program. You'll have the opportunity to enter files (Word docs, Excel Workbooks, images, sound files, etc.), and save them to the database. You will also be able to retrieve your data from the database and save them to files.
The first is I had seen this before and was also able to duplicate it. If you looked at my original description, and code, you will see that I'm pulling in the bytes. So I need to be able to take those bytes and write them out. I do not know the file that is stored, and need to determine this from the data stored in the OLE Object. Include name (if possible) and extension.
The second problem with the code provided, is that you do not determine the name that was stored in the OLE Object or the file type (extension). If you do not add an extension to your file name, the MS Office products ill not open it properly and gets an error.
Since I already have the data coming from the database, I need a means of taking those bytes, writing them out to a proper file, getting the file extension type or storage type of the item (gif, jpg, xls, doc, etc) so the name provided will have the proper extension, and/or getting the original file name that was used to place the item into the OLE object. With the original name I can get the extension from that.
Addressing your first paragraph, "you will see that I'm pulling in the bytes." - If you looked at my example you would see that it is pulling in bytes also, storing the data as "long binary" (bytes) in the database, and writing the bytes out to a file. So it's not like my example demonstrated something different than you were doing.
You're problem is that you have no clue about what type of object it is, and you want to determine that from the binary data (stored bytes). For that, unfortunately, I'm not going to be able to help you.
Just as a lead, VB6 used a control that is obselete in .Net - it was the OLE control. This control had a method called "CreateEmbed" that stored the data in an Access OLE Object format, which I think may have been some type of a wrapper for the data, that told Access what type of data it was, so when it was opened, it could open the OLE data using the appropriate container.
Okay, then I do not understand something. You store the picture as lone bytes, and when you open the table it states that. The original data from my db was done within an access import into a picture box, so my table data says "Microsoft Excel Worksheet", "Microsoft Word Document" or "Microsoft Photo Editor 3.0 Photo".
I could manuall cretae a extionsion field as you have, but I'm not understanding how I can take my exeampe and converti it into the output as you have it.
For example, in my code above I have the data being passed as an object in the routine:
Public Sub OLEObjectWrite(ByVal theOutput As Object, ByVal theFile_Location As String, ByVal Append As Boolean)
within this routine the object is set to a byte array:
Dim outBytes() As Byte = theOutput
will your WriteAllBytes handle this correctly? Currently with my example above, I mension the file size difference between the orignal imported version and the one that is written out with what I currently have. Even with putting the correct extension on the file name, such as JPG, the file will not open and display the picture.
That's exactly what my last post was talking about. When data is stored in a "Long Binary" format, it does NOT have Access' "wrapper" around it that tells Access what type of data it is. That is why you see "Long Binary" when you open up Access, and NOT "Microsoft Excel Worksheet", etc.
** Read the link I provided, it explains it all. **
The reason you're file size is increasing, is you are APPENDING additional data to the file that does not belong there. In other words, you are (most likely) merging data, either from different columns in the same row, or from different files, and that's why you get that error.
'Here's an example:
'Set to your .jpg Dim myPicture As String = "C:\TEMP\pics\starbucks logo.jpg"
'Set the new file to save it to: Dim mySavedPic As String = "C:\TEMP\myPicture.jpg"
'Read all bytes from the .jpg into a Byte() array Dim bites As Byte() = My.Computer.FileSystem.ReadAllBytes(myPicture)
'** bites now stores all of the binary data of the picture in a Byte array.
'Write all of this out to a different file: My.Computer.FileSystem.WriteAllBytes(mySavedPic, bites, False)
'Worked like a charm. (** same EXACT size as before **)
MsgBox("Open the file now and look at it: " & mySavedPic)
'Reproduce your error: 'This is important: DO NOT APPEND YOUR DATA
'Delete the file. My.Computer.FileSystem.DeleteFile(mySavedPic)
'Here's an example that will reproduce your error: Dim myExcelFile As String = "C:\TEMP\Book1.xls" 'Set to an Excel file
Dim picBytes As Byte() = My.Computer.FileSystem.ReadAllBytes(myPicture) Dim xlsBytes As Byte() = My.Computer.FileSystem.ReadAllBytes(myExcelFile)
'Write them both out to the same file - *APPENDING* them (Appending is the error) ' File size increases, and is unopenable by the OS, or any other program: My.Computer.FileSystem.WriteAllBytes(mySavedPic, xlsBytes, True) My.Computer.FileSystem.WriteAllBytes(mySavedPic, picBytes, True)
'Make sure when you are retrieving your data from the database, that you retrieve the ' correct data - and ONLY the data for the 1 object you want to write to a file.
' Make sure you are NOT trying to write out the whole returned row from the database, ' but just the column in the row that contains the OLE data.
' For example, this SELECT statement retrieves only the "data" column from the 1 row that has ' and ID of 1: ' SELECT [data] FROM myTable WHERE ID = 1
MsgBox("Try to look at the following file. You will not be able to, " & _ "because we merged 2 files into 1, and the file size has increased: " & _ vbCrLf & vbCrLf & mySavedPic)
Ah, I'll take a look at this in more detail. I think I'm now understanding your explanation. Thanks. I'm giving you the points, but can I ask more question sit they arrise on this?
