OleDbDataReader.GetString Exception - Specified cast is not valid

Question

When my C# (.NET 3.5) application attempts to access a MS Access 2007 database the OleDbReader.GetString() method is throwing an exception:

Specified cast is not valid.

What am I doing wrong?

OleDbCommand cmd = null;
OleDbDataReader reader = null;
String queryString = "SELECT ids.ENUM_H, bas.[BAS BACnet Object Type/Instance] FROM [OV2 BAS] AS bas INNER JOIN [OV2 RefID] AS ids ON bas.[Ref ID] = ids.[Ref ID]";

this.Open();

try
{
    cmd = new OleDbCommand(queryString, this._conn);
    reader = cmd.ExecuteReader();

    if (!reader.HasRows)
    {
        Exception e = new Exception("Read of mapping table returned no results.");
        throw e;
    }
    else
    {
        while (reader.Read())
        {
            Int32 index;
            String classTypeString = null; // = reader.GetString(reader.GetOrdinal(MappingTable.OBJECT_IDENTIFIER_COLUMN_NAME)).Substring(0, 2);
            int it = reader.GetOrdinal(MappingTable.OBJECT_IDENTIFIER_COLUMN_NAME);
            string st = reader.GetString( it );  // <-- **Exception is thrown here** <--
            st = st.Substring(0,2);
            String classIdString = reader.GetString(reader.GetOrdinal(MappingTable.OBJECT_IDENTIFIER_COLUMN_NAME)).Substring(2);

            index = Convert.ToInt32(classIdString);
            ClassIds[index, 0] = reader.GetString(reader.GetOrdinal("ENUM_H"));
            ClassIds[index, 1] = classTypeString;
        }
    }
}
catch (Exception e)
{
    Console.WriteLine("ERROR: " + e.Message);
    Console.WriteLine(e.ToString());
    throw e;
}

this.Close();

I know that the Open() and Close() methods work. Something is wrong with my query, or the way I am processing the results. Thanks.

What's the type of that column? If you call `reader.GetValue()` instead, what happens? Might that value be null? — Jon Skeet, Nov 04 '11 at 16:52
Yes, it does, but it returns values for `ids.ENUM_H`, which is expected. When I step through `it` does evaluate to 1 as expected. When I change it to `string st = reader.GetString(1)` I get the same error. It's like it's not pulling anything from the database for `bas.[BAS BACnet Object Type/Instance]`. — Jim Fell, Nov 04 '11 at 16:55
@JimFell: It means there's no data for that field in that row. Will add an answer. — Jon Skeet, Nov 04 '11 at 17:53

score 9 · Accepted Answer · answered Nov 04 '11 at 17:54

9

Okay, so reader.IsDBNull(1) is returning true... which means there's no data in that particular row for that field.

You need to work out what that means, and handle it appropriately. You may want to modify the query to not include such rows, or use reader.IsDBNull to detect such rows and act appropriately, e.g. using a default value for the field.

answered Nov 04 '11 at 17:54

Jon Skeet

1,421,763
867
9,128
9,194

Thanks for the help! Now that I know what's happening I can handle it. I looked in the database and you were right; there is no data in that row for that field. – Jim Fell Nov 04 '11 at 17:56

score 0 · Answer 2 · answered Aug 12 '14 at 08:43

0

Sometimes it happens because of Cell format. If your Cell format is set to General you should change it to Text .

answered Aug 12 '14 at 08:43

MojAmiri

506
7
8

OleDbDataReader.GetString Exception - Specified cast is not valid

2 Answers2