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.

Deduct

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;
   }

Fungoid

thanks very much, it's working fine. I'm new comer in Asp.net. – Deduct

You are more than welcome. I have added a suggestion about using in an edit. Feel free to neglect it :) – Fungoid

I got your point, I will use it, thanks a lot for your suggestions and help. – Deduct

Answer 2

After going through so much trouble with this error I discovered something hidden about it:

I declared a connection object as a public variable. I then use it through several functions, and at one point I decided not to reset its connection string since it was already set by a preceding procedure.

The subsequent function, which called the previous function while in a loop through a table set by the public connection object thus lost its connection after the previous function returned its values, and CLOSED ITS CONNECTION OBJECT (which is a public object shared by the two functions)!

I hope that made sense... it took me hours to be able to figure out what was going on.

Ibbie