BlogOfBob

February 18, 2007

@ 11:19 PM

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