Return_Value = 11

I have a stored proc that retrives a segment of data, via a select statement and also returns @@RowCount to tell me the total number of records. It goes something like this:








SELECT ProductID FROM tbl_Products


@Counter = @@RowCount





SELECT ProductID FROM tbl_Products WHERE ProductID>1 AND ProductID<=25


RETURN @Counter





The Idea is that I can populate a "Showing items 1 to 25 of 75" label on my web form. Don't worry overly about syntax issues in the above snippet, I am only trying to get across what I am doing.





When I run my SP from query analyser it works properly. I get a list of my products, and the Return_Value is set correctly.





When I run my SP from a command as follows, the return value *ALWAYS* is set to 11.





Function MyFunction() as Integer


Dim dtr As SqlDataReader


Dim oConn As SqlConnection = New SqlConnection(sConn)


Dim cmd As New SqlCommand("sproc_ProductListByCategory", oConn)





With cmd


.CommandType = CommandType.StoredProcedure


.Parameters.Add("@Return_Value", DbType.Int32)


.Parameters("@Return_Value").Direction = ParameterDirection.ReturnValue


End With





oConn.Open()


dtr = cmd.ExecuteReader(CommandBehavior.CloseConnection)





'get the total number of products


Dim iReturn As Integer = cmd.Parameters("@Return_Value").Value





While dtr.Read


'code to process my product info


End While





oConn.Close()


dtr.Close()


cmd.Dispose()





Return iReturn





End Function


If I call the cmd object twice (eg once as ExecuteNonQuery and then as ExecuteDataReader) then it works just fine. But this seems stoopid as it involves processing the SP twice!!





So, has anyone else experienced this?


Anyone know what causes this problem?


Bearing in mind I already know an inefficient one, anyone know an *efficient* workaround? ;-)





TIA,


Mark