Excel vba SQL Stored procedure calling with input parameters

Question

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

You're Set-ing the exact same object reference twice: Set rst1 = New ADODB.Recordset is redundant and can be removed. — Mathieu Guindon
– Mathieu Guindon, Commented Feb 6, 2018 at 21:10
Is there not an actual opened connection somewhere? Which specific line is throwing the error? — Mathieu Guindon
– Mathieu Guindon, Commented Feb 6, 2018 at 21:14
I know, I was just saying it's redundant. Do you have an ADODB.Connection object that you .Open anywhere? I've never used a ADODB.Recordset without an explicit ADODB.Connection... also try removing the @ from the parameter names, and make sure the parameters are supplied in the exact same order as the SP is expecting them. — Mathieu Guindon
– Mathieu Guindon, Commented Feb 6, 2018 at 21:17
You're going to have to try a bit harder than that... SO is Q&A, not "hold my hand until it works" ;-) — Mathieu Guindon
– Mathieu Guindon, Commented Feb 6, 2018 at 21:47

ABCD · Accepted Answer · 2018-02-06 22:16:19Z

1

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

answered Feb 6, 2018 at 22:16

ABCD

215 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mathieu Guindon · Accepted Answer · 2018-02-06 21:09:08Z

0

Try using EXEC:

.CommandText = "exec spGetPriceChangeTest"

edited Feb 6, 2018 at 21:09

Mathieu Guindon

71.5k8 gold badges113 silver badges250 bronze badges

answered Feb 6, 2018 at 20:45

Greg Viers

3,5233 gold badges22 silver badges36 bronze badges

2 Comments

Mathieu Guindon Over a year ago

Given .CommandType = adCmdStoredProc, I doubt this would be it. Changing .CommandType to adCmdText as well, could work though.

ABCD Over a year ago

I have another stored procedures not accepting any parameters and it works as the code above perfectly. Im sensing that the datatype or the datetime parameter is an issue.

Collectives™ on Stack Overflow

Excel vba SQL Stored procedure calling with input parameters

2 Answers 2

Comments

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Linked

Related