DataReader DIES on Image column

I'm at the point where I doubt an answer exists for this. It seems so simple but after hrs of searching....in vain....

I have a stored procedure - written like all the other working stored procedures. What makes this stored procedure unique is that it is returning a single image from an SQL Server 2005 column of type Image.

I'm using the same code (Visual Studio 2005 VB.NET) that works for retrieving output parameters like UniqueIdentifiers and Varchars.

But when it comes time for Sqldatareader to execute on an Image it throws up this error message:
'Byte[] [2]: the Size property has an invalid size of 0'

Here is the relevant code
cmdGet = New SqlClient.SqlCommand("getItemImage", cn)

...

cmdGet.CommandType = CommandType.StoredProcedure

...
cmdGet.Parameters.Add("@LibraryItemID", SqlDbType.UniqueIdentifier).Direction = ParameterDirection.Input
cmdGet.Parameters.Add("@ImageTypeID", SqlDbType.UniqueIdentifier).Direction = ParameterDirection.Input
cmdGet.Parameters.Add("@ItemImage", SqlDbType.Image).Direction = ParameterDirection.Output
...
Dim reader As SqlDataReader
reader = cmdGet.ExecuteReader(CommandBehavior.SequentialAccess) -> this is where it dies

Can anybody please resusitate this sorry code-situation?
Peter

[1344 byte] By [peterLH] at [2008-1-8]
# 1

I believe it is because image type does not have fixed size and, actually, returned different way as other simple types. Why do you return it as a parameter? You could use reader to get it as a field or use DataAdapter to return it as a single value inside of the DataTable. It will work I do not see any advantage of using output parameter in this specific case.

VMazur at 2007-10-2 > top of Msdn Tech,.NET Development,.NET Framework Data Access and Storage...
# 2
Thanks for replying.

I didn't know that Stored Procedures could return fields rather than parameters. So made the switch to a query and used fields, as recommended, to return the images successfully.

Was going with stored procedures to reuse them with different applications that would sit ontop of the database ... but at this point making it work is more important.

cheers
Peter

peterLH at 2007-10-2 > top of Msdn Tech,.NET Development,.NET Framework Data Access and Storage...

.NET Development

Site Classified