Imports System.Data
Imports System.Data.OleDb
Imports System.IO
Public Class Food
Private _dataset As DataSet
Private _conn As OleDbConnection
Private _cmd As OleDbCommand
Private _adapter As OleDbDataAdapter
Private _rowCount As Integer = 0
Private _position As Integer = 0
Private Sub Food_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
showdata()
End Sub
Private Sub showdata()
Dim _conn As New OleDbConnection(_conStr)
_conn.Open()
Dim sql As String = "SELECT * FROM Menu"
Dim _cmd As New OleDbCommand(sql, _conn)
Dim adapter As New OleDbDataAdapter(_cmd)
Dim data As New DataSet()
adapter.Fill(data, "food")
DataGridView1.DataSource = data.Tables("food")
_conn.Close()
End Sub
Private Sub Bindings()
'Binding ข้อมูลกับ TextBox
tbxfoodID2.DataBindings.Add("Text", _dataset, _
"food.Food_ID")
tbxfoodtype2.DataBindings.Add("Text", _dataset, _
"food.Food_Type")
tbxfoodname2.DataBindings.Add("Text", _dataset, _
"food.Food_Name")
tbxfoodprice2.DataBindings.Add("Text", _dataset, _
"food.Food_Price")
'ให้เริ่มแสดงที่แถวแรก()
Me.BindingContext(_dataset, "Menu").Position = 0
End Sub
Private Sub TabControl1_SelectedIndexChanged() _
Handles TabControl1.SelectedIndexChanged
If (TabControl1.SelectedIndex = 0) Then
ClearBindings()
btndelete.Enabled = False
ElseIf (TabControl1.SelectedIndex = 1) Then
showdata()
'Bindings()
btndelete.Enabled = True
End If
End Sub
Private Sub ClearBindings()
Reset()
tbxfoodID.Text = " "
Cmbtype.Text = " "
tbxfoodname.Text = " "
tbxfoodprice.Text = " "
End Sub
Private Sub btnsave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnsave.Click
Dim _conn As New OleDbConnection(_conStr)
_conn.Open()
If tbxfoodID.Text = "" Then
MessageBox.Show("กรุณากรอกรหัสอาหาร")
Me.tbxfoodID.Focus()
Exit Sub
End If
If Cmbtype.Text = "" Then
MessageBox.Show("กรุณาเลือกประเภทอาหาร")
Me.Cmbtype.Focus()
Exit Sub
End If
If tbxfoodname.Text = "" Then
MessageBox.Show("กรุณากรอกชื่ออาหาร")
Me.tbxfoodname.Focus()
Exit Sub
End If
If tbxfoodprice.Text = "" Then
MessageBox.Show("กรุณากรอกราคาอาหาร")
Me.tbxfoodprice.Focus()
Exit Sub
End If
Dim sql As String = "INSERT INTO " & _
"Menu(Food_ID, " & _
"Food_Type, " & _
"Food_Name, " & _
"Food_Price) " & _
"VALUES(@id, @type, @name, @price)"
Dim _cmd As New OleDbCommand(sql, _conn)
_cmd.Parameters.AddWithValue("@id", tbxfoodID.Text)
_cmd.Parameters.AddWithValue("@type", Cmbtype.SelectedItem)
_cmd.Parameters.AddWithValue("@name", tbxfoodname.Text)
_cmd.Parameters.AddWithValue("@price", tbxfoodprice.Text)
Dim afftectedRows As Integer = _cmd.ExecuteNonQuery()
If (afftectedRows = -1) Then
MsgBox("เกิดข้อผิดพลาด!" & vbNewLine & "ไม่สามารถจัดเก็บข้อมูลได้")
End If
_conn.Close()
ClearBindings()
showdata()
MsgBox("บันทึกรายการอาหารเรียบร้อย")
End Sub
Private Sub btndelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btndelete.Click
If (MsgBox("คุณต้องการลบรายการอาหารนี้ใช่หรือไม่ ?", MsgBoxStyle.OkCancel) = _
MsgBoxResult.Cancel) Then
Return
End If
Dim FoodID As String = DataGridView1.SelectedRows.Item(0).Cells(0).Value
Dim sql As String = "DELETE * FROM Menu WHERE Food_ID = " & FoodID
_cmd.CommandText = sql
_cmd.ExecuteNonQuery()
btndelete.Enabled = True
End Sub
Private Sub DataGridView1_CellContentClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.CellContentClick
If (DataGridView1.SelectedRows.Count > 0) Then
btndelete.Enabled = True
Else
btndelete.Enabled = False
End If
End Sub
End Class
Private Sub btndelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btndelete.Click
If (MsgBox("คุณต้องการลบรายการอาหารนี้ใช่หรือไม่ ?", MsgBoxStyle.OkCancel) = _
MsgBoxResult.Cancel) Then
Exit Sub
End If
Dim FoodID As String = "''"
If DataGridView1.SelectedRows.Item(0).Cells(0).Value IsNot Nothing Then
FoodID = DataGridView1.SelectedRows.Item(0).Cells(0).Value.ToString()
FoodID = "'" & FoodID & "'"
End If
Dim sql As String = "DELETE Menu WHERE Food_ID = " & FoodID
If _cmd Is Nothing Then
_cmd = New OleDbCommand(sql, _conn)
Else
_cmd.CommandText = sql
End If
If _conn.State <> ConnectionState.Open Then
_conn.Open()
End If
'-------[ถ้า run ผ่านก็ลบ 2 บันทัดข้างล่างนี้ทิ้ง]----------
Clipboard.SetText(sql)
MsgBox("เปิด notepad แล้วกด ctrl+v ลองเอา sql ที่ได้ไป run ดู" & vbCrLf & sql)
'-----------------------------------------------------------
_cmd.ExecuteNonQuery()
_conn.Close()
btndelete.Enabled = True
End Sub