System.Data.SQLite parameter issue

Question

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;
    }
}

Ronald Wildenberg · Accepted Answer · 2011-10-07 14:26:58Z

32

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)

edited Oct 7, 2011 at 14:26

answered Apr 15, 2009 at 11:07

Ronald Wildenberg

32.3k17 gold badges95 silver badges133 bronze badges

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

8 Comments

CasperT Over a year ago

Sorry, no, I tried that as well. It would give the following error: "Insufficient parameters supplied to the command"

Ronald Wildenberg Over a year ago

Ah, I see your problem. Not all parameter names are the same: @uNavnParam versus @uNameParam. It's a simple typo :)

Ronald Wildenberg Over a year ago

And you should still remove the single quotes around your parameters.

CasperT Over a year ago

Yeah you are right, so the fuss was just a stupid typo :( ("INSERT INTO Brugere (navn, brugernavn, password, pc_id, noter, licens_id) VALUES (@uNavnParam, @bNavnParam, @passwdParam, @pc_idParam, @noterParam, @licens_idParam)", conn); = Wrong input error. I hope it is not a another silly typo.

Ronald Wildenberg Over a year ago

This is probably something else. You could try to find out what the exact SQL statement is that is sent to your database and check the error message that is generated if you execute the statement directly.

|

CasperT · Accepted Answer · 2009-04-15 12:00:30Z

6

Thanks to rwwilden and Jorge Villuendas, the answer is:

var insertSQL = new SQLiteCommand("INSERT INTO Brugere (navn, brugernavn, password, pc_id, noter, licens_id)" +
" VALUES (@uNavnParam, @bNavnParam, @passwdParam, @pc_idParam, @noterParam, @licens_idParam)", conn);
insertSQL.Parameters.AddWithValue("@uNavnParam", uNavn);
insertSQL.Parameters.AddWithValue("@bNavnParam", bNavn);
insertSQL.Parameters.AddWithValue("@passwdParam", passwd);
insertSQL.Parameters.AddWithValue("@pc_idParam", pc_id);
insertSQL.Parameters.AddWithValue("@noterParam", noter);
insertSQL.Parameters.AddWithValue("@licens_idParam", licens_id);

insertSQL.ExecuteNonQuery(); //Execute query

answered Apr 15, 2009 at 12:00

CasperT

3,47513 gold badges45 silver badges56 bronze badges

Comments

Konrad Viltersten · Accepted Answer · 2012-10-26 13:41:35Z

4

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.

edited Oct 26, 2012 at 13:41

Konrad Viltersten

40.2k90 gold badges297 silver badges525 bronze badges

answered Feb 13, 2012 at 16:29

bro

411 bronze badge

Comments

Stark · Accepted Answer · 2010-04-13 12:47:37Z

1

replace

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

with

VALUES (?, ?, ?, ?, ?, ?)

answered Apr 13, 2010 at 12:47

Stark

191 bronze badge

Collectives™ on Stack Overflow

System.Data.SQLite parameter issue

4 Answers 4

8 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

8 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related