Data from SqlDataReader not displaying in else statement

Question

I have a SqlDataReader that I'm pulling data from. I'm trying to display different messages to the user based on data from the reader. When my if statement returns true the data is displayed correctly when the if statement returns false I get a lengthy error:

System.InvalidOperationException: Invalid attempt to read when no data is present.
at System.Data.SqlClient.SqlDataReader.CheckDataIsReady(Int32 columnIndex, Boolean allowPartiallyReadColumn, Boolean permitAsync, String methodName)
at System.Data.SqlClient.SqlDataReader.TryReadColumn(Int32 i, Boolean setTimeout, Boolean allowPartiallyReadColumn) at System.Data.SqlClient.SqlDataReader.GetValueInternal(Int32 i)
at System.Data.SqlClient.SqlDataReader.GetValue(Int32 i)
at System.Data.SqlClient.SqlDataReader.get_Item(String name)

Here is my code:

protected void Page_Load(object sender, EventArgs e)
{
 MembershipUser user = Membership.GetUser();
 string key = user.ProviderUserKey.ToString();
 SqlConnection con = new SqlConnection(connectionString);
 SqlCommand cmd = new SqlCommand("usp_GetDetails", con);
 cmd.CommandType = CommandType.StoredProcedure;
 cmd.Parameters.AddWithValue("@UserKey", key);
 SqlDataReader reader;
 try
 {
 con.Open();
 reader = cmd.ExecuteReader();
 reader.Read();
 getBidNumber = reader["SelectedBid"].ToString();
 getCompanyName = reader["CompanyName"].ToString();
 getUserKey = reader["MembershipUserKey"].ToString();
 getMerchantKey = reader["MerchantKey"].ToString();
 getMerchantName = reader["MerchantName"].ToString();
 this.DataBind();
 if (getBidNumber != null)
 {
 lblBidSelected.Text = "Congratulations you have selected #" + getBidNumber + " from " + getCompanyName + ". Click here to review you bid details.";
 }
 else
 {
 lblNoBidSelected.Text = "You have not selected a processor. Please <a href='bids-visit.aspx?mkey=" + getMerchantKey + "'>click here</a> to review your bids.";
 }
 }
 catch (Exception err)
 {
 lblError.Text = "There was an error " + err;
 }
 finally
 {
 con.Close();
 }
}

Answer 1

I recommend using the much more readable HasRows property of the SqlDataReader, like this:

if (reader.HasRows) 
{
 while (reader.Read()) 
 {
 // Do your reading logic here
 getBidNumber = reader["SelectedBid"].ToString();
 getCompanyName = reader["CompanyName"].ToString();
 getUserKey = reader["MembershipUserKey"].ToString();
 getMerchantKey = reader["MerchantKey"].ToString();
 getMerchantName = reader["MerchantName"].ToString();
 }
}

Note: This makes it very obvious as to why or why not any data was processed by your reading logic.

UPDATE:

Change your if-else logic to this:

if (!String.IsNullOrEmpty(getBidNumber))
{
 lblBidSelected.Text = "Congratulations you have selected #" + getBidNumber + " from " + getCompanyName + ". Click here to review you bid details.";
}
else
{
 lblNoBidSelected.Text = "You have not selected a processor. Please <a href='bids-visit.aspx?mkey=" + getMerchantKey + "'>click here</a> to review your bids.";
}

Note: A string will initialize to an empty string, not null, and ToString() will not return null; so using the IsNullOrEmpty() method to check for a value is more in line with your approach.

• brandozz: This takes care of my error, but I still can't seem to get data to my else statement when my condition in the if statement is false.
• Joel Coehoorn: Why not just if (reader.Read())? That's shorter, and certainly readable enough.
• Karl Anderson: @JoelCoehoorn - .Read() returns a Boolean, but can you tell that from the name of the method? Contrast that with .HasRows, it is pretty obvious that true or false is the answer from that name.
• Karl Anderson: @brandozz - see UPDATE: in my answer.
• brandozz: This worked, the problem I was having was an incorrect join in my stored procedure. Thanks for your help!

Answer 2

Since your are inside else it means:

reader==null -> getMerchantKey = reader["MerchantKey"].ToString()==null

-> you get your error

Answer 3

The problem is not inside the else block. The exception is thrown earlier, at this line:

getBidNumber = reader["SelectedBid"].ToString();

The reason it throws there is because the call to reader.Read() did not produce any results. It only seems like the problem is at the else block, because if the call to reader.Read() succeeded the if condition would always test to true. What you really want is code like this:

if (reader.Read())
{
 getBidNumber = reader["SelectedBid"].ToString();
 getCompanyName = reader["CompanyName"].ToString();
 lblBidSelected.Text = "Congratulations you have selected # ... "
}
else
{
 //You'll need to change your error message, because the merchant key is unknown
 blNoBidSelected.Text = "You have not selected a processor. ... "
}

• brandozz: What about my condition in the if statement? if (getBidNumber != null)
• Joel Coehoorn: @brandozz .ToString() will never return null. If the value of the column is null, .ToString() will return an empty string. So if the call to reader.Read() succeeds, that if() condition will always be true. It's impossible for the current code to ever reach the else block.
• brandozz: Joel, does it return an Empty string?
• brandozz: It doesn't seem like my if statement is always returning true, if that were the case then my congratulations message would display. What I'm getting is either nothing displayed at all or text displayed without data from my reader.
• Joel Coehoorn: When you have text without data from your reader, that's empty strings where you were expecting null.

Answer 4

You are getting this error before reaching the if statement probably because your query returns no row. Check reader.Read() return value before proceeding.

Answer 5

First of all initialise like this

string getBidNumber =null;

and when do string conversion check reader["SelectedBid"] is null or not.

if(reader["SelectedBid"] !=null)
{
getBidNumber = reader["SelectedBid"].ToString();
}

Rest of code all same. Best of luck

• brandozz: I did try this but I just can't get data into the else statement.