Works in Query Analyzer, does not in ASP.NET, is there a better way?

[onedizzydevil]

This is what works in the Query Analyer and it gives me back the number of times a Editee is listed in the groupUserRoles (association) table, ideally this column would be true or false but I would settle for a number 0 - whatever as long as it is 0 is not in table anything else means is in table.

[tt]
Declare @Editor int
Declare @Editee int
Declare @Group int
Set @Editee = 1
Set @Editor = 0
Set @Group = 0

SELECT DISTINCT roleid, rolelabel, (select count(*) from groupUserRoles where userno = @editee and roleno = roles.roleid) As isInCount
FROM roles
INNER JOIN groupUserRoles ON roles.roleID = groupUserRoles.roleNo
WHERE roleID in (SELECT roleNo FROM groupUserRoles WHERE groupNo = @Group AND userNo = @Editee OR userNo = @Editor)
AND groupUserRoles.userNo = @Editor

[/tt]

Put this same query in the ASP.NET Query String the

[tt](select count(*) from groupUserRoles where userno = @editee and roleno = roles.roleid) As isInCount[/tt]

section of the query always returns false, everything else works correctly.

Do you know of a better way to do this with one Query to the server and NOT using a Stored Procedure?

The results I need are a list all the @Editors roles (roleID, roleLabel) and some indication of if the @Editee is also assigned the same role or not (ie True, False or 0, 1, 0 and #).

Thanks your help is greatly appreciated.

Wayne Sellars

"Programming, today is a race between software developers, striving to build bigger and better idiot-proof programs, and the Universe, trying to produce bigger and better idiots. So far, Universe 1 - Programmers 0."