No data exists for the row/column

Question

While executing the following code in ASP.NET with C#, I am getting an exception like "No data exists for the row/column."

OdbcConnection DbConnection = new   OdbcConnection(ConfigurationManager.AppSettings["ConnectionStr"]);        
DbConnection.Close();        
string cmdText = "SELECT Team_ID FROM team_details WHERE Team_Code=?";        
OdbcCommand cmd = new OdbcCommand(cmdText, DbConnection);
cmd.Parameters.Add("?Code", OdbcType.VarChar).Value = tbCode.Text;
DbConnection.Open();
OdbcDataReader DR = cmd.ExecuteReader();
DR.Read();        
if (DR.GetValue(0) != DBNull.Value)        
{
    args.IsValid = false;
}
else
{
    args.IsValid = true;
}
DbConnection.Close();

I'm using Visual Studio 2008 and MySQL. I'm using MySql ODBC connector 5.1 and MDAC2.8.

Please guide me how to resolve this.

Answer 1

The Read method returns a boolean indicating whether any rows were returned or not, so you should always test this value prior to accessing the result of the reader.

  OdbcDataReader DR = cmd.ExecuteReader();



if (DR.Read() && DR.GetValue(0) != DBNull.Value)    
 {   
    args.IsValid = false; 
 } 
 else 
 {   
     args.IsValid = true; 
 } 
 DbConnection.Close();

EDIT ---- You should also consider using the connections since this will ensure that is is closed (disposed) even though an exception occurs inside the using statement.

You could also get rid of the if statement, but that is a matter of taste.

Finally standard .NET idiom would be to keep the connection string in the ConnectionStrings section of the web.config. This is not shown below.

   using(OdbcConnection DbConnection = new  OdbcConnection(ConfigurationManager.AppSettings["ConnectionStr"]))
   {
          string cmdText = "SELECT Team_ID FROM team_details WHERE Team_Code=?";        
          OdbcCommand cmd = new OdbcCommand(cmdText, DbConnection);
          cmd.Parameters.Add("?Code", OdbcType.VarChar).Value = tbCode.Text;
          DbConnection.Open();
          OdbcDataReader DR = cmd.ExecuteReader();
          args.IsValid = DR.Read() && DR.GetValue(0) != DBNull.Value;
   }