CODEWITHSUNDEEP
c#sqlexceptionado.netsql-server-ce
dinesh
dinesh

Reputation: 3

SqlCeException was unhandled

While trying to update my database, I am getting this exception:

There was an error parsing the query: [ Token line number = 1,Token line offset = 28,Token in error = Group ]

My code is as follows :

private void btnUpdate_Click(object sender, EventArgs e)
{
    con.Open();          
    string strQuery = "UPDATE test_group SET Main Group = '" + comboBox1.Text + "', Sub Group = '" + richTextBox2.Text + "' WHERE Group Id = '" + richTextBox1.Text + "'";

    System.Data.SqlServerCe.SqlCeCommand cmdSelect = new System.Data.SqlServerCe.SqlCeCommand();
    cmdSelect.Connection = con;
    cmdSelect.CommandType = CommandType.Text;
    cmdSelect.CommandText = strQuery;

    cmdSelect.ExecuteNonQuery();
    updatedb();

    con.Close();
}

Plz help me out. Thanks in Advance for your assistance.

Upvotes: 0

Views: 703

Answers (4)

Nitu Bansal
Nitu Bansal

Reputation: 3856

please try this

 string strQuery = "UPDATE test_group SET [Main Group] = '" + comboBox1.Text + "', Sub Group = '" 
  + richTextBox2.Text + "' WHERE Group Id = '" + richTextBox1.Text + "'";

there was a space between Main and Group

Upvotes: -1

Tim Schmelter
Tim Schmelter

Reputation: 460340

GROUP is a reserved keyword, you need to wrap them in brackets. The same is true for columns with white-spaces:

UPDATE test_group SET [Main Group] = @group, [Sub Group] = @subGroup ...

But you should really use sql-parameters to prevent sql-injection.

using (var con = new SqlCeConnection("ConnectionString"))
{ 
    string strQuery = @"UPDATE test_group SET [Main Group] = @group, 
                                              [Sub Group]  = @subGroup 
                        WHERE [Group Id] = @groupID";
    using(var command = new SqlCeCommand(strQuery, con))
    {
        command.Parameters.AddWithValue("@group", comboBox1.Text);
        command.Parameters.AddWithValue("@subGroup", richTextBox2.Text );
        command.Parameters.AddWithValue("@groupID", richTextBox1.Text );
        con.Open();
        int updated = command.ExecuteNonQuery();
    }
}

Note that AddWithValue needs to detect the type from the value, so if Group Id is an int you should use int.Parse first.

Side-note: It is certainly not a good practice to use column names with spaces. It is a pain to always have to use the [ ], isn't it?

Upvotes: 4

Steve
Steve

Reputation: 216363

The main problem are the field names that contains spaces. In this case your field names should be enclosed in square brackets, but you have also another dangerous problem. Concatenating strings to build a sql command is a very bad practice that could lead to a dangerous security problem called Sql Iniection, not to mention the very likely syntax error if one or more of your strings contains a single quote. The correct way to execute a query is through the use of a parameterized query

string strQuery = "UPDATE test_group SET [Main Group] = @grp, [Sub Group] = @sub " + 
                  "WHERE [Group Id] = @grpID";
using(SqlCeCommand cmdSelect = new System.Data.SqlServerCe.SqlCeCommand(strQuery, con))
{
   cmdSelect.Parameters.AddWithValue("@grp", comboBox1.Text);
   cmdSelect.Parameters.AddWithValue("@sub", richTextBox2.Text);
   cmdSelect.Parameters.AddWithValue("@grpID", richTextBox1.Text);
   cmdSelect.ExecuteNonQuery();
   updatedb();
   con.Close();
}

I really suggest you to change that field names removing the spaces (if it possible in the context of your application) because you will have this kind of problems every time you try to use this table

Upvotes: 2

aikixd
aikixd

Reputation: 514

You have spaces in your column names, you should put braces around them: [column name]

Upvotes: 1

Related Questions