Images from SQL Server

  • Thread starter Thread starter James Lang
  • Start date Start date
J

James Lang

Hi

I am trying to display in an aspx page an image from the employees table in
the sql server 2000 Northhwind database



"Select photo from employees where employeeid = 1"





This is as far a I got


============================================================================
=================

Dim myConnection As New SqlConnection(Constants.SQLConnectionString)

Dim myCommand As New SqlCommand("Select photo from Employees where
EmployeeID =1" , myConnection)



Dim bytes() As Byte



Try

myConnection.Open()

Dim dr As SqlDataReader

dr = myCommand.ExecuteReader(CommandBehavior.CloseConnection)



Do While (dr.Read())



'Response.BinaryWrite(bytes()) myDataReader.Item("Photo"))

bytes = dr("photo")





Loop



myConnection.Close()

Response.Write("Person info successfully retrieved!")

Catch SQLexc As SqlException

lblError.Text = SQLexc.Message

end try




============================================================================
=================

I am at a loss with what to do with the byte array next.

All I could think of was write it to a file then reference that as the URL
for an image control

But I am sure there is a better way

Any help regarding this would be great



Cheers

James
 
James said:
Hi

I am trying to display in an aspx page an image from the employees
table in the sql server 2000 Northwind database

"Select photo from employees where employeeid = 1"

This is as far a I got
Click to expand...
I am at a loss with what to do with the byte array next.

All I could think of was write it to a file then reference that as
the URL for an image control

But I am sure there is a better way

Any help regarding this would be great
Click to expand...

You're almost there.

I removed the bytes array from your code, and I brought the
Response.BinaryWrite back in. You also need to set the
Response.ContentType.

*****************************************************************
<%@ Page Language="VB" %>
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.OleDb" %>
<script runat="server">
Public Sub Page_Load(sender As Object, e As EventArgs)
Dim myConnection As New SqlConnection(Constants.SQLConnectionString)
Dim myCommand As New SqlCommand("Select photo from Employees where
EmployeeID =1" , myConnection)
Try
myConnection.Open()
Dim dr As SqlDataReader
dr =
myCommand.ExecuteReader(CommandBehavior.CloseConnection)
Do While (dr.Read())
Response.ContentType = "image/jpg"
Response.BinaryWrite(myDataReader.Item("Photo"))
Loop
myConnection.Close()
Catch SQLexc As SqlException
lblError.Text = SQLexc.Message
end try
End Sub
</script>
******************************************************************

Save this code in a separate file, let's say "photo.aspx".
Note that the file doesn't contain any HTML tags, only code.
This file will now act as a jpg or gif image.

Therefore, just reference this file from your main file:
<img src="photo.aspx">

Later you can extend this to something like:
<img src="photo.aspx?id=5">
 
A Big Thanks to you Jos

I had lots of problems getting this to work but as it turns out there is a
78bit header to the bitmap images in the employees table

Once I stripped that of it worked great although it was trial and error for
about 4 hours. ( Creating my own table, then adding images to it worked
fine so nothing wrong with the code logic but the Northwind images still did
not work so I created an new windows app and wrote the images to a file
"test.bmp" but they would not open. Light at the end of the tunnel time I
thought there must be something wrong with the binary stream. Opening other
bitmap files and viewing there binary contents indicated there was
additional bytes at the start of the stream. I removed them an hay presto I
had an image

Using and ASPX page as the source of the image was a something I never
thought of and now I know the technique I can use it for all sorts of stuff

Here is the code I used if anyone else wants to play with the Northwind DB

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
Dim id As Integer = Request.QueryString("ID")
Dim myConnection As New SqlConnection(Constants.SQLConnectionString)
Dim myCommand As New SqlCommand("Select photo from Employees where
EmployeeID =" & ID, myConnection)
Try
myConnection.Open()
Dim dr As SqlDataReader
dr = myCommand.ExecuteReader(CommandBehavior.CloseConnection)
Do While (dr.Read())
bytes = dr("photo")
Dim i As Integer
Dim x(bytes.GetUpperBound(0) - 78) As Byte

For i = 78 To bytes.GetUpperBound(0)
x(i - 78) = (bytes(i))
Next i
Response.BinaryWrite(x)
Loop


myConnection.Close()
Catch SQLexc As SqlException
lblError.Text = SQLexc.Message
End Try

End Sub

Once again thanks Jos

Cheers
James
 
Back
Top