Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
Dim i As Integer = 0
Dim sqlAdd As String = ""
Dim xx As OleDb.OleDbDataReader
comOdb = New OleDbCommand("SELECT * FROM [ต้นฉบับ$]", Conn1)
xx = comOdb.ExecuteReader
With Conn
If .State = ConnectionState.Open Then .Close()
.ConnectionString = strConn
.Open()
End With
Dim dgNum As Integer = DataGridView1.RowCount
Dim comAdd As New SqlCommand
Dim ss As String = ""
If xx.Read Then
' 3 , 4 , 28 , 30 เป็นตัวเลขครับผม
For i = 1 To dgNum
sqlAdd = "INSERT INTO Graduate (GdtID,EdtID,GdtRank,GdtTitle,GdtFname,"
sqlAdd &= " GdtLname,GdtAdd,GdtTumbol,GdtAumphur,GdtProName,GdtProID,GdtHonor)"
sqlAdd &= " VALUES(" & xx(3).ToString & ","
sqlAdd &= "" & xx(14).ToString & ","
sqlAdd &= "'" & xx(4).ToString & "',"
sqlAdd &= "'" & xx(5).ToString & "',"
sqlAdd &= "'" & xx(6).ToString & "',"
sqlAdd &= "'" & xx(7).ToString & "',"
sqlAdd &= "'" & xx(25).ToString & "',"
sqlAdd &= "'" & xx(26).ToString & "',"
sqlAdd &= "'" & xx(27).ToString & "',"
sqlAdd &= "'" & xx(11).ToString & "',"
sqlAdd &= "" & xx(28).ToString & ","
If xx(30).ToString = "0" Then
ss = "3"
Else
ss = xx(30)
End If
sqlAdd &= "" & ss & ")"
With comAdd
.CommandType = CommandType.Text
.CommandText = sqlAdd
.Connection = Conn
.ExecuteNonQuery()
End With
If i = dgNum Then
MessageBox.Show("บันทึกลงฐานข้อมูล เรียบร้อยแล้ว !!!", "ผลการทำงาน", MessageBoxButtons.OK, MessageBoxIcon.Information)
Me.Hide()
End If
Next i
Exit Sub
Else
MessageBox.Show("เกิดข้อผิดพลาด !!!", "ผลการทำงาน", MessageBoxButtons.OK, MessageBoxIcon.Error)
Exit Sub
End If
Conn1.Close()
DtApt = Nothing
Conn1 = Nothing
End Sub
Tag : .NET, MySQL, Excel (Excel.Application), VB.NET, VS 2005 (.NET 2.x)
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.OleDb
Public Class frmAddDatabase
Dim Conn1 As New OleDb.OleDbConnection
Dim DtApt As New OleDbDataAdapter
Dim comOdb As OleDbCommand
Dim strConn2 As String
Dim ToClose As Boolean = False
Private Sub btnBorwse_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnBorwse.Click
Dim strFileName As String
With ofDlg
.Title = "กรุณาเลือกไฟล์ฐานข้อมูล Excel"
.Filter = "ไฟล์ฐานข้อมูล Excel (*.mdb)|*.xls"
.FileName = ""
.Multiselect = False
.FilterIndex = 0
If .ShowDialog() = Windows.Forms.DialogResult.OK Then
strFileName = .FileName
txtSql.Text = strFileName
Dim objConn As New OleDbConnection
strConn2 = "Provider=Microsoft.JET.OLEDB.4.0;" & "Data source=" & strFileName & ";Extended Properties=Excel 8.0;"
objConn = New OleDbConnection(strConn2)
objConn.Open()
With Conn1
If .State = ConnectionState.Open Then .Close()
.ConnectionString = strConn2
.Open()
End With
Else
strFileName = ""
txtSql.Text = ""
End If
End With
DataGridView1.DataSource = Nothing
grbSearch.Enabled = False
grbManage.Enabled = True
btnSave.Enabled = False
btnShow.Enabled = True
End Sub
Private Sub btnShow_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnShow.Click
Dim sqlShow As String
Dim ds As New DataSet
Dim sqlCountGdt As String = ""
sqlShow = "select * from [ต้นฉบับ$]"
DtApt = New OleDbDataAdapter(sqlShow, Conn1)
DtApt.Fill(ds, "[ต้นฉบับ$]")
DataGridView1.DataSource = ds.Tables("[ต้นฉบับ$]")
DataGridView1.Refresh()
grbSearch.Enabled = False
grbManage.Enabled = True
btnSave.Enabled = True
btnShow.Enabled = False
End Sub