Programming for Everybody: VB. net Tutorial import data from Excel to SQL server

VB. net Tutorial import data from Excel to SQL server

 Imports System.Data.OleDb

Imports System.Data.SqlClient

Public Class Form15

    Private Sub Form15_Load(sender As Object, e As EventArgs) Handles Me.Load

        Dim conn As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;" &

         "Data Source=F:\names2.xlsx;Extended Properties= 'Excel 8.0;HDR=False'")

        conn.Open()

        Dim cmd As New OleDbCommand("Select * from [sheet4$]", conn)

        Dim da As New OleDbDataAdapter

        da.SelectCommand = cmd

        Dim dt As New DataTable

        dt.Clear()

        da.Fill(dt)

        DataGridView1.DataSource = dt

        conn.Close()

    End Sub


    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

        Dim conn2 As New SqlConnection("Data source=.;initial catalog=names2;integrated security=true")

        For Each row As DataGridViewRow In DataGridView1.Rows

            Dim cmd2 As New SqlCommand("Insert into table2(id,name1,age,telephone)Values(@id,@name1,@age,@telephone)", conn2)

            cmd2.Parameters.AddWithValue("id", row.Cells("id").Value.ToString)

            cmd2.Parameters.AddWithValue("name1", row.Cells("name1").Value.ToString)

            cmd2.Parameters.AddWithValue("age", row.Cells("age").Value.ToString)

            cmd2.Parameters.AddWithValue("telephone", row.Cells("telephone").Value.ToString)

            conn2.Open()

            cmd2.ExecuteNonQuery()

            conn2.Close()

        Next

        MessageBox.Show("All Data inserted successfully")

    End Sub

End Class



No comments:

Post a Comment