Specified cast is not valid with datareader

Question

Hi friends of stackoverflow,

I'm writing a question here because i'm having problems to detect why sometimes a read to a field of a datareader returns a invalid cast exception. I will give all information possible to understand my situation. I'm working with ASP.NET 3.5

• I have a Module that have a function that returns a IDataReader and receives a sql query. something like this

  function get_dr(query as String) as IDataReader
  dim connection = new SqlClient.SqlConnection("connection string")
  connection.open()
  dim command = connection.createCommand
  command.commandText = query
  dim reader = command.executeReader(CommandBehavior.CloseConnection)
  return reader
  end function
  

• I have a Class with a Shared function that recovers a new dataReader and returns a date. something like this:

  public shared function getDate() as Date    
  using dr = get_dr("SELECT dbo.getDate()")    
  if dr.read() and dr(0) isnot dbnull.value then   
  return dr.GetDateTime(0)   
  end if   
  end using   
  end function   
  

when in another code i call the getDate() function, it gives me a call stack like this.

System.InvalidCastException: Specified cast is not valid.
at System.Data.SqlClient.SqlBuffer.get_DateTime()
at System.Data.SqlClient.SqlDataReader.GetDateTime(Int32 i)

Why sometimes i'm getting this error? i was thinking this is because that a lot of users is calling this function in conjunction with another functions of my application (those functions eventually uses get_dr too), mixing the data of the dataReader on another executions, but i need to know if im doing something wrong or maybe to do something better.

Notes:

• dbo.getDate is a sql function that ALWAYS returns a date.
• don't worry about bad writing code, those are only examples but they have the necessary to understand the scenario.
• sorry for my bad english

Really thanks in advance.

Answer 1

One possible reason - you declare connection inside of the function that returns DataReader. When you're out of the function that connection goes out of scope. That means that at some unpredictable point (depends on memory usage etc.) Garbage Collector will collect it. If you try to use the DataReader at that point - all bets are off.

One way to solve it is to declare connection outside of function get_dr and pass it there as a parameter. But also seeing that you're returning a single value and if you don't plan to use the reader for multiple values I suggest using ExecuteScalar instead - it will save you a lot of headaches.