Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP General > ASP with stored procedure problem....

Reply
Thread Tools

ASP with stored procedure problem....

 
 
Vitamin
Guest
Posts: n/a
 
      11-07-2003
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)


 
Reply With Quote
 
 
 
 
Ray at
Guest
Posts: n/a
 
      11-07-2003
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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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.


 
Reply With Quote
 
 
 
 
Vitamin
Guest
Posts: n/a
 
      11-07-2003
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:%(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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.
>
>



 
Reply With Quote
 
Vitamin
Guest
Posts: n/a
 
      11-07-2003
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 <%=sLocation%>" <myfirstname at lane 34 . komm> wrote in message
news:%(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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.
>
>



 
Reply With Quote
 
Ray at
Guest
Posts: n/a
 
      11-07-2003
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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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 <%=sLocation%>" <myfirstname at lane 34 . komm> wrote in message
> news:%(E-Mail Removed)...
> > Try doing:
> >
> > CREATE PROCEDURE sp_admin_member_searching
> > @request_page INT = 1,
> > @page_size INT = 0,
> > @id INT = 0,
> > @membership_id INT = 0,
> > @area_



 
Reply With Quote
 
Vitamin
Guest
Posts: n/a
 
      11-07-2003
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:%(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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 <%=sLocation%>" <myfirstname at lane 34 . komm> wrote in message
> > news:%(E-Mail Removed)...
> > > Try doing:
> > >
> > > CREATE PROCEDURE sp_admin_member_searching
> > > @request_page INT = 1,
> > > @page_size INT = 0,
> > > @id INT = 0,
> > > @membership_id INT = 0,
> > > @area_

>
>



 
Reply With Quote
 
Vitamin
Guest
Posts: n/a
 
      11-07-2003
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
****...

"Ray at <%=sLocation%>" <myfirstname at lane 34 . komm> wrote in message
news:%(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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 <%=sLocation%>" <myfirstname at lane 34 . komm> wrote in message
> > news:%(E-Mail Removed)...
> > > Try doing:
> > >
> > > CREATE PROCEDURE sp_admin_member_searching
> > > @request_page INT = 1,
> > > @page_size INT = 0,
> > > @id INT = 0,
> > > @membership_id INT = 0,
> > > @area_

>
>



 
Reply With Quote
 
Vitamin
Guest
Posts: n/a
 
      11-07-2003
yes~
i found the solution la~
thanks very much~~~

"Ray at <%=sLocation%>" <myfirstname at lane 34 . komm> wrote in message
news:%(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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 <%=sLocation%>" <myfirstname at lane 34 . komm> wrote in message
> > news:%(E-Mail Removed)...
> > > Try doing:
> > >
> > > CREATE PROCEDURE sp_admin_member_searching
> > > @request_page INT = 1,
> > > @page_size INT = 0,
> > > @id INT = 0,
> > > @membership_id INT = 0,
> > > @area_

>
>



 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
'Procedure or function <stored procedure name> has too many arguments specified',,,ARGH! Mike P ASP .Net 0 06-19-2006 01:19 PM
SQL Server Stored Procedure with Output Parameter and ASP.NET SQLCommand Mr Not So Know It All ASP .Net 4 01-17-2006 07:24 PM
Sample code for Connecting and Executing a stored procedure is SQL server using ASP.NET Peri ASP .Net 1 06-13-2005 04:56 AM
Call Stored Procedure via ASP.Net (VB) Sam ASP .Net 6 05-11-2005 04:06 PM
Hitting Sql Server Stored Procedure Breakpoint from ASP.NET app =?Utf-8?B?Um9iIEM=?= ASP .Net 0 04-16-2005 12:27 AM



Advertisments