TwoDent TwoDent - 3 months ago 26
Vb.net Question

How to create parameterized queries with excel ? (VB.NET)

I'm trying to use parameterized queries to run faster update clauses in an Excel sheet... but when trying to do , vb.net says "Operation must use an updateable query".

With regular queries with concatenations , the query works:

Protected Friend Sub reemplazarDato(ByVal columna As String, ByVal dato As String, ByVal con As String)
Dim cmd As String = ""
conexion.Open()
For Each itm In arrayErrores
cmd = "UPDATE [" & obtenerHojaActual(columna, con) & "$] SET [" & columna & "]='" & dato & "' WHERE [" & columna & "]='" & itm & "'"
Try
Dim comando As New OleDbCommand(cmd, conexion)
comando.ExecuteNonQuery()
comando.Dispose()
Catch ex As Exception
repairmanMessage("Error inesperado", ex.Message, My.Resources._error).ShowDialog()
End Try
Next
conexion.Close()
End Sub


But when I try this way, the query dont work... ("Operation must use an updateable query")

Protected Friend Sub reemplazarDato(ByVal columna As String, ByVal dato As String, ByVal con As String)
Dim cmd As String = ""
Dim hoja As String = obtenerHojaActual(columna, con)
Dim comando As New OleDbCommand
comando.Connection = conexion
conexion.Open()
For Each itm In arrayErrores
cmd = "UPDATE [" & hoja & "$] SET [@columna]=@dato WHERE [@columna]=@itm"
comando.CommandText = cmd
comando.Parameters.Add("@columna", OleDbType.VarChar, columna.Length).Value = columna
comando.Parameters.Add("@dato", OleDbType.VarChar, dato.Length).Value = dato
comando.Parameters.Add("@itm", OleDbType.VarChar, itm.ToString.Length).Value = itm
Try
comando.ExecuteNonQuery()
comando.Parameters.Clear()
Catch ex As Exception
repairmanMessage("Error inesperado", ex.Message, My.Resources._error).ShowDialog()
End Try
Next
conexion.Close()
comando.Dispose()
End Sub


how can I do this with excel?

Answer

It is not valid to parameterize the column name - i.e. columna needs to be set using string concatenation as in your first code block, and not as a parameter. Also, you don't need to define the SQL and command parameters for each iteration - just define them once and set the value each time around the loop.

Protected Friend Sub reemplazarDato(ByVal columna As String, ByVal dato As String, ByVal con As String)
    Dim hoja As String = obtenerHojaActual(columna, con)
    Dim comando As New OleDbCommand
    comando.Connection = conexion
    comando.CommandText = "UPDATE [" & hoja & "$] SET [" & columna & "]=@dato WHERE [" & columna & "]=@itm"
    comando.Parameters.Add("@dato", OleDbType.VarChar, dato.Length)
    comando.Parameters.Add("@itm", OleDbType.VarChar, itm.ToString.Length)
    conexion.Open()
    For Each itm In arrayErrores
        comando.Parameters("@dato").Value = dato
        comando.Parameters("@itm").Value = itm
        Try
            comando.ExecuteNonQuery()
        Catch ex As Exception
            repairmanMessage("Error inesperado", ex.Message, My.Resources._error).ShowDialog()
        End Try
    Next
    conexion.Close()
    comando.Dispose()
End Sub