ASP Displaying Images from within SQL DB

[Sp33di3]

I've been searching and searching for this and there are lots of posts - but
very few answers.

I have a SQL2000 Database that has jpg images in a table that I want to
display in a webpage. I can return all the other information in the
database except the pictures. I can't take the images out and link them. I
have 2 different results. The code included below gives me a page that says
"This is a picture:" and then box with a red X.

If I change it around I can get a very long page filled with nothing but
jumbled characters.

Can someone please point me to a resolution

Thanks



*****MAIN.ASP********
DIM objConn
Dim objRS
Dim objCommand
Set objConn = Server.CreateObject("ADODB.Connection")
Set objRS = Server.CreateObject("ADODB.Recordset")
Set objCommand = Server.CreateObject("ADODB.Command")
'connection string to SQL Database
objConn.Open "Provider=SQLOLEDB.1;Password=XXXXXXXX;Persist Security
Info=True;User ID=sa;Initial Catalog=test;Data Source=XXXXXXXX"

objCommand.ActiveConnection = objConn
objCommand.CommandText = "SELECT * FROM Sheet1 WHERE Surname LIKE 'Blythe'"
objCommand.CommandType=adCmdText
Set objRS = objCommand.Execute

if objRS.EOF = False Then
While not objRS.EOF
Response.Write objRS("First") & " " & objRS("Surname")& " " & objRS("DOB")
& " " & "<BR>"
objRS.MoveNext
Wend

Else
Response.Write "No Records Found"
end if
%>
This is a Picture:

<IMG alt="" src="ShowPicture.asp">
<%
'close DB Connection and Clean up.
objRS.Close
objConn.Close
Set objCommand = Nothing
Set objRS = Nothing
Set objConn = Nothing
%>
</Body>
</html>



**************ShowPicture.asp************
FILE="C:\Program Files\Common Files\SYSTEM\ADO\msado15.dll" -->


DIM objConn
Dim objRS
Dim objCommand
Set objConn = Server.CreateObject("ADODB.Connection")
Set objRS = Server.CreateObject("ADODB.Recordset")
Set objCommand = Server.CreateObject("ADODB.Command")
'connection string to SQL Database
objConn.Open "Provider=SQLOLEDB.1;Password=XXXXXXXX;Persist Security
Info=True;User ID=sa;Initial Catalog=test;Data Source=XXXXXXXX"

objCommand.ActiveConnection = objConn
objCommand.CommandText = "SELECT picture FROM Sheet1 WHERE Surname LIKE
'Blythe'"
objCommand.CommandType=adCmdText
Set objRS = objCommand.Execute

if objRS.EOF = False Then
Response.ContentType = "image/jpeg"
Response.BinaryWrite objRS("picture")
Else
Response.Write "No Records Found"
end if

'close DB Connection and Clean up.
objRS.Close
objConn.Close
Set objCommand = Nothing
Set objRS = Nothing
Set objConn = Nothing

 

[CJM]

To be honest I'm not sure how well SQL Server handles BLOBs, but regardless,
I would recommend a different solution:

Store your JPG's in a folder under the web server, and store the path to the
JPG in the DB. Then simple point to the pic from within your ASP:
<IMG alt="" src="<%=objRS("PicturePath"%>">

It will make your code simpler, slicker and more maintainable...

hth

Chris

 

[Sp33di3]

I said in my original post that I can't take the images out of the DB.

 

[CJM]

I said in my original post that I can't take the images out of the DB.

Apologies... I scanned over the post too quickly!

But I'm curious as to why you can't? I assume it is a constraint
someone\something else is placing on you?

I'm afraid I can't really be any more help; I've never used BLOBs to store
images

Good Luck.

Chris