SQL query in C# (System.Data.OleDb.OleDbException: 'Syntax error (missing operator) in query expression)

Question

OleDbCommand commandtwo = new OleDbCommand("SELECT * from tblShowings WHERE ShowFilmID = " + filmID.Text + " AND Showdate = " + date.Text + " AND Showtime = " + time.Text + "", con);

What is wrong with my SQL query? I keep getting this error:

System.Data.OleDb.OleDbException: 'Syntax error (missing operator) in query expression 'ShowFilmID = 1111 AND Showdate = 67/87/9999 AND Showtime = 10:00'

Well clearly your strings in the query need quotes around them to be valid SQL. But as @LukaszSzozda says, use parameters instead of string concatenation and you will solve a lot of other problems at the same time. — iakobski
@steve i was just testing a random date to test if the sql query would work — maryam2oo3

vendettamit vendettamit · Accepted Answer · 2019-12-24T15:51:37

Your current code is vulnerable to Sql Injections. You should be using parameterized query to avoid sql injections and handling of value types correctly.

The error in your code is because you are missing ' single quotes for string value types.

"ShowFilmID = '" + date.Text + "'" + ...

Here's an example how you should be using parameterized query:

OleDbCommand command = new OleDbCommand(
  "SELECT * from tblShowings WHERE ShowFilmID = ? AND Showdate = ? AND Showtime = ?", con);
OleDbParameterCollection paramCollection = command.Parameters;
OleDbParameter myParm = paramCollection.Add(
        new OleDbParameter("ShowFilmID", filmID.Text),
        new OleDbParameter("Showdate", date.Text),
        new OleDbParameter("Showtime", time.Text));

SQL query in C# (System.Data.OleDb.OleDbException: 'Syntax error (missing operator) in query expression)

2 Answers