5

I have lots of SQL scripts, many of which use various different variables throughout, and I'd like to be able to drop the results directly into Excel. The hope is to do this as 'smoothly' as possible, so that when someone gives me a new SQL script (which may be relatively complicated), it is relatively clean to set up the spreadsheet that gathers its results.

Currently trying to get this working using ADODB Command objects parameters, but I can't even manage to get a very basic example to work. I have the following VBA:

Dim oConnection As ADODB.Connection
Set oConnection = New ADODB.Connection
oConnection.ConnectionString = "MyConnectionString"
oConnection.Open 
Dim cmd As ADODB.Command
Set cmd = New ADODB.Command    
cmd.ActiveConnection = oConnection

Up to here is just setting up the connection, which seems to work fine.

cmd.CommandText = "DECLARE @DateID integer;" & _
"SELECT TOP 10 * FROM dbo.SomeRecords " & _
"WHERE DateID = @DateID" 
cmd.CommandType = adCmdText

Dim DateID As ADODB.Parameter
Set DateID = cmd.CreateParameter("@DateID", adInteger, adParamInput)
cmd.Parameters.Append DateID
DateID.Value = 20120831

Dim rst AS ADODB.RecordSet
Set rst = cmd.Execute()

ActiveSheet.Range("A1").CopyFromRecordset rst

Unfortunately, this doesn't return anything. However, if I replace the line:

"WHERE DateID = @DateID"

with this: 

"WHERE DateID = 20120831"

Then the query returns exactly what you'd expect (the top 10 records from August 31), so obviously I'm not passing the value of the variable from VBA into SQL properly, but I have to admit that I'm pretty much stuck.

Certainly something is being passed into SQL (if I change the type of the variable @DateID to datetime in the SQL, then I get a SQL Server arithmetic overflow error, from trying to convert something to datetime), but it isn't doing what I was expecting.

I guess there are two questions: Is there a way to fix this code? Is there a better way of achieving the general goal described at the start?

podiluska
  • 50,950
  • 7
  • 98
  • 104
John Faben
  • 151
  • 1
  • 9
  • Using `?DateID` instead of `@DateID`, I get the error that I have not declared the variable `@DateIDDateID` (or `@P1DateID`, if I don't use `cmd.NamedParameters = True`). This is agonisingly close to what I need, but won't quite work (what if the SQl code uses a variable more than once? How will "?" distinguish between them?) – John Faben Oct 09 '12 at 10:47
  • 1
    You can create a sproc with @dateID as parameter and call this sproc from your vb.Net code.This will be easier way to achieve this. – AnandPhadke Oct 09 '12 at 09:11

3 Answers3

2

Try this

cmd.CommandText = "DECLARE @DateID integer;" & _
"SET @DateID = ?DateID;" & _
"SELECT TOP 10 * FROM dbo.SomeRecords " & _
"WHERE DateID = @DateID" 
.....
Set DateID = cmd.CreateParameter("?DateID", adInteger, adParamInput)

Re:

in which case why bother having names for them in the first place

Well, so that you can match them up as shown above. By all means use it many times, but use a sql-server local declare and set it there as shown.

RichardTheKiwi
  • 105,798
  • 26
  • 196
  • 262
  • 1
    Thanks. This doesn't quite work (as the SQL thinks the variable is named `@DateIDDateID` if you do it like that), but if you just put `SET @DateID = ?` (and then `Set @var2 = ?`, `Set @var3 = ?`) etc, then this is functionally equivalent to what I was looking for (you just have to make sure to put the parameters in the right order in the VBA) - as usual, can't believe I didn't think of it myself! Thanks. – John Faben Oct 09 '12 at 12:47
1

Remove the DECLARE @DateID integer; from the SQL string. ie:

cmd.CommandText = "SELECT TOP 10 * FROM dbo.SomeRecords " & _ 
    "WHERE DateID = @DateID"
podiluska
  • 50,950
  • 7
  • 98
  • 104
  • This doesn't work on it's own - just get a SQL Server error message saying 'must declare the scalar variable @DateID'. – John Faben Oct 09 '12 at 10:42
1

Parameters in an ADO parameterized query want a ? as placeholder so:

"SELECT TOP 10 * FROM dbo.SomeRecords WHERE DateID = ?"
Alex K.
  • 171,639
  • 30
  • 264
  • 288
  • This works, if you also set cmd.NamedParameters = True. However, it is not very useful, as it means I can't specify the names of the parameters inside the SQL code (in which case why bother having names for them in the first place). I'm sure I must be missing something – John Faben Oct 09 '12 at 10:43
  • 1
    Still, without being able to name the parameters, you will at least prevent SQL injection. Therefore, this method still has benefits. – Taylor K. Oct 09 '12 at 13:08