SQLite inserting string data with parameters leads to invalid cast exception

Question

Good morning,

I was working on some unit tests for our SQLite 3 database code, and ran into this interesting problem. Here is a unit test that passes with flying colors:

[Fact]
public void TestDB()
{
    FileInfo fi = new FileInfo(Path.GetTempPath() + Guid.NewGuid().ToString() + ".sqlite");
    using (SQLiteConnection conn = new SQLiteConnection($"Data Source={fi.FullName};Version=3;"))
    {
        conn.Open();
        conn.ExecuteNonQuery("CREATE Table Temp(A INTEGER NOT NULL PRIMARY KEY, B STRING);");

        string sql = "INSERT INTO Temp (A, B) VALUES (@A, @B)";
        string s = "Test";
        using (SQLiteCommand command = new SQLiteCommand(sql, conn))
        {
            command.Parameters.Add("@A", System.Data.DbType.Int32).Value = 1;
            command.Parameters.Add("@B", System.Data.DbType.String).Value = s;
            command.ExecuteNonQuery();
        }

        sql = $"SELECT B FROM Temp WHERE A = 1";
        using (SQLiteCommand command = new SQLiteCommand(sql, conn))
        using (SQLiteDataReader reader = command.ExecuteReader())
        {
            bool read = reader.Read();
            Assert.True(read);
            if (read)
            {
                Assert.False(reader.IsDBNull(0));
                string b = reader.GetString(0);
                Assert.Equal(s, b);
            }
        }
    }
}

but if I change one line:

string s = "1";

An InvalidCastException is thrown on the reader.GetString(0) line. Thinking maybe an additional hint is necessary by specifying the column, I also tried this:

SQLiteParameter a = new SQLiteParameter("@A", System.Data.DbType.Int32, "A") { Value = 1 };
SQLiteParameter b = new SQLiteParameter("@B", System.Data.DbType.String, "B") { Value = s };
command.Parameters.Add(a);
command.Parameters.Add(b);

Nothing changed.

Anyone have any ideas how to use parameters and insert data into a string column where the data is a numeric string?

Answer 1

@steve16351 had the answer.

The problem was actually in CREATE TABLE where the B column was type STRING. SQLite uses type TEXT.

Changing that to: CREATE TABLE Temp(A INTEGER NOT NULL PRIMARY KEY, B TEXT); fixed the problem.

Thank you!