I'm trying to call a SQL Server stored procedure with two parameters in VBA using ADO; however I got error message "Run-time error '13' Type mismatch". I've spent hours searching on online but just can't get it working... Here is my code:
Dim cnn As New ADODB.Connection
Dim cmd As New ADODB.Command
cnn.ConnectionString = "Provider=SQLNCLI11;" _
& "Server=(Local);" _
& "Database=Inventory;" _
& "Integrated Security=SSPI;" _
& "DataTypeCompatibility=80;"
cnn.Open
With cmd
.ActiveConnection = cnn
.CommandType = "adCmdText"
.CommandText = "usp_mySP"
.Parameters.Append .CreateParameter("@fileName1", adChar, adParamInput, , File1.Name)
.Parameters.Append .CreateParameter("@fileName2", adChar, adParamInput, , File2.Name)
.Execute
End With
The File1.Name
and File2.Name
are names of two latest-modified files that I got using FileSystemObject.
The usp_mySP
is my stored procedure that essentially uses bcp command to export a tab-delimited file. It works perfectly fine in SQL Sever.
Any advice or comments?
After another couple hours of trial and error I finally get the code working. And thanks @Mat's for pointing out to change adChar
to adVarChar
.
Dim cnn As New ADODB.Connection
Dim cmd As New ADODB.Command
cnn.ConnectionString = "Provider=SQLNCLI11;" _
& "Server=(Local);" _
& "Database=Inventory;" _
& "Integrated Security=SSPI;" _
& "DataTypeCompatibility=80;"
cnn.Open
With cmd
.ActiveConnection = cnn
.CommandType = adCmdStoredProc
.CommandText = "usp_mySP"
.Parameters.Append .CreateParameter("@fileName1", adVarChar, adParamInput, 50, File1.Name)
.Parameters.Append .CreateParameter("@fileName2", adVarChar, adParamInput, 50, File2.Name)
.Execute
End With
MsgBox"Thank God! Finally it's working!"
cnn.Close