SQL was expecting something that it could convert into a single integer as the value of @0 parameter.
You provided it a Dynamic object (a table query result, which is a series of "row-like" objects). You need to iterate through the Dynamic object and create a single string variable which contains each of the viewIds in a comma-separated fashion.
My scenario is slightly different as there is no user interaction and as such no IsPost.
Here is Mike's code:
var db = Database.Open("Books");
var categories = db.Query("Select CategoryId, Category FROM Categories");
if(IsPost){
var temp = Request["categoryId"].Split(new[]{','}, StringSplitOptions.RemoveEmptyEntries);
var parms = temp.Select((s, i) => "@" + i.ToString()).ToArray();
var inclause = string.Join(",", parms);
var sql = "SELECT Category FROM Categories WHERE CategoryId IN ({0})";
categories = db.Query(String.Format(sql, inclause), temp);
My 'temp' variable is "23,37"
When I feed that to the parms statement it leaves me with parms = @0 @1 @2 @3 @4
Can someone suggest how to modify the code so that it only produces @0 @1?
One extra complication is that my temp variable could be "123,37,984" as an example.
var input = "23,37";
var temp = input.Split(new[]{','}, StringSplitOptions.RemoveEmptyEntries);
var parms = temp.Select((s, i) => "@" + i.ToString()).ToArray();
var inclause = string.Join(",", parms);
var sql = ""SELECT * FROM Trips WHERE ViewId IN ({0})";
var views = db.Query(String.Format(sql, inclause), temp);
Or more simply, you can take the advice offered later in the article and create a file of extension methods to go into App_Code, then you code will simply be:
var input = "23,37";
var sql = ""SELECT * FROM Trips WHERE ViewId IN ({0})";
var views = db.QueryIn(sql, input);
With a little bit of effort (and assistance from the forum) I could probably understand the 'untidy' code and with some documentation I could probably figure it out again in a few months.
The helper is seriously above my comprehension level and I could potentially spend the next few days staring at it and asking all sorts of dumb questions here.
I am minimizing initial effort and the anticipated amount of help I need, and maximizing maintainability.
But if you are telling me that the untidy code cannot be adapted, then the helper is the way to go.
wavemaster
Participant
1351 Points
1162 Posts
an array as input to a query plus error message
Dec 29, 2012 02:45 AM|LINK
Suppose the result of a query of one table is Show= 23, 57, 14, 31
Now I need to query a table, select all columns where ViewId = Show
Here is my query:
var views = db.Query("SELECT * FROM Trips WHERE ViewId = @0", Show);The error message is " cannot implicitly convert 'System.Collections.Generic,IEnumberable <dynamic>' to 'string'
Is it even possible to have a query with an array as input?
What is the error message about?
TIA
Robert
GmGregori
Contributor
5574 Points
749 Posts
Re: an array as input to a query plus error message
Dec 29, 2012 07:35 AM|LINK
Look at this Mike's article: WebMatrix - Database Helpers for IN Clauses.
It shows how to use a comma separated string as parameter of an in clause.
rrrsr7205
Participant
1308 Points
316 Posts
Re: an array as input to a query plus error message
Dec 29, 2012 01:06 PM|LINK
SQL was expecting something that it could convert into a single integer as the value of @0 parameter.
You provided it a Dynamic object (a table query result, which is a series of "row-like" objects). You need to iterate through the Dynamic object and create a single string variable which contains each of the viewIds in a comma-separated fashion.
wavemaster
Participant
1351 Points
1162 Posts
Re: an array as input to a query plus error message
Dec 29, 2012 04:53 PM|LINK
My scenario is slightly different as there is no user interaction and as such no IsPost.
Here is Mike's code:
My 'temp' variable is "23,37"
When I feed that to the parms statement it leaves me with parms = @0 @1 @2 @3 @4
Can someone suggest how to modify the code so that it only produces @0 @1?
One extra complication is that my temp variable could be "123,37,984" as an example.
TIA, Robert
Mikesdotnett...
All-Star
155659 Points
19987 Posts
Moderator
MVP
Re: an array as input to a query plus error message
Dec 29, 2012 05:49 PM|LINK
var input = "23,37"; var temp = input.Split(new[]{','}, StringSplitOptions.RemoveEmptyEntries); var parms = temp.Select((s, i) => "@" + i.ToString()).ToArray(); var inclause = string.Join(",", parms); var sql = ""SELECT * FROM Trips WHERE ViewId IN ({0})"; var views = db.Query(String.Format(sql, inclause), temp);Or more simply, you can take the advice offered later in the article and create a file of extension methods to go into App_Code, then you code will simply be:
var input = "23,37"; var sql = ""SELECT * FROM Trips WHERE ViewId IN ({0})"; var views = db.QueryIn(sql, input);Web Pages CMS | My Site | Twitter
wavemaster
Participant
1351 Points
1162 Posts
Re: an array as input to a query plus error message
Dec 29, 2012 06:15 PM|LINK
I am making a trade of.
With a little bit of effort (and assistance from the forum) I could probably understand the 'untidy' code and with some documentation I could probably figure it out again in a few months.
The helper is seriously above my comprehension level and I could potentially spend the next few days staring at it and asking all sorts of dumb questions here.
I am minimizing initial effort and the anticipated amount of help I need, and maximizing maintainability.
But if you are telling me that the untidy code cannot be adapted, then the helper is the way to go.
wavemaster
Participant
1351 Points
1162 Posts
Re: an array as input to a query plus error message
Dec 29, 2012 07:27 PM|LINK
Ok, a little bit of progress here.
trim2 = "32, 37"
inclause = "@0, @1"
var sql = "SELECT * FROM Trips WHERE TripId IN ({0})"; var trips = db.Query(String.Format(sql, inclause), trim2);This gives an error message:
An SqlCeParameter with ParameterName '1' is not contained by this SqlCeParameterCollection.
A little test, which works fine:
var viewed = db.Query("SELECT * FROM Trips WHERE TripId IN (@0, @1)", 23, 37);This produces the desired results.
Mikesdotnett...
All-Star
155659 Points
19987 Posts
Moderator
MVP
Re: an array as input to a query plus error message
Dec 29, 2012 08:01 PM|LINK
Why didn't you try either of the suggestions I posted? That isn't in either of them.
Web Pages CMS | My Site | Twitter
wavemaster
Participant
1351 Points
1162 Posts
Re: an array as input to a query plus error message
Dec 29, 2012 10:40 PM|LINK
My apologies, I completely missed the improvements you made to that code section.
With those changes the code now dynamically sets inclause and temp. Nice.
I want to read up on .Select(s, i). What would I search for?
Thanks as always.
Mikesdotnett...
All-Star
155659 Points
19987 Posts
Moderator
MVP
Re: an array as input to a query plus error message
Dec 30, 2012 08:29 AM|LINK
Select<TSource, TResult>(IEnumerable<TSource>, Func<TSource, Int32, TResult>)
Projects each element of a sequence into a new form by incorporating the element's index.
Web Pages CMS | My Site | Twitter