Excel Dataset to SQL Table

Hello

I want to extract data from excel sheet then pass it to SQL table
I am trying to pass the excel data to Dataset and the the same DS insert it to SQL i attached my code.
I am getting some errors in the insert like null errors i this my DS is not correct.

thanks

'tomar datos de excel
        Dim xlWBook = CType(Globals.ThisAddIn.Application.ActiveWorkbook, excel.Workbook).GetVstoObject()
        xlWBook.SaveAs("C:\libro1.xlsx")
        Dim worksheet = CType(Globals.ThisAddIn.Application.ActiveWorkbook.Worksheets(1),  _
        excel.Worksheet).GetVstoObject()
        Dim cnExcel As New Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\libro1.xlsx;" & _
"Extended Properties=Excel 8.0;")

        Dim oDa As New OleDbDataAdapter
        Dim oDs As New Data.DataSet
        Dim oCmd As New OleDbCommand
        cnExcel.Open()

        oCmd.CommandText = "SELECT * FROM cte"
        oCmd.Connection = cnExcel

        oDa.SelectCommand = oCmd

        oDa.Fill(oDs, "Clientes2")

        Dim rrrrr = oDs.Tables(0).Columns

        Try
            'Insertar en sql
            Dim sConnectionString As String
            sConnectionString = "Password=123;User ID=sa;" & _
                                "Initial Catalog=avi2;" & _
                                "Data Source=(local)"

            Dim objConn As New SqlConnection(sConnectionString)
            objConn.Open()

            ' Create an instance of a DataAdapter.
            Dim daAuthors As New SqlDataAdapter("select * from clientes2", objConn)
            daAuthors.Fill(oDs, "Clientes2")
            daAuthors.FillSchema(oDs, Data.SchemaType.Mapped, "Clientes2")


            Dim objCommandBuilder As New SqlCommandBuilder(daAuthors)
            Dim ins = objCommandBuilder.GetInsertCommand
            daAuthors.Update(oDs, "clientes2")


        Catch ex As Exception
            MsgBox(ex.Message)

        End Try
       





        Dim rrr = worksheet.ListObjects.Item(1).GetVstoObject.Range.Name.ToString
        Dim com As New Data.OleDb.OleDbCommand("SELECT * FROM [Tabla_DatosExternos_1]", cnExcel)
        Try
            com.ExecuteNonQuery()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

Open in new window

Saul_lAsked:
Who is Participating?
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.