Using IIS ASP to display a blob field in SQL Server 2005 2

[ziggs]

I'm using Windows 2000 Server IIS ASP and I'm attempting to display a picture via an vbscript/ASP of a Blob image in SQL Server 2005. Below, is part of the code (the important part) that I'm using after reading up a bit. Unfortunately, I'm not getting the Blob image in one of our Vendor's SQL database to pop up. Instead, I'm getting a box with a red X in the middle of it.

Any ideas as to what I'm missing?

SQL1 = "SELECT RD.ID, RD.image"
SQL1 = SQL1 & " WHERE RD.ID = '29'"
set rs = my_conn.Execute(SQL1)

<TR>
<TD bgcolor=<%=FieldColor%> align=center colspan=1><B>ID</B>
</TD>

<TD bgcolor=<%=FieldColor%> align=center
colspan=1><B>Picture/B>
</TD>
</TR>

<%if rs.EOF then%>

<TR>
<TD bgcolor=<%=InfoField%> align=center
colspan=10><B>No definition found</B></TD>
</TR>

<%
else
do until rs.EOF%>
<TR>

<TD bgcolor=<%=InfoField%>
align=center><%=rs(&amp;amp;quot;ID&amp;amp;quot%>&nbsp;
</TD>

<TD bgcolor=<%=InfoField%> align=center><img
src="<%=rs(&amp;amp;quot;image&amp;amp;quot%>">&nbsp;
</TD>

 

[wvdba]

your code:
SQL1 = "SELECT RD.ID, RD.image"
"SQL1 = SQL1 & " WHERE RD.ID = '29'"
should you have a table name here?
like
SELECT RD.ID, RD.image FROM your_table

 

[ziggs]

I did a bad job at cutting, pasting and trying to reduce this to a coding that's easier to work with. The corrected stated is:

SQL1 = "SELECT RD.ID, RD.image from RD"
"SQL1 = SQL1 & " WHERE RD.ID = '29'"

Thanks

 

[tsuji]

[0] Rendering image(s) from the blob field of rs can be quite a detour. Let me suppose they are all of mime type image/jpg to simplify a bit.

[1] First set up a image rendering asp, say img_rendering.asp. It looks something like this.
[tt]
<%
'img_rendering.asp
response.expires=0
response.buffer=true
response.clear

idx=cint(request.querystring("idx"))
response.contenttype="image/jpg" 'if not same, use session again with idx dependent
response.binarywrite Session("image" & idx) 'byte()

session("image" & idx)=""; 'free up memory
response.end
%>
[/tt]
[2] Then looping through rs in the main page looks like this.
[tt]
<%
else
idx=0
do until rs.EOF
session("image" & idx)=rs("image").value
%>
<TR>

<TD bgcolor="<%=InfoField%>" align="center"><%=rs("ID").value %>&nbsp;
</TD>

<TD bgcolor="<%=InfoField%>" align="center">
<img src="img_rendering.asp?idx=<% =idx %>" alt="image index <% =idx %>">&nbsp;
</TD>

</TR>
<%
rs.movenext
idx=idx+1
loop
end if
rs.close
set rs=nothing
%>
[/tt]
[3] In case every row may have different type, that info can be passed through a new session variable so that the rendering asp can vary the response.contenttype accordingly. That is just a detail for more general case.

[4] All attributes in tags should generally be quoted, that is much better from html point of view, though ie would take everything gracefully in case no space appeared!

 

[foxbox]

http://support.microsoft.com/kb/173308

 

[tsuji]

amendment
I got a typo there
>[self]session("image" & idx)=""; 'free up memory
No semi-colon!
[tt]session("image" & idx)=""[highlight] [/highlight] 'free up memory[/tt]

 

[ziggs]

Sorry, I did not double check my work. Here's the correct codes that I'm using. Something went wrong in the cut and paste process. AND FOXBOX, I was able to show a single value with the website you provided. Now, I'm trying to figure out the looping details that Tsuji provided. Tsuji, the name of the table is still correct, RD.ID and RD.Image but I can't figure out your guide since I probably used the wrong names.

<TR>
<TD bgcolor=<%=FieldColor%> align=center colspan=1><B>ID</B>
</TD>

<TD bgcolor=<%=FieldColor%> align=center
colspan=1><B>Picture/B>
</TD>
</TR>

<%if rs.EOF then%>

<TR>
<TD bgcolor=<%=InfoField%> align=center
colspan=10><B>No definition found</B></TD>
</TR>

<%
else
do until rs.EOF%>
<TR>

<TD bgcolor=<%=InfoField%> align=center><%=rs("ID")%>&nbsp;</TD>

<TD bgcolor=<%=InfoField%> align=center>img
src=<%=rs("img")%>&nbsp;</TD>

 

[ziggs]

Ooops, it's:

<TD bgcolor=<%=InfoField%> align=center>img
src=<%=rs("image")%>&nbsp;</TD>

for last line

 

[ziggs]

Actually, I have it now. Thanks so much to all.

 

[ziggs]

One last problem, I added a width and height to Tsuji's suggested code to reduce the picture size. Thus, giving a preview picture. In this same code that calls the blob picture, is there a way that a user can click on this preview photo and pull up the same picture in it's original size. I tried combinations of a hyperlink, but the idx is throwing me off.

<TD bgcolor="<%=InfoField%>" align=left valign=top><img src="img_rendering.asp?idx=<% =idx %>" width="250" height="159" alt="image index <% =idx %>width=">&nbsp;</TD>

 

[tsuji]

Haven't looked closely. But if you want to re-use the data, keep the session alive and leave it in the memory at the server. That's the general idea.
[tt]
[red]'[/red]session("image" & idx)="" 'free up memory
[/tt]

 

[wvdba]

i use this code to display the image and when clicking on image, it gives option to save or open the image in it's actual size.

<a href="imagedownload.asp"></a>
<img src="imagedisplay.asp" width="140" height="174"></a>"

 

[ziggs]

Sorry for not understanding, but since I'm viewing hundreds of jpegs at a time in this search, how long does this data stay alive? In other words, I don't want the server load up it's memory and start to slow down.

With my limited skills, I used this line and just created another asp called BigPicture.asp that just shows the photo. If there's a more efficient way, I'm interested.

Thx

<TD bgcolor="<%=InfoField%>" align=left valign=top>
<a href="BigPicture.asp?id=<%=rs(&quot;ID&quot%>" target=_new"><img src="img_overview.asp?idx=<% =idx %>" width="250" height="159" alt="image index <% =idx %>width=">&nbsp;</TD>

 

[ziggs]

wbvda, I'll try it later today. I didn't see your post until now.
TIA

 

[ziggs]

My final problem is that when a user runs this ASP, the pictures can be viewed on the page, but won't print. Instead, I get the name of the picture, "image index 0", "image index 1", etc. Is there a slight modification that I need to have the image print?