bombplayer
Programmer
When running an insert command I am receiving the subject error. I have tried to change some things in the code to reference ideas that I found on the net but to no avail. Can anyone help?
Create Procedure sp_saPostPOSData
As
Set nocount on
Declare @C_SyStudentID int,
@C_AdEnrollID int,
@C_Source char,
@C_Discount int,
@C_Payments int,
@C_FaPmtPeriodID int,
@C_Academicyear int,
@C_Sentback int,
@C_sabankdepid int,
@C_commrate int,
@C_commpaid int,
@C_glpost int,
@C_fastudentaid int,
@C_totalizer int ,
@C_salesdate datetime,
@C_units int,
@C_totalsales money,
@C_sabillcode char(3),
@C_sycampusid int,
@C_descrip char(40),
@C_type char(4),
@C_amount money,
@C_status char(1),
@C_amrepid int,
@C_adduserid int,
@C_userid int,
@C_date datetime,
@C_postdate datetime,
@C_dateadded datetime,
@C_datelstmod datetime,
@C_ref char,
@C_adtermid int
Declare @MyTran int -- Next Transaction Number
Declare C Cursor For
Select SyStudentID,
AdEnrollID,
Source,
Discount,
Payments,
FaPmtPeriodID,
Academicyear,
Sentback,
sabankdepid,
commrate,
commpaid,
glpost,
fastudentaidid,
totalizer,
salesdate,
units,
totalsales,
sabillcode,
sycampusid,
descrip,
type,
amount,
status,
amrepid,
adduserid,
userid,
date,
postdate,
dateadded,
datelastmod,
ref,
adtermid From POSTable
Open C
Fetch Next From C
Into @C_SyStudentID,
@C_AdEnrollID,
@C_Source,
@C_Discount,
@C_Payments,
@C_FaPmtPeriodID,
@C_Academicyear,
@C_Sentback,
@C_sabankdepid,
@C_commrate,
@C_commpaid,
@C_glpost,
@C_fastudentaid,
@C_totalizer,
@C_salesdate,
@C_units,
@C_totalsales,
@C_sabillcode,
@C_sycampusid,
@C_descrip,
@C_type,
@C_amount,
@C_status,
@C_amrepid,
@C_adduserid,
@C_userid,
@C_date,
@C_postdate,
@C_dateadded,
@C_datelstmod,
@C_ref,
@C_adtermid
While @@Fetch_Status = 0
BEGIN
-- Update SyStudent (NextTranNum, ARBalance)
IF (@C_Totalizer in ('5001','5002','5003','5004','5005','5006','5007','5008','5009'))
Update SyStudent WITH (ROWLOCK)
Set ArBalance = (ArBalance + @C_TotalSales),
NextTranNum = NextTranNum + 1,
DateLstMod = GetDate()
Where SyStudentID in (@C_SyStudentID)
IF (@C_Totalizer in ('1201','1202','1203','1204','1205','1206','1230','1231','1232','1233'))
Update SyStudent WITH (ROWLOCK)
Set ArBalance = (ArBalance - @C_TotalSales),
NextTranNum = NextTranNum + 1,
DateLstMod = GetDate()
Where SyStudentID in (@C_SyStudentID)
IF (@C_Totalizer in ('22','26','30','34'))
Update SyStudent WITH (ROWLOCK)
Set ArBalance = (ArBalance + @C_TotalSales),
NextTranNum = NextTranNum + 1,
DateLstMod = GetDate()
Where SyStudentID in (@C_SyStudentID)
-- Get new transaction Number
Select @MyTran = NextTranNum
From SyStudent (ROWLOCK)
Where SyStudentID in (@C_SyStudentID)
-- Update AdEnroll (ARBalance)
IF (@C_Totalizer in ('5001','5002','5003','5004','5005','5006','5007','5008','5009'))
Update AdEnroll WITH (ROWLOCK)
Set ArBalance = (ArBalance + @C_TotalSales)
Where AdEnrollID in (@C_AdEnrollID)
IF (@C_Totalizer in ('1201','1202','1203','1204','1205','1206','1230','1231','1232','1233'))
Update AdEnroll WITH (ROWLOCK)
Set ArBalance = (ArBalance - @C_TotalSales)
Where AdEnrollID in (@C_AdEnrollID)
IF (@C_Totalizer in ('22','26','30','34'))
Update AdEnroll WITH (ROWLOCK)
Set ArBalance = (ArBalance + @C_TotalSales)
Where AdEnrollID in (@C_AdEnrollID)
-- Insert New Row into SaTrans
Insert SaTrans WITH (ROWLOCK)
(SyStudentID,
SyCampusID,
AdEnrollID,
Source,
Type,
TranNum,
Descrip,
Status,
Date,
PostDate,
Amount,
PaymentType,
FaPmtPeriodID,
AdTermID,
SaBankAccountID,
Ref,
Academicyear,
Sentback,
sabankdepid,
commrate,
commpaid,
glpost,
fastudentaidid,
sabillcode,
amrepid,
adduserid,
userid,
dateadded,
datelstmod)
Select @C_SyStudentID,
@C_SyCampusID,
@C_AdEnrollID,
@C_Source,
@C_type,
@MyTran,
@C_descrip,
@C_status,
@C_date,
@C_postdate,
@C_amount,
' ',
@C_FaPmtPeriodID,
@C_adtermid,
' ',
@C_ref,
@C_Academicyear,
@C_Sentback,
@C_sabankdepid,
@C_commrate,
@C_commpaid,
@C_glpost,
@C_fastudentaid,
@C_sabillcode,
@C_amrepid,
@C_adduserid,
@C_userid,
@C_dateadded,
@C_datelstmod from POSTable
-- Get Next Row
Fetch Next From C
Into @C_SyStudentID,
@C_AdEnrollID,
@C_Source,
@C_Discount,
@C_Payments,
@C_FaPmtPeriodID,
@C_Academicyear,
@C_Sentback,
@C_sabankdepid,
@C_commrate,
@C_commpaid,
@C_glpost,
@C_fastudentaid,
@C_totalizer,
@C_salesdate,
@C_units,
@C_totalsales,
@C_sabillcode,
@C_sycampusid,
@C_descrip,
@C_type,
@C_amount,
@C_status,
@C_amrepid,
@C_adduserid,
@C_userid,
@C_date,
@C_postdate,
@C_dateadded,
@C_datelstmod,
@C_ref,
@C_adtermid
END
Close C
Deallocate C
return(0)
ErrTag:
return(1)
GO
Create Procedure sp_saPostPOSData
As
Set nocount on
Declare @C_SyStudentID int,
@C_AdEnrollID int,
@C_Source char,
@C_Discount int,
@C_Payments int,
@C_FaPmtPeriodID int,
@C_Academicyear int,
@C_Sentback int,
@C_sabankdepid int,
@C_commrate int,
@C_commpaid int,
@C_glpost int,
@C_fastudentaid int,
@C_totalizer int ,
@C_salesdate datetime,
@C_units int,
@C_totalsales money,
@C_sabillcode char(3),
@C_sycampusid int,
@C_descrip char(40),
@C_type char(4),
@C_amount money,
@C_status char(1),
@C_amrepid int,
@C_adduserid int,
@C_userid int,
@C_date datetime,
@C_postdate datetime,
@C_dateadded datetime,
@C_datelstmod datetime,
@C_ref char,
@C_adtermid int
Declare @MyTran int -- Next Transaction Number
Declare C Cursor For
Select SyStudentID,
AdEnrollID,
Source,
Discount,
Payments,
FaPmtPeriodID,
Academicyear,
Sentback,
sabankdepid,
commrate,
commpaid,
glpost,
fastudentaidid,
totalizer,
salesdate,
units,
totalsales,
sabillcode,
sycampusid,
descrip,
type,
amount,
status,
amrepid,
adduserid,
userid,
date,
postdate,
dateadded,
datelastmod,
ref,
adtermid From POSTable
Open C
Fetch Next From C
Into @C_SyStudentID,
@C_AdEnrollID,
@C_Source,
@C_Discount,
@C_Payments,
@C_FaPmtPeriodID,
@C_Academicyear,
@C_Sentback,
@C_sabankdepid,
@C_commrate,
@C_commpaid,
@C_glpost,
@C_fastudentaid,
@C_totalizer,
@C_salesdate,
@C_units,
@C_totalsales,
@C_sabillcode,
@C_sycampusid,
@C_descrip,
@C_type,
@C_amount,
@C_status,
@C_amrepid,
@C_adduserid,
@C_userid,
@C_date,
@C_postdate,
@C_dateadded,
@C_datelstmod,
@C_ref,
@C_adtermid
While @@Fetch_Status = 0
BEGIN
-- Update SyStudent (NextTranNum, ARBalance)
IF (@C_Totalizer in ('5001','5002','5003','5004','5005','5006','5007','5008','5009'))
Update SyStudent WITH (ROWLOCK)
Set ArBalance = (ArBalance + @C_TotalSales),
NextTranNum = NextTranNum + 1,
DateLstMod = GetDate()
Where SyStudentID in (@C_SyStudentID)
IF (@C_Totalizer in ('1201','1202','1203','1204','1205','1206','1230','1231','1232','1233'))
Update SyStudent WITH (ROWLOCK)
Set ArBalance = (ArBalance - @C_TotalSales),
NextTranNum = NextTranNum + 1,
DateLstMod = GetDate()
Where SyStudentID in (@C_SyStudentID)
IF (@C_Totalizer in ('22','26','30','34'))
Update SyStudent WITH (ROWLOCK)
Set ArBalance = (ArBalance + @C_TotalSales),
NextTranNum = NextTranNum + 1,
DateLstMod = GetDate()
Where SyStudentID in (@C_SyStudentID)
-- Get new transaction Number
Select @MyTran = NextTranNum
From SyStudent (ROWLOCK)
Where SyStudentID in (@C_SyStudentID)
-- Update AdEnroll (ARBalance)
IF (@C_Totalizer in ('5001','5002','5003','5004','5005','5006','5007','5008','5009'))
Update AdEnroll WITH (ROWLOCK)
Set ArBalance = (ArBalance + @C_TotalSales)
Where AdEnrollID in (@C_AdEnrollID)
IF (@C_Totalizer in ('1201','1202','1203','1204','1205','1206','1230','1231','1232','1233'))
Update AdEnroll WITH (ROWLOCK)
Set ArBalance = (ArBalance - @C_TotalSales)
Where AdEnrollID in (@C_AdEnrollID)
IF (@C_Totalizer in ('22','26','30','34'))
Update AdEnroll WITH (ROWLOCK)
Set ArBalance = (ArBalance + @C_TotalSales)
Where AdEnrollID in (@C_AdEnrollID)
-- Insert New Row into SaTrans
Insert SaTrans WITH (ROWLOCK)
(SyStudentID,
SyCampusID,
AdEnrollID,
Source,
Type,
TranNum,
Descrip,
Status,
Date,
PostDate,
Amount,
PaymentType,
FaPmtPeriodID,
AdTermID,
SaBankAccountID,
Ref,
Academicyear,
Sentback,
sabankdepid,
commrate,
commpaid,
glpost,
fastudentaidid,
sabillcode,
amrepid,
adduserid,
userid,
dateadded,
datelstmod)
Select @C_SyStudentID,
@C_SyCampusID,
@C_AdEnrollID,
@C_Source,
@C_type,
@MyTran,
@C_descrip,
@C_status,
@C_date,
@C_postdate,
@C_amount,
' ',
@C_FaPmtPeriodID,
@C_adtermid,
' ',
@C_ref,
@C_Academicyear,
@C_Sentback,
@C_sabankdepid,
@C_commrate,
@C_commpaid,
@C_glpost,
@C_fastudentaid,
@C_sabillcode,
@C_amrepid,
@C_adduserid,
@C_userid,
@C_dateadded,
@C_datelstmod from POSTable
-- Get Next Row
Fetch Next From C
Into @C_SyStudentID,
@C_AdEnrollID,
@C_Source,
@C_Discount,
@C_Payments,
@C_FaPmtPeriodID,
@C_Academicyear,
@C_Sentback,
@C_sabankdepid,
@C_commrate,
@C_commpaid,
@C_glpost,
@C_fastudentaid,
@C_totalizer,
@C_salesdate,
@C_units,
@C_totalsales,
@C_sabillcode,
@C_sycampusid,
@C_descrip,
@C_type,
@C_amount,
@C_status,
@C_amrepid,
@C_adduserid,
@C_userid,
@C_date,
@C_postdate,
@C_dateadded,
@C_datelstmod,
@C_ref,
@C_adtermid
END
Close C
Deallocate C
return(0)
ErrTag:
return(1)
GO