Database connection problem.

[Gagan Sindhu Dewangan]

Below is the following procedure where I am facing the problem, the work of the below procedure is to update the database.

query result:sp_helpt ext bProjectUpdate

Text
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------


CREATE PROCEDURE bProjectUpdate
@CallingMemberI D udtKeyAlias, --DefCallingMembe rID, --DefID
@KeyType udtKeyType,
@ForceValidatio n int, --DefForceValidat ion --DefTrueFalse --0=false, 1=true
@ProjectID udtUniqueKey,
@Reference udtReference, --DefReference
@Name udtName, --DefName
@StartDate udtLongDate,
@EndDate udtLongDate,
@ProjectStatusK ey udtKeyAlias,
@ParentKey udtKeyAlias, --DefID, --DefReference, --DefKey
@OrganizationKe y udtKeyAlias --DefID, --DefReference, --DefKey

AS

set nocount on

declare
@RetCode int,
@ProcName varchar(100),
@HistoryText udtHistoryText,
@PhaseID udtUniqueKey, --DefID
@ParentProjectI D udtUniqueKey, --DefID
@ProjectTypeID udtSystemCodeID ,--DefProjectTypeI D, --DefSystemCodeID
@ProjectStatusI D udtSystemCodeID ,
@OrganizationID udtUniqueKey, --DefID
@ValidCallingMe mberID udtUniqueKey, --DefMemberID, --DefID
@ValidationStri ng varchar(255), --DefValidationSt ring
@ErrorMessage varchar(255), --DefErrorMessage
@nExternalID udtExternalID, --Int version of ExternalID --DefExternalID
@nExternalTypeI D int, --Int version of ExternalTypeID --DefExternalType ID
@Entity varchar(255),
@CurrReference udtReference,
@CurrName udtName,
@CurrStartDate udtLongDate,
@CurrEndDate udtLongDate,
@CurrParentID udtUniqueKey,
@CurrProjectSta tusID udtSystemCodeID ,
@CurrOrganizati onID udtUniqueKey

--Initialisations
select @ValidationStri ng = '',
@RetCode = 0,
@ProcName = 'bProjectUpdate '


-- CallingMember must a valid Member
exec @RetCode = dMemberValidate @CallingMemberI D, 1, @ProcName
if @RetCode <> 0
return @RetCode


exec @RetCode = dKeyTypeValidat e @CallingMemberI D, @KeyType, @ProcName
if @RetCode <> 0
return @RetCode

-- Verify that the Project exists
if not exists (select ProjectID from Project where ProjectID = @ProjectID)
begin
exec @RetCode = ErrorMessageGet @CallingMemberI D, 1023, @ProcName
return @RetCode
end
else
begin
select @CurrReference = Reference,
@CurrName = Name,
@CurrStartDate = StartDate,
@CurrEndDate = EndDate,
@CurrProjectSta tusID = ProjectStatusID ,
@ProjectTypeID = ProjectTypeID,
@CurrParentID = ParentProjectID ,
@CurrOrganizati onID = OrganizationID
from Project
where ProjectID = @ProjectID
end

-- Verify that the new Reference Code does not clash with any existing Project in this organization
if exists (select ProjectID from Project where ProjectID <> @ProjectID and Reference=@Refe rence and ProjectTypeID=@ ProjectTypeID and OrganizationID= @OrganizationID )
begin
exec @RetCode = ErrorMessageGet @CallingMemberI D, 1032, @ProcName
return @RetCode
end

if isnull(@ParentK ey, '') <> ''
begin

if @KeyType = 1
--Validate that the Parent Project is valid
select @ParentProjectI D = ProjectID
from Project
where ProjectID = @ParentKey
and ProjectTypeID = 1
else
select @ParentProjectI D = ProjectID
from Project
where Reference = @ParentKey
and ProjectTypeID = 1


if @ParentProjectI D is null
begin
--Invalid Parent Project Key
execute @RetCode = ErrorMessageGet @CallingMemberI D, 1027, @ProcName
return @RetCode
end
end
else
select @ParentProjectI D = ParentProjectID from Project where ProjectID = @ProjectID


--OrganizationKey
-- Organization can either be passed in.
-- To Do (in which case it should be validated against the set that this calling member
-- is allowed to add employees for.
-- OR
-- It can be defaulted from the CallingMember's Organization.
if @OrganizationKe y <> ''
if @KeyType = 1
select @OrganizationID = OrganizationID from Organization where OrganizationID = @OrganizationKe y
else
select @OrganizationID = OrganizationID from Organization where Reference = @OrganizationKe y
else
select @OrganizationID = OrganizationID from Organization_Me mber where MemberID = @CallingMemberI D

if @OrganizationID is null
begin
-- No organization found with that reference
execute @RetCode = ErrorMessageGet @CallingMemberI D, 1019, @ProcName
return @RetCode
end


-- Validate the Project Status
exec @RetCode = dProjectStatusV alidateKey @CallingMemberI D, @KeyType, @ProcName, @ProjectStatusK ey, @ProjectStatusI D output, @ErrorMessage output
if @ProjectStatusI D = -1
begin
exec @RetCode = ErrorMessageGet @CallingMemberI D, @RetCode, @ProcName
return @RetCode
end

BEGIN TRAN


--Process Project
if @ProjectTypeID = 1
begin
select @Entity = 'Project'
update Project
set Reference = @Reference,
Name = @Name,
StartDate = @StartDate,
EndDate = @EndDate,
ProjectStatusID = @ProjectStatusI D,
ParentProjectID = @ParentProjectI D,
OrganizationID = @OrganizationID
where ProjectID = @ProjectID

select @PhaseID = NULL
select @PhaseID = ProjectID from Project where ParentProjectID = @ProjectID and ProjectTypeID = 2
-- Also update the default Phase - (with ProjectTypeID= 2 but same reference and name as the Project)
if @PhaseID is not null
begin
update Project
set Reference = @Reference,
Name = @Name,
StartDate = @StartDate,
EndDate = @EndDate,
ProjectStatusID = @ProjectStatusI D,
OrganizationID = @OrganizationID
where ProjectID = @PhaseID
end
end
--Otherwise the Phase
else
begin
select @Entity = 'Phase'
select @PhaseID = @ProjectID
update Project
set Reference = @Reference,
Name = @Name,
StartDate = @StartDate,
EndDate = @EndDate,
ProjectStatusID = @ProjectStatusI D,
ParentProjectID = @ParentProjectI D,
OrganizationID = @OrganizationID
where ProjectID = @PhaseID

-- TBD - 01.DEC.2000 - PBO Issue #286 - Supplemental - Per CCN, this should
-- also set the Project's RecordStatus to 1 (Inactive) so I added the extra
-- set statement.
--Need to mark the default Phases as deleted once someone adds a new one.
update Project set ProjectStatusID = 2, RecordStatus = 1 where ParentProjectID = @ParentProjectI D and ProjectTypeID = 2

-- TBD - 01.DEC.2000 - PBO Issue #286 - Supplemental - Per CCN, we should also
-- delete any rows in the Employee_Projec t table that reference any Project
-- table rows that have been tagged as inactive by the above statement.
delete ep
from employee_projec t as ep
join project as p on p.ProjectID = ep.ProjectID
and p.RecordStatus <> 0
and p.ParentProject ID = @ParentProjectI D
and p.ProjectTypeID = 2

end


select @ProjectID, @PhaseID, @Reference, @Name, @StartDate, @EndDate, @ProjectStatusI D

--Write an action history record
select @HistoryText = ''
select @HistoryText = isnull(@History Text + '<' + @Entity + 'Update>', '<NULL>')
if @ProjectTypeID = 1
select @HistoryText = isnull(@History Text + '<' + @Entity+ 'ID>' + cast(@ProjectID as varchar(50)) + '</' + @Entity+ 'ID>', '<NULL>')
else
select @HistoryText = isnull(@History Text + '<' + @Entity+ 'ID>' + cast(@PhaseID as varchar(50)) + '</' + @Entity+ 'ID>', 'NULL')
if @Reference <> @CurrReference
select @HistoryText = isnull(@History Text + '<Reference><Fr omReference>' + @CurrReference + '</FromReference>< ToReference>' + @Reference + '</ToReference></Reference>', '<NULL>')
if @Name <> @CurrName
select @HistoryText = isnull(@History Text + '<Name><FromNam e>' + @CurrName + '</FromName><ToNam e>' + @Name + '</ToName></Name>', '<NULL>')
if @StartDate <> @CurrStartDate
select @HistoryText = isnull(@History Text + '<StartDate><Fr omStartDate>' + cast(@CurrStart Date as varchar(50)) + '</FromStartDate>< ToStartDate>' + cast(@StartDate as varchar(50)) + '</ToStartDate></StartDate>', '<NULL>')
if @EndDate <> @CurrEndDate
select @HistoryText = isnull(@History Text + '<EndDate><From EndDate>' + cast(@CurrEndDa te as varchar(50)) + '</FromEndDate><To EndDate>' + cast(@EndDate as varchar(50)) + '</ToEndDate></EndDate>', '<NULL>')
if @ProjectStatusI D <> @CurrProjectSta tusID
select @HistoryText = isnull(@History Text + '<ProjectStatus ID><FromProject StatusID>' + cast(@CurrProje ctStatusID as varchar(50)) + '</FromProjectStat usID><ToProject StatusID>' + cast(@ProjectSt atusID as varchar(50)) + '</ToProjectStatus ID></ProjectStatusI
D>', '<NULL>')
if @OrganizationID <> @CurrOrganizati onID
select @HistoryText = isnull(@History Text + '<OrganizationI D><FromOrganiza tionID>' + cast(@CurrOrgan izationID as varchar(50)) + '</FromOrganizatio nID><ToOrganiza tionID>' + cast(@Organizat ionID as varchar(50)) + '</ToOrganizationI D></OrganizationID> ', '<N
ULL>')
if @ParentProjectI D <> @CurrParentID
select @HistoryText = isnull(@History Text + '<ParentProject ID><FromParentP rojectID>' + cast(@CurrParen tID as varchar(50)) + '</FromParentProje ctID><ToParentP rojectID>' + cast(@ParentPro jectID as varchar(50)) + '</ToParentProject ID></ParentProjectID >', '<
NULL>')
select @HistoryText = isnull(@History Text + '</' + @Entity+ 'Update>', '<NULL>')

if @ProjectTypeID = 1
exec bActionHistoryA dd @CallingMemberI D, 2, 'pr', @ProjectID,'upd ate', @HistoryText
else
exec bActionHistoryA dd @CallingMemberI D, 2, 'ph', @PhaseID,'updat e', @HistoryText

COMMIT TRAN