CODEWITHSUNDEEP
vb.netexcel
TwoDent
TwoDent

Reputation: 385

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?

Upvotes: 0

Views: 81

Answers (1)

Mark
Mark

Reputation: 8150

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

Upvotes: 1

Related Questions