Imports System.Data.OleDb
Imports System.Linq
Imports System.Data.SqlClient
'Imports Microsoft.Office.Core
Imports Excel = Microsoft.Office.Interop.Excel
Imports ExcelAutoFormat = Microsoft.Office.Interop.Excel.XlRangeAutoFormat
Imports Microsoft.Office.Interop
Imports System.IO
Imports System.Xml.XPath
Imports System.Data
Imports System.Xml
Public Class Form1
Public Conn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Application.StartupPath & "\MyData.MDB")
Dim da As OleDbDataAdapter
Dim dt As DataTable
Dim sql As String
Dim Myreader As OleDbDataReader
Dim cmd As OleDbCommand
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Me.CenterToScreen()
End Sub
Private Sub bt_exit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles bt_exit.Click
'Application.Exit()
Close()
End Sub
Private Sub bt_Import_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles bt_Import.Click
'นำเข้าข้อมูลจาก excel มาใส่ตาราที่ 1
Dim iconn As OleDbConnection
Dim idta As OleDbDataAdapter
Dim idts As DataSet
Dim excel As String
Dim OpenFileDialog As New OpenFileDialog
OpenFileDialog1.FileName = ""
OpenFileDialog1.InitialDirectory = My.Computer.FileSystem.SpecialDirectories.Desktop
OpenFileDialog1.Filter = "Excel files (*.xlsx)|*.xlsx|XLS Files (*.xls)|*xls"
If (OpenFileDialog1.ShowDialog(Me) = System.Windows.Forms.DialogResult.OK) Then
DataGridView1.Columns.Clear()
Dim fi As New FileInfo(OpenFileDialog1.FileName)
Dim FileName As String = OpenFileDialog1.FileName
excel = fi.FullName
iconn = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excel + ";Extended Properties=Excel 12.0;")
idta = New OleDbDataAdapter("Select * From [Sheet1$]", iconn)
idts = New DataSet
idta.Fill(idts, "[Sheet1$]")
DataGridView1.DataSource = idts
DataGridView1.DataMember = "[Sheet1$]"
iconn.Close()
End If
End Sub
Private Sub bt_showData_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles bt_showData.Click
'เลือกตารางข้อมูลชื่อ Table1 มาแสดงในตารางที่ 2
sql = "SELECT * From Table1"
da = New OleDbDataAdapter(sql, Conn)
Try
If Conn.State = ConnectionState.Open Then Conn.Close()
Conn.Open()
dt = New DataTable
da.Fill(dt)
Conn.Close()
If dt.Rows.Count > 0 Then
DataGridView2.Columns.Clear()
DataGridView2.DataSource = dt
End If
Catch ex As Exception
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
End Sub
Private Sub btSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btSearch.Click
'dName,SCB,KTB,TMB,UOB
'ค้นหาข้อมูลจากชื่อ และแสดงข้อมูลในตาราที่ 3
If Conn.State = ConnectionState.Open Then Conn.Close()
Conn.Open()
cmd = New OleDbCommand("Select * From Table1 Where dName=@dName", Conn)
cmd.Parameters.AddWithValue("dName", TextBox1.Text)
Dim da As New OleDbDataAdapter
da.SelectCommand = cmd
Dim dt As New DataTable
da.Fill(dt)
DataGridView3.DataSource = dt
Conn.Close()
End Sub
Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
End Sub
End Class
Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
'เช็คว่าใน DataGridView3 มีข้อมูลไม๊
if ........... then
' ถ้ามี forloop ยัดมันลง DataGridView4
for ............
end if
End Sub
Dim dbName As String
Dim dbAddress As String
Dim dbZip As String
Using connObj As New SqlClient.SqlConnection("<connectionString>")
Using cmdObj As New SqlClient.SqlCommand("select name, address, zip from terr where id = '33334'", connObj)
connObj.Open()
Using readerObj As SqlClient.SqlDataReader = cmdObj.ExecuteReader
'This will loop through all returned records
While readerObj.Read
dbName = readerObj("name").ToString
dbAddress = readerObj("address").ToString
dbZip = readerObj("zip").ToString
'handle returned value before next loop here
End While
End Using
connObj.Close()
End Using
End Using
Load balance : Server 05
