Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

System.Data.SQLite parameter issue

I have the following code:

try
{
    //Create connection
    SQLiteConnection conn = DBConnection.OpenDB();

    //Verify user input, normally you give dbType a size, but Text is an exception
    var uNavnParam = new SQLiteParameter("@uNavnParam", SqlDbType.Text) { Value = uNavn }; 
    var bNavnParam = new SQLiteParameter("@bNavnParam", SqlDbType.Text) { Value = bNavn };
    var passwdParam = new SQLiteParameter("@passwdParam", SqlDbType.Text) {Value = passwd};
    var pc_idParam = new SQLiteParameter("@pc_idParam", SqlDbType.TinyInt) { Value = pc_id };
    var noterParam = new SQLiteParameter("@noterParam", SqlDbType.Text) { Value = noter };
    var licens_idParam = new SQLiteParameter("@licens_idParam", SqlDbType.TinyInt) { Value = licens_id };

    var insertSQL = new SQLiteCommand("INSERT INTO Brugere (navn, brugernavn, password, pc_id, noter, licens_id)" +
    "VALUES ('@uNameParam', '@bNavnParam', '@passwdParam', '@pc_idParam', '@noterParam', '@licens_idParam')", conn);
    insertSQL.Parameters.Add(uNavnParam); //replace paramenter with verified userinput
    insertSQL.Parameters.Add(bNavnParam);
    insertSQL.Parameters.Add(passwdParam);
    insertSQL.Parameters.Add(pc_idParam);
    insertSQL.Parameters.Add(noterParam);
    insertSQL.Parameters.Add(licens_idParam);
    insertSQL.ExecuteNonQuery(); //Execute query

    //Close connection
    DBConnection.CloseDB(conn);

    //Let the user know that it was changed succesfully
    this.Text = "Succes! Changed!";
}
catch(SQLiteException e)
{
    //Catch error
    MessageBox.Show(e.ToString(), "ALARM");
}

It executes perfectly, but when I view my "brugere" table, it has inserted the values: '@uNameParam', '@bNavnParam', '@passwdParam', '@pc_idParam', '@noterParam', '@licens_idParam' literally. Instead of replacing them.

I have tried making a breakpoint and checked the parameters, they do have the correct assigned values. So that is not the issue either.

I have been tinkering with this a lot now, with no luck, can anyone help?

Oh and for reference, here is the OpenDB method from the DBConnection class:

public static SQLiteConnection OpenDB()
{
    try
    {
        //Gets connectionstring from app.config
        const string myConnectString = "data source=data;";

        var conn = new SQLiteConnection(myConnectString);
        conn.Open();
        return conn;
    }

    catch (SQLiteException e)
    {
        MessageBox.Show(e.ToString(), "ALARM");
        return null;
    }
}
like image 743
CasperT Avatar asked Apr 15 '09 11:04

CasperT


People also ask

Does SQLite support parameterized query?

In SQLite, parameters are typically allowed anywhere a literal is allowed in SQL statements. Parameters can be prefixed with either : , @ , or $ . command.

What is System Data SQLite core?

System.Data.SQLite. The official SQLite database engine for both x86 and x64 along with the ADO.NET provider.


2 Answers

When you use System.Data.SqlClient then you provide parameter types from System.Data.SqlDbType enumeration.

But if you use System.Data.SQLite then you have to use **System.Data.DbType** enumeration.

like image 179
bro Avatar answered Nov 10 '22 15:11

bro


You should remove the quotes around your parameter names in the INSERT statement.

So instead of

VALUES ('@uNameParam', '@bNavnParam', '@passwdParam', '@pc_idParam',
        '@noterParam', '@licens_idParam')

use

VALUES (@uNameParam, @bNavnParam, @passwdParam, @pc_idParam,
        @noterParam, @licens_idParam)
like image 45
Ronald Wildenberg Avatar answered Nov 10 '22 13:11

Ronald Wildenberg