By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,046 Members | 2,105 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,046 IT Pros & Developers. It's quick & easy.

Images from SQL Server

P: n/a


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




Nov 17 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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
Nov 17 '05 #2

P: n/a
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" <jo***************@fastmail.fm> wrote in message
news:uB**************@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

Nov 17 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.