No data is read in SqlReader Invalid attempt to read when no data is present.

Question

I know this problem has been posted millions of times before, but this might be different.

I am using a sql table with columns id,year,month,pdffile

in my webpage, I would like the user to view the pdf document that they would like to see from what time frame they choose.

so far with this:

string year = ddlYear.SelectedValue.ToString();    
string month = ddlMonth.SelectedValue.ToString();
pdfFrame.Attributes["src"] = "../pdfDocs.ashx?Year=" + year + "&Month=" + month;

it loads the pdf, if it is there. that is my problem. how would I catch the error in my question title? I want to catch that error and display a message in a label to the user saying that no file was found. I am using a handler to read the varbinary that is my pdf file.

Here is the handler code:

SqlConnection conn = new SqlConnection(@"Server=DEV6\MSSQLHOSTING;Database=Intranet;Trusted_Connection=True;");
conn.Open();
SqlCommand cmd = new SqlCommand("select PDFDocument from LeaveChart where (Year like @Year and Month like @Month)", conn);
cmd.Parameters.AddWithValue("Year", context.Request.QueryString["Year"]);
cmd.Parameters.AddWithValue("Month", context.Request.QueryString["Month"]);
SqlDataReader reader = cmd.ExecuteReader();

reader.Read();

byte[] content = (byte[])reader.GetValue(0);
MemoryStream ms = new MemoryStream(content);
ms.Position = 0;
context.Response.ContentType = "Application/pdf";
ms.WriteTo(context.Response.OutputStream);
context.Response.End();
reader.Close();
conn.Close();

like I said, that works to display present files. can anyone offer some suggestions? is it possible to send a message from the handler back to the web page saying no file and how to display it without shocking the customer?

thanks in advance

And I guess return type of your handler is MemoryStream? – PM. Mar 28 '13 at 14:27 — PM., Mar 28 '13 at 14:27
if(!reader.Read()) ShowNoFile(); ? – gsharp Mar 28 '13 at 14:27 — gsharp, Mar 28 '13 at 14:27
yes. the return type is memory stream @PM – Nicholas Aysen Mar 28 '13 at 14:32 — Nicholas Aysen, Mar 28 '13 at 14:32

score 0 · Accepted Answer · answered Mar 28 '13 at 14:48

If you can change the signature of your Handler, then:

public bool <YourHandlerName>(out MemoryStream ms)
{
SqlConnection conn = new SqlConnection(@"Server=DEV6\MSSQLHOSTING;Database=Intranet;Trusted_Connection=True;");
conn.Open();
SqlCommand cmd = new SqlCommand("select PDFDocument from LeaveChart where (Year like  @Year and Month like @Month)", conn);
cmd.Parameters.AddWithValue("Year", context.Request.QueryString["Year"]);
cmd.Parameters.AddWithValue("Month", context.Request.QueryString["Month"]);
SqlDataReader reader = cmd.ExecuteReader();
reader.Read();
ms = new MemoryStream(content);
if(reader.HasRows)
 {
  byte[] content = (byte[])reader.GetValue(0);
  ms.Position = 0;
  context.Response.ContentType = "Application/pdf";
  ms.WriteTo(context.Response.OutputStream);
  context.Response.End();
  reader.Close();
  conn.Close();
  return true;
 }
 return false;
}

I guess I got your question correctly.

if I took this verbatim, I would get errors regarding the memory stream. telling me that it would give a different meaning to ms which is used in a parent or current scope. — Nicholas Aysen, Mar 28 '13 at 14:56
Another way could be changing the signature and returning the memory stream.like: **public bool (out MemoryStream ms)** to **public MemoryStream (out bool fileFound)** and then set the fileFound accordingly. MemoryStream will be empty if file not found. — PM., Mar 28 '13 at 15:00

No data is read in SqlReader Invalid attempt to read when no data is present.

1 Answers1