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
– iakobski, Commented Dec 24, 2019 at 15:43
@steve i was just testing a random date to test if the sql query would work — goomba
– goomba, Commented Dec 24, 2019 at 15:51

vendettamit · Accepted Answer · 2019-12-24 16:01:36Z

1

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));

edited Dec 24, 2019 at 16:01

answered Dec 24, 2019 at 15:51

vendettamit

14.7k2 gold badges36 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Steve Over a year ago

What makes you think that ShowFilmID is a string field and requires quotes around the values to compare?

vendettamit Over a year ago

Thanks! @Steve fixed it. Used date field for illustration.

bbcompent1 · Accepted Answer · 2019-12-24 17:39:55Z

0

Just a point to ponder here from a security standpoint. You need to ensure your data input/output is validated/sanitized to avoid exploit. The ideal process is stored procedure and parameterized values. If that is not possible, ensure that you have encoded your values so to avoid SQL Injection. Just my 2 cents worth.

answered Dec 24, 2019 at 17:39

bbcompent1

4941 gold badge10 silver badges29 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related