locked
Query error occurs when filter SQL query using Uniqueidentifier variable type with a static value RRS feed

  • Question

  • User1909155429 posted
    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>

     

     

    Monday, March 10, 2014 9:52 AM

Answers

  • User1208776063 posted

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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, March 10, 2014 10:42 AM
  • User1208776063 posted

    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.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, March 11, 2014 8:08 AM

All replies

  • User1208776063 posted

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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, March 10, 2014 10:42 AM
  • User1909155429 posted

    That is a brilliant answer. It works !Smile

    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

    Monday, March 10, 2014 1:43 PM
  • User1208776063 posted

    What is actually  comparing, values  ?

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

    Monday, March 10, 2014 1:59 PM
  • User1909155429 posted

    is there an alternative approach besides ?

     

    Tuesday, March 11, 2014 8:04 AM
  • User1208776063 posted

    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.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, March 11, 2014 8:08 AM