SQLite inserts a Character as an Integer interpreted as the decimal ASCII value. This happens if you do not specify the DBtype of a parameter used in a SQLiteCommand.
You can verify the problem with the code below.
All the SQLiteCommands insert a Character/String as asumed. But the last one inserts a number instead of a string.
In my code I have to do it the last way. I know that there are workarounds (like shown) but I think the behaviour of SQLite is wrong.
So do you agree that this is a bug? Is it a problem of the SQLite driver or the SQLite core?
Public Sub main()
Dim sqlite As New SQLiteConnection("Data Source=C:\temp\test.s3db;Version=3;")
sqlite.Open()
Dim command As New SQLiteCommand("CREATE TABLE test (character VARCHAR(5))", sqlite)
command.ExecuteNonQuery()
'Works:
command.CommandText = "INSERT INTO test (character) VALUES ('S')"
command.ExecuteNonQuery()
'Works:
command.CommandText = "INSERT INTO test (character) VALUES (@test)"
Dim parameter As New SQLiteParameter("test", DbType.StringFixedLength)
parameter.Value = "T"c
command.Parameters.Clear()
command.Parameters.Add(parameter)
command.ExecuteNonQuery()
'Works:
parameter = New SQLiteParameter("test", "U")
command.Parameters.Clear()
command.Parameters.Add(parameter)
command.ExecuteNonQuery()
'Fails:
parameter = New SQLiteParameter("test", "V"c)
command.Parameters.Clear()
command.Parameters.Add(parameter)
command.ExecuteNonQuery()
sqlite.Close()
End Sub