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, Feb 06 '18 at 21:10
Is there not an actual opened connection somewhere? Which specific line is throwing the error? — Mathieu Guindon, Feb 06 '18 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, Feb 06 '18 at 21:17
I have a connection object created and the command object executes with the help of the connection. Error is on the cmd1.execute statement. the connection is open and is working well as I have another stored procedures without parameters which are working well. — ABCD, Feb 06 '18 at 21:18
try `Set .ActiveConnection = yourConnectionObject`, and if that still doesn't work then have this code written in a class module (a worksheet's code-behind can do), declare the connection `WithEvents` and handle its "on error" (not sure of the name) event to output the actual error message. — Mathieu Guindon, Feb 06 '18 at 21:21
A quick Google search with "ADODB -2147217900 (80040e14)" seems to point to a syntax error. Verify the stored procedure works. — Mathieu Guindon, Feb 06 '18 at 21:23
stored procedure works on dry run. tried passing the values by copying from the quick watch feature and it did run well in sql — ABCD, Feb 06 '18 at 21:26
datetime = DateSerial(2018, 2, 5) + TimeSerial(18, 0, 0) is one of the parameters to the sp. Can this be a problem ? the datatype for all the parameters in sql is varchar expect one which is datetime. can datatype on excel side be a problem? — ABCD, Feb 06 '18 at 21:29
See [this post](https://stackoverflow.com/a/41837692/1188513) for how you can listen to the messages your connection is sending. The event you want to handle is `InfoMessage`. — Mathieu Guindon, Feb 06 '18 at 21:30
on changing it to adVarChar, it gives me run-time error 3708 application defined or object defined error. — ABCD, Feb 06 '18 at 21:32
Great! Progress! You need to handle the connection's `InfoMessage` event if you want to retrieve an actually helpful error message. — Mathieu Guindon, Feb 06 '18 at 21:33
Can you show me with an example. Please help. Can you show me with my code? — ABCD, Feb 06 '18 at 21:45
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, Feb 06 '18 at 21:47

score 1 · Accepted Answer · answered Feb 06 '18 at 22:16

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

score 0 · Answer 2 · edited Feb 06 '18 at 21:09

0

Try using EXEC:

.CommandText = "exec spGetPriceChangeTest"

edited Feb 06 '18 at 21:09

Mathieu Guindon

69,817
8
107
235

answered Feb 06 '18 at 20:45

Greg Viers

3,473
3
18
36

Given `.CommandType = adCmdStoredProc`, I doubt this would be it. Changing `.CommandType` to `adCmdText` as well, could work though. – Mathieu Guindon Feb 06 '18 at 21:10
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. – ABCD Feb 06 '18 at 21:14

Excel vba SQL Stored procedure calling with input parameters

2 Answers2