Link to home
Start Free TrialLog in
Avatar of BirdWatchers
BirdWatchers

asked on

Trigger Stored Procedure and pass two paramters in Excel

I can't seem to get this code to work.  I have a stored procedure and I need to pass two filenames (with paths).
The connection is working fine.  How do you pass parameters to a SP?

Dim conn As ADODB.Connection
Dim cmd As ADODB.Command
Dim ConnString As String

     'Server to use ATLMSQLBPCP01.masco.loc
    '**************************************************************
    'Set Variables
    '**************************************************************
    Server = Sheets("Form").Range("F9").Value
    Appset = Sheets("Form").Range("F10").Value
       
    '****** Connect
    ConnString = "driver={SQL Server};server=" & Server & ";database=" & Appset & ";"
    Set conn = New ADODB.Connection
    conn.ConnectionString = ConnString
     
    Set cmd = New ADODB.Command
    cmd.ActiveConnection = ConnString
    cmd.CommandType = adCmdStoredProc
    cmd.CommandText = "csp_UpSertJrn"
    cmd.Parameters.Append cmd.CreateParameter("@File1", adVarChar, adParamInput, 60, "\\Client\C$\Users\DSilverman\Desktop\JrnUploadHeader.txt")
    cmd.Parameters.Append cmd.CreateParameter("@File2", adVarChar, adParamInput, 60, "\\Client\C$\Users\DSilverman\Desktop\JrnUploadDetail.txt")
    cmd.Execute
    conn.Close
 
    Set conn = Nothing
    Set cmd = Nothing
ASKER CERTIFIED SOLUTION
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.