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

2

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 & "][email protected] WHERE [" & headerCell & "][email protected]"
    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 & "][email protected]"
    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
    
asked by TwoDent 18.09.2016 в 19:59
source

1 answer

3

I was debugging the code on 32 bits , and found the cause of the problem.

The links to the question are very useful, especially How To Use ADO.NET to Retrieve and Modify Records in an Excel Workbook With Visual Basic .NET

To make UPDATE on a Excel sheet, we have two options: with or without a header.

1. With header or also call record update

The heading is the name of the columns, and when it is present, it must be indicated in the connection string, by means of the property HDR=YES :

conexion.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" &
                            "Data Source=" & value & ";" &
                            "Extended Properties='Excel 8.0;HDR=YES;IMEX=0;TypeGuessRows=0;'"
'-----------------------------------------------------------^

In this way we can make the statement UPDATE as we do it for any query to a database:

UPDATE Tabla SET campo1 = valor1, campo2 = valor2, ...

For the question code:

comando.CommandText =
    "UPDATE [Sheet1$] SET [Date] = DateSerial(2016, 9, 19) WHERE Name = 'Daniel'"

' Con parámetros:
comando.CommandText = "UPDATE [Sheet1$] SET [Date] = ? WHERE Name = ?"

The column C has the name Date , which is a reserved word, and therefore it is essential to use the brackets, [ and ] instead Name we see that no you need.

Interestingly, Excel 8.0 works for the engine ACE 12.0

2. No header or also call individual cell update

This time, in the connection string we must set the HDR=NO property:

conexion.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" &
                            "Data Source=" & value & ";" &
                            "Extended Properties='Excel 12.0 Xml;HDR=NO;'"
' ---------------------------------------------------------------^

IMEX and TypeGuessRows can not be used here because they depend on the rows having a column header, that is, they are only used when HDR=YES

Here the sentence UPDATE , requires some rules to follow:

  • Specify the range of cells that will be updated.

  • Instead of the column names, use the letter F followed by an ordinal, which indicates the column.

For example, to change the header:

     A         B        C      D    ...
  +------+-----------+------+-----+---
1 | Name | Last_name | Date | Age |     <=== Encabezado
  +------+-----------+------+-----+---
2 |      |           |      |     |
...

by the name of the cells:

     A         B        C      D    ...
  +------+-----------+------+-----+---
1 |  A1  |     B1    |  C1  |  D1 |     <=== Encabezado
  +------+-----------+------+-----+---
2 |      |           |      |     |
...

It must be done:

comando.CommandText = "UPDATE [Sheet1$A1:D1] SET F1 = 'A1', F2 = 'B1', F3 = 'C1', F4 = 'D1'"

' Con parámetros:
comando.CommandText = "UPDATE [Sheet1$A1:D1] SET F1 = ?, F2 = ?, F3 = ?, F4 = ?"

For the question code:

comando.CommandText = "UPDATE [Sheet1$C2:C2] SET [F1] = DateSerial(2016, 9, 19)"

' Con parámetros:
comando.CommandText = "UPDATE [Sheet1$C2:C2] SET [F1] = ?"

I used DateSerial , to avoid problems with the format of dates.

You should always indicate the range of cells, even if only one is updated.

    
answered by 19.09.2016 / 08:19
source