CODEWITHSUNDEEP
c#sqlitecastingwindows-ceexecutescalar
B. Clay Shannon-B. Crow Raven
B. Clay Shannon-B. Crow Raven

Reputation: 10237

Why am I getting "Invalid Cast Exception" with this SQLite code?

I've got this code to get a count from a SQLite table:

internal static bool TableExistsAndIsNotEmpty(string tableName)
{
    int count;
    string qry = String.Format("SELECT COUNT(*) FROM {0}", tableName);
    using (SQLiteConnection con = new SQLiteConnection(HHSUtils.GetDBConnection()))
    {
        con.Open();
        SQLiteCommand cmd = new SQLiteCommand(qry, con);
        count = (int)cmd.ExecuteScalar();
    }
    return count > 0;
}

When it runs, I get, "Invalid Cast Exception"

As is probably obvious, the value being returned from the query is an int, that is to say the count of records (I get "2" when I run the query, namely "SELECT COUNT(*) FROM WorkTables" in Sqlite Browser).

So what is being invalidly cast here?

As a sort of a side note, I know it's better to use query parameters, and I found out how to do this in a Windows Store App here [How can I use SQLite query parameters in a WinRT app?, but don't know how to do it in an oldfangled (Windows Forms/Windows CE) app.

I would think it would be something like this:

string qry = "SELECT COUNT(*) FROM ?";
using (SQLiteConnection con = new SQLiteConnection(HHSUtils.GetDBConnection()))
{
    con.Open();
    SQLiteCommand cmd = new SQLiteCommand(con);
    count = cmd.ExecuteScalar(qry, tableName);
}

...but nothing of the ilk that I tried compiled.

Upvotes: 1

Views: 4570

Answers (1)

Steve
Steve

Reputation: 216273

In this context the ExecuteScalar returns a System.Int64.
Applying the (int) cast creates the exception you are seeing

object result = cmd.ExecuteScalar();
Console.WriteLine(result.GetType());  // System.Int64

You could solve your problem with Convert.ToInt32

SQLiteCommand cmd = new SQLiteCommand(qry, con);
count = Convert.ToInt32(cmd.ExecuteScalar());

Upvotes: 9

Related Questions