CODEWITHSUNDEEP
vbaexcelstored-proceduresparameter-passing
jdids
jdids

Reputation: 571

Issues passing parameters to stored procedure with Excel VBA

Seems to be a common question, but I cannot get any examples to work with my code.

Sorry in advance if this is trivial as I'm a DBA and not a developer.

I have a stored procedure that requires two parameters LaborRate and EndDate. LaborRate is a decimal data type and will get its variable from worksheet BOMSum and cell G1. EndDate is a date data type and will get its variable from worksheet BOMSum and cell G2. All of my attempts have failed even after looking through tutorials and other recommendations on this website.

Here's my very simple code I started out with that actually runs correct but runs without specifying any params:

Sub Execute_SP()
Dim conn As ADODB.Connection
Dim cmd As ADODB.Command

Set conn = New ADODB.Connection
conn.ConnectionString = "Provider=SQLOLEDB;Data Source=<ServerName>;Initial Catalog=<DB>;User ID=<DB_User>;Password=<pwd>"
conn.Open

Set cmd = New ADODB.Command
cmd.ActiveConnection = conn
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "CostingInfo"

cmd.Execute
conn.Close

Set conn = Nothing
Set cmd = Nothing
ActiveWorkbook.RefreshAll
End Sub

Here's what I have now. I'm trying to pass two parameters LaborRate and EndDate from cell G1 and G2.

Sub Execute_SP()
Dim conn As ADODB.Connection
Dim cmd As ADODB.Command
Dim prm1 As ADODB.Parameter
Dim prm2 As ADODB.Parameter

Set conn = New ADODB.Connection
conn.ConnectionString = "Provider=SQLOLEDB;Data Source=<ServerName>;Initial Catalog=<DB>;User ID=<DB_User>;Password=<pwd>"
conn.Open

Set cmd = New ADODB.Command
cmd.ActiveConnection = conn
cmd.CommandType = adCmdStoredProc
cmd.CommandText = CostingInfo
prm1 = cmd.CreateParameter("@LaborRate", adDecimal, adParamInput)
prm1.Precision = 28
prm1.NumericScale = 4
cmd.Parameters.Append prm1
prm1.Value = ActiveSheet.Range("G1").Text
prm2 = cmd.CreateParameter("@endDate", adDate, adParamInput)
prm2.Value = ActiveSheet.Range("G2").Text
cmd.Parameters.Append prm2
cmd.Execute
conn.Close

Set conn = Nothing
Set cmd = Nothing
ActiveWorkbook.RefreshAll
End Sub

I've gotten errors regarding precision and object variable or with block variables not set. I'm stumped and very removed from VBA programming.

EDIT: After some suggestions by Rude Dawg, here's is my code now. I'm now getting a "Syntax error or access violation" error, which when is debugged points to the cmd.Execute section having the issue:

Sub Execute_SP()
Dim conn As ADODB.Connection
Dim cmd As ADODB.Command
Dim prm1 As ADODB.Parameter
Dim prm2 As ADODB.Parameter

Set conn = New ADODB.Connection
conn.ConnectionString = "Provider=SQLOLEDB;Data Source=<ServerName>;Initial Catalog=<DB>;User ID=    <DB_User>;Password=<pwd>"
conn.Open

Set cmd = New ADODB.Command
cmd.ActiveConnection = conn
cmd.CommandType = adCmdStoredProc
cmd.CommandText = CostingInfo
set prm1 = cmd.CreateParameter("@LaborRate", adDecimal, adParamInput)
prm1.Precision = 28
prm1.NumericScale = 4
prm1.Value = Sheets("BOMSum").Range("G1").VALUE
cmd.Parameters.Append prm1
set prm2 = cmd.CreateParameter("@endDate", adDate, adParamInput)
prm2.Value = Sheets("BOMSum").Range("G2").VALUE
cmd.Parameters.Append prm2
cmd.Execute
conn.Close

Set conn = Nothing
Set cmd = Nothing
ActiveWorkbook.RefreshAll
End Sub

Upvotes: 0

Views: 4296

Answers (2)

jdids
jdids

Reputation: 571

Ended up finding the issue.

After bashing my head against my keyboard, I found out that I didn't have double quotes around my stored procedure name.

Here's my end result if anyone is interested:

Sub Execute_SP()
Dim conn As ADODB.Connection
Dim cmd As ADODB.Command
Dim prm1 As ADODB.Parameter
Dim prm2 As ADODB.Parameter
Dim rs As ADODB.Recordset

Set conn = New ADODB.Connection
conn.ConnectionString = "Provider=SQLOLEDB;Data Source=<ServerName>;Initial Catalog=<DB>;User ID=    <DB_User>;Password=<pwd>"

conn.Open

Set cmd = New ADODB.Command
cmd.ActiveConnection = conn
cmd.CommandType = adCmdStoredProc
Set prm1 = cmd.CreateParameter("@LaborRate", adDecimal, adParamInput, 14,     ThisWorkbook.Sheets("BOMSumTbl").Range("G1").Value)
prm1.Precision = 28
prm1.NumericScale = 4
cmd.Parameters.Append prm1
Set prm2 = cmd.CreateParameter("@EndDate", adDate, adParamInput, 7,     ThisWorkbook.Sheets("BOMSumTbl").Range("G2").Value)
cmd.Parameters.Append prm2
cmd.CommandText = "CostingInfo"
Set rs = cmd.Execute(adCmdStoredProc)
conn.Close

Set conn = Nothing
Set cmd = Nothing
Set rs = Nothing
ActiveWorkbook.RefreshAll
End Sub

Upvotes: 1

Rude Dawg
Rude Dawg

Reputation: 88

try this slight mod to your entry.

set prm1 = cmd.CreateParameter("@LaborRate", adDecimal, adParamInput)
prm1.Precision = 28
prm1.NumericScale = 4
cmd.Parameters.Append prm1
prm1.Value = Sheets("BOMSum").Range("G1").VALUE
set prm2 = cmd.CreateParameter("@endDate", adDate, adParamInput)
prm2.Value = Sheets("BOMSum").Range("G2").VALUE
cmd.Parameters.Append prm2

also, why are you appending prm1 before you pulled the cell "G1" value? Is that a slight typo?

Upvotes: 1

Related Questions