ASP.Net - TableAdapter is returning phantom results

Question

Hi,

I have a TableAdapter that is fetching rows from an Stored Procedure. For some reason, when there are no results, the TableAdapter returns an INT with value 0 instead of NULL. The SP has NOCOUNT ON.

The problem with this is that I have a ListView in the frontend with an EmptyDataTemplate, which is not being shown. When I ran the query in Query Analyzer, I see that it is returning 0 instead of NULL.

The SP has a simple 'SELECT * FROM WHERE ' with one INT parameter (NOT Output). Running it in Query Analyzer, I get no output, as expected.

Anybody know what's going on?!

Answer 1

It looks like the stored procedure is returning the result. All stored procedure return a result code, it's most likely this.

Answer 2

Check the ExcuteMode property of the tableAdapter for the query. Be sure it is set to "Reader". There are 3 options for ExecuteMode:

1. Reader - Returns rows of data
2. Scalar - Returns single value
3. NonQuery - Returns an int with number of rows affected

Also check the Parameters Collection for a return value parameter. It should have properites AllowDBNull (true if you allow nulls), Direction (ReturnValue). This parameter should hold the results of your SP. In your example it would be null since there were no records returned.