locked
DataReader DIES on Image column RRS feed

  • Question

  • 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
    Wednesday, August 15, 2007 5:56 AM

Answers

  • 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.

     

    Wednesday, August 15, 2007 10:19 AM

All replies

  • 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.

     

    Wednesday, August 15, 2007 10:19 AM
  • 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
    Thursday, August 16, 2007 3:55 AM