vbastored-proceduresado

VBA Call Stored Procedure with Parameters Using ADO


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?


Solution

  • 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