CODEWITHSUNDEEP
c#sqlitesystem.data.sqlitesqlite-net
Scott Jermaine Guyton
Scott Jermaine Guyton

Reputation: 161

Inserting multiple parameter values to a sqlite table using c#

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.

Upvotes: 1

Views: 3870

Answers (1)

Steve
Steve

Reputation: 216303

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

Upvotes: 1

Related Questions