(adsbygoogle = window.adsbygoogle || []).push({}); This was something I came up with when I had to update a table from a spreadsheet.



This does not take into account of parameterizing your inputs or sanitizing them. This was just a quick and dirty way of importing data from a spreadsheet to a SQL database
I dumped the data into a table originally so I could get the column names. In my case they were F1 and F2
You must login or register to view this content.

    

Imports System.Data.OleDb

Imports System.Data.SqlClient

Public Class frm_Main





    Private Sub frm_Main_Load(sender As Object, e As EventArgs) Handles MyBase.Load

        Dim excelDataTable As New DataTable

        excelDataTable = ImportExcelToDataTable("Provider=Microsoft.ACE.OLEDB.12.0ata Source=.\spread.xls;Extended Properties=""Excel 12.0;HDR=No;IMEX=1""")

        grdview_Excel.DataSource = excelDataTable









    End Sub



    Private Function ImportExcelToDataTable(excelConnString As String) As DataTable

        ImportExcelToDataTable = New DataTable

        Dim excelConn As OleDbConnection = New OleDbConnection(excelConnString)

        Dim oldbDataAdaptor As OleDbDataAdapter = New OleDbDataAdapter("SELECT * FROM [Sheet1$]", excelConn)



        excelConn.Open()

        oldbDataAdaptor.Fill(ImportExcelToDataTable)

        excelConn.Close()



    End Function



    Private Sub importDataToSQL(excelDataTabe As DataTable)

        Dim ConnectionString As String = "Data Source=sqlServer;Initial Catalog=HUB;User ID=USENAME;Password=PASSWORD providerName=System.Data.SqlClient"

        Dim sql As String = ""

        Dim dbConn As SqlConnection

        dbConn = New SqlConnection(ConnectionString)

        dbConn.Open()



        For Each row In excelDataTabe.Rows

      

                'F1 and F2 just happen to be the column names that we got from excel. This can be seen in the Datagrid that is on the form

                SQL = "INSERT INTO TABLE VALUES('" & row.Item("F1") & "'"

                Dim sqlcommand As SqlCommand = New SqlCommand(SQL, dbConn)

                sqlcommand.CommandText = SQL

                sqlcommand.ExecuteNonQuery()



    

        Next

        dbConn.Close()

    End Sub

End Class