July 13, 2003 at 7:45 pm
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?
July 13, 2003 at 8:31 pm
Sure the connection is open? Permissions set on the proc? Tried checking to see if you're getting a recordset back at all?
Andy
July 13, 2003 at 8:42 pm
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
July 13, 2003 at 8:58 pm
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
July 13, 2003 at 9:19 pm
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
--------------------------------------
July 13, 2003 at 10:04 pm
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