Error: InvalidCastException: Unable to cast object of type 'System.String' to type 'System.Int32

[mldardy]

I get this error but not sure where to fix it. I know it seems to be in this block of code:

    public async Task<List<Property>> All()
    {
        var cmd = Database.Connection.CreateCommand();
        cmd.CommandText = @"(SELECT 'PropertyCode', 'ClientCode', 'CorePropertyTypeID','PropertyName','ServiceStartDate','ServiceEndDate' FROM 'Property' WHERE 'RecordDisabled' = 0 );";
        return await Reader(await cmd.ExecuteReaderAsync());
    }

    private async Task<List<Property>> Reader(DbDataReader reader) {
        var properties = new List<Property>();
        using (reader)
        {
            while(await reader.ReadAsync())
            {
                var property = new Property(Database)
                {
                    Id = await reader.GetFieldValueAsync<int>(0),
                    //ClientId = await reader.GetFieldValueAsync<int>(1),
                    //TypeId = await reader.GetFieldValueAsync<int>(2),
                    //Name = await reader.GetFieldValueAsync<string>(3),
                    //ServiceStartDate = await reader.GetFieldValueAsync<DateTime>(4),
                    //ServiceEndDate = await reader.GetFieldValueAsync<DateTime>(5)
                };
                properties.Add(property);
            }
        }
        return properties;
    }
}

[DaveMaxwell]

Id = await reader.GetFieldValueAsync<int>(0),

Most likely it’s this line, and it would be because there’s a non-numeric value in PropertyCode field on the Property table.

[mldardy]

Is there any way to alleviate that so it will go thru even there is a non-numeric value for PropertyCode. Basically can the code I provided by changed for PropertyCode?

[DaveMaxwell]

You have three choices:

• Change the datatype of id to something that allowed character values (i.e. string).
• Change the query to always return a numeric value

SELECT CASE WHEN TRY_CONVERT(int, PropertyCode) IS NULL 
            THEN -1
            ELSE CONVERT(int, PropertyCode)
       END AS PropertyCodeNumeric

• Use the typeParse method (warning: This syntax should be close, but may not be 100% correct. test thoroughly)

bool checkInt = Int32.TryParse(await reader.GetFieldValueAsAsync(0), out Id);

Personally, I’d change the field type in the code since that matches what’s in the database. Shoehorning something into a specific time is usually dangerous and troublesome.

NOTE: I would also suggest using variable names which matches the result set out of your database. It will save confusion later, and reduces the chances of non-matching data types. Putting PropertyCode into id will undoubtedly cause you confusion later.

[mldardy]

The database datatype for that field INT(11). My original code is using (0), is that not a match?

[DaveMaxwell]

Then I would venture you’re getting a null value returned from your code somewhere. I would suggest debugging your code to see what is actually in reader.GetFieldValueAsync(0)

[mldardy]

I did a debug and it is reading a 0 for the value returned. When it should obviously give values from the database table.

[mldardy]

I figured out what it was. My SQL syntax was incorrect. Instead of having it in parentheses I removed the parentheses and the single quotes around each field and it worked!