Query SQL Server for Binary Image and display in HTML/ASP.

Once I query SQL Server for a binary image how would I display it in an ASP/HTML page?  I have no problems with the query it self...I'm just not sure how to convert(?) it and display it as a jpg or something similar?

SQL Server 2000 (image stored as an "image" datatype (16))
ASP (not .net) utilizing VBScript and HTML
irishskiAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MorcalavinCommented:
I would imagine something like

Response.ContentType = "image/jpeg"
Set objStream = Server.CreateObject("ADODB.Stream")
Response.BinaryWrite objStream.Write(yourbinarydata).Read
objStream.close
objStream = nothing
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
irishskiAuthor Commented:
Morcalavin: I tried what you suggested and my result is an error.  I've attached my code snippet.  My error is:
Error Type:
ADODB.Stream (0x800A0C93)
Operation is not allowed in this context.
/tyt/testimages.asp, line 38


<%@ Language=VBScript %>
 
<html>
<head>
<title>test</title>
 
</head>
<body bgcolor="#f1eacf">
<!-- #include file ="connection.asp" -->
 
<%
'********** connection.asp is my connection to SQL Server 2000, this works fine.
 
'********** Create the recordset to hold information
	Dim objRS
	Dim strQ
 
	Set objRS = Server.CreateObject("ADODB.Recordset")
	Set objRS.ActiveConnection = objConn
 
'********** Get all current records with images
 
	strQ = "SELECT * FROM ImageProfile"
	
	objRS.Open strQ
 
	objRS.MoveFirst
 
	%>
	<table width="400" border=1>
	<%
	Do While Not objRS.EOF
	
 
	Response.ContentType = "image/jpeg"
	Set objStream = Server.CreateObject("ADODB.Stream")
	objStream.Open
	Response.BinaryWrite objStream.Write(objRS("IPImage")).Read
	objStream.Close
	objStream = Nothing
 
%>
		<tr>
			<td><%=objRS("IPCode")%></td>
			<td><%=objRS("SequenceNumber")%></td>
		</tr>
<%		
 
	objRS.MoveNext
 
	Loop
	%>
	</table>
	<%
 
		
	objRS.close
%>
 
</body>
</html>

Open in new window

0
MorcalavinCommented:
You won't be able to mix html with the content type you want.  You are seving an image/jpeg, not text/html.  The image code would have to be in a different file and reference like this:
<img src="image.asp"/>

It shouldn't contain any markup, just database code and the code to print the binary image.

For the stream party, try setting objStream.type = 1
That should flag it as binary.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

irishskiAuthor Commented:
Thanks for the reply Morcalvin... here are my results.

I made a separate .asp file for the image (image.asp) and referenced it using an IMG tag as you suggested.  My main page loads fine however the image errors out.  When viewing just the image.asp in the browser here is the error I receive:
"
Error Type:
Microsoft VBScript runtime (0x800A01A8)
Object required: 'objStream.Write(...)'
/tyt/image.asp, line 25
"
The code is image.asp:
Any ideas?  

Thanks in advance for the time.
<%@ Language=VBScript %>
<!-- #include file ="connection.asp" -->
<%
'********** connection.asp is my connection to SQL Server 2000, this works fine.
 
'********** Create the recordset to hold information
	Dim objRS
	Dim strQ
 
	Set objRS = Server.CreateObject("ADODB.Recordset")
	Set objRS.ActiveConnection = objConn
 
'********** Get all current records with images
 
	strQ = "SELECT * FROM ImageProfile"
	
	objRS.Open strQ
 
	objRS.MoveFirst
 
	Response.ContentType = "image/jpeg"
	Set objStream = Server.CreateObject("ADODB.Stream")
	objStream.Open
	objStream.Type=1
	Response.BinaryWrite objStream.Write(objRS("IPImage")).Read
	objStream.Close
	objStream = Nothing
 
	objRS.MoveNext
	objRS.close
%>

Open in new window

0
irishskiAuthor Commented:
Solved it!

Here is what my image.asp now looks like.  It works!


<%@ Language=VBScript %>
<!-- #include file ="connection.asp" -->
<%
'********** connection.asp is my connection to SQL Server 2000, this works fine.
 
'********** Create the recordset to hold information
	Dim objRS
	Dim strQ
 
	Set objRS = Server.CreateObject("ADODB.Recordset")
	Set objRS.ActiveConnection = objConn
 
'********** Get all current records with images
 
	strQ = "SELECT * FROM ImageProfile"
	
	objRS.Open strQ
 
	objRS.MoveFirst
 
	Response.ContentType = "image/jpeg"
 
	Response.Expires = 0
	Response.Buffer = TRUE
	Response.Clear
 
	Response.BinaryWrite objRS("IPImage")
	Response.End 
 
	objStream.Close
	objStream = Nothing
	objRS.close
%>

Open in new window

0
irishskiAuthor Commented:
Thanks for the help!
0
MorcalavinCommented:
Glad you got it working.  I've never actually done a binary write from the database before, I usually do it from files(that's why I was using ADODB.Stream)

You can remove these from your image file:
objStream.Close
objStream = Nothing

I'm actually surprised you aren't getting an error, since objStream doesn't exist in your new code.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.