[SOLVED] Excel vba SQL Stored procedure calling with input parameters

Excel vba SQL Stored procedure calling with input parameters

Thank you in advance. I am trying to run a stored procedure from excel which accepts input parameters and it is giving error Run-time error '-2147217900 (80040e14)' automation error.

   Set cmd1 = New ADODB.Command
             With cmd1
             .ActiveConnection = sConnString
             .CommandText = "spGetPriceChangeTest"
             .CommandType = adCmdStoredProc
             .CommandTimeout = 360
             .Parameters.Append .CreateParameter("@suppliercode", adVariant, adParamInput, , Range("A" & (x + 2)).Value)
             .Parameters.Append .CreateParameter("@date1", adDBTimeStamp, adParamInput, , datetime)
             .Parameters.Append .CreateParameter("@proddescription", adVariant, adParamInput, , Range("D" & (x + 1)).Value)
             .Parameters.Append .CreateParameter("@vendorcode", adVariant, adParamInput, , Range("C" & (x + 2)).Value)
             .Parameters.Append .CreateParameter("@type", adVariant, adParamInput, , Range("B" & (x + 2)).Value)
             End With
             Set rst1 = New ADODB.Recordset
             Set rst1 = cmd1.Execute

Solution

When creating a parameter, ensure that the size defined in SQL is mentioned in the .CreateParameter("",adVarchar,10,value)

This post helped. It talks about how we can capture an error in detail.

https://support.microsoft.com/en-us/help/167957/info-extracting-error-information-from-ado-in-vb