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

Question

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?

I don't believe that it is valid to parameterize column names in SQL - i.e. columna needs to be set using string concatenation as in your first code block, and not as a parameter. — Mark
– Mark, Commented Aug 15, 2016 at 20:38

Mark · Accepted Answer · 2016-08-15 20:50:51Z

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

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related