none
realizar insert en hoja de excel desde Visual basic.net RRS feed

  • Pregunta

  • Hola buenos días, estoy intentando hacer un insert into en una hoja de excel desde visual basic.net pero me genera el siguiente error:

    La operación debe usar una consulta actualizable.

    Les coloco el codigo por medio del cual intento hacer el insert:

                            Dim dt As Data.DataTable

            Try
                Dim archivo As String = Application.StartupPath & "\ArchivoXML\Ruta.xml"
                Dim oXML As Xml.XmlDocument
                Dim oNodes As Xml.XmlNode
                Dim oNode As Xml.XmlNode
                DataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill
                oXML = New Xml.XmlDocument
                oXML.Load(Trim(archivo))
                oNodes = oXML.DocumentElement
                oNode = oNodes.ChildNodes(0)
                oXML.Save(archivo)

                Dim strRuta As String = oNode.Item("Ruta").InnerText
                stConexion = ("Provider=Microsoft.ACE.OLEDB.12.0;" & ("Data Source=" & (strRuta & ";Extended Properties=""Excel 12.0;Xml;HDR=YES;IMEX=2"";")))


                Dim strsql As String = "Insert into [Hoja1$] (Fecha,IM,Servicio,Area,Grupo,Pais,Estado) values ('" & dtpFecha.Value.ToString & "','" & txtIM.Text & "','" & txtServicio.Text & "','" & txtArea.Text & "','" & txtGrupo.Text & "','" & txtPais.Text & "','" & cboEstado.Text & "')"
                Dim connectionString As String

                Dim odbCnn As Data.OleDb.OleDbConnection
                'Try 

               connectionString = stConexion
                odbCnn = New Data.OleDb.OleDbConnection(connectionString)
                Dim odbda As New Data.OleDb.OleDbDataAdapter(strSql, odbCnn)
                Dim cb As New Data.OleDb.OleDbCommandBuilder(odbda)
                Dt = New DataTable
                odbda.Fill(Dt)
                odbda.Update(Dt)



            Catch ex As Exception
                MsgBox(ex.Message, MsgBoxStyle.Critical, "Error")
            End Try




                                  
    sábado, 10 de junio de 2017 14:20

Respuestas

  • "Vladimir Angarita" escribió:

    > he hecho lo que me recomendaste, pero no me funciona, me sale el mismo error.
    >
    >   La operación debe usar una consulta actualizable.
    >
    > stConexion = (...Extended Properties=""Excel 12.0;Xml;HDR=YES;IMEX=2""

    Porque en las propiedades extendidas de la cadena de conexión estás indicando el parámetro IMEX=2, cuando tu intención es AÑADIR UN NUEVO REGISTRO, no ejecutar una consulta de SELECCIÓN, y a decir verdad, tampoco me he dado cuenta de eliminar dicho parámetro en mi respuesta.

    Tienes dos opciones:

        - eliminar por completo el parámetro IMEX:

         
       Dim cadenaConexion As String = "Provider=Microsoft.ACE.OLEDB.12.0;" &
                                          "Extended Properties='Excel 12.0 Xml;HDR=YES';" &
                                          "Data Source=" & strRuta


        - o establecer su valor a cero, que es el modo de exportación:

         
       Dim cadenaConexion As String = "Provider=Microsoft.ACE.OLEDB.12.0;" &
                                          "Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=0';" &
                                          "Data Source=" & strRuta

    Pero si le indicas el valor 1 ó 2, obtendrás el mensaje de error La operación debe usar una consulta actualizable cuando intentas ejecutar una consulta de acción INSERT INTO o UPDATE.

    Hablando de consultas SQL de acción, te advierto que obtendrás el oportuno error si intentas ejecutar una consulta de eliminación (DELETE), ya que el ISAM de Excel no permite la eliminación de datos.


    Enrique Martínez Montejo
    [MS MVP - Visual Studio y Tecnologías de Desarrollo]

    Nota informativa: La información contenida en este mensaje, así como el código fuente incluido en el mismo, se proporciona «COMO ESTÁ», sin garantías de ninguna clase, y no otorga derecho alguno. Usted asume cualquier riesgo al poner en práctica, utilizar o ejecutar lo recomendado o sugerido en el presente mensaje.

    Si esta respuesta le ha resultado útil, recuerde marcarla como satisfactoria.

    Si usas Visual Basic .NET y deseas ser productivo y feliz, se inteligente y activa la instrucción
    Option Strict.

    sábado, 10 de junio de 2017 20:26
    Moderador
  • "Vladimir Angarita" escribió:

    > estoy intentando hacer un insert into en una hoja de excel desde visual basic.net
    > pero me genera el siguiente error:
    >
    >     La operación debe usar una consulta actualizable.
    >
    > Les coloco el codigo por medio del cual intento hacer el insert:
    >
    > Dim strsql As String = "Insert into [Hoja1$] (...)"
    >
    > Dim odbda As New Data.OleDb.OleDbDataAdapter(strSql, odbCnn)
    > Dim cb As New Data.OleDb.OleDbCommandBuilder(odbda)
    > Dt = New DataTable
    > odbda.Fill(Dt)
    > odbda.Update(Dt)

    Hola, Vladimir:

    Con el código fuente que estás intentando ejecutar, te puede pasar de todo: ¡hasta coger un gran dolor de cabeza! :-))

    Para configurar las propiedades del adaptador de datos (odbda) mediante un objeto OleDbCommandBuilder (cb), tienes que especificar una consulta SQL de selección (SELECT ...), pero no una consulta SQL de datos añadidos (INSERT INTO).

    Pero aunque resuelvas ese error, después te aparecerá otro, porque ignoro de dónde has sacado la siguiente cadena de conexión para conectarte con el libro de Excel:

    >  Dim strRuta As String = oNode.Item("Ruta").InnerText
    >  stConexion = ("Provider=Microsoft.ACE.OLEDB.12.0;" & ("Data Source=" & (strRuta &
    >                ";Extended Properties=""Excel 12.0;Xml;HDR=YES;IMEX=2"";")))

    A día de hoy, NO EXISTE ningún ISAM de Excel llamado 'Excel 12.0;Xml'. Si deseas conectarte mediante OleDb con un libro de Excel no binario versión 2007 o superior, tienes que utilizar el siguiente ISAM:

        ...;Extended Properties='Excel 12.0 Xml;...'

    Es decir, sobra el carácter punto y coma entre el 0 y la palabra Xml.

    Si tu intención es añadir un nuevo registro a una hoja de cálculo llamada Hoja1 de una manera fácil y sencilla, mejor será que te olvides de la conexión de datos que estás ejecutando y te limites a configurar debidamente un objeto OleDbCommand para ejecutar la consulta INSERT INTO mediante una llamada a su método ExecuteNonQuery.

    Lo primero que tienes que hacer al comienzo del módulo, clase o formulario donde se encuentre el código, es importar el siguiente espacio de nombres:

    Imports System.Data.OleDb

    Y después, tras la siguiente línea de código

        oXML.Save(archivo)

    elimina todo el código que has publicado y sustitúyelo por el siguiente:

                Dim cadenaConexion As String = "Provider=Microsoft.ACE.OLEDB.12.0;" &
                                               "Extended Properties='Excel 12.0 Xml;HDR=YES';" &
                                               "Data Source=" & oNode.Item("Ruta").InnerText
    
                ' Establecer una conexión con el libro de Excel
                Using cnn As New OleDbConnection(cadenaConexion)
    
                    ' Crear un comando
                    Dim cmd As OleDbCommand = cnn.CreateCommand()
    
                    ' Especificamos una consulta INSERT INTO con parámetros por posición.
                    cmd.CommandText = "INSERT INTO [Hoja1$] (Fecha,IM,Servicio,Area,Grupo,Pais,Estado) VALUES (?,?,?,?,?,?,?)"
    
                    ' Añadimos los parámetros en el mismo orden en el que
                    ' se encuentran especificados los campos en la consulta
                    ' SQL de datos añadidos (INSERT INTO)
                    '
                    cmd.Parameters.AddWithValue("", dtpFecha.Value)
                    cmd.Parameters.AddWithValue("", txtIM.Text)
                    cmd.Parameters.AddWithValue("", txtServicio.Text)
                    cmd.Parameters.AddWithValue("", txtArea.Text)
                    cmd.Parameters.AddWithValue("", txtGrupo.Text)
                    cmd.Parameters.AddWithValue("", txtPais.Text)
                    cmd.Parameters.AddWithValue("", cboEstado.Text)
    
                    ' Abrir la conexión
                    cnn.Open()
    
                    ' Ejecutar la consulta
                    cmd.ExecuteNonQuery()
    
                End Using
    
            Catch ex As Exception
                ' Se ha producido un error
                MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
    
            End Try

    Un saludo


    Enrique Martínez Montejo
    [MS MVP - Visual Studio y Tecnologías de Desarrollo]

    Nota informativa: La información contenida en este mensaje, así como el código fuente incluido en el mismo, se proporciona «COMO ESTÁ», sin garantías de ninguna clase, y no otorga derecho alguno. Usted asume cualquier riesgo al poner en práctica, utilizar o ejecutar lo recomendado o sugerido en el presente mensaje.

    Si esta respuesta le ha resultado útil, recuerde marcarla como satisfactoria.

    Si usas Visual Basic .NET y deseas ser productivo y feliz, se inteligente y activa la instrucción
    Option Strict.


    sábado, 10 de junio de 2017 17:57
    Moderador

Todas las respuestas

  • "Vladimir Angarita" escribió:

    > estoy intentando hacer un insert into en una hoja de excel desde visual basic.net
    > pero me genera el siguiente error:
    >
    >     La operación debe usar una consulta actualizable.
    >
    > Les coloco el codigo por medio del cual intento hacer el insert:
    >
    > Dim strsql As String = "Insert into [Hoja1$] (...)"
    >
    > Dim odbda As New Data.OleDb.OleDbDataAdapter(strSql, odbCnn)
    > Dim cb As New Data.OleDb.OleDbCommandBuilder(odbda)
    > Dt = New DataTable
    > odbda.Fill(Dt)
    > odbda.Update(Dt)

    Hola, Vladimir:

    Con el código fuente que estás intentando ejecutar, te puede pasar de todo: ¡hasta coger un gran dolor de cabeza! :-))

    Para configurar las propiedades del adaptador de datos (odbda) mediante un objeto OleDbCommandBuilder (cb), tienes que especificar una consulta SQL de selección (SELECT ...), pero no una consulta SQL de datos añadidos (INSERT INTO).

    Pero aunque resuelvas ese error, después te aparecerá otro, porque ignoro de dónde has sacado la siguiente cadena de conexión para conectarte con el libro de Excel:

    >  Dim strRuta As String = oNode.Item("Ruta").InnerText
    >  stConexion = ("Provider=Microsoft.ACE.OLEDB.12.0;" & ("Data Source=" & (strRuta &
    >                ";Extended Properties=""Excel 12.0;Xml;HDR=YES;IMEX=2"";")))

    A día de hoy, NO EXISTE ningún ISAM de Excel llamado 'Excel 12.0;Xml'. Si deseas conectarte mediante OleDb con un libro de Excel no binario versión 2007 o superior, tienes que utilizar el siguiente ISAM:

        ...;Extended Properties='Excel 12.0 Xml;...'

    Es decir, sobra el carácter punto y coma entre el 0 y la palabra Xml.

    Si tu intención es añadir un nuevo registro a una hoja de cálculo llamada Hoja1 de una manera fácil y sencilla, mejor será que te olvides de la conexión de datos que estás ejecutando y te limites a configurar debidamente un objeto OleDbCommand para ejecutar la consulta INSERT INTO mediante una llamada a su método ExecuteNonQuery.

    Lo primero que tienes que hacer al comienzo del módulo, clase o formulario donde se encuentre el código, es importar el siguiente espacio de nombres:

    Imports System.Data.OleDb

    Y después, tras la siguiente línea de código

        oXML.Save(archivo)

    elimina todo el código que has publicado y sustitúyelo por el siguiente:

                Dim cadenaConexion As String = "Provider=Microsoft.ACE.OLEDB.12.0;" &
                                               "Extended Properties='Excel 12.0 Xml;HDR=YES';" &
                                               "Data Source=" & oNode.Item("Ruta").InnerText
    
                ' Establecer una conexión con el libro de Excel
                Using cnn As New OleDbConnection(cadenaConexion)
    
                    ' Crear un comando
                    Dim cmd As OleDbCommand = cnn.CreateCommand()
    
                    ' Especificamos una consulta INSERT INTO con parámetros por posición.
                    cmd.CommandText = "INSERT INTO [Hoja1$] (Fecha,IM,Servicio,Area,Grupo,Pais,Estado) VALUES (?,?,?,?,?,?,?)"
    
                    ' Añadimos los parámetros en el mismo orden en el que
                    ' se encuentran especificados los campos en la consulta
                    ' SQL de datos añadidos (INSERT INTO)
                    '
                    cmd.Parameters.AddWithValue("", dtpFecha.Value)
                    cmd.Parameters.AddWithValue("", txtIM.Text)
                    cmd.Parameters.AddWithValue("", txtServicio.Text)
                    cmd.Parameters.AddWithValue("", txtArea.Text)
                    cmd.Parameters.AddWithValue("", txtGrupo.Text)
                    cmd.Parameters.AddWithValue("", txtPais.Text)
                    cmd.Parameters.AddWithValue("", cboEstado.Text)
    
                    ' Abrir la conexión
                    cnn.Open()
    
                    ' Ejecutar la consulta
                    cmd.ExecuteNonQuery()
    
                End Using
    
            Catch ex As Exception
                ' Se ha producido un error
                MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
    
            End Try

    Un saludo


    Enrique Martínez Montejo
    [MS MVP - Visual Studio y Tecnologías de Desarrollo]

    Nota informativa: La información contenida en este mensaje, así como el código fuente incluido en el mismo, se proporciona «COMO ESTÁ», sin garantías de ninguna clase, y no otorga derecho alguno. Usted asume cualquier riesgo al poner en práctica, utilizar o ejecutar lo recomendado o sugerido en el presente mensaje.

    Si esta respuesta le ha resultado útil, recuerde marcarla como satisfactoria.

    Si usas Visual Basic .NET y deseas ser productivo y feliz, se inteligente y activa la instrucción
    Option Strict.


    sábado, 10 de junio de 2017 17:57
    Moderador
  • hola mi pana! he hecho lo que me recomendaste, pero no me funciona, me sale el mismo error.

    La operación debe usar una consulta actualizable.

    así quedo me codigo:

       Try
                Dim archivo As String = Application.StartupPath & "\ArchivoXML\Ruta.xml"
                Dim oXML As Xml.XmlDocument
                Dim oNodes As Xml.XmlNode
                Dim oNode As Xml.XmlNode
                DataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill
                oXML = New Xml.XmlDocument
                oXML.Load(Trim(archivo))
                oNodes = oXML.DocumentElement
                oNode = oNodes.ChildNodes(0)
                oXML.Save(archivo)

                Dim cadenaConexion As String = "Provider=Microsoft.ACE.OLEDB.12.0;" &
                                                "Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=2';" &
                                                "Data Source=" & oNode.Item("Ruta").InnerText

                'Establecer una conexión con el libro de Excel
                Using cnn As New OleDbConnection(cadenaConexion)

                    ' Crear un comando
                    Dim cmd As OleDbCommand = cnn.CreateCommand()

                    ' Especificamos una consulta INSERT INTO con parámetros por posición.
                    cmd.CommandText = "INSERT INTO [Hoja1$] (Fecha,IM,Servicio,Area,Grupo,Pais,Estado) VALUES (?,?,?,?,?,?,?)"

                    ' Añadimos los parámetros en el mismo orden en el que
                    ' se encuentran especificados los campos en la consulta
                    ' SQL de datos añadidos (INSERT INTO)
                    '
                    cmd.Parameters.AddWithValue("", dtpFecha.Value)
                    cmd.Parameters.AddWithValue("", txtIM.Text)
                    cmd.Parameters.AddWithValue("", txtServicio.Text)
                    cmd.Parameters.AddWithValue("", txtArea.Text)
                    cmd.Parameters.AddWithValue("", txtGrupo.Text)
                    cmd.Parameters.AddWithValue("", txtPais.Text)
                    cmd.Parameters.AddWithValue("", cboEstado.Text)

                    ' Abrir la conexión
                    cnn.Open()

                    ' Ejecutar la consulta
                    cmd.ExecuteNonQuery()

                End Using

            Catch ex As Exception
                ' Se ha producido un error
                MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)

            End Try

    sábado, 10 de junio de 2017 19:16
  • No será un tema de permisos de escritura?

    Saludos

    sábado, 10 de junio de 2017 19:39
  • "Vladimir Angarita" escribió:

    > he hecho lo que me recomendaste, pero no me funciona, me sale el mismo error.
    >
    >   La operación debe usar una consulta actualizable.
    >
    > stConexion = (...Extended Properties=""Excel 12.0;Xml;HDR=YES;IMEX=2""

    Porque en las propiedades extendidas de la cadena de conexión estás indicando el parámetro IMEX=2, cuando tu intención es AÑADIR UN NUEVO REGISTRO, no ejecutar una consulta de SELECCIÓN, y a decir verdad, tampoco me he dado cuenta de eliminar dicho parámetro en mi respuesta.

    Tienes dos opciones:

        - eliminar por completo el parámetro IMEX:

         
       Dim cadenaConexion As String = "Provider=Microsoft.ACE.OLEDB.12.0;" &
                                          "Extended Properties='Excel 12.0 Xml;HDR=YES';" &
                                          "Data Source=" & strRuta


        - o establecer su valor a cero, que es el modo de exportación:

         
       Dim cadenaConexion As String = "Provider=Microsoft.ACE.OLEDB.12.0;" &
                                          "Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=0';" &
                                          "Data Source=" & strRuta

    Pero si le indicas el valor 1 ó 2, obtendrás el mensaje de error La operación debe usar una consulta actualizable cuando intentas ejecutar una consulta de acción INSERT INTO o UPDATE.

    Hablando de consultas SQL de acción, te advierto que obtendrás el oportuno error si intentas ejecutar una consulta de eliminación (DELETE), ya que el ISAM de Excel no permite la eliminación de datos.


    Enrique Martínez Montejo
    [MS MVP - Visual Studio y Tecnologías de Desarrollo]

    Nota informativa: La información contenida en este mensaje, así como el código fuente incluido en el mismo, se proporciona «COMO ESTÁ», sin garantías de ninguna clase, y no otorga derecho alguno. Usted asume cualquier riesgo al poner en práctica, utilizar o ejecutar lo recomendado o sugerido en el presente mensaje.

    Si esta respuesta le ha resultado útil, recuerde marcarla como satisfactoria.

    Si usas Visual Basic .NET y deseas ser productivo y feliz, se inteligente y activa la instrucción
    Option Strict.

    sábado, 10 de junio de 2017 20:26
    Moderador