sqlite numeric value in string field causes InvalidCastException

Question

I have a SQLite table with the following definition:

create table routes(id INTEGER PRIMARY KEY AUTOINCREMENT, name string)

and want to fetch records using System.Data.SQLite:

cmdRoute.CommandText = "SELECT id, name FROM routes";
using (var rdrRoute = cmdRoute.ExecuteReader())
{
    if(rdrRoute.Read())
    {
        var route = new Route();
        route.Id = rdrRoute.GetInt32(0);
        route.Name = rdrRoute.GetString(1); // Throws InvalidCastException
    }
}

The GetString throws an InvalidCastException when the value in the database is numeric. When I put the rdrRoute.GetValue(1) in a watch, it shows that the type is "object {string}". When I change the value to a non-numeric value, it works fine.

I've looked in the SQLite DataReader code and it looks like SQLite checks the value to map it to a set of allowed types.

Is this the intended behaviour and how can I prevent this, in a way that strings containing numbers are still strings?

Luuk how about if you try this route.Name = rdrRoute.GetString(1).ToString(); or try casting it as a string like this route.Name = (string)rdrRoute.GetString(1); — MethodMan
– MethodMan, Commented Jan 31, 2013 at 14:04
rdrRoute.GetString(1) already crashes with the InvalidCastException. — Luuk
– Luuk, Commented Jan 31, 2013 at 14:05
did you try it with casting as a string (string)rdrRoute.GetString(1); — MethodMan
– MethodMan, Commented Jan 31, 2013 at 14:06
(string)rdrRoute.GetValue(1) works, but I want to know why the GetString(1) doesn't work. — Luuk
– Luuk, Commented Jan 31, 2013 at 14:10
I know why you're getting error and I will add this to my answer as well for better readability and explanation. — MethodMan
– MethodMan, Commented Jan 31, 2013 at 14:19

MethodMan · Accepted Answer · 2013-01-31 15:34:51Z

4

for the SqlDataReader.GetString Method, No conversions are performed; therefore, the data retrieved must already be a string.

Call IsDBNull to check for null values before calling this method.

Change you code to this and it should work

cmdRoute.CommandText = "SELECT id, name FROM routes";
using (var rdrRoute = cmdRoute.ExecuteReader())
{
    if(rdrRoute.Read())
    {
        var route = new Route();
        route.Id  = rdrRoute["id"];
        if (!rdrRoute.IsDBNull(rdrRoute.GetOrdinal("name")))
        {
           route.Name = rdrRoute["name"].ToString();
           //route.Name = rdrRoute.GetString(rdrRount.GetOrdinal("name"));
           //may not work in .Net 4.0
        }
    }
}

.NET 4.0 GetString() may cause errors / bugs in .NET 4.0 I would try rolling the project back to ver 3.5 or create a project that uses the code sample and create it as .NET 3.5, if this works with the GetString() I have found that there are some bugs in .net 4.0 and I am not sure when the KB Fix is going to release

edited Jan 31, 2013 at 15:34

answered Jan 31, 2013 at 14:10

MethodMan

18.9k6 gold badges41 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Luuk Over a year ago

(string)rdrRoute.GetString(1) crashes with InvalidCastException, and the Id is an Int32, so I need to convert it with Convert.ToInt32(rdrRoute["id"]). rdrRoute["name"].ToString(); works though.

Collectives™ on Stack Overflow

sqlite numeric value in string field causes InvalidCastException

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related