VBA SQL Server Argument are of wrong type

Question

I have a simple VBA code that needs to execute a SP from SQL Server with one parameter. I copied the code from a text book, making relevant adjustments, but I keep getting this error. Here is the code:

Public Sub ExecuteStoredProcAsMethod()
Dim objConn As ADODB.Connection
Dim rsData As ADODB.Recordset
Dim sConnect As String

'Create the conntection string.
sConnect = "Provider=SQLOLEDB;Data Source=MYSERVER; " & _
"Initial Catalog=DisaggregatedPatronage;Integrated Security=SSPI"

'Create the connection and Recordset objects.
Set objConn = New ADODB.Connection
Set rsDate = New ADODB.Recordset

'Open the connection and execute the SP.
objConn.Open sConnect
objConn.spSurveyDataTest "Bus", rsData

'Make sure we got records back
If Not rsData.EOF Then
    'Dump the contens of the recodset on the worksheet
    Sheet1.Range("A1").CopyFromRecordset rsDate
    'Close the recordset
    rsData.Close
    'Fit the column wirdths to the data
    Sheet1.UsedRange.EntireColumn.AutoFit
Else
    MsgBox "Error: No records returned. ", vbCritical
End If

'Clean up our ADO objects.
If CBool(objConn.State And adStateOpen) Then objConn.Close
Set objConn = Nothing
Set rsData = Nothing


End Sub

Kapol · Accepted Answer · 2014-02-19 22:47:23Z

1

You declare an rsData variable, but you initialize rsDate. Aren't you using the Option Explicit statement?

answered Feb 19, 2014 at 22:47

Kapol

6,4834 gold badges26 silver badges47 bronze badges

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

1 Comment

user2343837 Over a year ago

the devil is in the detail. Thank you SO MUCH!

Collectives™ on Stack Overflow

VBA SQL Server Argument are of wrong type

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related