Not showing image from sql database?

[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(); }