leavesandmusic - Wednesday, January 3, 2018 2:35 PM

I have code written in vba that inserts data, via Excel, into SQL-Server.  So far everything works well.  However, sometimes it does not work. 

When it does not work, I receive the following error: Run-time error '-2147467259 (80004005)': [Microsoft][ODBC SQL Server Driver]Invalid use of default parameter. 

This error message is listed right beside my SQL.Execute command found below.

    Sub A()

    Dim Cn As ADODB.Connection
    Dim Server_Name As String
    Dim Database_Name As String
    Dim SQLStr As String
       
    Server_Name = "MyServer"
    Database_Name = "MyDataBase"
   
    Set Cn = New ADODB.Connection
    Cn.Open "Driver={SQL Server};Server=" & Server_Name & ";Database=" & Database_Name & ";"

    Dim SQL As ADODB.Command
    Set SQL = New ADODB.Command
   
    SQL.CommandText = "UPDATE [Something].[dbo].[tbl_Another_Table] SET [Value] =(?) WHERE [ID] = 1;"
    Dim I As Integer
    For I = 2 To 300
    SQL.CommandText = SQL.CommandText + "UPDATE [Something].[dbo].[tbl_Another_Table] SET [Value] =(?) WHERE [ID] =" & CStr(I) & ";"
    Next I
  
    Dim y As Integer
    For y = 1 To 300
    SQL.Parameters.Append SQL.CreateParameter("Target & CStr(y)", adVarChar, adParamInput, 50, Cells(y + 2, 15).Value)
    Next y
   
    SQL.ActiveConnection = Cn
    SQL.Execute   ####  This is where I always receive my error message :  --ERROR MESSAGE :   Run-time error '-2147467259 (80004005)':
    [Microsoft][ODBC SQL Server Driver]Invalid use of default parameter  ####

    Cn.Close
    Set Cn = Nothing
   
   
    End Sub