CODEWITHSUNDEEP
asp.netsql-serverstored-procedurestableadapter
Donnie Thomas
Donnie Thomas

Reputation: 4038

TableAdapter is returning phantom results in ASP.NET

I have a TableAdapter that is fetching rows from a stored procedure. For some reason, when there are no results, the TableAdapter returns an INT with value 0 instead of NULL. The stored procedure 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 stored procedure has a simple 'SELECT * FROM WHERE ' with one INT parameter (NOT Output). Running it in Query Analyzer, I get no output, as expected.

What's going on?

Upvotes: 0

Views: 678

Answers (2)

Johnno Nolan
Johnno Nolan

Reputation: 29659

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

Upvotes: 1

Drell
Drell

Reputation: 166

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.

Upvotes: 1

Related Questions