+1 to data column using SQL Server Compact 4.0

Question

I am trying to create a simple button, that when clicked, adds 1 to the related column. I use a dropdown box to select the ID, then add 1 to the value. However, I am presented with the error:

A first chance exception of type 'System.Data.SqlServerCe.SqlCeException' occurred in System.Data.SqlServerCe.dll

and it highlights cm.ExecuteNonQuery();

I have gone through several attempts at this but it's getting me a little confused as to why I can't simply run the SQL statement.

Here is the code

private void button2_Click(object sender, EventArgs e) {
    try {
        SqlCeCommand cm = new SqlCeCommand("UPDATE fixedBugs SET Success = Success + 1 WHERE Fixed_ID = '" + comboBox1.Text, mySqlConnection);
        cm.ExecuteNonQuery();
    } catch (SqlCeException) {
        MessageBox.Show("Error");
    }
}

`'" + comboBox1.Text` -- you have an extra apostrophe or you need a closing one -- depends on the data type. Also look into using parameterized queries -- much safer. — sgeddes, Jan 08 '16 at 22:56
Thankyou - this has helped a lot! Still trying to get it to input data into the database though. — Matt Murphy, Jan 08 '16 at 23:02

score 2 · Answer 1 · edited May 23 '17 at 12:07

2

Your command has a opening apostrophe which is not being closed. This should fix it.

SqlCeCommand cm = new SqlCeCommand("UPDATE fixedBugs SET Success = Success + 1 WHERE Fixed_ID = '" + comboBox1.Text + "'", mySqlConnection);

But that's a security issue since the user can manage to add extra commands to your query, which could ruin your entire database.

This is a better solution since using parameters is more safe.

SqlCeCommand cm = new SqlCeCommand("UPDATE fixedBugs SET Success = Success + 1 WHERE Fixed_ID = @fixedid;", mySqlConnection);
cm.Parameters.AddWithValue("@fixedid", comboBox1.Text);

This will prevent future headaches.

This question has better detailed answers that may help enlighten your mind...

edited May 23 '17 at 12:07

Community

1
1

answered Jan 08 '16 at 22:57

Phiter

14,570
14
50
84

This is a bad answer. What about security issue? – Hamlet Hakobyan Jan 08 '16 at 22:58
2

Well I'm pointing out his error, the security issue is something I can add to the answer but he is asking about the error. – Phiter Jan 08 '16 at 22:59
This has solved the error - however nothing is being input into the database – Matt Murphy Jan 08 '16 at 23:00
Yes, but not working code is better than working one with security issue ;) – Hamlet Hakobyan Jan 08 '16 at 23:00
I'll improve my answer :) – Phiter Jan 08 '16 at 23:01
As you can see I wrote `bad answer` not `wrong answer`. So improving can make it better ;) – Hamlet Hakobyan Jan 08 '16 at 23:04
Nevermind - I didn't have a default value set on my database so it was trying to +1 to null, my bad. I will select this answer - thankyou Phiter – Matt Murphy Jan 08 '16 at 23:05
@MattMurphy, I'm glad that I have helped but I'm changing my answer for a better one so hang in there and try using my next solution – Phiter Jan 08 '16 at 23:07

score 2 · Answer 2 · edited Jan 08 '16 at 23:05

2

"UPDATE fixedBugs SET Success = Success + 1 WHERE Fixed_ID = '" + comboBox1.Text + "'"

Need to close the string parameter with ' in query?

edited Jan 08 '16 at 23:05

myselfmiqdad

2,518
2
18
33

answered Jan 08 '16 at 22:58

nee21

189
2
2
13

Kosala W · Answer 3 · 2016-01-09T00:01:08.107

You need to think about below things;

User must select a value.
Security

Dispose the command after using it.

    string selectedValue = comboBox1.Text;
    if (string.IsNullOrEmpty(selectedValue))
    {
        MessageBox.Show("Please select something");
        return;
    }
    string sql = "UPDATE fixedBugs SET Success = ISNULL(Success,0) + 1 WHERE Fixed_ID = @selectedValue";
    try
    {
        using (SqlCeCommand cm = new SqlCeCommand(sql, mySqlConnection))
        {
            SqlCeParameter param = new SqlCeParameter("@selectedvalue", SqlDbType.NText);
            cm.Parameters.Add(param);
            cm.Parameters["@selectedvalue"].Size = 50;
            cm.Parameters["@selectedvalue"].Value = selectedValue.Trim();
            cm.ExecuteNonQuery();
        }
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
    }

PS: Code is not tested.

+1 to data column using SQL Server Compact 4.0

3 Answers3