This is driving me absolutely up the wall! If anyone can suggest a fix for this I will be eternally grateful! And apologies for the length of the post.
PROBLEM
I am trying to execute a SQL stored procedure through VBA using a recordset with parameters created by looping through an excel range and on execute as soon as I hit a string value I keep getting the error "Conversion failed when converting the nvarchar value 'LIU AUS PI Civil Liability Policy Wording (08-10)' to data type int." - Needless to say that the field ("Wording") which would contain this value is set to NVARCHAR(255), the Stored Procedure accepts it as a SQL_VARIANT and then immediately Casts it to an NVARCHAR(255) AND the recordset parameter records it as an adVARWChar! What is going on??
VBA CODE
'Create the command set
Set rsCmd = New ADODB.Command
'define the command
rsCmd.CommandType = adCmdStoredProc
rsCmd.CommandText = "dbo.USP_Insert_Policy"
'29/01/2016
'Make sure we have appended the policy ID.
rsCmd.Parameters.Append rsCmd.CreateParameter("PolicyID", adInteger, adParamInput, 10, lPolicyID)
rsCmd.Parameters("PolicyID").Precision = 2
'Get remaining policy details from wksProgData and place into
'command for submission to db via recordset.
For Each rngFields In wksProgData.Range("PolicyDetailsRange")
If rngFields.Offset(1, 0).value <> 0 Then
'Everything gets added as a string value.
rsCmd.Parameters.Append rsCmd.CreateParameter( _
Name:=rngFields.value, _
Type:=adVarWChar, _
Size:=Len(rngFields.Offset(1, 0).value), _
Direction:=adParamInput, _
value:=rngFields.Offset(1, 0).value)
End If
Next rngFields
'Make sure we have a connection initialised.
If gcnConnection Is Nothing Then CreateConnection
'Open connection to database.
If gcnConnection.State <> adStateOpen Then gcnConnection.Open
'Set connection for command
rsCmd.ActiveConnection = gcnConnection
'Execute the command and set to recordset.
Set rsData = rsCmd.Execute
SQL TABLE
Column Name Data Type
Submission_ID nvarchar(255)
Rater_ID int
Additional_Insureds_Number int
Funds_Number int
Contractors_Number int
Date_Created datetime
Policy_Number nvarchar(255)
Expiring_Policy_Number nvarchar(255)
Expiring_Change_Details nvarchar(MAX)
Inception_Date datetime
Expiration_Date datetime
Continuity_Date datetime
New_Subsidiary_Asset_Size money
Quote_Options int
Conclusion nvarchar(MAX)
Conclusion_UW_Date datetime
Conclusion_Authorised_Date datetime
Post_Quote_Changes nvarchar(MAX)
Post_Quote_Changes_UW_Date datetime
Post_Quote_Changes_Authorised_Date datetime
Current_Status nvarchar(255)
LOB_Code nvarchar(255)
SIC_Division nvarchar(255)
SIC_Description nvarchar(255)
SIC_Code nvarchar(255)
PL_Code_Category nvarchar(255)
PL_Code nvarchar(255)
Class_Code nvarchar(255)
Rater_Code nvarchar(255)
Close_Code nvarchar(255)
Jurisdiction nvarchar(255)
Territorial nvarchar(255)
Terrorism nvarchar(255)
Side_C nvarchar(255)
TRIA nvarchar(255)
Account_Grading nvarchar(255)
Currency nvarchar(255)
Exchange_Rate int
Wording nvarchar(255)
Wording_Discretion int
Wording_Comments nvarchar(MAX)
Wording_Other nvarchar(255)
Wording_Primary nvarchar(255)
Non_LIU_Wording_Comments nvarchar(MAX)
NSW_Building_VIC_EPA_Cover_Comments nvarchar(MAX)
Transaction_Type nvarchar(255)
Policy_Comments nvarchar(MAX)
Received_Date datetime
Layers nvarchar(255)
FAC_Included nvarchar(255)
Lead_Follow nvarchar(255)
Policy_Period int
Professional_Services nvarchar(MAX)
Factiva_Search nvarchar(255)
Factiva_Articles_Found nvarchar(255)
OFAC_Trade_Sanctions_Details nvarchar(MAX)
Renewal_Changes_Details nvarchar(MAX)
XOL_Additional_Comments nvarchar(MAX)
XOL_Primary_Wording_Reinstatements nvarchar(MAX)
XOL_Deeming_Provisions nvarchar(MAX)
XOL_Coverage_For_Partners_Previous_Busines nvarchar(MAX)
Transaction_Date datetime
Premium_Structure nvarchar(255)
Separate_Rated_DO nvarchar(255)
Separate_DO_Premium money
Insuring_Clause_1_1 nvarchar(255)
Override_Discount nvarchar(255)
Non_Standard_Excess nvarchar(MAX)
Breadth_Of_Coverage nvarchar(255)
APRA_Category nvarchar(255)
APRA_Description nvarchar(255)
APRA_Code nvarchar(255)
Bond_Description nvarchar(255)
Bond_Code nvarchar(255)
PRI_XS nvarchar(255)
Rater_Name nvarchar(255)
Policy_ID int
Confirmed bit
Layer bit
STORED PROCEDURE
CREATE PROCEDURE [dbo].[USP_Insert_Policy] @PolicyID SQL_VARIANT = NULL,
@Submission_ID SQL_VARIANT =NULL,
@Additional_Insureds_Number SQL_VARIANT=NULL,
@Funds_Number SQL_VARIANT=NULL,
@Contractors_Number SQL_VARIANT=NULL,
@Date_Created SQL_VARIANT=NULL,
@Policy_Number SQL_VARIANT=NULL,
@Expiring_Policy_Number SQL_VARIANT=NULL,
@Expiring_Change_Details SQL_VARIANT=NULL,
@Inception_Date SQL_VARIANT=NULL,
@Expiration_Date SQL_VARIANT=NULL,
@Continuity_Date SQL_VARIANT=NULL,
@New_Subsidiary_Asset_Size SQL_VARIANT=NULL,
@Quote_Options SQL_VARIANT=NULL,
@Conclusion SQL_VARIANT=NULL,
@Conclusion_UW_Date SQL_VARIANT=NULL,
@Conclusion_Authorised_Date SQL_VARIANT=NULL,
@Post_Quote_Changes SQL_VARIANT=NULL,
@Post_Quote_Changes_UW_Date SQL_VARIANT=NULL,
@Post_Quote_Changes_Authorised_Date SQL_VARIANT=NULL,
@Current_Status SQL_VARIANT=NULL,
@LOB_Code SQL_VARIANT=NULL,
@SIC_Division SQL_VARIANT=NULL,
@SIC_Description SQL_VARIANT=NULL,
@SIC_Code SQL_VARIANT=NULL,
@PL_Code_Category SQL_VARIANT=NULL,
@PL_Code SQL_VARIANT=NULL,
@Class_Code SQL_VARIANT=NULL,
@Rater_Code SQL_VARIANT=NULL,
@Close_Code SQL_VARIANT=NULL,
@Jurisdiction SQL_VARIANT=NULL,
@Territorial SQL_VARIANT=NULL,
@Terrorism SQL_VARIANT=NULL,
@Side_C SQL_VARIANT=NULL,
@TRIA SQL_VARIANT=NULL,
@Account_Grading SQL_VARIANT=NULL,
@Currency SQL_VARIANT=NULL,
@Exchange_Rate SQL_VARIANT=NULL,
@Wording NVARCHAR(255) =NULL,
@Wording_Discretion SQL_VARIANT=NULL,
@Wording_Comments SQL_VARIANT=NULL,
@Wording_Other SQL_VARIANT=NULL,
@Wording_Primary SQL_VARIANT=NULL,
@Non_LIU_Wording_Comments SQL_VARIANT=NULL,
@NSW_Building_VIC_EPA_Cover_Comments SQL_VARIANT=NULL,
@Confirmed SQL_VARIANT=NULL,
@Transaction_Type SQL_VARIANT=NULL,
@Policy_Comments SQL_VARIANT=NULL,
@Received_Date SQL_VARIANT=NULL,
@Layers SQL_VARIANT=NULL,
@FAC_Included SQL_VARIANT=NULL,
@Lead_Follow SQL_VARIANT=NULL,
@Policy_Period SQL_VARIANT=NULL,
@Professional_Services SQL_VARIANT=NULL,
@Factiva_Search SQL_VARIANT=NULL,
@Factiva_Articles_Found SQL_VARIANT=NULL,
@OFAC_Trade_Sanctions_Details SQL_VARIANT=NULL,
@Renewal_Changes_Details SQL_VARIANT=NULL,
@XOL_Additional_Comments SQL_VARIANT=NULL,
@XOL_Primary_Wording_Reinstatements SQL_VARIANT=NULL,
@XOL_Deeming_Provisions SQL_VARIANT=NULL,
@XOL_Coverage_For_Partners_Previous_Busines SQL_VARIANT=NULL,
@Transaction_Date SQL_VARIANT=NULL,
@Premium_Structure SQL_VARIANT=NULL,
@Separate_Rated_DO SQL_VARIANT=NULL,
@Separate_DO_Premium SQL_VARIANT=NULL,
@Insuring_Clause_1_1 SQL_VARIANT=NULL,
@Override_Discount SQL_VARIANT=NULL,
@Non_Standard_Excess SQL_VARIANT=NULL,
@Breadth_Of_Coverage SQL_VARIANT=NULL,
@APRA_Category SQL_VARIANT=NULL,
@APRA_Description SQL_VARIANT=NULL,
@APRA_Code SQL_VARIANT=NULL,
@Bond_Description SQL_VARIANT=NULL,
@Bond_Code SQL_VARIANT=NULL,
@PRI_XS SQL_VARIANT=NULL,
@Layer SQL_VARIANT=NULL,
@Rater_Name SQL_VARIANT=NULL
AS
IF @PolicyID <> 0
BEGIN
--Update table where policy ID is @PolicyID
UPDATE tblPolicy
SET Submission_ID =CAST(@Submission_ID AS NVARCHAR(255)),
Additional_Insureds_Number =CAST( @Additional_Insureds_Number AS int ),
Funds_Number =CAST( @Funds_Number AS int ),
Contractors_Number =CAST( @Contractors_Number AS int ),
Date_Created =CAST(@Date_Created AS Datetime),
Policy_Number =CAST(@Policy_Number AS NVARCHAR(255)),
Expiring_Policy_Number = CAST(@Expiring_Policy_Number AS NVARCHAR(255)),
Expiring_Change_Details =CAST(@Expiring_Change_Details AS NVARCHAR(MAX)),
Inception_Date =CAST(@Inception_Date AS DateTime ),
Expiration_Date =CAST(@Expiration_Date AS DateTime ),
Continuity_Date =CAST(@Continuity_Date AS DATETime ),
New_Subsidiary_Asset_Size =CAST( @New_Subsidiary_Asset_Size AS money ),
Quote_Options =CAST( @Quote_Options AS int ),
Conclusion =CAST(@Conclusion AS NVARCHAR(MAX)),
Conclusion_UW_Date =CAST (@Conclusion_UW_Date AS DATETIME),
Conclusion_Authorised_Date =CAST(@Conclusion_Authorised_Date AS DateTime),
Post_Quote_Changes =CAST(@Post_Quote_Changes AS NVARCHAR(MAX)),
Post_Quote_Changes_UW_Date =CAST(@Post_Quote_Changes_UW_Date AS datetime),
Post_Quote_Changes_Authorised_Date =CAST(@Post_Quote_Changes_Authorised_Date AS datetime),
Current_Status =CAST(@Current_Status AS NVARCHAR(255)),
LOB_Code =CAST(@LOB_Code AS nvarchar(255)),
SIC_Division = CAST(@SIC_Division AS nvarchar(255)),
SIC_Description =CAST(@SIC_Description AS nvarchar(255)),
SIC_Code =CAST(@SIC_Code AS nvarchar(255)),
PL_Code_Category =CAST(@PL_Code_Category AS nvarchar(255)),
PL_Code =CAST(@PL_Code AS nvarchar(255)),
Class_Code =CAST(@Class_Code AS nvarchar(255)),
Rater_Code =CAST(@Rater_Code AS nvarchar(255)),
Close_Code =CAST(@Close_Code AS nvarchar(255)),
Jurisdiction =CAST(@Jurisdiction AS nvarchar(255)),
Territorial =CAST(@Territorial AS nvarchar(255)),
Terrorism =CAST(@Terrorism AS nvarchar(255)),
Side_C =CAST(@Side_C AS nvarchar(255)),
TRIA =CAST(@TRIA AS nvarchar(255)),
Account_Grading =CAST(@Account_Grading AS nvarchar(255)),
Currency =CAST(@Currency AS nvarchar(255)),
Exchange_Rate =CAST( @Exchange_Rate AS int ),
Wording =CAST(@Wording AS varchar(255)),
Wording_Discretion =CAST( @Wording_Discretion AS int ),
Wording_Comments =CAST(@Wording_Comments AS nvarchar(MAX)),
Wording_Other =CAST(@Wording_Other AS nvarchar(255)),
Wording_Primary =CAST(@Wording_Primary AS nvarchar(255)),
Non_LIU_Wording_Comments =CAST(@Non_LIU_Wording_Comments AS nvarchar(MAX)),
NSW_Building_VIC_EPA_Cover_Comments=CAST(@NSW_Building_VIC_EPA_Cover_Comments AS nvarchar(MAX)),
Confirmed =CAST( @Confirmed AS bit ),
Transaction_Type =CAST(@Transaction_Type AS nvarchar(255)),
Policy_Comments =CAST(@Policy_Comments AS nvarchar(MAX)),
Received_Date =CAST(@Received_Date AS datetime ),
Layers =CAST( @Layers AS nvarchar(255)),
FAC_Included =CAST( @FAC_Included AS nvarchar(255)),
Lead_Follow =CAST( @Lead_Follow AS nvarchar(255)),
Policy_Period =CAST( @Policy_Period AS int ),
Professional_Services =CAST( @Professional_Services AS nvarchar(MAX)),
Factiva_Search =CAST( @Factiva_Search AS nvarchar(255)),
Factiva_Articles_Found =CAST( @Factiva_Articles_Found AS nvarchar(255)),
OFAC_Trade_Sanctions_Details=CAST( @OFAC_Trade_Sanctions_Details AS nvarchar(MAX)),
Renewal_Changes_Details =CAST( @Renewal_Changes_Details AS nvarchar(MAX)),
XOL_Additional_Comments =CAST( @XOL_Additional_Comments AS nvarchar(MAX)),
XOL_Primary_Wording_Reinstatements =CAST( @XOL_Primary_Wording_Reinstatements AS nvarchar(MAX)),
XOL_Deeming_Provisions =CAST( @XOL_Deeming_Provisions AS nvarchar(MAX)),
XOL_Coverage_For_Partners_Previous_Busines =CAST(@XOL_Coverage_For_Partners_Previous_Busines AS nvarchar(MAX)),
Transaction_Date =CAST( @Transaction_Date AS datetime ),
Premium_Structure =CAST (@Premium_Structure AS nvarchar(255)),
Separate_Rated_DO =CAST (@Separate_Rated_DO AS nvarchar(255)),
Separate_DO_Premium =CAST( @Separate_DO_Premium AS money ),
Insuring_Clause_1_1 =CAST (@Insuring_Clause_1_1 AS nvarchar(255)),
Override_Discount =CAST (@Override_Discount AS nvarchar(255)),
Non_Standard_Excess =CAST(@Non_Standard_Excess AS nvarchar(MAX)),
Breadth_Of_Coverage =CAST( @Breadth_Of_Coverage AS nvarchar(255)),
APRA_Category =CAST( @APRA_Category AS nvarchar(255)),
APRA_Description =CAST( @APRA_Description AS nvarchar(255)),
APRA_Code =CAST( @APRA_Code AS nvarchar(255)),
Bond_Description =CAST( @Bond_Description AS nvarchar(255)),
Bond_Code =CAST( @Bond_Code AS nvarchar(255)),
PRI_XS =CAST( @PRI_XS AS nvarchar(255)),
Layer =CAST( @Layer AS bit ),
Rater_Name =CAST( @Rater_Name AS nvarchar(255))
WHERE tblPolicy.Policy_ID=@PolicyID
SELECT * FROM tblPolicy WHERE Policy_ID = @PolicyID
END;
The only thing I can think of now is that my parameters are not in the same sequence as my stored procedure. However if they need to be in the same sequence then why bother to use paramters and just go straight for SQL in my VBA code.
