Re: Output Parameter Returns Nothing

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

That's because ADO.NET expects you to return the rowset first. After having
passed back all of the rows, any OUTPUT parameters are set and passed back
in the TDS. If you add a DataTable.Load (datareader) to read all of the
rows, the output parameter will be populated. But of course, by then you
have the count of the rows anyway (in Table.Rows.Count).

This approach (of counting first) is inefficient and (in this case) simply
doubles the workload on the server. It also is only an approximate guess. If
a row is added or removed between the time you count and the time the last
row is fetched, the count will be off.

This is discussed in detail in my book.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------

"Sergey Zuyev" <SergeyZuyev@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:120891E4-CE1A-47A0-B760-FAF6E06683F1@xxxxxxxxxxxxxxxx
Hello!
I'm using SQL Server 2005. I have Stored Procedure that returns Table
Records and RowCount as output parameter:

CREATE PROCEDURE [dbo].[GetListByPage]
@RecordCount bigint OUTPUT
AS
BEGIN
-- get record count
SELECT @RecordCount = (SELECT COUNT(ProductId) FROM [StoreProducts])
-- get table
SELECT * FROM StoreProducts
END

I'm using DAAB to get data from the procedure, but Output parameter
returns
nothing.
The only time it returns a result if I don't use second Sql Statement in
procedure that actually returns table. Am I doing something wrong? Any
ideas?
Thanks !

Code :
Dim db As Database = DatabaseFactory.CreateDatabase()
'
Dim proc As String = "GetListByPage"
Dim cmd As DbCommand = db.GetStoredProcCommand(proc)
'
db.AddOutParameter(cmd, "@RecordCount", DbType.Int64, 0)

Dim dataReader As IDataReader = db.ExecuteReader(cmd)

Dim o As Object = db.GetParameterValue(cmd, "@RecordCount")



--
Programmer


.

Relevant Pages

  • GUID from SQL Uniqueidentifier - Empty?!
    ... and one output parameter. ... Public Function BuildExtract(byval ProjectNumber as string, ... Dim cmd As SqlCommand = New SqlCommand ... Dim newGUID As Guid ...
    (microsoft.public.dotnet.languages.vb)
  • GUID from SQL Uniqueidentifier -> Empty?!
    ... and one output parameter. ... Public Function BuildExtract(byval ProjectNumber as string, ... Dim cmd As SqlCommand = New SqlCommand ... Dim newGUID As Guid ...
    (microsoft.public.dotnet.languages.vb)
  • Re: DAAB output pararmeters
    ... I'm only passing an ArrayList and letting SQL Server match up the ... Dim rowsAffected As Int32 ... I usually include the output parameter and populate that way, ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Problem with return value.
    ... "Norman Yuan" wrote: ... SP to return value of output parameter. ... Dim ConCred As SqlConnection ... CREATE PROCEDURE dbo.GetCredit @UserID varchar, ...
    (microsoft.public.dotnet.general)
  • Re: Problem with return value.
    ... By getting a returned DataReader, you need to call DataRead.Readto get ... SP to return value of output parameter. ... Dim ConCred As SqlConnection ... CREATE PROCEDURE dbo.GetCredit @UserID varchar, ...
    (microsoft.public.dotnet.general)