Conversion failed when converting from a character string to uniqueidentifier.
I have a Gridview filtered from a Dropdownlist
i use the following 'Where' clause in my SQL query string. If i run the page i get the above error. If i exclude either from the string it works.
where @RoleId=-1 OR (vw_aspnet_UsersInRoles.RoleId=@RoleID)
The parameters are obtained from dropdownlist of values bound to another sqldatasource including a static appended item.
<asp:ListItemText="--
ALL --"Value="-1"></asp:ListItem>
Member
288 Points
886 Posts
Query error occurs when filter SQL query using Uniqueidentifier variable type with a static value
Mar 10, 2014 09:52 AM|peterthegreat|LINK
I have a Gridview filtered from a Dropdownlist
i use the following 'Where' clause in my SQL query string. If i run the page i get the above error. If i exclude either from the string it works.
where @RoleId=-1 OR (vw_aspnet_UsersInRoles.RoleId=@RoleID)
The parameters are obtained from dropdownlist of values bound to another sqldatasource including a static appended item.
<asp:ListItem Text="-- ALL --" Value="-1" ></asp:ListItem>
Contributor
5590 Points
1297 Posts
Re: Query error occurs when filter SQL query using Uniqueidentifier variable type with a static v...
Mar 10, 2014 10:42 AM|dotnetzoom|LINK
-1 is not a valid Uniqueidentifier. You would need a case statement to check if ALL is selected.
This is sort of a query like 1=1 and potentially gets you all roles if you do not append other valid conditions.
Member
288 Points
886 Posts
Re: Query error occurs when filter SQL query using Uniqueidentifier variable type with a static v...
Mar 10, 2014 01:43 PM|peterthegreat|LINK
That is a brilliant answer. It works !
So if @RoleID =-1 selected, then things being equal (vw_aspnet_UsersInRoles.RoleID =vw_aspnet_UsersInRoles.RoleId) returns all records.
What is actually comparing, values ?
Thanks
Contributor
5590 Points
1297 Posts
Re: Query error occurs when filter SQL query using Uniqueidentifier variable type with a static v...
Mar 10, 2014 01:59 PM|dotnetzoom|LINK
Yes, when RoleID is -1, where clause will compare value to itself.
Member
288 Points
886 Posts
Re: Query error occurs when filter SQL query using Uniqueidentifier variable type with a static v...
Mar 11, 2014 08:04 AM|peterthegreat|LINK
is there an alternative approach besides ?
Contributor
5590 Points
1297 Posts
Re: Query error occurs when filter SQL query using Uniqueidentifier variable type with a static v...
Mar 11, 2014 08:08 AM|dotnetzoom|LINK
You can write an IF Else statement in a stored procedure
That is much more cleaner way to handle filtering in your scenario.