SqlCeCommand Parameters not working

I have a SQL Server Compact database and I'm trying to insert a record into it using cmd.ExecuteNonQuery(). This method worked perfectly fine in another project, but it doesn't work now.

private void AddNewProfile() {
    try {
        using(SqlCeConnection conn = new SqlCeConnection(Properties.Settings.Default.dbConnectionString)) {
            using(SqlCeCommand cmd = new SqlCeCommand()) {
                cmd.Connection = conn;

                cmd.CommandText = "INSERT INTO Profiles (ProfileName, ProfilePath, ProfileDescription) VALUES ('@name', '@path', '@desc');";
                cmd.Parameters.AddWithValue("@name", SqlDbType.Text).Value = "New Profile";
                cmd.Parameters.AddWithValue("@path", SqlDbType.Text).Value = "C:\\";
                cmd.Parameters.AddWithValue("@desc", SqlDbType.Text).Value = "A blank profile.";

                conn.Open();
                cmd.ExecuteNonQuery();
                conn.Close();
            }
        }
    }
    catch(Exception ex) {
        MessageBox.Show(ex.Message, "Error");
    }
}

The problem comes in with the parameters - I practically copied the code from one of my other projects, but it doesn't work correctly. Instead of executing this:

INSERT INTO Profiles (ProfileName, ProfilePath, ProfileDescription) 
VALUES ('New Profile', 'C:\\', 'A blank profile.');

it executes this:

INSERT INTO Profiles (ProfileName, ProfilePath, ProfileDescription) 
VALUES ('@name', '@path', '@desc');

What is the problem here?

Jon Skeet
people
quotationmark

Two problems:

Firstly, your SQL is specifying literal values because of the quotes. It should be:

INSERT INTO Profiles (ProfileName, ProfilePath, ProfileDescription)
VALUES (@name, @path, @desc)

That way the SQL refers to the parameters, rather than literals with values of `"@name", "@path" and "@desc".

(I've removed the unnecessary semi-colon as well.)

Secondly, calling AddWithValue, but providing the type as the value, then overwriting the value. That's pointless and confusing - the type you're specifying is going to be lost. You should be using:

cmd.Parameters.Add("@name", SqlDbType.Text).Value = "New Profile";

Finally, you don't need to call conn.Close() - it's already in a using statement... and you can pass conn to the SqlCeCommand constructor, to make things slightly simpler.

people

See more on this question at Stackoverflow