weird problem with OdbcDataReader.GetBoolean() throwing cast is not valid exception for bool column

Question

Ok, this one is driving me completely crazy. I have a table in a PostgreSQL database and I'm trying to get the value a boolean column for a specific record with OdbcDataReader.GetBoolean(int col).

The problem is that GetBoolean() keeps throwing a cast is not valid exception even though the same code worked just several days ago. What's even weirder is the same code works fine in another form.

The only change made from the previous working version of my application was adding a column to the table. That said, the index of the column that I need hasn't changed. Oh yeah, getting the value with GetValue() and then calling GetType() on the result returns System.String and the true/false values get translated to 1/0.

I'm all out of ideas.

Answer 1

I don't think passing a System.String to GetBoolean() is going to work for you - as you can see from your exception you're getting an InvalidCastException, which means that internally somewhere it's trying to do something like this:

string s = "true";
bool b = (bool)s;

That clearly won't work.

If you can see that ODBC is passing you back a System.String then you want to use either Convert.ToBoolean(), bool.TryParse(), or bool.Parse, depending on what exactly fits best with the rest of your code.

As to why it WAS working and now isn't - has someone else changed the underlying data type on the database field to a character-based type?

This pattern is what we use to get boolean data out of an OdbcDataReader:

data.Gender = reader["Gender"] == DBNull.Value ? 
    false : Convert.ToBoolean(reader["Gender"]);

Laborious, yes. But it works well for us. The underlying database for this code is Access ("yes/no" field type) or MS SQL Server ("bit" field type).

Answer 2

While I still have no idea what is causing this exception, I've managed to come up with code that works (not actual code from my app):

val1 = reader.GetInt32(0);
val2 = reader.GetBoolean(4);
val3 = reader.GetBoolean(8);

This, however, does not:

val3 = reader.GetBoolean(8);
val1 = reader.GetInt32(0); // causes invalid cast exception
val2 = reader.GetBoolean(4);

Apparently, the column order has something to do with it.