Random query

rashmi kc

I am doing Randomly selecting the questions , when i select from the chapters from the checkboxliat only from that chapters all the questions should display in C# windows forms
using access .

I am getting error like this

Syntax error (missing operator) in query expression 'where chaptername='Electromagnetic Induction''.

and my query is

" SELECT top 5 questions FROM [tblques] ORDER BY Rnd(quescode) where
chaptername=' " + checkedListBox1.SelectedItem + " ' ", con);

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Vulpes14y ago
Accepted answer
Assuming we're right that it's an embedded single quote that's causing the problem (it's difficult to see what else might be wrong), then I agree with Jignesh that using a parameter would be the best way to solve it.

However, another way to solve it is to 'escape' the single quote by doubling it:

string temp = checkedListBox1.SelectedItem.ToString().Replace("'", "''");

The query string then becomes:

"SELECT top 5 questions FROM [tblques] ORDER BY Rnd(quescode) where
chaptername='" + temp + "'"

If it still doesn't work, then the problem must lie elsewhere.
Jignesh Trivedi14y ago
hi Rashmi,

Example :If our text is "Electomagnetic Induction's Principles"
so our final query (which run on SQL server) become

SELECT top 5 questions FROM [tblques] ORDER BY Rnd(quescode) where
chaptername='Electomagnetic Induction's Principles'

right? so now our query is braek from 's and sql server consider only one part (Electomagnetic Induction) so error is comes.

hope you understand now.
rashmi kc14y ago
I didnt understood can u just briefly explain me

can i use my query like this ...

" SELECT top 5 questions FROM [tblques] ORDER BY Rnd(quescode) where
chaptername=' " + checkedListBox1.SelectedItem + " ' ", con);

Syntax error (missing operator) in query expression

ths s the error
Jignesh Trivedi14y ago
hi Rashmi,

I agree with Vulpes.
In this case you can use parameterize query.

Please refer

http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbparameter%28v=vs.80%29.aspx
http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbparameter.aspx\

hope this will help you.
Vulpes14y ago
Do any items in the CheckedlListBox contain embedded single quotes?

For example:

Electomagnetic Induction's Principles

If so, then Access will read the embedded quote as the end of the string and the remaining characters will generate an error.