Query error occurs when filter SQL query using Uniqueidentifier variable type with a static value[Answered] RSS

• • Reply
  peterthegrea...

  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

  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:ListItem Text="-- ALL --" Value="-1" ></asp:ListItem>

   

   

• • Reply
  dotnetzoom

  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.

  WHERE vw_aspnet_UsersInRoles.RoleID =(CASE WHEN @RoleID='-1' THEN vw_aspnet_UsersInRoles.RoleId ELSE @RoleID END)

  This is sort of a query like 1=1 and potentially gets you all roles if you do not append other valid conditions.

• • Reply
  peterthegrea...

  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

• • Reply
  dotnetzoom

  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

  peterthegreat

  What is actually  comparing, values  ?

  Yes, when RoleID  is -1, where clause will compare value to itself.

• • Reply
  peterthegrea...

  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 ?

   

• • Reply
  dotnetzoom

  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

  IF @RoleID=-1
  BEGIN
     //Get all roles
  END
  ELSE
  BEGIN
    SELECT * FROM Table WHERE RoleID = @RoleID
  END

  That is much more cleaner way to handle filtering in your scenario.