Not showing image from sql database?

AdamskiR

Hi, Thanks for your reply :) I have looked at that article before but using that I end up with errors? The code I am using to retrieve the image is: private void Page_Load(object sender, System.EventArgs e) { //get the image id from the url string NewsID = Request.QueryString["ID"]; //build our query statement string sqlText = "SELECT ImageFile FROM News WHERE NewsID = " + NewsID; SqlCommand command = new SqlCommand( sqlText, sqlConnection1); //open the database and get a datareader sqlConnection1.Open(); SqlDataReader dr = command.ExecuteReader(); if ( dr.Read()) { Response.ContentType = "image/GIF"; Response.BinaryWrite((byte[])dr["ImageFile"]); } sqlConnection1.Close(); } Any ideas? Thanks

minhpc_bk

Hi there, I have looked at that article before but using that I end up with errors? Because the article presents a successful way of displaying an image which is saved in DB, then I think it might give you a hint on figuring out your problem. Your sample code seems to have no problem in there, that can be used to load the image from DB BUT with an assumption that the image content is saved properly by this I mean the content should not be corrupted. So what you can try next is to check whether the image data is valid, the sample code below can be used to do a checking:

private void SaveImage(byte[] buffer)
{
MemoryStream stream = new MemoryStream(buffer, 0, buffer.Length);
Bitmap bitmap = new Bitmap(stream);
bitmap.Save(@"C:\image.gif", ImageFormat.Gif);
bitmap.Dispose();
}

...
SaveImage((byte[])dr["Content"]);
...

You can use the sample code in a simple console application or in your web form (remember to grant permissions to the ASPNET account to write a file). After running that code, can you see the image on disk?

AdamskiR

Hi Sorry about this but I am new to .net. Where does this code need to be added. On the showpicture page or the addnews page? I have tried to add it but get errors when trying to build it with this part of code:

...SaveImage((byte[])dr["Content"]);...

Thanks

minhpc_bk

You can put the sample code in the page which you are currently using to load the image:

using System.IO;
using System.Drawing;
using System.Drawing.Imaging;
...
private void Page_Load(object sender, System.EventArgs e)
{
//get the image id from the url
string NewsID = Request.QueryString["ID"];

//build our query statement
string sqlText = "SELECT ImageFile FROM News WHERE NewsID = " + NewsID;
SqlCommand command = new SqlCommand( sqlText, sqlConnection1);
             
//open the database and get a datareader
sqlConnection1.Open();
SqlDataReader dr = command.ExecuteReader();
if ( dr.Read()) 
{
	//Response.ContentType = "image/GIF";
	//Response.BinaryWrite((byte\[\])dr\["ImageFile"\]);
                 	
	SaveImage((byte\[\])dr\["Content"\]);
}
sqlConnection1.Close();

}

private void SaveImage(byte[] buffer)
{
MemoryStream stream = new MemoryStream(buffer, 0, buffer.Length);
Bitmap bitmap = new Bitmap(stream);
bitmap.Save(@"C:\image.gif", ImageFormat.Gif);
bitmap.Dispose();
}

Also, you need to add a reference to the System.Drawing.dll assembly, and grant access permission (write) on C to the ASPNET account which is basically used to run the ASP.NET application. + Or you can simply add a new Console Application project in Visual Studio then add the sample code like this:

....
class Class1
{
[STAThread]
static void Main(string[] args)
{
//The code here is the same as one in the Page_Load method.
...
}

private static void SaveImage(byte\[\] buffer)
{ 
	MemoryStream stream = new MemoryStream(buffer, 0, buffer.Length);   
	Bitmap bitmap = new Bitmap(stream);   
	bitmap.Save(@"C:\\image.gif", ImageFormat.Gif);   
	bitmap.Dispose();
}

}
...

+ Perhaps, there is an easier way, in the Page_load you simply declare a binary buffer:

byte[] buffer = (byte[])dr["Content"];

you can see the buffer length when running your ASP.NET application in debug mode, or write out to the screen, then make comparison this value with the image size before it gets uploaded to see whether they are the same. + If the image data is not valid, you will receive an ArgumentException error when initializing a new Bitmap object.

AdamskiR

Hi Thanks again for your help so far with this. Much appreciated. I have added the code to my page and also added a reference to the System.Drawing.dll Although I am getting this error: Server Error in '/TrustNews' Application. -------------------------------------------------------------------------------- Content Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.IndexOutOfRangeException: Content Source Error: Line 41: //Response.ContentType = "image/GIF"; Line 42: //Response.BinaryWrite((byte[])dr["ImageFile"]); Line 43: SaveImage((byte[])dr["Content"]); Line 44: } Line 45: sqlConnection1.Close(); Line: 43 if i try to access ShowPicture.aspx?id=69 Could it be how they are being added to the database? I dont know if this will help more but here is all the pages code. AddNewsItems.aspx.cs

using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;

namespace TrustNews.Admin
{
/// /// Summary description for AddNewsItems.
///
public class AddNewsItems : System.Web.UI.Page
{
protected System.Web.UI.WebControls.TextBox txtNewsTitle;
protected System.Web.UI.WebControls.DropDownList DropDownList1;
protected System.Web.UI.WebControls.Button Button1;
protected System.Data.SqlClient.SqlCommand sqlCommand1;
protected System.Data.SqlClient.SqlConnection sqlConnection1;
protected System.Data.SqlClient.SqlDataAdapter sqlDataAdapter1;
protected System.Data.DataSet dataSet1;
protected System.Data.SqlClient.SqlCommand sqlSelectCommand1;
protected System.Data.SqlClient.SqlCommand sqlInsertCommand1;
protected System.Data.SqlClient.SqlCommand sqlUpdateCommand1;
protected System.Data.SqlClient.SqlCommand sqlDeleteCommand1;
protected System.Web.UI.WebControls.HyperLink HyperLink1;
protected System.Web.UI.WebControls.HyperLink HyperLink2;
protected System.Web.UI.HtmlControls.HtmlInputFile ImageUpload;
protected System.Web.UI.WebControls.TextBox ImageName;
protected System.Web.UI.WebControls.TextBox txtNewsDescription;

	private void Page\_Load(object sender, System.EventArgs e)
	{
		if (!Page.

minhpc_bk

Hi AdamskiR, sorry for my lack of knowledge Don't worry about that, you'll become a professional soon :)! + Yes, you're right , to check whether the image data is valid or not you just need to launch the ShowPicture.aspx page with the id parameter in the browser, in fact you don't need to run the entire application unless you configure the authentication settings in Form mode. So the url in the address box looks some thing like:

http://localhost/TrustNews/ShowPicture.aspx?id=69

Here, the news item with the id = 69 is supposed to be in DB with an image. Alternatively, you can simply run a simple console application with a hard coded id value to see if the image data is saved properly in DB. + Now I want to make sure that the sample code in the AddNewsItems.aspx and ShowNewsDetail.aspx does not cause the error. You can try to comment out the code below in the ShowNewsDetail.aspx page:

...
//Picture.Text = "";
...

then run the application again, + Basically, the IndexOutOfRangeException error happens due to the fact that you are trying to access in code an element of an array with an index that is outside the bounds of the array. + Another way that can help you much is to run the application in debug mode, you can check out this document: Debugging ASP.NET Web Applications During Development[^]

AdamskiR

Hi minhpc_bk If i enter the URL of http://localhost/TrustNews/ShowPicture.aspx?id=69 or any of the news items with images in them (67-71) then I still end up with the view of a broken image. All except id=70 which shows as a blank page and has the 'ShowPicture' title in the title bar of the browser which seems bizarre! I commented out the line that you mentioned:

//Picture.Text = "";

and what this did was stop it from showing as a broken image but it just showed as a Label text. I have tried debugging but this isnt helping me. Thanks Adam

minhpc_bk

Now you can try with the sample code provided in my posts to save the image to disk to check if the is a valid image. You also just need to launch the ShowPicture.aspx page in the browser and debug with this page. Remember to grant write permission on the folder you want to save the image to the ASPNET account. Or you can see the buffer length at run time in debug mode then compare with the original size. You can also try with a simple console application (with it you don;t need to worry about the permission). Here, I want to make sure that the image pulled out DB is valid.

AdamskiR

Hi I must be missing something somewhere, I have added ASPNET permissions to the folder but I can not seem to get the ShowPicture.aspx page to run correctly when trying to save an image. (I am sure I got the page to build once somehow without errors but it didnt save an image but I cant remember to be sure now if it was right!:^)) From debugging, it seems to be finding an error here on line 38:

SqlDataReader dr = command.ExecuteReader();

But I cant seem to get any other command to build. Thanks

minhpc_bk

Hi Adam, + What does the error say on line 38? + If your current project is big, you should make a seperate simple application to test the ShowPicture.aspx page only, or run that code in a window-based application or console app. Run the application in debug mode and try to watch what is the error. + One more thing is that in an ASP.NET application, I normally save uploaded images to disk rather than inserting to DB unless I have a good reason such as replication. In my opinion, this way is simpler, but it's just an idea. Hopefully, you can figure out soon, I'm willing to help :).

AdamskiR

Hi When running the page in debug mode and a breakpoint on that line it returns the following error:

Line 1: Incorrect syntax near '='.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near '='.

Source Error:

Line 36: //open the database and get a datareader
Line 37: sqlConnection1.Open();
Line 38: SqlDataReader dr = command.ExecuteReader();
Line 39: if ( dr.Read()) //yup we found our image
Line 40: {

The project isnt very big at all, this news feature is all there is! One of the reasons for saving it into a database is so I can learn how to, as this is more of a project to let me get to grips with pulling different information out of a database :) Youve been a great help so far! Adam

minhpc_bk

Hi Adam, The error happens due to the fact that the sql statement has incorrect syntax:

//build our query statement
string sqlText = "SELECT ImageFile FROM News WHERE NewsID = " + NewsID;

The NewsID column is of the int type, so can you make sure that the NewsID variable has a valid integer value? In debug mode, you can copy the sqlText and try to run it in the Query Analyzer of SQL Server where you can easily correct the syntax of the sql statement.

AdamskiR

Hi again! I think that I have now resolved the sql statement which now shows results when put in the query analizer.... But how do I fix the:

Exception Details: System.IndexOutOfRangeException: Content

Line 42: Response.BinaryWrite((byte[])dr["ImageFile"]);

The NewsID column is a valid integer and primary key for the 'News' table: **Column Name** - NewsID **Data Type** - int **Value** - 4 Thanks Adam

minhpc_bk

Hi Adam, You can try with these step in debug mode: + Try to cast the image data to a binary buffer like this:

Response.ContentType = "image/GIF";

byte[] buffer = (byte[])dr["ImageFile"];

Response.BinaryWrite(buffer);

+ When the execution reachs the casting code, try to watch the dr object. In the Quick Watch window, click the plus sign to see the MetaData property, there are basically 2 items in there. + The first one is of the System.Data.SqlClient._SqlMetaData, check the length property then compare with the original size of the image before it gets uploaded. + The second one 'metaType' should be of the System.Data.SqlClient.MetaImage Can you check that?

AdamskiR

Hi I have added this piece of code. I uploaded a .gif file which should be 8k in size and 230px X 92px When I ran the application in debug mode and from looking at what you requested the length is: Length - 2147483647 I dont quite see what the Quick Watch window does as it didnt seem to allow me to do anything:confused: Thanks

minhpc_bk

+ Oops, wrong place, I'm sorry about that, you should look up the _comBuf property of the dr object in the Quick Watch window. Click the plus sign to expand the _comBuf node, there's basically one item in there. In the Value column, you will see something like {Length=8192} where the 8192 is the size of the image in bytes. + I think the IndexOutOfRangeException: Content error happens due to the dr["Content"] code. If this is the case, you can take one of the two ways below: ++ You need to ensure that the column name in the query is the same as one being used to read data from the datareader object, by this I mean if the column name in the sql command is ImageFile:

  select ImageFile from News where NewsID=1'

then the column to read data from the DataReader object must be ImageFile : dr["ImageFile"]

       byte\[\] buffer = (byte\[\])dr\["ImageFile"\];

++ Alternatively, you can use the index value to pull data from the datareader object like dr[0] as there's only one column in the dr object:

       byte\[\] buffer = (byte\[\])dr\[0\];

AdamskiR

It looks like there could be something wrong with the way the image is being uploaded as in the _comBuf property it is showing as < undefined value >

minhpc_bk

If the _comBuf property is showing < undefined value >, that also means that the image data may not be saved in DB properly. To quickly find the root cause, I think you should take step by step: + You should look at the uploaded data before saving to DB to make sure that the image data does not get lost while uploading. + You then try to test with the code to save the image to DB. After running this code, you can use the Query Analyzer to query the record which is just added, pay your attention to the column containing the image data to see if there is data in there. If no, you need to relook at the uploading code, there are a number of examples on CP or simply google for examples available out there. If yes, you can move on with the code to read the image data from DB. + Finally, you can run the code to write binary data to the output stream. Good luck to you :)

minhpc_bk

Hi Adam, One more thing that I failed to remind you that the DataReader object works "on-line" and there is no data in its buffer until you really invoke the dr object with the column name or column index to pull data from that column in DB. Take a look at the sample code below:

1: SqlDataReader dr = command.ExecuteReader();
2: if ( dr.Read())
3: {
4: Response.ContentType = "image/GIF";
5: byte[] buffer = (byte[])dr[0];
6: Response.BinaryWrite(buffer);
7: }
8: sqlConnection1.Close();

Before the execution reachs the line 6, you still see the < undefined value > in the _comBuf property. Once you invoke the code to pull data from the first column dr[0] or dr["ImageFile"] at line 5, then the image data by that time is actually pulled out of DB and stored in the buffer of the dr datareader object. So in general, when the line 6 gets reached, you can look up the _comBuf property to see the image size. [Edit] In addition to the Quick Watch window, when you run your application in debug mode at runtime you can make use of the Locals window to watch the value of the dr object. For more information, you can see Using the Locals Window[^]

sfawcett

when you get an image out it may be too big to come in one go so this is how i get an image out using a web service [WebMethod] public byte[] GetPic(int iId) { MemoryStream ms = new MemoryStream(); try { string sSql="select Picture from tblPics where id="+iId.ToString(); SqlConnection objConn = new SqlConnection(msConnection); SqlCommand objCommand = new SqlCommand(sSql,objConn); objConn.Open(); SqlDataReader objData = objCommand.ExecuteReader(CommandBehavior.SequentialAccess); int bufferSize = 1024; byte[] buffer = new byte[bufferSize]; if(objData.Read()) { long startIndex = 0; long retval = objData.GetBytes(0, startIndex, buffer, 0,bufferSize); while (retval == bufferSize) { ms.Write(buffer,0,buffer.Length); startIndex += bufferSize; retval = objData.GetBytes(0, startIndex, buffer, 0, bufferSize); } ms.Write(buffer,0,buffer.Length); } objData.Close(); objConn.Close(); } catch(Exception ex) { Debug.WriteLine(ex.Message); } return ms.GetBuffer(); }