ASP with stored procedure problem....

[Vitamin]

I have written a stored procedure which will paging the recordset, and
return a range of record that i need, and i write a asp code to call it
however, no any return after the set objRs = objCmd.Execute
when i try to Response.write (objRs.recordco unt)
it said the recordset is close....

how can i solve this problem?? thx
=============== =====
Here is my stored procedure
=============== =====

CREATE PROCEDURE sp_admin_member _searching
@request_page INT = 1,
@page_size INT = 0,
@id INT = 0,
@membership_id INT = 0,
@area_id INT = 0,
@return_page INT = 0 OUTPUT,
@total_records INT = 0 OUTPUT,
@total_pages INT = 0 OUTPUT
AS
BEGIN
DECLARE
@sql_request NVARCHAR(4000)
-- create temp table to store all member information
CREATE TABLE #temp(
record_id INT IDENTITY(1, 1),
id INT,
membership_id INT,
Company_name_tc NVARCHAR(50),
Company_name_en CHAR(100),
register_date DATETIME
)
-- initial sql statement to insert company record to temp table
SET @sql_request = N'INSERT INTO #temp (id, membership_id, Company_name_tc ,
Company_name_en , register_date) '
SET @sql_request = @sql_request + N'SELECT id, membership_id,
company_name_tc , company_name_en , register_date FROM tblCompanyInfor mation '
SET @sql_request = @sql_request + N'WHERE 1 = 1 '

-- determine user have provide id or not
IF @id > 0 SET @sql_request = @sql_request + N'AND id = ' + CAST(@id AS
NVARCHAR) + ' '

-- determine user have provide membership id or not
IF @membership_id <> '' SET @sql_request = @sql_request + N'AND
membership_id = ' + CAST(@membershi p_id AS NVARCHAR) + ' '

-- determine user have provide area id or not
IF @area_id <> '' SET @sql_request = @sql_request + N'AND area_id = ' +
CAST(@area_id AS NVARCHAR) + ' '

SET @sql_request = @sql_request + N'ORDER BY id ASC'

-- execute insert record statement
EXECUTE sp_executesql @sql_request

-- get total records count
SET @total_records = @@ROWCOUNT

-- get total page
SET @total_pages = @total_records / @page_size
IF (@total_records % @page_size) <> 0 SET @total_pages = @total_pages + 1

-- set current page position
IF @request_page > @total_pages
SET @request_page = 1
ELSE IF @request_page < 1
SET @request_page = @total_pages
-- return the next page number
SET @return_page = @request_page

-- initial sql statement to paging the result
IF @request_page = 1
BEGIN
SELECT id, membership_id, Company_name_tc , Company_name_en , register_date
FROM #temp
WHERE record_id >= ((@request_page - 1) * @page_size) AND record_id <=
(@request_page * @page_size)
ORDER BY Company_name_en ASC
END
ELSE
BEGIN
SELECT id, membership_id, Company_name_tc , Company_name_en , register_date
FROM #temp
WHERE record_id >= (((@request_pag e - 1) * @page_size) + 1) AND record_id
<= (@request_page * @page_size)
ORDER BY Company_name_en ASC
END
DROP TABLE #temp
END

=============== =====
here is my ASP code
=============== =====
dim objCn, objRs, objCmd, objParam
dim iPageSize, iTotalRecords, iTotalPages, iPage

set objCn = getConnection ()
set objCmd = getCommand(objC n, "sp_admin_membe r_searching", 4, true)

iPageSize = 10
iTotalRecords = 0
iTotalPage = 0

'@ determine command parameter
set objParam = objCmd.createPa rameter ("request_page" , 3, 1,
request("page") )
objCmd.Paramete rs.append objParam
set objParam = objCmd.createPa rameter ("page_size" , 3, 1, iPageSize)
objCmd.Paramete rs.append objParam
set objParam = objCmd.createPa rameter ("id", 3, 1, 0)
objCmd.Paramete rs.append objParam
set objParam = objCmd.createPa rameter ("membership_id ", 3, 1, 0)
objCmd.Paramete rs.append objParam
set objParam = objCmd.createPa rameter ("area_id", 3, 1, 0)
objCmd.Paramete rs.append objParam
set objParam = objCmd.createPa rameter ("return_pag e", 3, 2, iPage)
objCmd.Paramete rs.append objParam
set objParam = objCmd.createPa rameter ("total_records ", 3, 2,
iTotalRecords)
objCmd.Paramete rs.append objParam
set objParam = objCmd.createPa rameter ("total_page s", 3, 2,
iTotalPages)
objCmd.Paramete rs.append objParam

set objRs = objCmd.execute

releaseDataObje ct (objCmd)
releaseDataObje ct (objRs)
releaseDataObje ct (objCn)

[Ray at]

Try doing:

CREATE PROCEDURE sp_admin_member _searching
@request_page INT = 1,
@page_size INT = 0,
@id INT = 0,
@membership_id INT = 0,
@area_id INT = 0,
@return_page INT = 0 OUTPUT,
@total_records INT = 0 OUTPUT,
@total_pages INT = 0 OUTPUT
AS
BEGIN
SET NOCOUNT ON
-- rest of your sp

Ray at home

"Vitamin" <ha*@seeWhat.co m> wrote in message
news:u8******** ******@TK2MSFTN GP12.phx.gbl...

I have written a stored procedure which will paging the recordset, and
return a range of record that i need, and i write a asp code to call it
however, no any return after the set objRs = objCmd.Execute
when i try to Response.write (objRs.recordco unt)
it said the recordset is close....

how can i solve this problem?? thx
=============== =====
Here is my stored procedure
=============== =====

CREATE PROCEDURE sp_admin_member _searching
@request_page INT = 1,
@page_size INT = 0,
@id INT = 0,
@membership_id INT = 0,
@area_id INT = 0,
@return_page INT = 0 OUTPUT,
@total_records INT = 0 OUTPUT,
@total_pages INT = 0 OUTPUT
AS
BEGIN
DECLARE
@sql_request NVARCHAR(4000)

&c.

[Vitamin]

no other problems..
it seems cannot get the input value from ASP Command
although i set the page_size parameters = 1
it still prompt me error that "Divide by zero error encountered. ".....

"Ray at <%=sLocation% >" <myfirstname at lane 34 . komm> wrote in message
news:%2******** *******@tk2msft ngp13.phx.gbl.. .

Try doing:

CREATE PROCEDURE sp_admin_member _searching
@request_page INT = 1,
@page_size INT = 0,
@id INT = 0,
@membership_id INT = 0,
@area_id INT = 0,
@return_page INT = 0 OUTPUT,
@total_records INT = 0 OUTPUT,
@total_pages INT = 0 OUTPUT
AS
BEGIN
SET NOCOUNT ON
-- rest of your sp

Ray at home

"Vitamin" <ha*@seeWhat.co m> wrote in message
news:u8******** ******@TK2MSFTN GP12.phx.gbl...

I have written a stored procedure which will paging the recordset, and
return a range of record that i need, and i write a asp code to call it
however, no any return after the set objRs = objCmd.Execute
when i try to Response.write (objRs.recordco unt)
it said the recordset is close....

how can i solve this problem?? thx
=============== =====
Here is my stored procedure
=============== =====

CREATE PROCEDURE sp_admin_member _searching
@request_page INT = 1,
@page_size INT = 0,
@id INT = 0,
@membership_id INT = 0,
@area_id INT = 0,
@return_page INT = 0 OUTPUT,
@total_records INT = 0 OUTPUT,
@total_pages INT = 0 OUTPUT
AS
BEGIN
DECLARE
@sql_request NVARCHAR(4000)

&c.

[Vitamin]

i found that if i using connection.exec ute to instead of command object
it can work...but how can i get the return value from stored procedure...

thx~

"Ray at <%=sLocation% >" <myfirstname at lane 34 . komm> wrote in message
news:%2******** *******@tk2msft ngp13.phx.gbl.. .

Try doing:

CREATE PROCEDURE sp_admin_member _searching
@request_page INT = 1,
@page_size INT = 0,
@id INT = 0,
@membership_id INT = 0,
@area_id INT = 0,
@return_page INT = 0 OUTPUT,
@total_records INT = 0 OUTPUT,
@total_pages INT = 0 OUTPUT
AS
BEGIN
SET NOCOUNT ON
-- rest of your sp

Ray at home

"Vitamin" <ha*@seeWhat.co m> wrote in message
news:u8******** ******@TK2MSFTN GP12.phx.gbl...

I have written a stored procedure which will paging the recordset, and
return a range of record that i need, and i write a asp code to call it
however, no any return after the set objRs = objCmd.Execute
when i try to Response.write (objRs.recordco unt)
it said the recordset is close....

how can i solve this problem?? thx
=============== =====
Here is my stored procedure
=============== =====

CREATE PROCEDURE sp_admin_member _searching
@request_page INT = 1,
@page_size INT = 0,
@id INT = 0,
@membership_id INT = 0,
@area_id INT = 0,
@return_page INT = 0 OUTPUT,
@total_records INT = 0 OUTPUT,
@total_pages INT = 0 OUTPUT
AS
BEGIN
DECLARE
@sql_request NVARCHAR(4000)

&c.

[Ray at]

Your SP returns a recordset, correct?

Set objRS = connection.exec ute
''these are vars in your asp page
id = objRS.Fields.It em(0).Value
membership_id = objRS.Fields.It em(1).Value
Company_name_tc = objRS.Fields.It em(2).Value
Company_name_en = objRS.Fields.It em(3).Value
register_date = objRS.Fields.It em(4).Value

That would grab the values from the recordset. You could do that while loop
until objRS.EOF, or use objRS.GetRows() to put your recordset into a two
dimensional array. Is this what you mean, or are you asking things that I'm
not qualified to answer and should have just kept my mouth shut? :]

Ray at home

"Vitamin" <ha*@seeWhat.co m> wrote in message
news:uX******** ******@TK2MSFTN GP09.phx.gbl...

i found that if i using connection.exec ute to instead of command object
it can work...but how can i get the return value from stored procedure...

thx~

"Ray at <%=sLocation% >" <myfirstname at lane 34 . komm> wrote in message
news:%2******** *******@tk2msft ngp13.phx.gbl.. .

Try doing:

CREATE PROCEDURE sp_admin_member _searching
@request_page INT = 1,
@page_size INT = 0,
@id INT = 0,
@membership_id INT = 0,
@area_

[Vitamin]

sorry
i think i am made you confuse.
i mean i cannot get the return value from a variable of stored procedure
which had define as OUTPUT
....

"Ray at <%=sLocation% >" <myfirstname at lane 34 . komm> wrote in message
news:%2******** ********@TK2MSF TNGP12.phx.gbl. ..

Your SP returns a recordset, correct?

Set objRS = connection.exec ute
''these are vars in your asp page
id = objRS.Fields.It em(0).Value
membership_id = objRS.Fields.It em(1).Value
Company_name_tc = objRS.Fields.It em(2).Value
Company_name_en = objRS.Fields.It em(3).Value
register_date = objRS.Fields.It em(4).Value

That would grab the values from the recordset. You could do that while loop until objRS.EOF, or use objRS.GetRows() to put your recordset into a two
dimensional array. Is this what you mean, or are you asking things that I'm not qualified to answer and should have just kept my mouth shut? :]

Ray at home

"Vitamin" <ha*@seeWhat.co m> wrote in message
news:uX******** ******@TK2MSFTN GP09.phx.gbl...

i found that if i using connection.exec ute to instead of command object
it can work...but how can i get the return value from stored procedure...
thx~

"Ray at <%=sLocation% >" <myfirstname at lane 34 . komm> wrote in message
news:%2******** *******@tk2msft ngp13.phx.gbl.. .

Try doing:

CREATE PROCEDURE sp_admin_member _searching
@request_page INT = 1,
@page_size INT = 0,
@id INT = 0,
@membership_id INT = 0,
@area_

[Vitamin]

o...i know what problem it have
if i using set objRs = objCmd.execute
it will only return recordset
i cannot access the output parameter of stored procedure
if i using objCmd.execute only
then i can access the output parameter of stored procedure
shit...

"Ray at <%=sLocation% >" <myfirstname at lane 34 . komm> wrote in message
news:%2******** ********@TK2MSF TNGP12.phx.gbl. ..

Your SP returns a recordset, correct?

Set objRS = connection.exec ute
''these are vars in your asp page
id = objRS.Fields.It em(0).Value
membership_id = objRS.Fields.It em(1).Value
Company_name_tc = objRS.Fields.It em(2).Value
Company_name_en = objRS.Fields.It em(3).Value
register_date = objRS.Fields.It em(4).Value

That would grab the values from the recordset. You could do that while loop until objRS.EOF, or use objRS.GetRows() to put your recordset into a two
dimensional array. Is this what you mean, or are you asking things that I'm not qualified to answer and should have just kept my mouth shut? :]

Ray at home

"Vitamin" <ha*@seeWhat.co m> wrote in message
news:uX******** ******@TK2MSFTN GP09.phx.gbl...

i found that if i using connection.exec ute to instead of command object
it can work...but how can i get the return value from stored procedure...
thx~

"Ray at <%=sLocation% >" <myfirstname at lane 34 . komm> wrote in message
news:%2******** *******@tk2msft ngp13.phx.gbl.. .

Try doing:

CREATE PROCEDURE sp_admin_member _searching
@request_page INT = 1,
@page_size INT = 0,
@id INT = 0,
@membership_id INT = 0,
@area_

[Vitamin]

yes~
i found the solution la~
thanks very much~~~

"Ray at <%=sLocation% >" <myfirstname at lane 34 . komm> wrote in message
news:%2******** ********@TK2MSF TNGP12.phx.gbl. ..

Your SP returns a recordset, correct?

Set objRS = connection.exec ute
''these are vars in your asp page
id = objRS.Fields.It em(0).Value
membership_id = objRS.Fields.It em(1).Value
Company_name_tc = objRS.Fields.It em(2).Value
Company_name_en = objRS.Fields.It em(3).Value
register_date = objRS.Fields.It em(4).Value

That would grab the values from the recordset. You could do that while loop until objRS.EOF, or use objRS.GetRows() to put your recordset into a two
dimensional array. Is this what you mean, or are you asking things that I'm not qualified to answer and should have just kept my mouth shut? :]

Ray at home

"Vitamin" <ha*@seeWhat.co m> wrote in message
news:uX******** ******@TK2MSFTN GP09.phx.gbl...

i found that if i using connection.exec ute to instead of command object
it can work...but how can i get the return value from stored procedure...
thx~

"Ray at <%=sLocation% >" <myfirstname at lane 34 . komm> wrote in message
news:%2******** *******@tk2msft ngp13.phx.gbl.. .

Try doing:

CREATE PROCEDURE sp_admin_member _searching
@request_page INT = 1,
@page_size INT = 0,
@id INT = 0,
@membership_id INT = 0,
@area_