DropdownList SqlDataSource does not pull *all* records

I have a 开发者_运维知识库DropDownList which is tied to SQLDataSource. I am running the exact same query in SQL Management studio and it pulls all the records but when I execute it in SQLDataSource, it does not pull the two records that I just added. Why?

SELECT [RepID], [ManagerID] 
FROM MyDB1.dbo.Reps 
WHERE [Role] = 'Manager' AND ISNULL(RepType, 'Account Exec') <> 'House Account Exec'

union

SELECT [RepID], [ManagerID] 
FROM MyDB2.dbo.Reps 
WHERE [Role] = 'Manager' AND ISNULL(RepType, 'Account Exec') <> 'House Account Exec'

The problem line seems to be RepType field. I did verify it it is NULL for the records that I want to pull. I have spend more than an hour on it. Anyone come across such a thing? Any suggestions?

Output from SMSS

RepID       ManagerID
----------- -----------
1           1
9           9
10          10        <- this guy does not show in DropDown
42          42
333         333       <- and this guy
1026        1026
1065        1065
2014        1
3009        3009
3025        3025
5000        5000
5002        5002
6000        6000
9000        9000

this is not the only problem. Even if remove the ISNULL(RepType, 'Account Exec') condition, it still does not pull the repID=333, while it does pul repID=10.

What I found is that there is a duplicate of repID =10 but still it should behave the same way in SSMS and in dropdownlist.

This behaviour should not be like this. This is probably a bug but what I have found so far

1. Rep with RepID=10 exhisted for two records, that could be a problem.

2. In the two tables, one table has reptype='House Account Exec', while in other it is Null. This condition in interprted differently when the query is executed in SMSS and in SQLDatasource.

There are still things that I can not resolve. For example repID=333, appears in SMSS but not dropdownlist.

Suggest converting your SQL query into a stored procedure instead of your adhoc SQL statement.