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?
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.