Images from SQL Server

[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.CloseConne ction)



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

[Jos]

James Lang wrote:

> 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
>

<cut>

>
> 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

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.CloseConne ction)
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">

--

Jos Branders

[James Lang]

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.CloseConne ction)
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

"Jos" <josnospambranders@fastmail.fm> wrote in message
news:uBf3GDfRDHA.3192@TK2MSFTNGP10.phx.gbl...

> James Lang wrote:

> > 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
> >

> <cut>

> >
> > 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

>
> 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.CloseConne ction)
> 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">
>
> --
>
> Jos Branders
>
>