C# OleDb select query throws missing operator exception

Question

I'm currently connecting to and working with a MS Access database. I can do a generic select * query fine, so I am connected to the db, but when I try to select using parameters it throws a missing operator exception.

string selectStatement = "Select ID from pages where page_title = ? limit 1";
string title = Request.QueryString["pagetitle"];

OleDbCommand selectCommand = new OleDbCommand(selectStatement, conn);
selectCommand.Parameters.Add("@p1",OleDbType.VarChar);
selectCommand.Parameters["@p1"].Value = System.Web.HttpUtility.UrlDecode(title);
OleDbDataReader selectResult = selectCommand.ExecuteReader();

The error I get is on the ExecuteReader line:

Exception Details: System.Data.OleDb.OleDbException: Syntax error (missing operator) in query expression 'page_title = ? limit 1'.

I've tried using @p1 inside the query, as well as the current ?. I've tried adding the parameters different ways, including removing the @ in the parameter name. Nothing seems to work. Can someone point me in the right direction?

Alex · Accepted Answer · 2011-03-08 19:04:59Z

4

AFAIK, there is no LIMIT clause in MS Access.

And, parameters should be named, @p1 in your case, instead of ?

I haven't worked with Access for years, so I might be wrong, but instead of LIMIT, try this:

Select TOP 1 ID from pages where page_title = @p1

Also, if appropriate, consider this advice:

MS Access isn't quite the right DBMS to handle websites (I suspect in your case it's a website). For an alternative file-based database management systems, check SQLite and FirebirdSQL. Both have a lot of tools you can use for GUI, like SQLite Maestro, and respectively IBExpert.
Queries are more flexible in those DBMS.

edited Mar 8, 2011 at 19:04

answered Mar 8, 2011 at 18:59

Alex

14.7k5 gold badges45 silver badges60 bronze badges

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

4 Comments

MishieMoo Over a year ago

Legit? That stinks xD But this solves the question. Thanks for the quick response!

MishieMoo Over a year ago

Also, this isn't for a website. I'm doing some local automated tests, so no one but me will ever see this. I generally work in PHP/MySQL but I can't install those on this machine, so this whole thing is kind of kludgey (took me 4+ hours to finish this in C# when I could have done it in 20 mins!). I also can't install any other DBMSes on this machine. So my options were limited =)

Alex Over a year ago

Still, if you ever need something like this again, try FirebirdSQL. No install needed, just a pile of libraries copied in your application folder. And you'll be able to do queries like SELECT FIRST 10 SKIP 20 * FROM table. You can't "skip" rows in a lot of DBMS (Access is one of them), there is always a more complex way of placing this in the WHERE clause, like ... WHERE rownum between 21 and 30 or something.

David-W-Fenton Over a year ago

Note that Jet/ACE TOP N will return ties, so choose your ORDER BY appropriately. Of course, TOP N is meaningless without an ORDER BY.

Collectives™ on Stack Overflow

C# OleDb select query throws missing operator exception

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related