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.recordcount)
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 tblCompanyInformation '
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(@membership_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_page - 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(objCn, "sp_admin_member_searching", 4, true)

iPageSize = 10
iTotalRecords = 0
iTotalPage = 0

'@ determine command parameter
set objParam = objCmd.createParameter ("request_page", 3, 1,
request("page"))
objCmd.Parameters.append objParam
set objParam = objCmd.createParameter ("page_size", 3, 1, iPageSize)
objCmd.Parameters.append objParam
set objParam = objCmd.createParameter ("id", 3, 1, 0)
objCmd.Parameters.append objParam
set objParam = objCmd.createParameter ("membership_id", 3, 1, 0)
objCmd.Parameters.append objParam
set objParam = objCmd.createParameter ("area_id", 3, 1, 0)
objCmd.Parameters.append objParam
set objParam = objCmd.createParameter ("return_page", 3, 2, iPage)
objCmd.Parameters.append objParam
set objParam = objCmd.createParameter ("total_records", 3, 2,
iTotalRecords)
objCmd.Parameters.append objParam
set objParam = objCmd.createParameter ("total_pages", 3, 2,
iTotalPages)
objCmd.Parameters.append objParam

set objRs = objCmd.execute

releaseDataObject (objCmd)
releaseDataObject (objRs)
releaseDataObject (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

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.recordcount)
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. ".....

 

[Vitamin]

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

thx~

 

[Ray at]

Your SP returns a recordset, correct?

Set objRS = connection.execute
''these are vars in your asp page
id = objRS.Fields.Item(0).Value
membership_id = objRS.Fields.Item(1).Value
Company_name_tc = objRS.Fields.Item(2).Value
Company_name_en = objRS.Fields.Item(3).Value
register_date = objRS.Fields.Item(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]

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
....

Your SP returns a recordset, correct?

Set objRS = connection.execute
''these are vars in your asp page
id = objRS.Fields.Item(0).Value
membership_id = objRS.Fields.Item(1).Value
Company_name_tc = objRS.Fields.Item(2).Value
Company_name_en = objRS.Fields.Item(3).Value
register_date = objRS.Fields.Item(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

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

thx~

 

[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...

Your SP returns a recordset, correct?

Set objRS = connection.execute
''these are vars in your asp page
id = objRS.Fields.Item(0).Value
membership_id = objRS.Fields.Item(1).Value
Company_name_tc = objRS.Fields.Item(2).Value
Company_name_en = objRS.Fields.Item(3).Value
register_date = objRS.Fields.Item(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

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

thx~

 

[Vitamin]

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

Your SP returns a recordset, correct?

Set objRS = connection.execute
''these are vars in your asp page
id = objRS.Fields.Item(0).Value
membership_id = objRS.Fields.Item(1).Value
Company_name_tc = objRS.Fields.Item(2).Value
Company_name_en = objRS.Fields.Item(3).Value
register_date = objRS.Fields.Item(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

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

thx~