syntax error missing operator in query expression

Question

When I run the below query, I get syntax error in query expression.

private void button8_Click(object sender, EventArgs e)
{
    connection.Open();
    OleDbCommand command = new OleDbCommand();
    command.Connection = connection;
    string query1 = "UPDATE Points SET PNTS = 
                    (case when EmpName = '" + comboBox1.Text + 
                    "' then  '" + label15.Text + "' when EmpName = '" +
                    comboBox2.Text + "' then '" + label16.Text + 
                    "' when EmpName = '" + comboBox3.Text + "' then '" +
                    label17.Text + "' end) WHERE EmpName in ('" +
                    comboBox1.Text + "', '" + comboBox2.Text + "', '" +
                    comboBox3.Text + "')";

    command.CommandText = query1;
    command.ExecuteNonQuery();
    connection.Close();
}

The error is:

Syntax error (missing operator) in query expression '(case when EmpName = 'Sam' then '5.6' when EmpName = 'shane' then '1.6' when EmpName = 'Mike' then '0.8' end)'.

What database is this trying to update? It definitely doesn't look like the SQL for Case/When in SQL Server. (I'd strongly suggest building parameterized SQL instead of constructing the SQL using the input directly, too...) — Jon Skeet
– Jon Skeet, Commented May 29, 2015 at 16:10
Your code is extremely vulnerable to SQL injection attacks. For the sake of your users, please use parameterized queries. — p.s.w.g
– p.s.w.g, Commented May 29, 2015 at 16:10

collapsar · Accepted Answer · 2015-05-29 16:48:46Z

You have a syntax error in your sql (ms Access does not feature case expressions). Rewrite the source line as follows:

string query1 =
      "UPDATE Points SET PNTS = "
    + "SWITCH ("
        + "  EmpName = '" + comboBox1.Text + "', '" + label15.Text + "'"
        + ", EmpName = '" + comboBox2.Text + "', '" + label16.Text + "'"
        + ", EmpName = '" + comboBox3.Text + "', '" + label17.Text + "'"
        + ", true, ''"
    + ")"
    + " WHERE EmpName in ('" + comboBox1.Text + "', '" + comboBox2.Text + "', '" + comboBox3.Text + "')"
;

To counter the risk of sql injection, consider to use parametrized sql as some commentors have suggested:

OleDbParameter parameter;

// The n-th generic placeholder in the sql string will be set to the n-th registered Parameter Value.
// '12' represents the data size, adjustment may be needed ( can possibly be dropped altogether ) 
parameter = command.Parameters.Add("@InputParm", OleDbType.VarChar, 12);
parameter.Value = comboBox1.Text;
parameter = command.Parameters.Add("@InputParm", OleDbType.VarChar, 12);
parameter.Value = label15.Text;
parameter = command.Parameters.Add("@InputParm", OleDbType.VarChar, 12);
parameter.Value = comboBox2.Text;
parameter = command.Parameters.Add("@InputParm", OleDbType.VarChar, 12);
parameter.Value = label16.Text;
parameter = command.Parameters.Add("@InputParm", OleDbType.VarChar, 12);
parameter.Value = comboBox3.Text;
parameter = command.Parameters.Add("@InputParm", OleDbType.VarChar, 12);
parameter.Value = label17.Text;
parameter = command.Parameters.Add("@InputParm", OleDbType.VarChar, 12);
parameter.Value = comboBox1.Text;
parameter = command.Parameters.Add("@InputParm", OleDbType.VarChar, 12);
parameter.Value = comboBox2.Text;
parameter = command.Parameters.Add("@InputParm", OleDbType.VarChar, 12);
parameter.Value = comboBox3.Text;

string query1 =
      "UPDATE Points SET PNTS = "
    + "SWITCH ("
        + "  EmpName = ?, ?"
        + "  EmpName = ?, ?"
        + "  EmpName = ?, ?"
        + ", true, ''"
    + ")"
    + " WHERE EmpName in (?, ?, ?)"
;

Caveat

Code untested, derived from the docs.

How can I add label15.Text,label16.Text and label17.Text values to current database value and count total?

coder231 · Accepted Answer · 2015-05-29 16:24:53Z

0

(case when EmpName = 'Sam' then '5.6'.....)

You dont have to use quotation for the numerical value. Is "PNTS" in the database a string ? If not try removing the quotation for those values.

answered May 29, 2015 at 16:24

coder231

4632 silver badges15 bronze badges

Collectives™ on Stack Overflow

syntax error missing operator in query expression

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related