thankyou,
rack no is added field in cursor sql statement.
pl find attachment.
phani
Dana <[EMAIL PROTECTED]> wrote:
Show us the code.
-----Original Message-----
From: [email protected]
[mailto:[EMAIL PROTECTED] Behalf Of dasaga_phani
Sent: Monday, February 19, 2007 6:43 AM
To: [email protected]
Subject: [Access VBA Central] cursor not fetching data - sql server
Hi all,
i just modified existing cursor in stored procedure by adding one more
field to the cursor existing query, at the end of the procedure
inserting data into another table, only that fields data is not
fetching. In destination table, with exact data type, new field was
created.
Now,
How can i view the cursor data ?
or how my problem will be solved?
Please help.
phani.
[Non-text portions of this message have been removed]
---------------------------------
Heres a new way to find what you're looking for - Yahoo! Answers
----------
CREATE PROCEDURE InvStockBank
@cmpid varchar(6), @locid varchar(6), @finyr
varchar(15),
@ItemId varchar(10),@sdate datetime, @Flag
char(1) -- Item ID(Catid or Grpid or Subgrpid)
as
set nocount on
declare @vMatcode varchar(13) -- Material Code
declare @vMatName varchar(100) -- Material Name
declare @vUnit varchar(15) -- Unit of Measurement
declare @vQty numeric(16,4) -- Existing Stock Qty
declare @vRate numeric(16,4) -- Net of Exicise Rate
from Mat. Master
declare @vValue numeric(16,4) -- Value in Lacs.
declare @vOpenBal numeric(16,4) -- Opening Stock
declare @vRQty numeric(16,4)
declare @vIQty numeric(16,4)
declare @vNetofExciseRate numeric(16,4)
declare @vNetPrice numeric(16,4)
declare @vRackno varchar(10) -- physical location of
the material in stores
Delete from StockPosRpt
-- Declare CurCats cursor local static for
-- Select Matcode,MatNam,MAPrice,StdUom From InvMatMst
-- Where MatCode in (select distinct Matcode from invgridet where
Matcode Like @ItemID and [EMAIL PROTECTED] and [EMAIL PROTECTED] and [EMAIL
PROTECTED])
-- and BankFlg='Y'
IF @Flag = 'Y'
begin
Declare CurCats cursor local static for
Select Matcode,MatNam,MAPrice,StdUom,MANetPrice,Rackno From
InvMatMst Where Matcode Like @ItemID and [EMAIL PROTECTED] and [EMAIL
PROTECTED] and BankFlg='Y' order by Matcode
end
else
begin
Declare CurCats cursor local static for
Select Matcode,MatNam,MAPrice,StdUom,MANetPrice,Rackno From
InvMatMst Where Matcode Like @ItemID and [EMAIL PROTECTED] and [EMAIL
PROTECTED] order by Matcode
end
Open CurCats
fetch next from CurCats into
@vMatcode,@vMatName,@vRate,@vUnit,@vNetPrice,@vRackno
while @@fetch_status = 0
begin
--============= Calculating Net of Excise Value ==========================
-- set @vLRate =
((isnull(@vAssrate,0)*isnull(@vRecQty,0))+isnull(@vExcise,0)+isnull(@vSalestax,0)+isnull(@vOtherduty,0))/isnull(@vRecQty,0)
-- set @vNetofExciseRate = @vLRate - (isnull(@vexcise,0)/@vAppQty)
--=================================================================
set @vNetofExciseRate = 0
if @Flag='Y'
begin
select @vNetofExciseRate =ISNULL(
((((isnull(AssRate,0)*isnull(RecQty,0))+isnull(Excise,0)+isnull(SalesTax,0)+isnull(OtherDuty,0))/isnull(RecQty,0))-(isnull(Excise,0)/isnull(RecQty,0))),
0)
from invgridet where cast(left(grino,5) as int)=(SELECT
max(CAST(LEFT(h.GRINO,5) AS INT)) as GRINO from invgrihdr h,invgridet d where
h.grino=d.grino and h.finyrid=d.finyrid and [EMAIL PROTECTED] and
h.gridt<[EMAIL PROTECTED] and [EMAIL PROTECTED])
and [EMAIL PROTECTED] and [EMAIL PROTECTED]
end
else
begin
select @vNetofExciseRate
=(((isnull(AssRate,0)*isnull(RecQty,0))+isnull(Excise,0)+isnull(SalesTax,0)+isnull(OtherDuty,0))/isnull(RecQty,0))
from invgridet where cast(left(grino,5) as int)=(SELECT
max(CAST(LEFT(h.GRINO,5) AS INT)) as GRINO from invgrihdr h,invgridet d where
h.grino=d.grino and h.finyrid=d.finyrid and [EMAIL PROTECTED] and
h.gridt<[EMAIL PROTECTED] and [EMAIL PROTECTED])
and [EMAIL PROTECTED] and [EMAIL PROTECTED]
end
set @vQty =0
select @vOpenBal=isnull(sum(isnull(OpStockBal,0)),0) from
invwvsm where [EMAIL PROTECTED] and OpDate<[EMAIL PROTECTED] and [EMAIL
PROTECTED]
select @vRQty=isnull(sum(isnull(d.appqty,0)),0) from invgridet
d,invgrihdr h
where h.grino=d.grino and h.finyrid=d.finyrid and
h.gridt<[EMAIL PROTECTED] and [EMAIL PROTECTED] and [EMAIL PROTECTED]
select @vIQty=isnull(sum(isnull(d.isuqty,0)),0) from invmivdet
d,invmivhdr h
where h.mivno=d.mivno and h.finyrid=d.finyrid and
h.mivdt<[EMAIL PROTECTED] and [EMAIL PROTECTED] and [EMAIL PROTECTED]
set @vQty = @vOpenBal + @vRQty - @vIQty
-- set @vValue = @vQty * @vRate
-- SELECT @vNetofExciseRate,@vRate
set @vNetofExciseRate = isnull(@vNetofExciseRate,0)
-- select @vNetofExciseRate,@vRQty
if @vNetofExciseRate =0 and @vRQty=0
set @vValue = @vQty * isnull(@vRate,0)
else
set @vValue = @vQty * isnull(@vNetofExciseRate,0)
-- select @vRqty,@vIQty,@vOpenBal
if @vNetofExciseRate =0 and @vRQty=0
begin
INSERT INTO StockPosRpt
(Matcode,MatNam,Unit,Qty,Rate,Value,Flag,Rackno)
VALUES
(@vMatcode,@vMatName,@vUnit,@vQty,@vRate,@vValue,'M',@vRackno)
end
else
begin
-- select @vMatcode,@vNetofExciseRate
INSERT INTO StockPosRpt
(Matcode,MatNam,Unit,Qty,Rate,Value,Flag,Rackno)
VALUES
(@vMatcode,@vMatName,@vUnit,@vQty,@vNetofExciseRate,@vValue,'G',@vRackno)
end
fetch next from CurCats into
@vMatcode,@vMatName,@vRate,@vUnit,@vNetPrice,@vRackno
end
close CurCats
Deallocate CurCats
GO
[Non-text portions of this message have been removed]
