troubleshooting Question

Using QueryTable with SQL Server stored procedures from code - Including parameters

Avatar of NeoTeq
NeoTeq asked on
Microsoft SQL ServerMicrosoft Excel
6 Comments1 Solution1328 ViewsLast Modified:
Experts,

I am trying to automatically import some data generated by a SQL Server stored procedure into Excel, from code. It kind of works, but the problem I am running into is that I can't seem to get the parameters to work like they should (in my mind). The code is actually executed on the webserver (javascript), but, I can replicate the exact behaviour in Excel VBA itself so I will use VBA as an example:

Dim objTable As QueryTable

objTable = ActiveSheet.QueryTables.Add(strConnectionString,
   ActiveSheet.Range(ActiveSheet.Cells(1, 1), ActiveSheet.Cells(1, 1))

objTable.CommandText = "MyProcedure " & lngSomeValue & ", " & lngSomeOtherValue
objTable.Refresh True

This in itself works fine. However, given my situation, the values given to the SP are from user input and, well, SQL injection is a thing to worry about. So, I would rather do something like:

objTable = ActiveSheet.QueryTables.Add(strConnectionString,
   ActiveSheet.Range(ActiveSheet.Cells(1, 1), ActiveSheet.Cells(1, 1))

objTable.CommandText = "MyProcedure"
objTable.Parameters.Add("MyParam").SetParam 1, lngSomeValue
objTable.Parameters.Add("MySecondParam").SetParam 1, lngSomeOtherValue
objTable.Refresh True

...Which I can't get to work. How can I get the QueryTable to work while giving up individual parameters?

(I'm sorry if this is unclear - It feels kind of hard to explain)
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 6 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros