Cursor Problem in an DTS ActivX package.

Hi

I am using Cursor in an DTS ActivX package.

I got the problem when i update it's not updating prorerly Please find the code below and help me ..My insert is working fine but the problem with update it's only updating first record that to it's replace the last record in first record place

sSSDInsUpd = " DECLARE "& _ " @.CallID2 AS Int "& _ " Declare InsertCur Cursor For "& _ " SELECT DISTINCT CALLID "& _ " FROM [SSD] "& _ " WHERE ASSOCCLAIM is not null AND SSN IS NOT NULL AND "& _ " FILENETPROCESSFLAG=1 "& _ " OPEN InsertCur "& _ " FETCH NEXT FROM InsertCur "& _ " into @.CallID2 "& _ " WHILE @.@.FETCH_STATUS = 0 "& _ " BEGIN "& _ " If @.CallID2 not in (select CALLID FROM SSDTOARTS) "& _ " Begin "& _ " INSERT INTO SSDTOARTS ( "& _ " [ID], [CALLID],[FIRSTNAME],[MIDDLEINIT],[LASTNAME],[SUFFIX], "& _ " [DATEOFBIRTH],[SSN],[ADDR1],[ADDR2], [CITY], [STATE],[ZIP], "& _ " [DISTRICTCODE ],[COUNTYCODE ],[ACCENTCASENO],[ASSOCCLAIM], "& _ " [ASSOCCLMDTFILED ],[ASSOCCLMDECDT],[ASSOCCLMSTATUS], "& _ " [ASSOCCLMADDLACCENTACTION] , "& _ " [ASSOCCLMADDLACCENTACTIONTAKEN], "& _ " [SSAAPLFILED ],[SSAAPLDT],[SSAAPLRESULT], "& _ " [SSAAPLADDLACCENTACTION],[SSAAPLADDLACCENTACTIONTAKEN], "& _ " [SSAAplDecisionDt],[ROUNDNUMBER], [ARTSAplprocessedFlag],[ARTSprocessedDt],[DateModified] ) "& _ " SELECT DISTINCT "& _ " S.ID, S.CALLID, S.FIRSTNAME, S.MIDDLEINIT, S.LASTNAME,S.SUFFIX, "& _ " S.DATEOFBIRTH, S.SSN, S.ADDR1, S.ADDR2, S.CITY, S.STATE, S.ZIP, "& _ " S.DISTRICTCODE , S.COUNTYCODE , S.ACCENTCASENO, S.ASSOCCLAIM, "& _ " S.ASSOCCLMDTFILED ,S.ASSOCCLMDECDT, S.ASSOCCLMSTATUS, "& _ " S.ASSOCCLMADDLACCENTACTION , "& _ " S.ASSOCCLMADDLACCENTACTIONTAKEN, "& _ " S.SSAAPLFILED , S.SSAAPLDT, S.SSAAPLRESULT, "& _ " S.SSAAPLADDLACCENTACTION,S.SSAAPLADDLACCENTACTIONTAKEN, "& _ " S.SSAAplDecisionDt , S.ROUNDNUMBER, "& _ " 0 as ARTSAplprocessedFlag,Null as ARTSprocessedDt,getdate() as DateModified "& _ " FROM [SSD] AS S "& _ " WHERE S.ASSOCCLAIM = 1 AND S.SSN IS NOT NULL AND "& _ " S.CALLID = @.CallID2 AND S.FILENETPROCESSFLAG=1 "& _ " DECLARE @.err2 int "& _ " SELECT @.err2 = @.@.error "& _ " IF (@.err2 <> 0) "& _ " BEGIN "& _ " INSERT INTO SSDEXCEPTIONS (RecordData,ErrorMsg,Fileid,DateCreated) "& _ " SELECT "& _ " DISTINCT isnull(convert( varchar(10),CALLID),'') + ' |' + isnull(FIRSTNAME,'') + ' |' + "& _ " isnull(MIDDLEINIT,'') + '| ' + "& _ " isnull(LASTNAME,'')+ '|' + "& _ " isnull((SUFFIX),'') + '|' + "& _ " isnull(convert(varchar(10),DATEOFBIRTH ),'') + '|' + "& _ " isnull(convert(varchar(10),SSN),'') + '|' + "& _ " isnull(ADDR1,'') + '|' + "& _ " isnull(ADDR2,'') + '|' + "& _ " isnull(CITY,'') + '|' + "& _ " isnull([STATE],'') + '|' + "& _ " isnull(ZIP,'')+ '|' + "& _ " isnull(convert(varchar(10),DISTRICTCODE),'') + '|' + "& _ " isnull(convert(varchar (10),COUNTYCODE),'') + '|' + "& _ " isnull(ACCENTCASENO,'')+ '|' + "& _ " isnull(convert(nvarchar(2),ASSOCCLAIM),'') + '|' + "& _ " isnull(convert(varchar(10) ,ASSOCCLMDTFILED),'') + '|' + "& _ " isnull(convert(varchar(10) ,ASSOCCLMDECDT),'') + '|' + "& _ " isnull(ASSOCCLMSTATUS,'') + '|' + "& _ " isnull(convert(nvarchar(2 ),ASSOCCLMADDLACCENTACTION),'') + '|' + "& _ " isnull(ASSOCCLMADDLACCENTACTIONTAKEN,'')+ '|' + "& _ " isnull(convert(nvarchar(2),SSAAPLFILED),'') + '|' + "& _ " isnull(convert(varchar(10) ,SSAAPLDT),'') + '|' + "& _ " isnull(SSAAPLRESULT,'')+ '|' + "& _ " isnull(convert(nvarchar(2 ),SSAAPLADDLACCENTACTION),'') + '|' + "& _ " isnull(SSAAPLADDLACCENTACTIONTAKEN,'') + '|' + "& _ " isnull(convert(varchar(10 ),SSAAplDecisionDt),'')+ '|' + "& _ " isnull(convert(varchar(10 ),ROUNDNUMBER),'') ,ltrim(str(@.err2))," & sFileId & ",GETDATE() "& _ " FROM SSD WHERE CALLID=@.CALLID2 "& _ " END "& _ " END "& _ " ELSE "& _ " UPDATE [SSDTOARTS] SET "& _ " [ID]= S.ID, "& _ " [CALLID]= S.CALLID , "& _ " [FIRSTNAME]=S.FIRSTNAME , "& _ " [MIDDLEINIT]=S.MIDDLEINIT , "& _ " [LASTNAME]= S.LASTNAME, "& _ " [Suffix]= S.Suffix , "& _ " [DateOfBirth]=S.DateOfBirth , "& _ " [SSN]= S.SSN , "& _ " [ADDR1]= S.ADDR1 , "& _ " [ADDR2]= S.ADDR2 , "& _ " [CITY]= S.CITY , "& _ " [STATE]= S.STATE , "& _ " [ZIP]= S.ZIP , "& _ " [DistrictCode ]= S.DistrictCode , "& _ " [CountyCode ]= S.CountyCode , "& _ " [ACCENTCASENO]= S.ACCENTCASENO ,"& _ " [ASSOCCLAIM]= S.ASSOCCLAIM, "& _ " [ASSOCCLMDTFILED]= S.ASSOCCLMDTFILED , "& _ " [AssocClmDecDt]= s.AssocClmDecDt , "& _ " [ASSOCCLMSTATUS]= S.ASSOCCLMSTATUS, "& _ " [AssocClmAddlACCENTAction] = S.AssocClmAddlACCENTAction , "& _ " [AssocClmAddlACCENTActionTaken]= S.AssocClmAddlACCENTActionTaken, "& _ " [SSAAplFiled ]= S.SSAAplFiled , "& _ " [SSAAplDt]= S.SSAAplDt , "& _ " [SSAAplResult]= S.SSAAplResult, "& _ " [SSAAplAddlACCENTAction]= S.SSAAplAddlACCENTAction, "& _ " [SSAAplAddlACCENTActionTaken]= S.SSAAplAddlACCENTActionTaken , "& _ " [SSAAplDecisionDt] = S.SSAAplDecisionDt, "& _ " [ROUNDNUMBER]= S.ROUNDNUMBER , "& _ " [DateModified] = getdate(),"& _ " [ARTSprocessedDt]= NULL, "& _ " [ARTSAplprocessedFlag]= 0 "& _ " FROM [SSDtoARTS]AS SA, [SSD] AS S WHERE SA.CallID= @.Callid2 "& _ " AND S.ASSOCCLAIM IS NOT NULL AND S.FILENETPROCESSFLAG=1 "& _ " DECLARE @.err1 as int "& _ " SELECT @.err1 = @.@.error "& _ " IF (@.err1 <> 0) "& _ " BEGIN "& _ " INSERT INTO SSDEXCEPTIONS (RecordData,ErrorMsg,Fileid,DateCreated) "& _ " SELECT "& _ " DISTINCT isnull(convert( varchar(10),CALLID),'') + ' |' + isnull(FIRSTNAME,'') + ' |' + "& _ " isnull(MIDDLEINIT,'') + '| ' + "& _ " isnull(LASTNAME,'')+ '|' + "& _ " isnull((SUFFIX),'') + '|' + "& _ " isnull(convert(varchar(10),DATEOFBIRTH ),'') + '|' + "& _ " isnull(convert(varchar(10),SSN),'') + '|' + "& _ " isnull(ADDR1,'') + '|' + "& _ " isnull(ADDR2,'') + '|' + "& _ " isnull(CITY,'') + '|' + "& _ " isnull([STATE],'') + '|' + "& _ " isnull(ZIP,'')+ '|' + "& _ " isnull(convert(varchar(10),DISTRICTCODE),'') + '|' + "& _ " isnull(convert(varchar (10),COUNTYCODE),'') + '|' + "& _ " isnull(ACCENTCASENO,'')+ '|' + "& _ " isnull(convert(nvarchar(2),ASSOCCLAIM),'') + '|' + "& _ " isnull(convert(varchar(10) ,ASSOCCLMDTFILED),'') + '|' + "& _ " isnull(convert(varchar(10) ,ASSOCCLMDECDT),'') + '|' + "& _ " isnull(ASSOCCLMSTATUS,'') + '|' + "& _ " isnull(convert(nvarchar(2 ),ASSOCCLMADDLACCENTACTION),'') + '|' + "& _ " isnull(ASSOCCLMADDLACCENTACTIONTAKEN,'')+ '|' + "& _ " isnull(convert(nvarchar(2),SSAAPLFILED),'') + '|' + "& _ " isnull(convert(varchar(10) ,SSAAPLDT),'') + '|' + "& _ " isnull(SSAAPLRESULT,'')+ '|' + "& _ " isnull(convert(nvarchar(2 ),SSAAPLADDLACCENTACTION),'') + '|' + "& _ " isnull(SSAAPLADDLACCENTACTIONTAKEN,'') + '|' + "& _ " isnull(convert(varchar(10 ),SSAAplDecisionDt),'')+ '|' + "& _ " isnull(convert(varchar(10 ),ROUNDNUMBER),'') ,ltrim(str(@.err1))," & sFileId1 & ",GETDATE() "& _ " FROM SSD WHERE CALLID=@.CALLID2 "& _ " END "& _ " FETCH NEXT FROM InsertCur "& _ " INTO @.CALLID2 "& _ " END "& _ " CLOSE InsertCur "& _ " DEALLOCATE InsertCur "

Hi

Problem with While loop and if statement begin and end statements.check the below syantax This might help you

DECLARE @.price money
DECLARE @.get_price CURSOR

SET @.get_price = CURSOR FOR
SELECT price FROM titles

OPEN @.get_price

FETCH NEXT FROM @.get_price INTO @.price

WHILE (@.@.FETCH_STATUS = 0)
BEGIN
IF @.Price < 20
SELECT 'Under 20'
ELSE
SELECT @.Price

FETCH NEXT FROM @.get_price INTO @.price
END

CLOSE @.get_price
DEALLOCATE @.get_price