You were on the right track, but it sounds like a command object is warranted

(whatever that may be in ADO.NET).  I swear there's been so many sql server

api's over the years they begin to blur in the head.  You know I guess this problem

wasn't an ADO problem it was more of db-lib problem.  But it looks like its

reincarnated back with the thinness of ADO.NET.  This is an overall good thing,

but leads to your point of confusion.  Here's the snippet from the this msdn article related to your issue.

Peter Evans (__PETER Peter_)

William Vaughn

Beta V Corporation

April 2003

Applies to:

   Microsoft ADO.NET

   Microsoft SQL Server™

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnadonet/html/gazoutas.asp

Managing Return Value and OUTPUT Parameters

If you want to capture the integer passed back by the RETURN statement in TSQL

or your stored procedure's OUTPUT parameters, you're going to have

to take another few minutes to setup a Command object to capture these values f

rom the resultset. Again, and I can't emphasis this enough, these values are not made available

until you've processed all rowsets in each resultset (if there are any). Yes, a stored procedure

can return several resultsets, but the RETURN value and OUTPUT parameters are not populated

until all queries have been completed and all rowsets transmitted back to the client.

Handling Unwanted DataReader Rows

Okay, so you asked for too many rows in one of your queries and you want to step over

this rowset to get to your OUTPUT parameters when working with a DataReader.

Don't be tempted to simply close the DataReader without using the Cancel method

on the Command first. That's because ADO.NET completes the processing of any remaining rows

instead of telling SQL Server to cancel all further query processing for this Command.

Using the DataAdapter Fill Method to Process the RETURN Value and OUTPUT Parameters

The following code constructs a DataAdapter with its associated SelectCommand to execute

a multiple-resultset stored procedure that returns several OUTPUT parameters.

Note that the parameters must be named correctly when working with SQL Server.

That is they match the names used in the stored procedure. Order is not important,

but you must include any parameter that does not have a default value set in the stored procedure.

Listing 4. Constructing a Command and using Fill to execute the query

Dim bolEOF As Booleancn = New SqlConnection("server=demoserver;" & _    
"database=Pubs;integrated security=sspi")CreateTestProcedure()         
' Create test SP in Pubs DBda = New SqlDataAdapter("OutputTitleInfoByTID", cn)
da.SelectCommand.CommandType = CommandType.StoredProcedureWith 
da.SelectCommand.Parameters    
.Add("@TID", SqlDbType.VarChar, 6).Value = "BU1032"    
.Add("@Title", SqlDbType.VarChar, 80).Direction _        
= ParameterDirection.Output    
.Add("@Type", SqlDbType.VarChar, 40).Direction _        
= ParameterDirection.Output    
.Add("@Price", SqlDbType.Money).Direction _        
= ParameterDirection.Output    
.Add("@Advance", SqlDbType.Money).Direction _        
= ParameterDirection.Output    
.Add("@ReturnValue", SqlDbType.Int).Direction _        
= ParameterDirection.ReturnValue    
ds = New DataSet    da.Fill(ds)

Once the Fill method executes the query and processes the rowsets, the RETURN value and OUTPUT parameters

are available through the SelectCommand.Parameters collection either by ordinal position or by name as shown in listing 5.

Listing 5. Displaying the values of the Return Value and OUTPUT parameters

Debug.WriteLine("@ReturnValue:" & _    
.Item("@ReturnValue").Value.ToString)Debug.WriteLine("@Title:" & _    
.Item("@Title").Value.ToString)Debug.WriteLine("@Type:" & _    
.Item("@Type").Value.ToString)Debug.WriteLine("@Advance:" & _    
.Item("@Advance").Value.ToString)Debug.WriteLine("@Price:" & _    
.Item("@Price").Value.ToString)Debug.WriteLine("@Advance:" & _    
.Item("@Advance").Value.ToString)

Using a DataReader to Process the RETURN Value and OUTPUT Parameters

This same query can be executed directly with the Command.ExecuteReader (or ExecuteNonQuery

if you don't have a rowset to process), but there are several other steps you'll need to take

to process the returned values. Remember, you'll have to complete processing for all rowsets before attempting

to capture the Return value or OUTPUT parameters. The following code shows how to use the ExecuteReader and

a loop to process the rowsets, and then capture the Return value and OUTPUT parameters.

You'll find that OUTPUT parameters (even a lot of them) can be handled far faster than even a single row of data returned by a SELECT.

Listing 6. Displaying the values of the Return Value and OUTPUT parameters

With cmd.Parameters    cn.Open()    
dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)    
' Process rowset(s)    
bolEOF = dr.Read    
Do        
Do While bolEOF = True            ' Process rows            
bolEOF = dr.Read()        
Loop    
Loop While dr.NextResult = True    
cmd.Cancel()    
dr.Close()    
Debug.WriteLine("@ReturnValue:" & _        
.Item("@ReturnValue").Value.ToString)    
Debug.WriteLine("@Title:" & _        
.Item("@Title").Value.ToString)    
Debug.WriteLine("@Type:" & _        
.Item("@Type").Value.ToString)    
Debug.WriteLine("@Advance:" & _        
.Item("@Advance").Value.ToString)    
Debug.WriteLine("@Price:" & _        
.Item("@Price").Value.ToString)End With

Conclusion

This fairly brief article walked you through the details of handling resultsets and the rowsets and

other stuff they contain. You shouldn't have trouble getting to the gazoutas after this—at least I hope not.