SQLite with C# - Parameterized Insert of String will be stored as Numeric

Question

using (SQLiteCommand cmd = ctx.DB.Query("INSERT INTO tableName(Text1, Text2) VALUES (@text1, @text2)"))
{
      cmd.Prepare();
      cmd.Parameters.AddWithValue("@text1","abc");
      cmd.Parameters.AddWithValue("@text2", "123");
      cmd.ExecuteNonQuery();
}

This command leads to an error in the database. If I insert a bunch of data it converts the text2 value to a double instead of just a text with the value "123"

How do I disable the auto convert in the sqllite driver

Table Defintion:

CREATE TABLE `TableName` ( `Text1` TEXT NOT NULL, `Text2` TEXT NOT NULL )

EDIT:

    cmd.Parameters.AddWithValue("@text1", SqlDbType.Text).Value = "abc";
    cmd.Parameters.AddWithValue("@text2", SqlDbType.Text).Value = "123";
    cmd.ExecuteNonQuery();

Results into an exception:

Exception is :

Conversion Exception wrong format
at System.Number.StringToNumber(String str, NumberStyles options, NumberBuffer& number, NumberFormatInfo info, Boolean parseDecimal)
   at System.Number.ParseInt32(String s, NumberStyles style, NumberFormatInfo info)
   at System.String.System.IConvertible.ToInt32(IFormatProvider provider)
   at System.Convert.ToInt32(Object value, IFormatProvider provider)
   at System.Data.SQLite.SQLiteStatement.BindParameter(Int32 index, SQLiteParameter param) in c:\dev\sqlite\dotnet\System.Data.SQLite\SQLiteStatement.cs:Line 481.
   at System.Data.SQLite.SQLiteStatement.BindParameters() in c:\dev\sqlite\dotnet\System.Data.SQLite\SQLiteStatement.cs:Line 237.
   at System.Data.SQLite.SQLiteCommand.BuildNextCommand() in c:\dev\sqlite\dotnet\System.Data.SQLite\SQLiteCommand.cs:Line 388.
   at System.Data.SQLite.SQLiteCommand.GetStatement(Int32 index) in c:\dev\sqlite\dotnet\System.Data.SQLite\SQLiteCommand.cs:Line 395.
   at System.Data.SQLite.SQLiteDataReader.NextResult() in c:\dev\sqlite\dotnet\System.Data.SQLite\SQLiteDataReader.cs:Line 1864.
   at System.Data.SQLite.SQLiteDataReader..ctor(SQLiteCommand cmd, CommandBehavior behave) in c:\dev\sqlite\dotnet\System.Data.SQLite\SQLiteDataReader.cs:Line 123.
   at System.Data.SQLite.SQLiteCommand.ExecuteReader(CommandBehavior behavior) in c:\dev\sqlite\dotnet\System.Data.SQLite\SQLiteCommand.cs:Line 942.
   at System.Data.SQLite.SQLiteCommand.ExecuteNonQuery(CommandBehavior behavior) in c:\dev\sqlite\dotnet\System.Data.SQLite\SQLiteCommand.cs:Line 990.
   at System.Data.SQLite.SQLiteCommand.ExecuteNonQuery() in c:\dev\sqlite\dotnet\System.Data.SQLite\SQLiteCommand.cs:Line 975.

I don't know if this is a problem with SQLite, but in SQL Server AddWithValue has a bad habit of changing numeric strings to INTs. You should instead use cmd.Parameters.Add("@Parameter", SqlDbType.Foo, ...).Value = .... — Dour High Arch
– Dour High Arch, Commented Apr 11, 2018 at 16:24
writing it with native sql insert code leads to valid inserts — javaBeCool
– javaBeCool, Commented Apr 11, 2018 at 16:44
You're still using .AddWithValue, use .Add. From the stack trace it looks like SQLite is doing the text -> INT thing as well. — Dour High Arch
– Dour High Arch, Commented Apr 11, 2018 at 16:44

javaBeCool · Accepted Answer · 2018-04-11 17:06:31Z

4

using (SQLiteCommand cmd = ctx.DB.Query("INSERT INTO tableName(Text1, Text2) VALUES (@text1, @text2)"))
    {
          cmd.Prepare();
          cmd.Parameters.Add("@text1", DbType.AnsiString).Value = "abc";
          cmd.Parameters.Add("@text2", DbType.AnsiString).Value = "123";
          cmd.ExecuteNonQuery();
    }

Thanks to @DourHighArch for that hint

answered Apr 11, 2018 at 17:06

javaBeCool

3064 silver badges13 bronze badges

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

Collectives™ on Stack Overflow

SQLite with C# - Parameterized Insert of String will be stored as Numeric

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related