ASP with stored procedure problem....

Discussion in 'ASP General' started by Vitamin, Nov 7, 2003.

  1. Vitamin

    Vitamin Guest

    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)
    Vitamin, Nov 7, 2003
    #1
    1. Advertising

  2. Vitamin

    Ray at Guest

    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" <> wrote in message
    news:...
    > 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.
    Ray at, Nov 7, 2003
    #2
    1. Advertising

  3. Vitamin

    Vitamin Guest

    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:%...
    > 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" <> wrote in message
    > news:...
    > > 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, Nov 7, 2003
    #3
  4. Vitamin

    Vitamin Guest

    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:%...
    > 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" <> wrote in message
    > news:...
    > > 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, Nov 7, 2003
    #4
  5. Vitamin

    Ray at Guest

    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" <> wrote in message
    news:...
    > 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:%...
    > > Try doing:
    > >
    > > CREATE PROCEDURE sp_admin_member_searching
    > > @request_page INT = 1,
    > > @page_size INT = 0,
    > > @id INT = 0,
    > > @membership_id INT = 0,
    > > @area_
    Ray at, Nov 7, 2003
    #5
  6. Vitamin

    Vitamin Guest

    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:%...
    > 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" <> wrote in message
    > news:...
    > > 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:%...
    > > > 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, Nov 7, 2003
    #6
  7. Vitamin

    Vitamin Guest

    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:%...
    > 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" <> wrote in message
    > news:...
    > > 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:%...
    > > > 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, Nov 7, 2003
    #7
  8. Vitamin

    Vitamin Guest

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

    "Ray at <%=sLocation%>" <myfirstname at lane 34 . komm> wrote in message
    news:%...
    > 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" <> wrote in message
    > news:...
    > > 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:%...
    > > > 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, Nov 7, 2003
    #8
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. =?Utf-8?B?Um9iIEM=?=
    Replies:
    0
    Views:
    570
    =?Utf-8?B?Um9iIEM=?=
    Apr 16, 2005
  2. Sam
    Replies:
    6
    Views:
    9,276
    =?Utf-8?B?U2FuZHk=?=
    May 11, 2005
  3. Mike P
    Replies:
    0
    Views:
    3,297
    Mike P
    Jun 19, 2006
  4. M Wells
    Replies:
    0
    Views:
    339
    M Wells
    Mar 3, 2004
  5. .Net Sports

    Stored Procedure call from ASP page problem

    .Net Sports, Jun 4, 2007, in forum: ASP General
    Replies:
    3
    Views:
    193
    Daniel Crichton
    Jun 5, 2007
Loading...

Share This Page