Why won't C# accept a (seemingly) perfectly good Sql Server CE Query?
- by VoidKing
By perfectly good sql query, I mean to say that, inside WebMatrix, if I execute the following query, it works to perfection:
SELECT page AS location, (len(page) - len(replace(UPPER(page), UPPER('o'), ''))) / len('o') AS occurences, 'pageSettings' AS tableName FROM PageSettings WHERE page LIKE '%o%'
UNION
SELECT pageTitle AS location, (len(pageTitle) - len(replace(UPPER(pageTitle), UPPER('o'), ''))) / len('o') AS occurences, 'ExternalSecondaryPages' AS tableName FROM ExternalSecondaryPages WHERE pageTitle LIKE '%o%'
UNION
SELECT eventTitle AS location, (len(eventTitle) - len(replace(UPPER(eventTitle), UPPER('o'), ''))) / len('o') AS occurences, 'MainStreetEvents' AS tableName FROM MainStreetEvents WHERE eventTitle LIKE '%o%'
Here i am using 'o' as a static search string to search upon. No problem, but not exeactly very dynamic.
Now, when I write this query as a string in C# and as I think it should be (and even as I have done before) I get a server-side error indicating that the string was not in the correct format. Here is a pic of that error:
And (although I am only testing the output, should I get it to quit erring), here is the actual C# (i.e., the .cshtml) page that queries the database:
@{
Layout = "~/Layouts/_secondaryMainLayout.cshtml";
var db = Database.Open("Content");
string searchText = Request.Unvalidated["searchText"];
string selectQueryString = "SELECT page AS location, (len(page) - len(replace(UPPER(page), UPPER(@0), ''))) / len(@0) AS occurences, 'pageSettings' AS tableName FROM PageSettings WHERE page LIKE '%' + @0 + '%' ";
selectQueryString += "UNION ";
selectQueryString += "SELECT pageTitle AS location, (len(pageTitle) - len(replace(UPPER(pageTitle), UPPER(@0), ''))) / len(@0) AS occurences, 'ExternalSecondaryPages' AS tableName FROM ExternalSecondaryPages WHERE pageTitle LIKE '%' + @0 + '%' ";
selectQueryString += "UNION ";
selectQueryString += "SELECT eventTitle AS location, (len(eventTitle) - len(replace(UPPER(eventTitle), UPPER(@0), ''))) / len(@0) AS occurences, 'MainStreetEvents' AS tableName FROM MainStreetEvents WHERE eventTitle LIKE '%' + @0 + '%'";
@:beginning <br/>
foreach (var row in db.Query(selectQueryString, searchText))
{
@:entry
@:@row.location
@:@row.occurences
@:@row.tableName
<br/>
}
}
Since it is erring on the foreach (var row in db.Query(selectQueryString, searchText)) line, that heavily suggests that something is wrong with my query, however, everything seems right to me about the syntax here and it even executes to perfection if I query the database (mind you, un-parameterized) directly.
Logically, I would assume that I have erred somewhere with the syntax involved in parameterizing this query, however, my double and triple checking (as well as, my past experience at doing this) insists that everything looks fine here.
Have I messed up the syntax involved with parameterizing this query, or is something else at play here that I am overlooking?
I know I can tell you, for sure, as it has been previously tested, that the value I am getting from the query string is, indeed, what I would expect it to be, but as there really isn't much else on the .cshtml page yet, that is about all I can tell you.
