Accessing Stored procedure in ASP.Net - What is wrong?

I have the following stored procedure

drop procedure ce_selectCity;

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
create PROCEDURE ce_selectCity
@.recordCount int output
-- Add the parameters for the stored procedure here

--<@.Param2, sysname, @.p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2,, 0>
AS

declare @.errNo int

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.

SET NOCOUNT ON;

-- Insert statements for procedure here

select ciId,name from ce_city order by name

select @.recordCount = @.@.ROWCOUNT
select @.errNo = @.@.ERROR

if @.errNo <> 0 GOTO HANDLE_ERROR

return @.errNo
HANDLE_ERROR:
Rollback transaction
return @.errNo

Go

and i was just testing it like

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
db.connect()

Dim reader As SqlDataReader

Dim sqlCommand As New SqlCommand("ce_selectCity", db.getConnection)

Dim recordCountParam As New SqlParameter("@.recordCount", SqlDbType.Int)
Dim errNoParam As New SqlParameter("@.errNo", SqlDbType.Int)

recordCountParam.Direction = ParameterDirection.Output
errNoParam.Direction = ParameterDirection.ReturnValue

sqlCommand.Parameters.Add(recordCountParam)
sqlCommand.Parameters.Add(errNoParam)

reader = db.runStoredProcedureGetReader(sqlCommand)

If (db.isError = False And reader.HasRows) Then
Response.Write("Total::" & Convert.ToInt32(recordCountParam.Value) & "<br />")
While (reader.Read())
Response.Write(reader("ciId") & "::" & reader("name") & "<br />")
End While

End If
db.close()
End Sub

It returns ALL ROWS (5 in the table right now). So,recordCount should be 5. (When i run it inside SQL Server (directly) it does return 5, so i know its working there).

BUT, its returning 0.

What am i doing wrong??

EDIT:
Oh, and this is the function i use to execute stored procedure and get the reader

Public Function runStoredProcedureGetReader(ByRef sqlCommand As SqlCommand) As SqlDataReader
sqlCommand.CommandType = CommandType.StoredProcedure
Return sqlCommand.ExecuteReader
End Function
I dont know why, but its not showing the code properly.
i tried to put [ code ] [ /code ] aroung the code as in other forums, but i think this one works differently.

Can any Mod please change it? i couldn't find how to edit my post.|||

drop procedure ce_selectCity;

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
create PROCEDURE ce_selectCity
@.recordCount int output
-- Add the parameters for the stored procedure here

--<@.Param2, sysname, @.p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2,, 0>
AS

declare @.errNo int

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.

SET NOCOUNT ON;

-- Insert statements for procedure here

select ciId,name from ce_city order by name

select @.recordCount = @.@.ROWCOUNT
select @.errNo = @.@.ERROR

if @.errNo <> 0 GOTO HANDLE_ERROR

return @.errNo
HANDLE_ERROR:
Rollback transaction
return @.errNo

Go

and i was just testing it like

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
db.connect()

Dim reader As SqlDataReader

Dim sqlCommand As New SqlCommand("ce_selectCity", db.getConnection)

Dim recordCountParam As New SqlParameter("@.recordCount", SqlDbType.Int)
Dim errNoParam As New SqlParameter("@.errNo", SqlDbType.Int)

recordCountParam.Direction = ParameterDirection.Output
errNoParam.Direction = ParameterDirection.ReturnValue

sqlCommand.Parameters.Add(recordCountParam)
sqlCommand.Parameters.Add(errNoParam)

reader = db.runStoredProcedureGetReader(sqlCommand)

If (db.isError = False And reader.HasRows) Then
Response.Write("Total::" & Convert.ToInt32(recordCountParam.Value) & "<br />")
While (reader.Read())
Response.Write(reader("ciId") & "::" & reader("name") & "<br />")
End While

End If
db.close()
End Sub

It returns ALL ROWS (5 in the table right now). So, recordCount should be 5. (When i run it inside SQL Server (directly) it does return 5, so i know its working there).

BUT, its returning 0.

What am i doing wrong??

EDIT:
Oh, and this is the function i use to execute stored procedure and get the reader

Public Function runStoredProcedureGetReader(ByRef sqlCommand As SqlCommand) As SqlDataReader
sqlCommand.CommandType = CommandType.StoredProcedure
Return sqlCommand.ExecuteReader
End Function

|||

I don't do that personally, however, this has been answered a few times on this forum already, so I'll repeat it for you:

Output parameters are not available until you have read all the records if you use .ExecuteReader.

|||Damn and double damn for me

and
thanks and double thanks to you.

Sorry, i should've checked before, but was frustrated as i've been working on this one for 2 hours.|||

Besides what Motley said here's more info:

@.@. functions need to follow the statements for which they intend to be used against. In your code @.recordcount will have the correct value.But @.@.ERROR wil have the error information for the SELECT @.recordcount = @.@.Rowcount statement rather than the original SELECT you intended to. If you change the order it will be the other way. Your @.@.ERROR will have correct information but @.@.Rowcount will show only 1 record since it gets you the information from the SELECT @.err?No = @.@.ERROR statement.

Hope I was clear. Now to get both info, you can use COUNT(*) in your SELECT statement and follow it up with SELECT @.@.ERROR.

As for your .NET code its all messed up. You are initializing all your command objects and connection info and calling a function and not passing any of that info. You are better off putting everything in one event. Declare and initialize all the variables either in the page_load or in the function and just get the result.

Finally, you would add the parameters as :

sqlCommand.Parameters.Add("@.recordCountParam")
sqlCommand.Parameters.Add("@.errNoParam")