CODEWITHSUNDEEP
c#sqlsql-server-ce
Abluescarab
Abluescarab

Reputation: 547

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?

Upvotes: 0

Views: 985

Answers (3)

Soner Gönül
Soner Gönül

Reputation: 98840

You don't need to use single quotes when you declare your parameters. With single quotes, SQL will recognize them as a string literal not parameters. Just use them in your SqlCommand like;

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

Also you are using AddWithValue in a wrong way. It doesn't need the type.

cmd.Parameters.AddWithValue("@name", "New Profile");
cmd.Parameters.AddWithValue("@path", "C:\\");
cmd.Parameters.AddWithValue("@desc", "A blank profile.");

or you can use Add if you want to declare their types like;

cmd.Parameters.Add("@name", SqlDbType.Text).Value = "New Profile";
cmd.Parameters.Add("@path", SqlDbType.Text).Value = "C:\\";
cmd.Parameters.Add("@desc", SqlDbType.Text).Value = "A blank profile.");

And your conn.Close(); is redundant. The using statement will take care of it for you. Under the hood, SqlConnection.Dispose() calls the SqlConnection.Close() method.

Upvotes: 3

Sudhakar Tillapudi
Sudhakar Tillapudi

Reputation: 26209

Problem 1: You are enclosig the parameters (@name, @path, @desc) within single quotes, so that you are passing the values as @name, @path, @desc .

Solution 1: You should not enclose the Parameters within single quotes while using Parameterised queries.

Replace This:

cmd.CommandText = "INSERT INTO Profiles (ProfileName, ProfilePath, 
       ProfileDescription)  VALUES ('@name', '@path', '@desc');";

With This:

cmd.CommandText = "INSERT INTO Profiles 
    (ProfileName, ProfilePath, ProfileDescription) 
                     VALUES (@name, @path, @desc);";

Problem 2: you need to provide both parameter name and its value to the Parameters.AddWithValue() method

Solution 2:

Replace This:

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.";

With This:

cmd.Parameters.AddWithValue("@name","New Profile");
cmd.Parameters.AddWithValue("@path","C:\\");
cmd.Parameters.AddWithValue("@desc","A blank profile.");

Complete Code:

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","New Profile");
         cmd.Parameters.AddWithValue("@path","C:\\");
         cmd.Parameters.AddWithValue("@desc","A blank profile.");
         conn.Open();
         cmd.ExecuteNonQuery();             
        }
        }
    }
    catch(Exception ex) {
        MessageBox.Show(ex.Message, "Error");
    }
}

Upvotes: 1

Jon Skeet
Jon Skeet

Reputation: 1502546

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.

Upvotes: 5

Related Questions