Problems retrieving image from SQL server

I need to retrieve a small GIF stored in a SQL server Image field and
display it on an ASP page.

I have a wrapper page to return the image which I call from another page
like this. :



The code to retrieve the image doesn't seem to work, all i get is a default
image place holder and no image. The image is definately in the DB and in
GIF format. I can pull the images just fine in Access. I tried a few
different methods all of which only return an image place holder each time.

Any ideas much appreciated.

image_wrap.asp
===========
<%

Dim iProductID
iProductID = Request.QueryString("ProdID")

Dim objConn, objRS, strSQL
strSQL = "SELECT Image FROM Products WHERE ProdID = " & iProductID

Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open "dsn=dsn3;uid=me;pwd=secret;"

Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.Open strSQL, objConn

Response.ContentType = "image/gif"
Response.BinaryWrite objRS("Image")

objRS.Close
Set objRS = Nothing
objConn.Close
Set objConn = Nothing

%>

Re: Problems retrieving image from SQL server

On Mon, 20 Feb 2006 10:50:20 -0000, "Sean" wrote:

>I need to retrieve a small GIF stored in a SQL server Image field and
>display it on an ASP page.
>
>I have a wrapper page to return the image which I call from another page
>like this. :
>
>
>
>The code to retrieve the image doesn't seem to work, all i get is a default
>image place holder and no image. The image is definately in the DB and in
>GIF format. I can pull the images just fine in Access. I tried a few
>different methods all of which only return an image place holder each time.
>
>Any ideas much appreciated.
>
>image_wrap.asp
>===========
><%
>
> Dim iProductID
> iProductID = Request.QueryString("ProdID")
>
> Dim objConn, objRS, strSQL
> strSQL = "SELECT Image FROM Products WHERE ProdID = " & iProductID
>
> Set objConn = Server.CreateObject("ADODB.Connection")
> objConn.Open "dsn=dsn3;uid=me;pwd=secret;"
>
> Set objRS = Server.CreateObject("ADODB.Recordset")
> objRS.Open strSQL, objConn
>
> Response.ContentType = "image/gif"
> Response.BinaryWrite objRS("Image")
>
> objRS.Close
> Set objRS = Nothing
> objConn.Close
> Set objConn = Nothing
>
>%>


Take a look at:

http://www.aspfaq.com/show.asp?id=2149

Jeff

Re: Problems retrieving image from SQL server

"Jeff Cochran" wrote in message
news:43f9ad69.323407828@msnews.microsoft.com...
> On Mon, 20 Feb 2006 10:50:20 -0000, "Sean" wrote:
>
>>I need to retrieve a small GIF stored in a SQL server Image field and
>>display it on an ASP page.
>>
>>I have a wrapper page to return the image which I call from another page
>>like this. :
>>
>>
>>
>>The code to retrieve the image doesn't seem to work, all i get is a
>>default
>>image place holder and no image. The image is definately in the DB and in
>>GIF format. I can pull the images just fine in Access. I tried a few
>>different methods all of which only return an image place holder each
>>time.
>>
>>Any ideas much appreciated.
>>
>>image_wrap.asp
>>===========
>><%
>>
>> Dim iProductID
>> iProductID = Request.QueryString("ProdID")
>>
>> Dim objConn, objRS, strSQL
>> strSQL = "SELECT Image FROM Products WHERE ProdID = " & iProductID
>>
>> Set objConn = Server.CreateObject("ADODB.Connection")
>> objConn.Open "dsn=dsn3;uid=me;pwd=secret;"
>>
>> Set objRS = Server.CreateObject("ADODB.Recordset")
>> objRS.Open strSQL, objConn
>>
>> Response.ContentType = "image/gif"
>> Response.BinaryWrite objRS("Image")
>>
>> objRS.Close
>> Set objRS = Nothing
>> objConn.Close
>> Set objConn = Nothing
>>
>>%>
>
>
> Take a look at:
>
> http://www.aspfaq.com/show.asp?id=2149
>
> Jeff

Thanks, I looked at the examples and my modified code is pretty much the
same as http://support.microsoft.com/default.aspx/kb/173308, but still it's
not working. I also tried a couple of the other methods.

The original images were added to SQL server through an Access front end, I
am wondering if Access is adding something weird to the images before
storing them in SQL. The code appears to work, the image is being retreived,
but does not display.

Re: Problems retrieving image from SQL server

>The original images were added to SQL server through an Access front end, I
>am wondering if Access is adding something weird to the images before
>storing them in SQL.

Almost certainly. If you are using OLE Embedding (with a Bound Object Frame, or 'Insert Object', or just
pasting into a field) then the images are stored in the private format of whatever application is registered
to handle that file-type on that system, and further wrapped in headers etc. In fact, even after stripping
the headers there probably isn't a gif image to be found in there.

You can check how the images are stored by opening the table in Access. If the field contents appear as
'Long Binary Data' then you have the gif stored directly in the field, in which case your asp code should
work. If you have anything else (eg Microsoft Photo Editor 3.0 Photo, MS Paint Bitmap etc etc) then you
have OLE Embedded data, with the headers and private format problems that go with that (not to mention
a potentially huge overhead - with JPEG it can be up to 200 *times* the original file size).


--
_______________________________________________________
DBPix 2.0: Add pictures to Access, Easily & Efficiently
http://www.ammara.com/dbpix/access.html