Problem with SqlDataReader !

Question

This is in response to the question I asked earlier, since I can't post it there before 8 hours, I've created a new question, with the problem I'm facing,

Here's the code:

if (context.Request.QueryString["id"] != null)
{
 int id = int.Parse(context.Request.QueryString["id"].ToString());
 string connectionString = "Data Source=tarun-PC\\SQLEXPRESS;Initial Catalog=mydb;Integrated Security=True";
 string commandString = "Select fsImage from WHATSNSTORE_FEATURED_STORES where imageId=" + id;
 SqlConnection oConnection = new SqlConnection(connectionString);
 SqlCommand oCommand = new SqlCommand(commandString, oConnection);
 if (oConnection.State != ConnectionState.Open)
 {
 oConnection.Open();
 }
 SqlDataReader myDataReader = oCommand.ExecuteReader();
 //myDataReader.Read();
 try
 {
 if (myDataReader.Read())
 {
 context.Response.ContentType = "image/jpg";
 context.Response.BinaryWrite((byte[])myDataReader["fsImage"]);
 }
 }
 catch (Exception ex)
 {
 context.Response.Write(ex.Message);
 }

My Table

create table WHATSNSTORE_FEATURED_STORES
(
 imageId int primary key identity,
 fsImage image
)

The only problem now is, while debugging it skips the if(myDataReader.Read()) part, which indicates that there is no data present !

How do I solve the issue?

• Marco: Do you have some error back? In context.Response.Write(ex.Message); do you have something? Put a beakpoint on that line and check if your app steps there...
• Avitus: if there is no data present that means you need to get data.
• cSharpNovice: @Marco without the if(), when I run the code, The error I'm getting is "Invalid attempt to Read, when there is no data present" .. Again, I ran the query in SSMS, and I'm getting the desired result !

Answer 1

If your reader is empty it means your query did not return a result.

Looking at your code i'd say the image is missing from the database. You should probably assume that that can and will happen and either stream a blank image to the browser or return a HTTP 404 error.

[Update]

Did you check the generated SQL string? When you add the id to the SQL string it is cast back to a string. If the id is over 1000 you may get a localized representation of the integer (ie. 1.000 instead of 1000). That would explain why your query in SSMS does return a result while the page does not.

Original answer: I noticed you are using int.Parse; you may want to use int.TryParse instead. That will just return false if the value is not a valid int. Additionally, you're building your SQL by string concatenation. This is considered a bad practice. You should use parameters. Another best practice is to wrap the connection and the reader in a using statement. That will ensure the connection and reader are closed when they are no longer needed, especially in case of an exception. Finally, you may want to consider moving the connection string to your web.config file.

• cSharpNovice: I build this on a temporary just to check if I'm getting the functionality right, I generally try to keep good practices in my final version. Thanks :)
• Marnix van Valen: @cSharpNovice Your user name suggests otherwise :) Anyway, I've updated my answer. Please let me know if that fixes your issue.
• cSharpNovice: haha, this is just a temporary-id, having some problems logging in with my original id ! :)
• cSharpNovice: @Brian So I hard-coded the id value in the where clause to what I wanted, and to my amusement, the reader is still empty :@
• cSharpNovice: @Brian, the data from my db was mysteriously deleted, I restarted my SSMS, and there was just 1 record in my db ! err.. sorry for the trouble caused !

Answer 2

You should use oCommand.ExecuteScalar instead of reader since you only want one value. After that you can check if the value returned is null or not.

• cSharpNovice: I used the ExecuteScalar() but the object returned has null value !
• Brian Scott: @cSharpNovice: That's your problem then. Your query isn't returning anything and hence there's no data to .Read(). Try running your query in sql server management studio and make sure you are connecting to the correct database that contains the results.
• cSharpNovice: @Brian Checked all of that, I ran the query in SSMS and I'm getting the desired result !

Answer 3

Are you sure you've got the debugger setup correctly? Are you attaching to process and therefore the deployed .dbg files do not match the .dll files in the debugged bin directory? Have you tried assigning the myDataReader.Read() to a bool to see whether there is any database information present?

• cSharpNovice: Yes, I assigned it to a bool variable, the value in the bool variable is false, but I don't know why it's false, since there is data in my db !
• Brian Scott: @cSharpNovice: There's definitely something wrong with either your connection string or your query. This isn't strictly a .net programatic problem if there's simply no information coming back from your query.
• Brian Scott: @cSharpNovice: Is there something silly going on like the ID is stored as an NVARCHAR rather than an INT
• cSharpNovice: yes, but it's messing up my head, 'coz the query is running just fine in ssms, and even the connectionString is configured to the right db. :\