multiple Parameters - Syntax error in string in query expression: VS 2010 with MS-Access 2003

Question

I am receiving

OleDBException was unhandled error of "Syntax error (missing operator) in query
expression '(StudentID = 100' OR StudentName = 'Nick' OR StudentCNCI = '78894452)Bob'."

    private void btnFind_Click(object sender, EventArgs e)
    {

        string title = textBox1.Text.ToString();
        string queryString = "SELECT * FROM Students WHERE (StudentID = " + StudIDTb.Text.ToString() + "' OR StudentName = '" + StudNameTb.Text.ToString() + "' OR StudentCNCI = '" + StudCNCITb.Text.ToString() + ")" + title;

        OleDbCommand command = new OleDbCommand();
        command.CommandText = queryString;

        command.Connection = myCon;
        myCon.Open();

        OleDbDataReader dr = command.ExecuteReader(); // error pointing here
        while (dr.Read())
        {
            StudIDTb.Text += String.Format("StudentID: {0}\n", dr["StudentID"].ToString());
            StudNameTb.Text += String.Format("StudentName: {0}\n", dr["StudentName"].ToString());
            StudCNCITb.Text += String.Format("StudentCNIC: {0}\n", dr["StudentCNIC"].ToString());
            StudDOBTb.Text += String.Format("StudentDOB: {0}\n", dr["StudentDOB"].ToString());
        }
        myCon.Close();

     }

I have also tried...

string queryString = "SELECT * FROM Students WHERE (StudentID = " + StudIDTb.Text + "' OR StudentName = '" + StudNameTb.Text + "' OR StudentCNCI = '" + StudCNCITb.Text + ")" + title;

I don't want to give you wrong impression I am "lazy" but I am assuming I am getting this error because I have query it incorrectly or I have made a typo error or could it be something else. Please can someone help me, thanks in advance.

ps I know I am getting criticism for not using parameterized queries. I will change it once I got the basic right. I know a lot of similar questions have been asked here but I still can't get it right.

UPDATE 1 I have changed it to

"SELECT * FROM Students WHERE StudentID = " + StudIDTb.Text + " OR StudentName = '" + StudNameTb.Text + "', OR StudentCNCI = '" + StudCNCITb.Text + ")";

I am now receiving error of...

Syntax error (comma) in query expression

I am looking into it

Update 2

string queryString = "SELECT * FROM Students WHERE StudentID = " + StudIDTb.Text + "' OR StudentName = '" + StudNameTb.Text + "' OR StudentCNCI = '" + StudCNCITb.Text + "'";

Receiving the same error.

Looking into it

Update 3 If it can't be solved I do it the way it should be, using parameterized queries as highly recommended if it means to solve the problem and probably easy to spot any problems with the code

and what is this "+ title" supposed to mean in the end of your query string? — Alex P., Mar 10 '13 at 15:01
The error string is telling you exactly what the problem is, " '78894452)Bob'". And calling ToString on Text properties make no sense at all. — PhoenixReborn, Mar 10 '13 at 15:03
Good catch. Parameterize your query and that error goes away. — Mike C., Mar 10 '13 at 15:11
Discard the comma from this piece: `"', OR StudentCNCI = '"` — HansUp, Mar 10 '13 at 15:32
More likely a *different* error. In *Update 2* you now have an unbalanced single quote in this piece: `WHERE StudentID = " + StudIDTb.Text + "' OR StudentName` — HansUp, Mar 10 '13 at 16:03
Can you find a c# method to dump `queryString` to the console or a text file ... somewhere you can examine the statement you're asking the db engine to execute? Ideally, if you can't spot the error, copy the statement text and paste it into Design View of a new Access query for troubleshooting. — HansUp, Mar 10 '13 at 16:09
@HansUp - Good idea. Pls bear with me do you mean something like....SELECT * FROM Students WHERE StudentID — bucketblast, Mar 10 '13 at 16:10
I meant find something in c# to allow you to examine the completed SQL statement you're asking the db engine to execute, instead of relying on your imagination to guess what that statement looks like. In VBA we could simply do `Debug.Print queryString` I don't know what methods c# provides. — HansUp, Mar 10 '13 at 16:25
@HansUp - googled and found a result here....http://stackoverflow.com/questions/265192/get-the-generated-sql-statement-from-a-sqlcommand-object. As updated 3 I do it the proper what that way we will definitely know. Thank you very much for your time most appreciated — bucketblast, Mar 10 '13 at 17:32

Mike C. · Accepted Answer · 2013-03-10T15:15:28.797

It's telling you that your query is invalid. You have this

SELECT * 
FROM Students
WHERE (StudentID='a' OR StudentName='b' or StudentCNCI='c')Bob

It's not liking that Bob on the end and it's not clear why you need it. Explain what your intent is there, or just get rid of it as it doesn't appear to be necessary for your query.

string queryString = "SELECT * FROM Students WHERE StudentID = '" + 
  StudIDTb.Text + "' OR StudentName = '" + StudNameTb.Text + 
  "' OR StudentCNCI = '" + StudCNCITb.Text + "'";

As you mention in your post, you need to parameterize your query also. Let us know if you need help with that, but it is pretty straightforward, and a common post on here, so you already have plenty of resources to figure that out.

EDIT: If you like, you can remove the parenthesis. You'd really only need then if you were going to do a subquery or some such thing. They won't hurt your query, they're just not really necessary.

SELECT * 
FROM Students
WHERE StudentID='a' OR StudentName='b' or StudentCNCI='c'

Also, from other comments, you actually have multiple quote mismatches (one at the beginning and another at the end).

Mike C - most appreciated but I am getting the same error without Bob — bucketblast, Mar 10 '13 at 15:07

multiple Parameters - Syntax error in string in query expression: VS 2010 with MS-Access 2003

1 Answers1