.NET Framework - SqlDataReader and System.InvalidCastException

Asked By brian.gabrie on 30-Aug-08 09:22 PM
I am using trying to get some data out of a SQL 2000 database via
stored procedure, but I am getting some strange results.  It will pull
back the first 3 fields but the rest (7 other fields) seem to be
empty, and not just DBNull, but the objects just aren't there.

Here is the code:

SqlCommand cmd = new SqlCommand("psx_GetIntlAdrress", cn);
cmd.CommandType = CommandType.StoredProcedure;

SqlParameter paraOrderID = new SqlParameter("@OrderID",
SqlDbType.Char);
paraOrderID.Value = SourceMessage.SelectSingleNode("/Orders/
OrderHeader/OrderID").InnerText;
cmd.Parameters.Add(paraOrderID);

SqlDataReader rsIntlOrder = cmd.ExecuteReader();

if (rsIntlOrder.HasRows)
{
while (rsIntlOrder.Read())
{...



This gives me the "Object reference not set to an instance of an
object." exception:

string sSomeValue = rsIntlOrder[3] != DBNull.Value ?
rsIntlOrder[2].ToString().Trim() : "";



I have verified that the stored procedure is returning all values.  If
I break one a line that works and look at the datareader it shows that
there are 10 fields, but only the first 3 will not throw an error.
Drilling down, the remaining elements show System.InvalidCastException
for all data types.  Not sure what is going wrong here, hoping someone
can help.

Thanks,

Brian
brian.gabriel@gmail.com




brian.gabrie replied on 30-Aug-08 09:22 PM
Some more strange behavior, the following line will cause an exception
to be thrown:
rsIntlOrder.GetString(3).Trim()

If I place a breakpoint there, this line works fine in the Immediate
Window and does not throw an error.

Any ideas out there?

Thanks,

Brian
Miha Markic replied on 30-Aug-08 08:06 AM
I suggest you to get value as an object first and see what's inside, i.e.:
object value = rsIntlOrder.GetObject(3);
// check value
--
Miha Markic [MVP C#, INETA Country Leader for Slovenia]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

Some more strange behavior, the following line will cause an exception
to be thrown:
rsIntlOrder.GetString(3).Trim()

If I place a breakpoint there, this line works fine in the Immediate
Window and does not throw an error.

Any ideas out there?

Thanks,

Brian