I was recently using the old standby NorthWind database that has been a stock example database with SQL Server for a long time. It is no longer distributed with SQL Server 2005 but you can download updated scripts that will create and populate the database on SQL Server 2005. If you want them they can be downloaded here along with the companion pubs database as well. Don't let the title fool you, these scripts are 2005 aware even though the download page says 2000.
I have used the same or similar code to retrieve images stored in SQL server for a long while without issue but now I was getting errors retrieving the photo data.
Here is the code I was using:
For Each row As DataGridViewRow In Me.dgvEmployees.SelectedRows
Dim Emp As Employees = TryCast(row.DataBoundItem, Employees)
If Not Emp Is Nothing Then
Dim imageData() As Byte = CType(Emp.Photo, Byte())
Dim msTemp As New MemoryStream
msTemp.Write(imageData, 0, imageData.Length)
Dim img As Image = Image.FromStream(msTemp)
picEmployee.Image = img
End If
m_CurrentEmployeeID = CType(row.Cells(0).Value, Long)
lblEmployeeID.Text = m_CurrentEmployeeID.ToString
Next
The code was failing with an "Invalid Parameter" error on the line containing msTemp.Write
After some serious head scratching, and a little Google work I stumbled on this article on Visual Studio Magazines site. The article had a paragraph at the bottom that explained the mystery:
Notice a subtlety in this code that is particular of the Northwind database but has no relevance in general. The original Access database was converted into SQL Server's Northwind database, so the image field called Photo doesn't contain a true GIF file; instead it contains the OLE object that Access builds to wrap any image. As a result, the stream of bytes you read from the field is prefixed with a header you must strip off to get the bits of the image. Such a header is variable-length and also depends on the length of the originally imported file's name. For Northwind, the length of this offset is 78 bytes.
I changed my code to take that 78 byte header into account as follows and everything worked as it should have!
For Each row As DataGridViewRow In Me.dgvEmployees.SelectedRows
Dim Emp As Employees = TryCast(row.DataBoundItem, Employees)
If Not Emp Is Nothing Then
Dim imageData() As Byte = CType(Emp.Photo, Byte())
Dim msTemp As New MemoryStream
'note the image data in Northwinds contains a 78 byte header
'that needs to be stripped off.
msTemp.Write(imageData, 78, imageData.Length - 78)
Dim img As Image = Image.FromStream(msTemp)
picEmployee.Image = img
End If
m_CurrentEmployeeID = CType(row.Cells(0).Value, Long)
lblEmployeeID.Text = m_CurrentEmployeeID.ToString
Next
Hopefully this will help the next person to stumble upon this historical oddity!
Cheers,
Robert Porter