No values specified for some parameters (EXCEL / VB.NET / OLEDB)

Very good community! The following problem I had experienced before when I made modifications (UPDATES) to the excel sheet, but I always found some solution ... but ... not now ... and the funniest thing that I am following to the letter The steps mentioned by Microsoft in its official website (in English): link

  

With ADO.NET, you can insert and update records in a workbook in one of three ways:   Directly run a command to insert or update records one at a time. To do this, you can create an OLEDbCommand object on your connection and set its CommandText property to a valid command to insert records

     

INSERT INTO [Sheet1 $] (F1, F2) values ('111', 'ABC')

     

or a command to update records

     

UPDATE [Sheet1 $] SET F2 = 'XYZ' WHERE F1 = '111'

     

and then call the ExecuteNonQuery method.

In my program I have functions and methods that collect the sheet depending on the column that is passed as a parameter, and functions that return the exact position of an empty cell or with errors (A1, B2, C15 ...) compared hundreds of times if the functions are returning well what is desired, and if ... they are all working well ... then the problem would be the same substitution function ...

Protected Friend Sub reemplazarDato(ByVal DColumn As String, ByVal headerCell As String, ByVal cell As String, ByVal dato As String)
    Dim hoja As String = obtenerHojaActual(DColumn)
    Dim comando As New OleDbCommand
    comando.Connection = conexion
    comando.CommandText = "UPDATE [" & hoja & "$] SET [" & cell & "]=@dato WHERE [" & headerCell & "]=@DColumn"
    comando.Parameters.Add("@dato", OleDbType.Date).Value = dato
    comando.Parameters.Add("@DColumn", OleDbType.VarChar).Value = DColumn
    MsgBox(comando.CommandText)
    MsgBox("Dato nuevo: " + comando.Parameters(0).Value + " en la columna: " + comando.Parameters(1).Value)
    conexion.Open()
    Try
        comando.ExecuteNonQuery()
    Catch ex As Exception
        cajaMensaje("Error inesperado", ex.Message, My.Resources._error).ShowDialog()
    End Try
    conexion.Close()
    comando.Dispose()
End Sub

These message boxes are inserted to verify that the data is being inserted correctly and the query is being well written. I am going to quote an example that I am trying now ... I am trying to change the value of cell C2 of this excel sheet:

The commandText that prints the mssgbox of the program ...

  

Note the similarity to microsoft: UPDATE [Sheet1 $] SET F2 = 'XYZ' WHERE F1 = '111'

Will it be that the parameters are not storing the values ?, because if they do it.

And before they respond "is that you have to remove the brackets to the values that represent columns", because no, I have already removed them and it does not work the same (besides, the same page suggests that they be used at the time of mark columns), also try making a regular query with concatenations and equal, nothing (also the same page suggests that you use queries with parameters when writing them) ...

What could this strange problem be?

UPDATE

Reading through the web about the problem, I found something else; You need to deactivate the HDR property in order to modify a specific cell. Insert a parameter in the property of the String connection to indicate whether it was activated or deactivated in a certain operation.

Protected Friend Property setConnection(ByVal hdr As String) As String
    Set(value As String)
        Try
            conexion.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & value & ";Extended Properties='Excel 8.0;HDR=" & hdr & ";IMEX=0;TypeGuessRows=0;'"
        Catch ex As Exception
            cajaMensaje("Error inesperado", ex.Message, My.Resources._error).ShowDialog()
        End Try
    End Set
    Get
        Return conexion.ConnectionString
    End Get
End Property

I also found that a range should be indicated to modify this cell. I did the following but it did not work (I get the same error ...) and honestly I do not understand that about the range. The method then stayed as follows:

   Protected Friend Sub reemplazarDato(ByVal DColumn As String, ByVal cell As String, ByVal dato As String)
    Dim hoja As String = obtenerHojaActual(DColumn)
    Dim comando As New OleDbCommand
    setConnection("NO") = setRuta
    MsgBox(conexion.ConnectionString)
    comando.Connection = conexion
    comando.CommandText = "UPDATE [" & hoja & "$" & cell & ":" & cell & "] SET [" & cell & "]=@dato"
    MsgBox(comando.CommandText)
    comando.Parameters.Add("@dato", OleDbType.VarChar, dato.Length).Value = dato
    conexion.Open()
    Try
        comando.ExecuteNonQuery()
    Catch ex As Exception
        cajaMensaje("Error inesperado", ex.Message, My.Resources._error).ShowDialog()
    End Try
    conexion.Close()
    comando.Dispose()
End Sub