• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 487
  • Last Modified:

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
0
irishski
Asked:
irishski
  • 4
  • 3
1 Solution
 
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
 
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 new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
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

Featured Post

Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now