I am trying to execute a stored procedure via excel VBA, however the stored procedure only inserts a record into a single table and throws an error message when the other 2 records are to be inserted to their respective tables
The error message is thrown on the VBA side however in SQL management studio there are no errors yet it still only inserts to the single table.
--Edit: Error message I receive VBA side
Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.
USE [MainRoads]
GO
/****** Object: StoredProcedure [mruser].[sp_InsertUpdateProject] Script Date: 18/09/2013 3:04:49 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [mruser].[sp_InsertUpdateProject]
-- Add the parameters for the stored procedure here
--@ProjectRecord_ID numeric(18,0),
@lProjectNumber numeric(18,0),
@sProjectName nvarchar(300),
@sProjectType nvarchar(100),
@sDirectorate nvarchar(300),
@sProjectManager nvarchar(300),
@sContractType nchar(10),
@sProjectDescription nvarchar(max),
@sCurrentPhase nvarchar(50),
@sProjectStartDate date,
@sPlannedCompDate date,
@sReportingPeriod nchar(10),
@sProjectStatusAt date,
@sPSRApprovedBy nvarchar(100),
@sPSRApprovedDate date,
@sProjectOverallHlth text,
@sProjectWrkflwStatus nchar(10),
@sProjectRAGStatus nchar(10),
@sAssessRAG nchar(10),
@sSelectRAG nchar(10),
@sDevelopRAG nchar(10),
@sDeliverRAG nchar(10),
@sOperateRAG nchar(10),
@lTotalOrigBudget decimal(18,2),
@lTotalAppBudget decimal(18,2),
@lActualsLifeToDate decimal(18,2),
@lTotalForecastToComplete decimal(18,2),
@lTotalProjVar decimal(18,2),
@lCommFunding decimal(18,2),
@lStateFunding decimal(18,2),
@lOthFunding decimal(18,2),
@sCommFundType nvarchar(50),
@sStateFundType nvarchar(50),
@sOthFundType nvarchar(50)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
Declare @v_recordexist int
Select @v_recordexist = count(*) From Project_Information Where Project_Number = @lProjectNumber
--If record does not exists update record
IF @v_recordexist = 0
BEGIN
INSERT INTO [mruser].[Project_Information] ([Project_Number], [Project_Name], [Project_Type], [Directorate], [Project_Manager],[Contract_Type], [Project_Description], [Current_RO&DS_Phase], [Project_Start_Date],[Planned_Completion_Date], [Reporting_Period], [Project_Status_At], [PSR_Approved_By], [PSR_Approved_Date], [Project_Overall_Health], [Project_Workflow_Status], [Project_RAG_Status], [Date_Inserted] )
values(@lProjectNumber, @sProjectName, @sProjectType, @sDirectorate, @sProjectManager, @sContractType, @sProjectDescription, @sCurrentPhase, @sProjectStartDate, @sPlannedCompDate, @sReportingPeriod, @sProjectStatusAt, @sPSRApprovedBy, @sPSRApprovedDate, @sProjectOverallHlth, @sProjectWrkflwStatus, @sProjectRAGStatus, GETDATE())
--SELECT SCOPE_IDENTITY() as ProjectIDNumber
BEGIN
Insert into [mruser].[Project_Finance] ([Project_Number], [Total_Original_Budget], [Total_Approved_Budget], [Actuals_Life_To_Date], [Total_Forecast_Cost_To_Complete], [Total_Project_Variance], [Commonwealth_Funding], [State_Funding], [Other_Funding], [Commonwealth_Fund_Type], [State_Fund_Type], [Other_Fund_Type]) values (@lProjectNumber, @lTotalOrigBudget, @lTotalAppBudget, @lActualsLifeToDate, @lTotalForecastToComplete ,@lTotalProjVar ,@lCommFunding ,@lStateFunding ,@lOthFunding ,@sCommFundType ,@sStateFundType ,@sOthFundType)
--(strClientID, timeReg, timeValid, bCurrent, durum) VALUES (@strClientID,getdate(),getdate() + 30,'1','1')
Insert into [mruser].[Project_Milestones] ([Project_Number], [Assess_RAG_Status], [Select_RAG_Status], [Develop_RAG_Status], [Deliver_RAG_Status], [Operate_RAG_Status]) values(@lProjectNumber, @sAssessRAG, @sSelectRAG, @sDeliverRAG, @sDevelopRAG, @sOperateRAG)
--Insert into [mruser].[Project_Finance] ([Project_Number], [Total_Original_Budget], [Total_Approved_Budget], [Actuals_Life_To_Date], [Total_Forecast_Cost_To_Complete], [Total_Project_Variance], [Commonwealth_Funding], [State_Funding], [Other_Funding], [Commonwealth_Fund_Type], [State_Fund_Type], [Other_Fund_Type]) values (@lProjectNumber, @lTotalOrigBudget, @lTotalAppBudget, @lActualsLifeToDate, @lTotalForecastToComplete ,@lTotalProjVar ,@lCommFunding ,@lStateFunding ,@lOthFunding ,@sCommFundType ,@sStateFundType ,@sOthFundType)
END
End
Else
BEGIN
update [mruser].[Project_Information] set
[Project_Number] = @lProjectNumber,
[Project_Name] = @sProjectName,
[Project_Type] = @sProjectType,
[Directorate] = @sDirectorate,
[Project_Manager] = @sProjectManager,
[Contract_Type] = @sContractType,
[Project_Description] = @sProjectDescription,
[Current_RO&DS_Phase] = @sCurrentPhase,
[Project_Start_Date] = @sProjectStartDate,
[Planned_Completion_Date] = @sPlannedCompDate,
[Reporting_Period] = @sReportingPeriod,
[Project_Status_At] = @sProjectStatusAt,
[PSR_Approved_By] = @sPSRApprovedBy,
[PSR_Approved_Date] = @sPSRApprovedDate,
[Project_Overall_Health] = @sProjectOverallHlth,
[Project_Workflow_Status] = @sProjectWrkflwStatus,
[Project_RAG_Status] = @sProjectRAGStatus
where [Project_Number] = @lProjectNumber
update [mruser].[Project_Milestones] set
[Project_Number] = @lProjectNumber,
[Assess_RAG_Status] = @sAssessRAG,
[Select_RAG_Status] = @sSelectRAG,
[Develop_RAG_Status] = @sDeliverRAG,
[Deliver_RAG_Status] = @sDevelopRAG,
[Operate_RAG_Status] = @sOperateRAG
where [Project_Number] = @lProjectNumber
UPDATE [mruser].[Project_Finance] set
[Project_Number] = @lProjectNumber,
[Total_Original_Budget] = @lTotalOrigBudget,
[Total_Approved_Budget] = @lTotalAppBudget,
[Actuals_Life_To_Date] = @lActualsLifeToDate,
[Total_Forecast_Cost_To_Complete] = @lTotalForecastToComplete,
[Total_Project_Variance] = @lTotalProjVar,
[Commonwealth_Funding] = @lCommFunding,
[State_Funding] = @lStateFunding,
[Other_Funding] = @lOthFunding,
[Commonwealth_Fund_Type] = @sCommFundType,
[State_Fund_Type] = @sStateFundType,
[Other_Fund_Type] = @sOthFundType
where [Project_Number] = @lProjectNumber
END
End
