Operation Not Allowed When The object is closed

  • HI Guys,

    Following is the VB Part.

    Function getSalesByPayment(pCompanyId As String, pDeptcode As String) As adodb.Recordset

    Dim myCmd As New adodb.command

    With myCmd

    .ActiveConnection = gDatabase.Conn

    .CommandType = adCmdStoredProc

    .CommandText = "getSalesByPayment"

    .Parameters.Append .CreateParameter("@CompanyId", adVarChar, adParamInput, 3, pCompanyId)

    .Parameters.Append .CreateParameter("@DeptCode", adVarChar, adParamInput, 3, pDeptcode)

    Set getSalesByPayment = .Execute

    End With

    Set myCmd = Nothing

    End Function

    Following is the Stored Procedure.

    Declare @TempTable Table (Column1 Varchar(3), Column2 Varchar(3))

    insert into @TempTable select COMpanyId,DeptCode from Department

    select * from @TempTable

    WhenI try to do anything like move next in the VB, I get object is closed. Wht I should do?

  • Sure the connection is open? Permissions set on the proc? Tried checking to see if you're getting a recordset back at all?

    Andy

    http://www.sqlservercentral.com/columnists/awarren/

  • Hi Andy,

    There is no problem with permission or anything of that sort. Because if i change the stored procedure to just select * from department, then it works, i.e record set is returned and the object is open. When I use table variable or temporary table VB says "object is closed".

    Because table variable goes out of scope when stored procedure finishes, the resultset returned is also closed

    Can this be the case?. It cant the case, otherwise, use table variable will lose its meaning.

    By the way table variable and temporay table work beautifully in query analyser

    Any idea is welcome

    Thanks

    mani

  • Whenever you perform a t-sql action (such as an insert), a result set is returned with the number of rows affected. ADO, therefore, is getting the number of rows inserted back as its first recordset and therefore fails.

    In your SP, you need to use SET NOCOUNT OFF to turn this off. When you do you select, you turn it on.

    You can see the difference when you execute the SP in QA. look in the messages to see it.

    Regards,

    Dean

  • Hi Conwaydean,

    I tried set nocount off/on option as well. Still problem persists. For better understanding I will list actual stored Procedure here.

    --------------------------------------

    begin

    SET NOCOUNT OFF

    declare @TempTable table

    (CompanyId varchar(3),DeptCode varchar(3),DocketNo integer,TransactionType varchar(10),

    productDescription varchar(16),Totalvalue money,Paymethod varchar(10),paytendered money)

    insert into @TempTable

    select p.CompanyId ,p.DeptCode,p.DocketNo ,p.TransactionType,

    p.productDescription ,p.Totalvalue ,p.Paymethod , p.paytendered from postransaction P inner join

    (select distinct CompanyId,Deptcode,docketno

    from postransaction

    where

    CompanyId=@CompanyId

    and deptCode=@deptCode

    and status='Xreg'

    and isvoid=0

    and transactiontype='Sale' ) A

    on p.companyid=A.companyid

    and p.deptcode=A.deptcode

    and P.docketno=A.docketNo

    where transactiontype='Tendered'

    and productDescription<>'Rounding'

    SET NOCOUNT ON

    select A.Paymethod ,(isnull(sum(A.TotalValue),0)+isnull(sum(B.TotalValue),0))*-1 from

    (select * from @TempTable

    where totalvalue<0)A

    left outer join

    (select * from @TempTable

    where totalvalue>0)

    B on A.docketno=b.DocketNo

    group by a.paymethod

    order by A.Paymethod

    end

    --------------------------------------

  • Thanks a lot. It works when I reversed set nocount on/off in the above example.

    You saved lot of my time and i learned one more thing today

    Regards

    mani

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply