Monday, March 12, 2012

How to Return SqlDataReader and return value (page count) from SPROC

This is my function, it returns SQLDataReader to DATALIST control. Howto return page number with the SQLDataReader set ? sql server 2005,asp.net 2.0

Function get_all_events() As SqlDataReader
Dim myConnection As NewSqlConnection(ConfigurationManager.AppSettings("........."))
Dim myCommand As New SqlCommand("EVENTS_LIST_BY_REGION_ALL", myConnection)
myCommand.CommandType = CommandType.StoredProcedure

Dim parameterState As New SqlParameter("@.State", SqlDbType.VarChar, 2)
parameterState.Value = Request.Params("State")
myCommand.Parameters.Add(parameterState)

Dim parameterPagesize As New SqlParameter("@.pagesize", SqlDbType.Int, 4)
parameterPagesize.Value = 20
myCommand.Parameters.Add(parameterPagesize)

Dim parameterPagenum As New SqlParameter("@.pageNum", SqlDbType.Int, 4)
parameterPagenum.Value = pn1.SelectedPage
myCommand.Parameters.Add(parameterPagenum)

Dim parameterPageCount As New SqlParameter("@.pagecount", SqlDbType.Int, 4)
parameterPageCount.Direction = ParameterDirection.ReturnValue
myCommand.Parameters.Add(parameterPageCount)

myConnection.Open()
'myCommand.ExecuteReader(CommandBehavior.CloseConnection)
'pages = CType(myCommand.Parameters("@.pagecount").Value, Integer)
Return myCommand.ExecuteReader(CommandBehavior.CloseConnection)
End Function

Variable Pages is global integer.

This is what i am calling
DataList1.DataSource = get_all_events()
DataList1.DataBind()

How to return records and also the return value of pagecount ? i tried many options, nothing work. Please help !!. I am struck

please any help ? finally following code works without error, but still not returning any return value (pagecount) along with the list of records.

Dim MyReader As SqlDataReader
Dim myConnection As New SqlConnection(ConfigurationManager.AppSettings("Dx918Aveb8ax81"))
Dim myCommand As New SqlCommand("EVENTS_LIST_BY_REGION_ALL", myConnection)
myCommand.CommandType = CommandType.StoredProcedure

Dim parameterregion As New SqlParameter("@.Region", SqlDbType.VarChar, 5)
parameterregion.Value = Request.Params("reg")
myCommand.Parameters.Add(parameterregion)

Dim parameterPagesize As New SqlParameter("@.pagesize", SqlDbType.Int, 4)
parameterPagesize.Value = 10
myCommand.Parameters.Add(parameterPagesize)

Dim parameterPagenum As New SqlParameter("@.pageNum", SqlDbType.Int, 4)
parameterPagenum.Value = pn1.SelectedPage
myCommand.Parameters.Add(parameterPagenum)

Dim parameterPageCount As New SqlParameter("@.pagecount", SqlDbType.Int, 4)
parameterPageCount.Direction = ParameterDirection.ReturnValue
myCommand.Parameters.Add(parameterPageCount)

myConnection.Open()
MyReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection)
pages = myCommand.Parameters("@.pagecount").Value
DataList1.DataSource = MyReader
DataList1.DataBind()
myCommand.Dispose()
myConnection.Dispose()
Response.Write(pages)

|||Output parameters and return values are not available until after aDataReader is closed (which you should add after your DataBind()).|||thank you. I will try that today!!

No comments:

Post a Comment