CODEWITHSUNDEEP
sql-servervbaexcel
user2343837
user2343837

Reputation: 1015

VBA SQL Server Argument are of wrong type

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

Upvotes: 0

Views: 538

Answers (1)

Kapol
Kapol

Reputation: 6463

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

Upvotes: 1

Related Questions