Getting a syntax error (missing operator) in query expression in C#

Question

I am writing a code and getting a syntax error that I dont know from where that comes. This is the code I am using:

string u = "select uniqcode from login where users='" + textBoxX1.Text + "' ";

and this is the error I am getting:

"Syntax error (missing operator) in query expression ''select uniqcode from login where users='someuser' ''." (System.Data.OleDb.OleDbException)A System.Data.OleDb.OleDbException was thrown: "Syntax error (missing operator) in query expression ''select uniqcode from login where users='someuser' ''."

You should bind parameters instead of concatenating SQL string, because user can write in textbox sth like '; DROP TABLE ... ;-- — Lukasz Szozda
– Lukasz Szozda, Commented Sep 18, 2015 at 10:21
Why is the column users plural? Do you store multiple users comma separated?? — Tim Schmelter
– Tim Schmelter, Commented Sep 18, 2015 at 10:22
@AfshinHaghighat Did you try something u = "select uniqcode from login where users=?"; and then u.Parameters.Add("@users", OleDbType.VarChar).value = textBoxX1.Text; — Amnesh Goel
– Amnesh Goel, Commented Sep 18, 2015 at 10:32

Rakesh · Accepted Answer · 2015-09-18 10:54:55Z

1

It might be possible that textbox text is containing single quote ('). If yes then replace single quote (') by two single quotes ('')

Otherwise, try to execute it using sql parameters.

answered Sep 18, 2015 at 10:54

Rakesh

2281 silver badge6 bronze badges

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

1 Comment

Jan Köhler Over a year ago

No, it the textbox' value doesn't contain any quotes as you can see from the OP's description: select uniqcode from login where users='someuser' . The quotes are part of the query... I think this should be rather a comment than an answer as it won't help the OP. You tell him to use SQL Parameters; then show a short example as he obviously has no clue how to use them.

Rajesh · Accepted Answer · 2015-09-18 10:31:51Z

0

string u = "select login.uniqcode from [login] where users='" + textBoxX1.Text.Trim() + "'";

answered Sep 18, 2015 at 10:31

Rajesh

2081 silver badge4 bronze badges

2 Comments

Afshin Over a year ago

still the same error {"Syntax error (missing operator) in query expression ''select login.uniqcode from [login] where users='someuser'''."}

Amnesh Goel Over a year ago

@AfshinHaghighat if you will post complete code then it will be easy to figure out the problem..

Community · Accepted Answer · 2017-05-23 11:53:06Z

0

Since your error represent that you are using OleDb connection then you may use following code. This will avoid SQL Injection as well. However you may need to work a bit to following piece into your code.

u = "select uniqcode from login where users=?"; 

u.Parameters.Add("@users", OleDbType.VarChar).value = textBoxX1.Text;

You may see examples here and here

edited May 23, 2017 at 11:53

CommunityBot

11 silver badge

answered Sep 18, 2015 at 11:08

Amnesh Goel

2,6553 gold badges35 silver badges49 bronze badges

2 Comments

Afshin Over a year ago

Error 1 'string' does not contain a definition for 'Parameters' and no extension method 'Parameters' accepting a first argument of type 'string' could be found

Amnesh Goel Over a year ago

@AfshinHaghighat Ofcourse it won't work. I wrote it as well that you would need to work a bit with my code.. and on comments section.. I asked you to show your more code so that we can help.. Can you show more code around this line?

Fandango68 · Accepted Answer · 2018-04-25 07:05:06Z

You need to firstly read and understand there are SQL syntax limitations in OleDB.

"A single quote must be escaped with another single quote."

But really, forget single quotes.

Read more about using OleDB here. It's ancient technology anyway, so I would get away from OleDB and have your database ported over to SQL Server or MySQL.

However, what you may need is something like this...

try
{
    connw.Open();
    OleDbCommand command;
    command = new OleDbCommand(
        "SELECT *" +
        "FROM tableA WHERE Deliveries.EmployeeID = ?, Deliveries.FIN = ?, Deliveries.TodaysOrders = ? , connw);
    command.Parameters.Add(new OleDbParameter("@EMPID", Convert.ToDecimal(empsplitIt[1])));
    command.Parameters.Add(new OleDbParameter("@FIN", truckSplit[1].ToString()));
    command.Parameters.Add(new OleDbParameter("@TodaysOrder", "R"));
    catchReturnedRows = command.ExecuteNonQuery();//Commit   
    connw.Close();

}
catch (OleDbException exception)
{
    MessageBox.Show(exception.Message, "OleDb Exception");
}

Golden rule of database coding, is never pass a variable directly into the SQL statement like you've done above. That is opening yourself for SQL Injection big time.

Collectives™ on Stack Overflow

Getting a syntax error (missing operator) in query expression in C#

4 Answers 4

1 Comment

2 Comments

2 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

2 Comments

2 Comments

Comments

Linked

Related