Imports System
Imports System.Data.OleDb
Imports System.Drawing
Imports System.Windows.Forms
Public Class Form1
Inherits System.Windows.Forms.Form
Private WithEvents songsDataGridView As New System.Windows.Forms.DataGridView
Private strKeyWord As String = ""
Private Sub Form1_Load(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles MyBase.Load
PopulateData()
End Sub
Private Sub btnSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearch.Click
strKeyWord = Me.txtSearch.Text
PopulateData()
End Sub
Private Sub PopulateData()
'*** BindData From Database **'
Dim objConn As OleDbConnection
Dim dtAdapter As OleDbDataAdapter
Dim dt As New DataTable
Dim strConnString As String
strConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=mydatabase.mdb"
objConn = New OleDbConnection(strConnString)
objConn.Open()
Dim strSQL As String
strSQL = "SELECT * FROM customer WHERE 1=1 "
If strKeyWord <> "" Then
strSQL = strSQL & " AND Name Like '%" & strKeyWord & "%' "
End If
dtAdapter = New OleDbDataAdapter(strSQL, objConn)
dtAdapter.Fill(dt)
dtAdapter = Nothing
objConn.Close()
objConn = Nothing
Me.myDgv.DataSource = dt
'******************************'
Me.myDgv.Columns.Clear()
With myDgv.ColumnHeadersDefaultCellStyle
.BackColor = Color.Navy
.ForeColor = Color.White
.Font = New Font(songsDataGridView.Font, FontStyle.Bold)
End With
Dim ColumnTextBox As DataGridViewTextBoxColumn
'*** Column (Select) ***'
Dim ColumnCheckBox As New DataGridViewCheckBoxColumn()
ColumnCheckBox.Width = 30
ColumnCheckBox.DataPropertyName = "Select"
Me.myDgv.Columns.Add(ColumnCheckBox)
'*** Checkbox in Header ***'
Dim ckBox As New CheckBox()
Dim rect As Rectangle = myDgv.GetCellDisplayRectangle(0, -1, True)
ckBox.Size = New Size(18, 18)
rect.X = rect.Location.X + (rect.Width / 4)
ckBox.Location = rect.Location
AddHandler ckBox.CheckedChanged, New EventHandler(AddressOf ckBox_CheckedChanged)
Me.myDgv.Controls.Add(ckBox)
myDgv.Columns(0).Frozen = False
'*** Column (CustomerID) ***'
ColumnTextBox = New DataGridViewTextBoxColumn()
ColumnTextBox.DataPropertyName = "CustomerID"
ColumnTextBox.HeaderText = "CustomerID"
ColumnTextBox.HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter
ColumnTextBox.Width = 100
Me.myDgv.Columns.Add(ColumnTextBox)
'*** Column (Name) ***'
ColumnTextBox = New DataGridViewTextBoxColumn()
ColumnTextBox.DataPropertyName = "Name"
ColumnTextBox.HeaderText = "Name"
ColumnTextBox.HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter
ColumnTextBox.Width = 100
Me.myDgv.Columns.Add(ColumnTextBox)
'*** Column (Email) ***'
ColumnTextBox = New DataGridViewTextBoxColumn()
ColumnTextBox.DataPropertyName = "Email"
ColumnTextBox.HeaderText = "Email"
ColumnTextBox.HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter
ColumnTextBox.Width = 100
Me.myDgv.Columns.Add(ColumnTextBox)
'*** Column (CountryCode) ***'
ColumnTextBox = New DataGridViewTextBoxColumn()
ColumnTextBox.DataPropertyName = "CountryCode"
ColumnTextBox.HeaderText = "CountryCode"
ColumnTextBox.HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter
ColumnTextBox.Width = 100
Me.myDgv.Columns.Add(ColumnTextBox)
'*** Column (Budget) ***'
ColumnTextBox = New DataGridViewTextBoxColumn()
ColumnTextBox.DataPropertyName = "Budget"
ColumnTextBox.HeaderText = "Budget"
ColumnTextBox.HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter
ColumnTextBox.Width = 100
Me.myDgv.Columns.Add(ColumnTextBox)
'*** Column (Used) ***'
ColumnTextBox = New DataGridViewTextBoxColumn()
ColumnTextBox.DataPropertyName = "Used"
ColumnTextBox.HeaderText = "Used"
ColumnTextBox.HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter
ColumnTextBox.Width = 100
Me.myDgv.Columns.Add(ColumnTextBox)
End Sub
Private Sub ckBox_CheckedChanged()
Dim i As Integer = 0
For j As Integer = 0 To Me.myDgv.RowCount - 1
Me.myDgv(0, j).Value = True
Next
End Sub
<STAThreadAttribute()> _
Public Shared Sub Main()
Application.EnableVisualStyles()
Application.Run(New Form1())
End Sub
End Class
""'songsDataGridView' is already declared as Friend WithEvents songsDataGridView As System.Windows.Forms.DataGridView' in this class""
โค๊ตที่ลองแก้ก็ประมานี้อะค่ะ
Code (VB.NET)
Imports System.Data
Imports MySql.Data.MySqlClient
Imports System.Drawing
Imports System.Windows.Forms
Public Class Form3
Inherits System.Windows.Forms.Form
Private WithEvents songsDataGridView As New System.Windows.Forms.DataGridView
Private strkeyWord As String = ""
Private Sub หนาแรกToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles หนาแรกToolStripMenuItem.Click
Form2.Show()
Me.Hide()
End Sub
Private Sub Button6_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button6.Click
Close()
End Sub
Private Sub คนหาหนงสอToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles คนหาหนงสอToolStripMenuItem.Click
Form4.Show()
Me.Hide()
End Sub
Private Sub Form3_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
PopulateData()
End Sub
Private Sub btnSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearch.Click
strkeyWord = Me.txtsearch.Text
PopulateData()
End Sub
Private Sub PopulateData()
Dim FdCon As New MySqlConnection
Dim fdCom As New MySqlCommand("", FdCon)
Dim dt As New DataTable
Dim strConnString As String
strConnString = ("Database=book;Data Source=localhost;User Id=root;Password=1234")
FdCon = New MySqlConnection(strConnString)
FdCon.Open()
Dim strSQL As String
strSQL = "SELECT * FROM books WHERE 1=1 "
If strkeyWord <> "" Then
strSQL = strSQL & "AND NAME Like '%" & strkeyWord & "%' "
End If
fdCom = New MySqlCommand(strSQL, FdCon)
fdCom = Nothing
FdCon.Close()
FdCon = Nothing
Me.myDgv.Columns.Clear()
With myDgv.ColumnHeadersDefaultCellStyle
.BackColor = Color.Navy
.ForeColor = Color.White
.Font = New Font(songsDataGridView.Font, FontStyle.Bold)
End With
Dim ColumnTextBox As DataGridViewTextBoxColumn
Dim ColumnCheckBox As New DataGridViewCheckBoxColumn()
ColumnCheckBox.Width = 30
ColumnCheckBox.DataPropertyName = "Select"
Me.myDgv.Columns.Add(ColumnCheckBox)
Dim ckBox As New CheckBox()
Dim rect As Rectangle = myDgv.GetCellDisplayRectangle(0, -1, True)
ckBox.Size = New Size(18, 18)
rect.X = rect.Location.X + (rect.Width / 4)
ckBox.Location = rect.Location
AddHandler ckBox.CheckedChanged, New EventHandler(AddressOf ckBox_CheckedChanged)
Me.myDgv.Controls.Add(ckBox)
myDgv.Columns(0).Frozen = False
ColumnTextBox = New DataGridViewTextBoxColumn()
ColumnTextBox.DataPropertyName = "idbook"
ColumnTextBox.HeaderText = "idbook"
ColumnTextBox.HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter
ColumnTextBox.Width = 100
Me.myDgv.Columns.Add(ColumnTextBox)
ColumnTextBox = New DataGridViewTextBoxColumn()
ColumnTextBox.DataPropertyName = "namebook"
ColumnTextBox.HeaderText = "namebook"
ColumnTextBox.HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter
ColumnTextBox.Width = 100
Me.myDgv.Columns.Add(ColumnTextBox)
ColumnTextBox = New DataGridViewTextBoxColumn()
ColumnTextBox.DataPropertyName = "author"
ColumnTextBox.HeaderText = "author"
ColumnTextBox.HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter
ColumnTextBox.Width = 100
Me.myDgv.Columns.Add(ColumnTextBox)
ColumnTextBox = New DataGridViewTextBoxColumn()
ColumnTextBox.DataPropertyName = "type"
ColumnTextBox.HeaderText = "type"
ColumnTextBox.HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter
ColumnTextBox.Width = 100
Me.myDgv.Columns.Add(ColumnTextBox)
ColumnTextBox = New DataGridViewTextBoxColumn()
ColumnTextBox.DataPropertyName = "publisher"
ColumnTextBox.HeaderText = "publisher"
ColumnTextBox.HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter
ColumnTextBox.Width = 100
Me.myDgv.Columns.Add(ColumnTextBox)
ColumnTextBox.DataPropertyName = "pricer"
ColumnTextBox.HeaderText = "price"
ColumnTextBox.HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter
ColumnTextBox.Width = 100
Me.myDgv.Columns.Add(ColumnTextBox)
End Sub
Private Sub ckBox_CheckedChanged()
Dim i As Integer = 0
For j As Integer = 0 To Me.myDgv.RowCount - 1
Me.myDgv(0, j).Value = True
Next
End Sub
<STAThreadAttribute()> _
Public Shared Sub Main()
Application.EnableVisualStyles()
Application.Run(New Form3())
End Sub
End Class
Dim strConnString As String
strConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=mydatabase.mdb"
objConn = New OleDbConnection(strConnString)
objConn.Open
ถ้าระบบข้อมูลของเราอยู่ใน local host ต้องเขียนคำสั่งอย่างไรคะ
หรือว่าตามโค้ดที่พี่วินเขียน ต้องแก้คำสั่งส่วนอื่นด้วยมั๊ยคะ
รบกวนด้วยนะคะ พอดีเพิ่งเริ่มศึกษา ยังไม่ค่อยเข้าใจเท่าไหร่ค่ะ
ขอบคุณครับ ผมทำเหมือนดึง Database มา show ธรรมดา แต่ทุก Event ให้ใช้ SQl Search แบบ Like
ของผมแบบนี้เลยครับ work ครับ
Code (VB.NET)
Dim ds4 As New DataSet
Dim dt4 As New DataTable
ds4.Tables.Add(dt4)
Dim da4 As New OleDbDataAdapter
Dim mysql4 As String
Dim connetionString4 As String
'///ที่อยู่ของฐานข้อมูล
connetionString4 = "Provider=Microsoft.Jet.OLEDB.4.0;Data source=" & _
"D:\AA\BB.mdb"
conn = New OleDbConnection(connetionString4)
mysql4 = "SELECT * FROM " & Me.mytable & " WHERE 1=1 "
If strKeyWord <> "" Then
mysql4 = mysql4 & " AND " & Me.mytype & " Like '%" & strKeyWord & "%' "
End If
'///ทำการเชื่อมต่อฐานข้อมูล
da4 = New OleDbDataAdapter(mysql4, conn)
da4.Fill(dt4)
'///กรอกข้อมูลลงในตาราง หลังจากค้นหาหนังสือแล้ว
DataGridView2.DataSource = dt4.DefaultView
'///ปิดการเชื่อมต่อ
conn.Close()
Date :
2013-09-26 18:58:22
By :
poopun007
No. 31
Guest
ลืมบอกไปผมใช้ VB2010 ผมก็แก้ code ตรง
Code (VB.NET)
mysql4 = "SELECT * FROM " & Me.mytable & " WHERE 1=1 "
If strKeyWord <> "" Then
mysql4 = mysql4 & " AND " & Me.mytype & " Like '%" & strKeyWord & "%' "
End If
Private Sub find()
Dim dt As New DataTable
Dim Adapter As MySqlDataAdapter
Dim myConnection As New MySqlConnection
Dim objCmd As New MySqlCommand
Dim strConnString, strSQL As String
strConnString = "Server=localhost;User Id=root; Password=pass; Database=databaseName; Pooling=false;charset=utf8"
myConnection.ConnectionString = strConnString
Dim myCommand As MySqlCommand = myConnection.CreateCommand()
myCommand.CommandText = "SELECT `id`, `name`, `email`, `uid`, `money` FROM `mytable` WHERE uid = '" & txtscan.Text & "'"
myCommand.CommandType = CommandType.Text
Adapter = New MySqlDataAdapter(myCommand)
Adapter.Fill(dt)
myConnection.Close()
Me.dgName.DataSource = dt
Me.dgName.Columns.Clear()
Dim column As DataGridViewTextBoxColumn
column = New DataGridViewTextBoxColumn()
column.DataPropertyName = "id"
column.HeaderText = "ลำดับที่"
column.Width = 50
Me.dgName.Columns.Add(column)
column = New DataGridViewTextBoxColumn()
column.DataPropertyName = "uid"
column.HeaderText = "รหัสประจำตัวนักศึกษา"
column.Width = 148
Me.dgName.Columns.Add(column)
column = New DataGridViewTextBoxColumn()
column.DataPropertyName = "name"
column.HeaderText = "ชื่อ - นามสกุล"
column.Width = 150
Me.dgName.Columns.Add(column)
column = New DataGridViewTextBoxColumn()
column.DataPropertyName = "email"
column.HeaderText = "อีเมล์แอดเดรส"
column.Width = 150
Me.dgName.Columns.Add(column)
column = New DataGridViewTextBoxColumn()
column.DataPropertyName = "money"
column.HeaderText = "จำนวนเงิน"
column.Width = 150
Me.dgName.Columns.Add(column)
dt = Nothing
End Sub
ประมาณนี้ครับ ผมก็ดัดแปลงมาจากของพี่ win
โดยผมใช้ select แล้วก็ where หาเอาครับ
Date :
2014-08-25 12:52:24
By :
kokaew
No. 35
Guest
ขอโค้ด ค้นหาหน่อยคับ
Public Class Form1
Private Sub Label5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Label5.Click
End Sub
Private Sub DateTimePicker2_ValueChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DateTimePicker2.ValueChanged
End Sub
Private Sub Label2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Label2.Click
End Sub
Private Sub Label6_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Label6.Click
End Sub
Private Sub EmBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
End Sub
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'TODO: This line of code loads data into the 'EmDataSet.em' table. You can move, or remove it, as needed.
Me.EmTableAdapter.Fill(Me.EmDataSet.em)
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
EmBindingSource.AddNew()
End Sub
Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
EmBindingSource.EndEdit()
EmTableAdapter.Update(Me.EmDataSet.em)
End Sub
Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
EmBindingSource.RemoveCurrent()
EmBindingSource.EndEdit()
EmTableAdapter.Update(Me.EmDataSet.em)
End Sub
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
Load balance : Server 03
