CODEWITHSUNDEEP
vbaexcelstored-proceduresinput-parameters
ABCD
ABCD

Reputation: 21

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

Upvotes: 0

Views: 2020

Answers (2)

ABCD
ABCD

Reputation: 21

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

Upvotes: 1

Greg Viers
Greg Viers

Reputation: 3523

Try using EXEC:

.CommandText = "exec spGetPriceChangeTest"

Upvotes: 0

Related Questions