Hi I have a dataset which acts as a datasource to my datagrid. It just shows all the columns from a table. One of the columns is an image column. How do i show these icons\pictures on the grid?

My code looks like this:
dim cmd as sqlcommand
dim ds as new dataset

cmd.commandtext = "select * from tbl_auths"
cmd.connection = cn
'assuming connection (cn) to be open
da.Fill(ds,"tbl_auths")
dgrauth.datasource = ds.tables("tbl_auths")

2
Contributors
6
Replies
8
Views
12 Years
Discussion Span
Last Post by deeraj

Displaying Images from SQL Server database in ASP.NET DataGrid

Introduction

ASP.NET DataGrid web control can be bound quickly with any database table like SQL server. For most of the data types all you need to do is to add a bound column and set its datafield property to the column name from the table. However, displaying image data type i.e. binary data is not that easy. It requires some kind of extra coding from developer's side to make that work. In this article we will see how to do just that. SQL Server tables

The articles make use of a SQL server database table called images. The structure of which can be as follows:

CREATE TABLE [dbo].[IMAGES] (
[imgid] [int] IDENTITY (1, 1) NOT NULL ,
[imgdata] [image] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Creating a web form with DataGrid

In order to proceed with the example create a new ASP.NET web form say webform1.aspx and add a DataGrid web control to it. Add two columns to it :

• one bound column with datafield set to imgid and
• another templated column with an image web control.

In the Page_Load event of the web form bind the datagrid with the database.

If Not Page.IsPostBack Then
BindGrid()
End If
Public Sub BindGrid()
Dim connstr As String =
"Integrated Security=SSPI;
Initial Catalog=Northwind;
Data Source=SERVER\netsdk"
Dim cnn As New SqlConnection(connstr)
Dim da As New SqlDataAdapter("select * from images", cnn)
Dim ds As New DataSet()
da.Fill(ds, "images")
DataGrid1.DataSource = ds
DataGrid1.DataBind()
End Sub

Now write the ItemDataBound event for the datagrid as follows:

Private Sub DataGrid1_ItemDataBound
(ByVal sender As Object,
ByVal e As DataGridItemEventArgs)
Handles DataGrid1.ItemDataBound

Dim img As System.Web.UI.WebControls.Image
If e.Item.ItemType = ListItemType.AlternatingItem Or
e.Item.ItemType = ListItemType.Item Then
img = CType(e.Item.Cells(1).Controls(1),
System.Web.UI.WebControls.Image)
img.ImageUrl =
"webform2.aspx?id=" & e.Item.Cells(0).Text
End If
End Sub

This event gets fired for all the rows from the DataGrid just before data binding action. All the code from the event handler should be familiar to you except the line where we are setting ImageUrl property of the image web control. Generally this property is set to a valid image file (gif/jpg etc.) but here, we are setting it to another web form. What does that means? This means that the web form (webform2.aspx) is returning the image data based on some processing logic. That is why we are also passing image id to the web form via query string. Now, let us examine the code from the webform2.aspx. Creating web form that returns image data

The webform2.aspx simply contains following code in the Page_Load event handler.

Dim connstr As String = "Integrated Security=SSPI;
Initial Catalog=Northwind;Data Source=SERVER\netsdk"
Dim cnn As New SqlConnection(connstr)
Dim cmd As New SqlCommand
("select * from images where id="
& Request.QueryString("id"), cnn)
cnn.Open()
Dim bindata() As Byte = dr.GetValue(1)
Response.BinaryWrite(bindata)

Here, we are retrieving the image based on the query string passed. Note how we have used Response.BinaryWrite to emit the binary image data. Now, run the application and you should get all the images displayed in columns of the DataGrid. Summary

In this article we saw how to retrieve image data type from SQL server database and display the image in DataGrid web control. We saw here that ImageUrl of the image web control can be set to another web form that emits binary image data via Response.BinaryWrite method.

Thanks for that piece of code. But Im afraid Im not looking for this....because my project is in VB.NET and not ASP.NET....there is no "itemdatabound" event for datagrid in VB.NET. Please let me know if there is any other way....

Sorry about that...I was not paying attention when I posted that from work.

Here is a link to some sample code that may be useful.

Hope this helps

Thanks for that too...but again this is not what I am lookin for! He is displayin a picture from the app path. I want it from an SQL table.... Any other ideas?!

OK, point taken.

Here is some further information that I hope will be of more help : LINK

snippets:

byte[] m_bynewblob = (byte[])myDateSet.Tables["myTable"].Rows[m_iRowID]["Photo"];

This is a perplexing little problem, and I plan to solve it for you...... weekend project.

I will let you know what I figure out....if anything.

Somehow its not working. I worked on it today, I also used some other concepts like stream and memorystream...but all in vain. The image in the table is not loaded from any physical path. It is an image column having some value like '0x151C2F00020000000D000E0014..........'. It is essentialy small round images having a single color. I donno how the did it because the person who did it is now not part of the project. When I save it to some location using the code provided in the link, I am not able to open those images!

The project was in MS Access and is working fine still. I am trying to convert it into VB.NET and all these problems are happening! In my datagrid its showing as "byte[]array" where as in MS Access, the proper image is loaded.

Its great to know that you are interested in helping me. Thanks!! Looking forward to more help from you.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.