PERTEMUAN 10
IMPORT DATA EXCEL TO MYSQL WITH VISUAL BASIC.NET
1. Siapkan toolbox seperti gambar diatas (textboxt, button label dan progressbar,dll)
2. Buatlah database dengan ketentuan pada gambar
3. Masukan sourcecode berikut :
AWAS MANDIRI JANGAN ASAL MENGCOPY (TIDAK DIBERI TANDA)
Imports MySql.Data.MySqlClient
Public Class Form1
Private Function saveData(sql As String)
Dim mysqlCOn As MySqlConnection = New MySqlConnection("server=localhost;user
id=root;password=;database=dbpeople;sslMode=none")
Dim mysqlCmd As MySqlCommand
Dim resul As Boolean
Try
mysqlCOn.Open()
mysqlCmd = New MySqlCommand
With mysqlCmd
.Connection = mysqlCOn
.CommandText = sql
resul = .ExecuteNonQuery()
End With
Catch ex As Exception
MsgBox(ex.Message)
Finally
mysqlCOn.Close()
End Try
Return resul
End Function
Private Sub btnBrowse_Click(sender As Object, e As EventArgs) Handles btnBrowse.Click
With OpenFileDialog1
.Filter = "Excel files(*.xlsx)|*.xlsx|All files (*.*)|*.*"
.FilterIndex = 1
.Title = "Import data from Excel file"
End With
If OpenFileDialog1.ShowDialog() = DialogResult.OK Then
txtLocation.Text = OpenFileDialog1.FileName
End If
End Sub
Private Sub Timer1_Tick(sender As Object, e As EventArgs) Handles Timer1.Tick
If pg_load.Value = 100 Then
Timer1.Stop()
MsgBox("Success")
pg_load.Value = 0
Else
pg_load.Value += 1
End If
End Sub
Private Sub btnUpload_Click(sender As Object, e As EventArgs) Handles btnUpload.Click
Dim OLEcon As OleDb.OleDbConnection = New
OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & txtLocation.Text
& " ; " & "Extended Properties=Excel 8.0;")
Dim OLEcmd As New OleDb.OleDbCommand
Dim OLEda As New OleDb.OleDbDataAdapter
Dim OLEdt As New DataTable
Dim sql As String
Dim resul As Boolean
Try
OLEcon.Open()
With OLEcmd
.Connection = OLEcon
.CommandText = "select * from [Sheet1$]"
End With
OLEda.SelectCommand = OLEcmd
OLEda.Fill(OLEdt)
For Each r As DataRow In OLEdt.Rows
sql = "INSERT INTO tblperson (FNAME,LNAME,ADDRESS) VALUES ('" & r(0).ToString & "','"
& r(1).ToString & "','" & r(2).ToString & "')"
resul = saveData(sql)
If resul Then
Timer1.Start()
End If
Next
Catch ex As Exception
MessageBox.Show(ex.Message)
Finally
OLEcon.Close()
End Try
End Sub
End Class
