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) 7 4511
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.com> wrote in message
news:u8**************@TK2MSFTNGP12.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.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.
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***************@tk2msftngp13.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.com> wrote in message news:u8**************@TK2MSFTNGP12.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.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.
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:%2***************@tk2msftngp13.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.com> wrote in message news:u8**************@TK2MSFTNGP12.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.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.
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" <ha*@seeWhat.com> wrote in message
news:uX**************@TK2MSFTNGP09.phx.gbl... 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:%2***************@tk2msftngp13.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_
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****************@TK2MSFTNGP12.phx.gbl... 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" <ha*@seeWhat.com> wrote in message news:uX**************@TK2MSFTNGP09.phx.gbl... 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:%2***************@tk2msftngp13.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_
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****************@TK2MSFTNGP12.phx.gbl... 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" <ha*@seeWhat.com> wrote in message news:uX**************@TK2MSFTNGP09.phx.gbl... 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:%2***************@tk2msftngp13.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_
yes~
i found the solution la~
thanks very much~~~
"Ray at <%=sLocation%>" <myfirstname at lane 34 . komm> wrote in message
news:%2****************@TK2MSFTNGP12.phx.gbl... 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" <ha*@seeWhat.com> wrote in message news:uX**************@TK2MSFTNGP09.phx.gbl... 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:%2***************@tk2msftngp13.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_
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Michael Trosen |
last post by:
Hi Everyone,
I hope someone can help, I'm pretty new to pro*c programming.
I have the following application setup:
a pro*c program calls a stored procedure and recieves a cursor back:
the...
|
by: Rhino |
last post by:
I've spent the last couple of hours trying to figure out how to debug a Java
stored procedure and am just going in circles. The last straw came when I
got "Cannot open input stream for default"...
|
by: Jeff Wang |
last post by:
Hi all,
Can someone help me out? I've been struggling with this for almost a
week and still have no clue what's wrong.
Basically I want to write a DB2 stored procedure for OS/390 in REXX.
In...
|
by: Thomasb |
last post by:
With a background in MS SQL Server programming I'm used to temporary
tables. Have just started to work with DB2 ver 7 on z/OS and stumbled
into the concept of GLOBAL TEMPORARY TABLE.
I have...
|
by: Kent Lewandowski |
last post by:
hi all,
Recently I wrote some stored procedures using java jdbc code
(admittedly my first stab) and then tried to implement the same within
java packages (for code reuse). I encountered...
| |
by: Wojciech Wendrychowicz |
last post by:
Hello to All,
I'm trying to retrieve records from AS/400 in an VBA application.
So, I've made an RPG program, then a stored procedure wchich calls that RPG
program, and finally some VBA code to...
|
by: Eli |
last post by:
Hi all
We currently have a strange problem with calling a Stored Procedure
(SQL Database) in our C# Project. The only error I get is "System
error" which says a lot :)
Background:
We have...
|
by: Dabbler |
last post by:
I'm using an ObjectDataSource with a stored procedure and am getting the
following error when trying to update (ExecuteNonQuery):
System.Data.SqlClient.SqlException: Procedure or Function...
|
by: nishi57 |
last post by:
I hope I can get some help regarding this issue, which has been going
on for a while. I have a desktop user who is having problem running
"Stored Procedures". The DB2 Connect application works fine...
|
by: fniles |
last post by:
I am using VB.NET 2003 and SQL2000 database.
I have a stored procedure called "INSERT_INTO_MYTABLE" that accepts 1
parameter (varchar(10)) and returns the identity column value from that
table....
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The...
| |
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated ...
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence...
| |