Inserting multiple parameter values to a sqlite table using c#

Question

Here is the code for the method that I am creating:

public void writeIt(string inputText, bool fasting)
    {
        var dbConnect = new SQLiteConnection("Data Source=database.sqlite;Version=3;");
        dbConnect.Open();
        using (SQLiteTransaction insertTrans = dbConnect.BeginTransaction())
        {
            using (SQLiteCommand insertCommand = new SQLiteCommand(dbConnect))
            {
                SQLiteParameter resultEntry = new SQLiteParameter();
                insertCommand.CommandText = "INSERT INTO result(testResult, fasting) VALUES(@param1, @param2)";
                insertCommand.Parameters.Add(new SQLiteParameter("@param1", System.Data.SqlDbType.VarChar).Value = inputText);
                if(fasting)
                {
                    insertCommand.Parameters.Add(new SQLiteParameter("@param2", System.Data.SqlDbType.Int).Value = "1");
                }
                else
                {
                    insertCommand.Parameters.Add(new SQLiteParameter("@param2", System.Data.SqlDbType.Int).Value = "0");
                }
                insertCommand.ExecuteNonQuery();
            }
            insertTrans.Commit();
        }

I am using the official sqlite database engine (if that matters). Currently the error that shows when I try to store data says the following

"Unable to cast object of type 'System.String' to type 'System.Data.SQLite.SQLiteParameter'" at the line "insertCommand.Parameters.Add(new SQLiteParameter("@param1", System.Data.SqlDbType.VarChar).Value = inputText);".

I openly(and freely) admit that I am not quite sure how to resolve this, as I am a novice and this is my second project of any significance.

Thank you for your kindness in advance.

Steve · Accepted Answer · 2016-03-05 09:38:53Z

1

When you call the Add method of the DbParameterCollection (the base class behind the SQLiteParameterCollection) passing a DbParameter object, the return value is an Integer, not the DbParameter just added to the collection.
Thus you cannot use the return value as it was a DbParameter and try to set the Value property.

Instead you should use the Add overload that takes the parameter name and type. This returns an DbParameter object and thus you could write:

insertCommand.Parameters.Add("@param1", System.Data.SqlDbType.VarChar).Value = inputText;
if(fasting)
{
    insertCommand.Parameters.Add("@param2", System.Data.SqlDbType.Int).Value = 1;
}
else
{
    insertCommand.Parameters.Add("@param2", System.Data.SqlDbType.Int).Value = 0;
}

You are creating an integer type parameter so set its value to an integer not to a string

edited Mar 5, 2016 at 9:38

answered Mar 5, 2016 at 9:20

Steve

217k22 gold badges242 silver badges296 bronze badges

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

1 Comment

Scott Jermaine Guyton Over a year ago

Your hint solved the issue. To be clear (for other possible readers): insertCommand.Parameters.Add("@param1", System.Data.DbType.String).Value = inputText; now allows me to insert into the database. Thank you, been puzzling this out for about a month.

Collectives™ on Stack Overflow

Inserting multiple parameter values to a sqlite table using c#

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