Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2004
    Posts
    5

    Exclamation Unanswered: unable to get records from temp table using ASP

    PLease help me with a intruiging problem with Stored Procedure, which is driving me mad.......

    I wrote a Stored Procedure as listed below.
    I've included the options
    SET NOCOUNT ON
    SET QUOTED_IDENTIFIER OFF

    the results are stored in a #Temp table

    The SP executes fine on Query Analyzer, and shows the contents of the temp table. BUt when I run this SP using ASP, I got the following error
    -----------
    ADODB.Recordset (0x800A0E78)
    Operation is not allowed when the object is closed.
    /test_site/test.asp, line 59
    ------------

    I've tried using Recordset.MoveNextRecordset, if at all there are more than one recordset, obviously I got the same error







    CREATE PROCEDURE atul_med.[sp_rep_sale2]
    (
    @rep_in varchar(100)

    )
    AS
    SET NOCOUNT ON
    SET QUOTED_IDENTIFIER OFF

    DECLARE
    @REP int,
    @temp_pack varchar(20),
    @temp_ntp int,
    @temp_party_code varchar(20),
    @temp_party_name varchar(20),
    @temp_prod_code varchar(20),
    @temp_prod_desc varchar(20),
    @tot_qty int,
    @tot_amount int,
    @iCnt int,
    @tot_sale_qty int,
    @net_tot_sale_qty int,
    @TableName varchar(10)

    select @REP= @rep_in
    set @tot_sale_qty=0
    set @net_tot_sale_qty=0
    set @TableName='#Test2'
    --select @TableName='Test'
    set @page=1
    set @RecsPerPage=100

    Declare @SQL VarChar(1000),
    @cnt int,
    @iCurLine int, @SQLX VarChar(1000)


    set @iCurLine=1


    set @iCurLine=1

    select @Cnt = (SELECT count(*) FROM VW_PART_REP WHERE (REP = @REP))
    print "Nos Of Party :"+ convert(char(1),@iCnt)

    Create Table #Test2 (ID int identity,prod_desc nvarchar(100),pack nvarchar(100),ntp int)-- ,tot_qty int ,tot_amt int )




    while @iCurLine <= @cnt
    begin
    SELECT @SQL= "ALTER TABLE #Test2 ADD "
    SELECT @SQL = @SQL + "party_qty"+convert(char(2),@iCurLine)+" int "
    set @iCurLine=@iCurLine+1
    Exec (@SQL)
    print @sql
    end
    SELECT @SQL= "ALTER TABLE #Test2 ADD "
    SELECT @SQL = @SQL +"tot_qty int , tot_amt int "
    SELECT @SQL = @SQL
    exec (@SQL)
    print @SQL



    select * from #Test2

    --Create Table #Test1 (ID int identity,prod_desc nvarchar(100),pack nvarchar(100),ntp int,party_qty1 int ,party_qty2 int ,party_qty3 int ,party_qty4 int ,party_qty5 int ,tot_qty int ,tot_amt int )
    set @iCurLine=1


    --=============================================



    --print '=================Product Name========================'
    DECLARE PROD_CUR CURSOR FOR
    SELECT distinct(prod_code) as prod_code,prod_desc,pack,ntp FROM VW_sales_sum
    WHERE (REP = @REP)
    group by prod_code,prod_desc,pack,ntp

    Declare @SQL_ins VarChar(1000)

    OPEN PROD_CUR
    FETCH NEXT FROM PROD_CUR
    INTO @temp_prod_code,@temp_prod_desc,@temp_pack,@temp_n tp

    WHILE @@FETCH_STATUS = 0
    BEGIN


    --Print @temp_prod_desc+'-'+convert(varchar(10),@temp_pack)+'-'+convert(varchar(10),@temp_ntp)
    --print '=================prod_code :'+convert(varchar(10),@temp_prod_code)+'========= ==============='
    print "============Insert Statement============"

    SET @SQL_ins = "Insert into "+@TableName+" values("
    SET @SQL_ins = @SQL_ins +""""+ @temp_prod_desc+""","""+@temp_pack+""","""+convert (char(10),@temp_ntp)+""","


    DECLARE Party_CUR1 CURSOR FOR
    SELECT party_code,party_name FROM VW_PART_REP
    WHERE (REP = @REP) group by party_code,party_name

    DECLARE @SQL1 varchar(10),@SQL2 varchar(100)
    set @SQL2=''
    OPEN Party_CUR1
    FETCH NEXT FROM Party_CUR1
    INTO @temp_party_code,@temp_party_name



    WHILE @@FETCH_STATUS = 0
    BEGIN
    print "==Party_name :"+ @temp_party_name +"===Party_Code :"+convert(char(3),@temp_party_Code)
    set @tot_sale_qty=0
    set @tot_sale_qty= (select sum(issuedqty)as tot_qty from vw_sales_sum
    where party_code =@temp_party_code
    and (REP = @REP)
    and (prod_code=@temp_prod_code))
    if @tot_sale_qty IS NULL
    begin
    set @tot_sale_qty=0
    print "===Tot Qty :"+convert(varchar(10),@tot_sale_qty)
    end
    else
    begin
    print "===Tot Qty :"+convert(varchar(10),@tot_sale_qty)
    end
    SELECT @SQL1 = @tot_sale_qty
    select @net_tot_sale_qty=@net_tot_sale_qty+@tot_sale_qty
    --print @SQL1
    Select @SQL2 = @SQL2 + @SQL1 +","



    FETCH NEXT FROM Party_CUR1
    INTO @temp_party_code,@temp_party_name

    END

    CLOSE Party_CUR1
    DEALLOCATE Party_CUR1
    print '==============================='
    print @SQL2+convert(char(4),@net_tot_sale_qty)


    --set @net_tot_sale_qty=@tot_sale_qty+@net_tot_sale_qty
    --SET @SQL = @SQL+convert(char(10),@tot_sale_qty)+','
    --set @tot_sale_qty=0

    Select @SQL_ins = @SQL_ins+@SQL2+convert(char(8),@net_tot_sale_qty)
    Select @SQL_ins = @SQL_ins+","
    Select @SQL_ins = @SQL_ins+convert(char(12),(@net_tot_sale_qty*@temp _ntp))
    Select @SQL_ins = @SQL_ins+")"
    print @SQL_ins
    Exec (@SQL_ins)

    select @net_tot_sale_qty=0
    FETCH NEXT FROM PROD_CUR
    INTO @temp_prod_code,@temp_prod_desc,@temp_pack,@temp_n tp


    END


    CLOSE PROD_CUR

    DEALLOCATE PROD_CUR



    SELECT * FROM #Test2


    SET NOCOUNT OFF
    GO
    SET QUOTED_IDENTIFIER ON
    GO

  2. #2
    Join Date
    Feb 2004
    Posts
    492
    #temp tables cannot be accessed from outside sqlserver or other processes (not directly anyway), also, a #temp-table is dropped once the sp completes.

    ##temp-tables can be accessed from outside sqlserver and other processes but is dropped once when the connection is closed which created the table.

  3. #3
    Join Date
    Nov 2004
    Posts
    5

    re

    Thank you, Kaiowas for your reply.
    Similar to the code of SP I've posted, i've tried using simple SPs where I insert values into a #temp table and just before the last line of the PS I would write a SELECT statement. No doubt the#temp table gets destroyed after execution of SP is over but I could get the resultset returned from the SP in the ASP page. So to say I'm not trying to access the #temp table from ASP but I'm trying to retreive the resultset returned by the SP. I get it in Query Analyzer but not in the ASP page.

  4. #4
    Join Date
    Mar 2004
    Location
    Glasgow, Scotland
    Posts
    107
    Hi there,

    If the SP works in query analyzer but not in the ASP page then I would almost definately say that there is something wrong with your ASP code. I use ASP.NET to access SQL temp tables all the time...so I would look again at your ASP code.

    Cheers

  5. #5
    Join Date
    Nov 2004
    Posts
    5
    Hi mmcdonald !!

    May be you are right but I can't figure out where it is.

    The ASP code i use to invoke the SP is :-

    Set obj_rs1=Server.CreateObject("ADODB.Recordset")
    strSQL = " atul_med.[sp_rep_sale2] '0401'"
    obj_rs1.Open strSQL,Con,1,3

    So far there is no error.

    But the momment i try to access the values in the Recordset I get the error :-

    -----------
    ADODB.Recordset (0x800A0E78)
    Operation is not allowed when the object is closed.
    /test_site/test.asp, line 59
    ------------

    When I checked the Recordset State before accessing the value it shows that it is closed.

    This is a really crazy bug for one.......

    Pls see if you can throw some light in solving this.

  6. #6
    Join Date
    Mar 2004
    Location
    Glasgow, Scotland
    Posts
    107
    I tend to use either a DataReader or DataSet to access data like this as opposed to ADODB, here are a couple of methods from one my classes as an example:

    public static SqlDataReader fillDataReader(string sqlString)
    {

    SqlConnection conn = openConn();
    SqlCommand cmd = conn.CreateCommand();
    cmd.CommandType = CommandType.Text;
    cmd.CommandText = sqlString; SqlDataReader dr = cmd.ExecuteReader();

    return dr;

    }

    public static DataSet fillDataSet(string sqlString, string tableName, DataSet ds)
    {
    SqlConnection conn = openConn();
    SqlCommand cmd = new SqlCommand(sqlString, conn);
    SqlDataAdapter adpt= new SqlDataAdapter(cmd);
    adpt.Fill(ds,tableName);

    adpt.Dispose();
    cmd.Dispose();
    closeConn(conn);

    return ds;

    }

  7. #7
    Join Date
    Nov 2004
    Posts
    5

    Smile

    I'm working with ASP and not ASP.NET.
    But then, Thank you mmcdonald for trying to help me in you own way.
    I'm learning ASP.NET but I'm half way through a project in ASP where I encountered the problem.
    I would be very grateful if can show me some good links or e-books for learning .NET

  8. #8
    Join Date
    Nov 2004
    Posts
    5
    somebody pls help.............

  9. #9
    Join Date
    May 2004
    Posts
    125
    Just to try it....put this in the ASP code and see what happens:

    Set obj_rs1=Server.CreateObject("ADODB.Recordset")
    strSQL = "exec atul_med.[sp_rep_sale2] '0401'"
    obj_rs1.Open strSQL,Con,1,3

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •