Binary Images stored in sql database don't display in asp gridview (only broken link icon)

EDN Admin · May 21, 2011

Hi,
A little background first:
Im trying to setup a datagrid that will retrieve all fields from a table calle tbl_Media. The table has a key identity field called MediaID. The other fields are MediaName, MediaType, MediaOwner and MediaFile (an image type field). Ive
tested the below code and it correctly pulls all the table record data and places it in the gridview. However, the image column only shows nothing or a broken link icon depending on the browser Im testing. Ive spent the last 3 days trying to
get the images to display with no luck. Any help is very much appreciated. Thanks in advance for your help. Below is the code for Default.aspx, Default.aspx.cs, Handler.ashx and web.config:
----------------------------Default.aspx--------------------------------------
<%@ Page Title="" Language="C#" MasterPageFile="~/Site.master" AutoEventWireup="true" CodeFile=" Default.aspx.cs" Inherits="_Default" %>
<asp:Content ID="HeaderContent" runat="server" ContentPlaceHolderID="HeadContent 
</asp:Content> 
<asp:Content ID="BodyContent" runat="server" ContentPlaceHolderID="MainContent 
<asp:Label ID="Label1" runat="server" Font-Bold="True" Font-Size="Large"
 
Text="Main Media Page </asp:Label> 
 
 
<asp:Menu ID="Menu1" runat="server" BackColor="#B5C7DE" 
DynamicHorizontalOffset="2" Font-Names="Verdana" Font-Size="0.8em"
 
ForeColor="#284E98" StaticSubMenuIndent="10px 
<DynamicHoverStyle BackColor="#284E98" ForeColor="White" /> 
<DynamicMenuItemStyle HorizontalPadding="5px" VerticalPadding="2px" /> 
<DynamicMenuStyle BackColor="#B5C7DE" /> 
<DynamicSelectedStyle BackColor="#507CD1" /> 
<Items> 
<asp:MenuItem NavigateUrl="~/members/clients/pfcihconsulting/Agreements/Default.aspx"
 
Text="Official Documents" Value="Official Documents </asp:MenuItem> 
<asp:MenuItem NavigateUrl=""
 
Text="Billing" Value="Billing" Target="_blank </asp:MenuItem> 
<asp:MenuItem NavigateUrl="~/members/clients/pfcihconsulting/Contact/Default.aspx"
 
Text="Edit Contact Info" Value="Edit Contact Info </asp:MenuItem> 
<asp:MenuItem NavigateUrl="~/members/clients/pfcihconsulting/Media/Default.aspx"
 
Text="Media" Value="Media 
</asp:MenuItem> 
<asp:MenuItem NavigateUrl=""
 
Text="Project Collaboration" Value="Project Collaboration" Target="_blank </asp:MenuItem> 
<asp:MenuItem NavigateUrl="~/members/clients/pfcihconsulting/Remote/Default.aspx"
 
Text="Remote Access" Value="Remote Access </asp:MenuItem> 
<asp:MenuItem NavigateUrl="~/Account/ChangePassword.aspx"
 
Text="Change Password" Value="Change Password </asp:MenuItem> 
</Items> 
<StaticHoverStyle BackColor="#284E98" ForeColor="White" /> 
<StaticMenuItemStyle HorizontalPadding="5px" VerticalPadding="2px" /> 
<StaticSelectedStyle BackColor="#507CD1" /> 
</asp:Menu> 
 
This page allows your to upload and view files related to your project(s). 
 
If you have any files you'd like to add to your project, such as images,
 
audio files, video files, web pages, pdf files, or zip files, click on the browse
 
button below and navigate to the saved location of the file (i.e. Desktop, My
 
Documents foler, etc.).  This is very similar to the process of adding an
 
attachment to an email.  Once you click on the "Upload" button, please be
 
patient.  Depending on the size of the file you've selected to upload and
 
your internet connection, the upload may take some time to load.  Once the
 
image is loaded, this page will reload and you will see a "File Uploaded
 
Successfully!" message below the "Browse" button. 
<asp:TextBox ID="MediaID" runat="server" Visible="False </asp:TextBox> 
 
Owner:  
<asp:LoginName ID="LoginName1" runat="server" /> 
 
<asp:FileUpload ID="FileUpload1" runat="server" /> 
<asp:Button ID="btnUpload" runat="server" Text="Upload" 
OnClick="btnUpload_Click" /> 
 
<asp:Label ID="lblMessage" runat="server" Text="" 
Font-Names = "Arial </asp:Label> 
 
<asp:Label ID="Label2" runat="server" Font-Bold="True" Font-Italic="True"
 
Text="All Uploaded Project Files </asp:Label> 
 
 
<asp:GridView ID="GridView1" runat="server" AllowPaging="True"
 
AutoGenerateColumns="False" DataKeyNames="MediaID"
 
DataSourceID="SqlDataSource1" HorizontalAlign="Center" Width="500px 
<Columns> 
<asp:BoundField DataField="MediaID" HeaderText="MediaID" InsertVisible="False"
 
ReadOnly="True" SortExpression="MediaID" /> 
<asp:BoundField DataField="MediaName" HeaderText="MediaName"
 
SortExpression="MediaName" /> 
<asp:BoundField DataField="MediaType" HeaderText="MediaType"
 
SortExpression="MediaType" /> 
<asp:BoundField DataField="MediaOwner" HeaderText="MediaOwner"
 
SortExpression="MediaOwner" /> 
<asp:TemplateField HeaderText="Image 
<ItemTemplate> 
<asp:Image MediaID="Image1" runat="server" Width="200px" ImageUrl=<%# "Handler.ashx?MediaID=" + Eval("MediaID")%>
/> 
</ItemTemplate> 
</asp:TemplateField> 
</Columns> 
</asp:GridView> 
 
<asp:SqlDataSource ID="SqlDataSource1" runat="server" 
ConnectionString="<%$ ConnectionStrings:MYCONNECTIONSTRINGHERE %>"
 
SelectCommand="SELECT * FROM [tbl_Media]"
 
ConflictDetection="CompareAllValues" 
DeleteCommand="DELETE FROM [tbl_Media] WHERE [MediaID] = @original_MediaID AND (([MediaName] = @original_MediaName) OR ([MediaName] IS NULL AND @original_MediaName IS NULL)) AND (([MediaType] = @original_MediaType)
OR ([MediaType] IS NULL AND @original_MediaType IS NULL)) AND (([MediaFile] = @original_MediaFile) OR ([MediaFile] IS NULL AND @original_MediaFile IS NULL)) AND (([MediaOwner] = @original_MediaOwner) OR ([MediaOwner] IS NULL AND @original_MediaOwner IS NULL))"
 
InsertCommand="INSERT INTO [tbl_Media] ([MediaName], [MediaType], [MediaFile], [MediaOwner]) VALUES (@MediaName, @MediaType, @MediaFile, @MediaOwner)"
 
OldValuesParameterFormatString="original_{0}"
 
UpdateCommand="UPDATE [tbl_Media] SET [MediaName] = @MediaName, [MediaType] = @MediaType, [MediaFile] = @MediaFile, [MediaOwner] = @MediaOwner WHERE [MediaID] = @original_MediaID AND (([MediaName] = @original_MediaName)
OR ([MediaName] IS NULL AND @original_MediaName IS NULL)) AND (([MediaType] = @original_MediaType) OR ([MediaType] IS NULL AND @original_MediaType IS NULL)) AND (([MediaFile] = @original_MediaFile) OR ([MediaFile] IS NULL AND @original_MediaFile IS NULL))
AND (([MediaOwner] = @original_MediaOwner) OR ([MediaOwner] IS NULL AND @original_MediaOwner IS NULL)) 
<DeleteParameters> 
<asp

arameter Name="original_MediaID" Type="Int64" /> 
<asp

arameter Name="original_MediaName" Type="String" /> 
<asp

arameter Name="original_MediaType" Type="String" /> 
<asp

arameter Name="original_MediaFile" Type="Object" /> 
<asp

arameter Name="original_MediaOwner" Type="String" /> 
</DeleteParameters> 
<InsertParameters> 
<asp

arameter Name="MediaName" Type="String" /> 
<asp

arameter Name="MediaType" Type="String" /> 
<asp

arameter Name="MediaFile" Type="Object" /> 
<asp

arameter Name="MediaOwner" Type="String" /> 
</InsertParameters> 
<UpdateParameters> 
<asp

arameter Name="MediaName" Type="String" /> 
<asp

arameter Name="MediaType" Type="String" /> 
<asp

arameter Name="MediaFile" Type="Object" /> 
<asp

arameter Name="MediaOwner" Type="String" /> 
<asp

arameter Name="original_MediaID" Type="Int64" /> 
<asp

arameter Name="original_MediaName" Type="String" /> 
<asp

arameter Name="original_MediaType" Type="String" /> 
<asp

arameter Name="original_MediaFile" Type="Object" /> 
<asp

arameter Name="original_MediaOwner" Type="String" /> 
</UpdateParameters> 
</asp:SqlDataSource> 
 
</asp:Content>
------------------------------------------------Default.aspx.cs---------------------------------------------
using System; 
using System.Data; 
using System.Data.SqlClient; 
using System.Configuration; 
using System.Web; 
using System.Web.Security; 
using System.Web.UI; 
using System.Web.UI.WebControls; 
using System.Web.UI.WebControls.WebParts; 
using System.Web.UI.HtmlControls; 
using System.IO; 
using System.Drawing.Imaging; 
using System.Drawing;
public partial class _Default : System.Web.UI.Page 
{ 
protected void Page_Load(object sender, EventArgs e) 
{
}
private Boolean InsertUpdateData(SqlCommand cmd) 
{ 
String strConnString = System.Configuration.ConfigurationManager.ConnectionStrings["MYCONNECTIONSTRINGHERE"].ConnectionString; 
SqlConnection con = new SqlConnection(strConnString); 
cmd.CommandType = CommandType.Text; 
cmd.Connection = con; 
try 
{ 
con.Open(); 
cmd.ExecuteNonQuery(); 
return true; 
} 
catch (Exception ex) 
{ 
Response.Write(ex.Message); 
return false; 
} 
finally 
{ 
con.Close(); 
con.Dispose(); 
} 
}

protected void btnUpload_Click(object sender, EventArgs e) 
{ 
// Read the file and convert it to Byte Array 
string filePath = FileUpload1.PostedFile.FileName; 
string filename = Path.GetFileName(filePath); 
string ext = Path.GetExtension(filename); 
string contenttype = String.Empty;
//Set the contenttype based on File Extension 
switch (ext) 
{ 
case ".doc": 
contenttype = "application/vnd.ms-word"; 
break; 
case ".docx": 
contenttype = "application/vnd.ms-word"; 
break; 
case ".xls": 
contenttype = "application/vnd.ms-excel"; 
break; 
case ".xlsx": 
contenttype = "application/vnd.ms-excel"; 
break; 
case ".jpg": 
contenttype = "image/jpg"; 
break; 
case ".png": 
contenttype = "image/png"; 
break; 
case ".gif": 
contenttype = "image/gif"; 
break; 
case ".pdf": 
contenttype = "application/pdf"; 
break; 
} 
if (contenttype != String.Empty) 
{ 
 
Stream fs = FileUpload1.PostedFile.InputStream; 
BinaryReader br = new BinaryReader(fs); 
Byte[] bytes = br.ReadBytes((Int32)fs.Length); 
string strQuery = "INSERT INTO tbl_Media(MediaName, MediaType, MediaFile, MediaOwner) VALUES (@MediaName, @MediaType, @MediaFile, @MediaOwner)"; 
SqlCommand cmd = new SqlCommand(strQuery); 
String owner = @Profile.UserName; 
 
cmd.Parameters.Add("@MediaName", SqlDbType.VarChar).Value = filename; 
cmd.Parameters.Add("@MediaType", SqlDbType.VarChar).Value = contenttype; 
cmd.Parameters.Add("@MediaFile", SqlDbType.Binary).Value = bytes; 
cmd.Parameters.Add("@MediaOwner", SqlDbType.VarChar).Value = owner; 
 
InsertUpdateData(cmd); 
lblMessage.ForeColor = System.Drawing.Color.Green; 
lblMessage.Text = "File Uploaded Successfully"; 
} 
else 
{ 
lblMessage.ForeColor = System.Drawing.Color.Red; 
lblMessage.Text = "File format not accepted. Upload Image/Word/PDF/Excel formats"; 
} 
} 
}
------------------------------------------------Handler.ashx-------------------------------------------------
<%@ WebHandler Language="C#" Class="Handler" %>
using System; 
using System.Web; 
using System.Configuration; 
using System.Data; 
using System.Data.SqlClient; 
using System.Drawing; 
using System.Drawing.Imaging; 
using System.IO;
public class Handler : IHttpHandler 
{
public void ProcessRequest(HttpContext context) 
{ 
SqlConnection con = new SqlConnection(); 
con.ConnectionString = ConfigurationManager.ConnectionStrings 
["MYCONNECTIONSTRINGHERE"].ConnectionString;
// Create SQL Command 
SqlCommand cmd = new SqlCommand(); 
cmd.CommandText = "Select MediaID, MediaName, MediaType, MediaOwner, MediaImage from tbl_Media"; 
cmd.CommandType = System.Data.CommandType.Text; 
cmd.Connection = con;
SqlParameter ImageID = new SqlParameter 
("@MediaID", System.Data.SqlDbType.Int); 
ImageID.Value = context.Request.QueryString["MediaID"]; 
cmd.Parameters.Add(ImageID); 
con.Open(); 
SqlDataReader dReader = cmd.ExecuteReader(); 
dReader.Read(); 
context.Response.BinaryWrite((byte[])dReader["MediaID"]); 
dReader.Close(); 
con.Close(); 
} 
}
-----------------------------------------------------------web.config------------------------------------------------

<?xml version="1.0"?> 
 
<configuration> 
<connectionStrings> 
<add name="MYCONNECTIONSTRINGHERE" connectionString="Data Source=DBHOSTADDRESS;Initial Catalog=INITIALDB;User ID=TESTUSER;Password=TESTPASS" 
providerName="System.Data.SqlClient" /> 
</connectionStrings> 
<system.web> 
<customErrors defaultRedirect="~/Error.aspx"/> 
<authentication mode="Forms 
<forms loginUrl="~/Account/Login.aspx" timeout="2880"/> 
</authentication> 
<membership> 
<providers> 
<clear/> 
<add name="AspNetSqlMembershipProvider" type="System.Web.Security.SqlMembershipProvider" connectionStringName="ApplicationServices" enablePasswordRetrieval="false" enablePasswordReset="true" requiresQuestionAndAnswer="false" requiresUniqueEmail="false"
maxInvalidPasswordAttempts="5" minRequiredPasswordLength="6" minRequiredNonalphanumericCharacters="0" passwordAttemptWindow="10" applicationName="/"/> 
</providers> 
</membership> 
<profile> 
<providers> 
<clear/> 
<add name="AspNetSqlProfileProvider" type="System.Web.Profile.SqlProfileProvider" connectionStringName="ApplicationServices" applicationName="/"/> 
</providers> 
<properties> 
<add name="CompanyName"/> 
<add name="FirstName" /> 
<add name="LastName"/> 
<add name="Address1"/> 
<add name="Address2"/> 
<add name="City"/> 
<add name="State"/> 
<add name="Zip"/> 
<add name="Phone"/> 
<add name="Email"/> 
</properties> 
</profile> 
<roleManager enabled="true 
<providers> 
<clear/> 
<add connectionStringName="ApplicationServices" applicationName="/" name="AspNetSqlRoleProvider" type="System.Web.Security.SqlRoleProvider"/> 
<add applicationName="/" name="AspNetWindowsTokenRoleProvider" type="System.Web.Security.WindowsTokenRoleProvider"/> 
</providers> 
</roleManager> 
<compilation debug="true"/> 
</system.web> 
<system.webServer> 
<modules runAllManagedModulesForAllRequests="true"/> 
</system.webServer> 
<system.net> 
<mailSettings> 
<smtp from= mailto:no-reply@test.com no-reply@test.com > 
<network host="MYSMTPSERVER" password="" userName=""/> 
</smtp> 
</mailSettings> 
</system.net> 
</configuration>

View the full article

Binary Images stored in sql database don't display in asp gridview (only broken link icon)

EDN Admin

Well-known member

Similar threads