Option Explicit On
Option Strict On
Imports System.Data
Imports System.Data.SqlClient
Imports System.Text
Imports System.Globalization
Public Class frmComposition2
Inherits System.Windows.Forms.Form
Dim da As SqlDataAdapter
Dim ds As DataSet = New DataSet
Dim dt As DataTable
Dim dr As SqlDataReader
Dim cn As SqlConnection
Dim com As SqlCommand
Dim sb As New StringBuilder
Dim CurrentRecord As Integer = 0
Dim RecordCount As Integer = 0
Dim ActionFlag As String = ""
Dim tmpFileName As String = ""
Dim IsFind As Boolean = False
Dim ToClose As Boolean = False
Dim LastP_ID As String = ""
Private Sub connection()
cn = New SqlConnection()
With cn
If .State = ConnectionState.Open Then .Close()
.ConnectionString = strCn ' string ที่ใช้ต่อ connection
.Open()
End With
End Sub
Private Sub frmComposition2_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Me.WindowState = FormWindowState.Maximized
LockAllCtrl()
connection()
Windows.Forms.Cursor.Current = Cursors.WaitCursor
Dim sqlPosition As String = ""
sqlPosition = "SELECT Supplier_ID,Shop_Name "
sqlPosition &= " FROM Supplier "
sqlPosition &= " ORDER BY Supplier_ID"
da = New SqlDataAdapter(sqlPosition, cn)
da.Fill(ds, "Supplier")
If ds.Tables("Supplier").Rows.Count <> 0 Then
With txtSupplier_ID_Purchase
.DataSource = ds.Tables("Supplier")
.DisplayMember = "Shop_Name"
.ValueMember = "Supplier_ID"
End With
End If
With cboSearch
.Items.Add("ชื่อพนักงาน")
.Items.Add("ชื่อวัตถุดิบ")
.SelectedIndex = 0
End With
ShowData()
FormatDataGridWithData()
Windows.Forms.Cursor.Current = Cursors.Default
End Sub
Private Sub ShowData()
connection()
Dim sqlDepartment As String = ""
sqlDepartment = "SELECT * FROM v_Purchase" 'v_Purchase เป็น view ใน sqlserver อ่ะเชื่อม 2 ตาราง
sqlDepartment &= " ORDER BY Purchase_ID"
If IsFind = True Then
ds.Tables("v_Purchase").Clear()
End If
da = New SqlDataAdapter(sqlDepartment, cn)
da.Fill(ds, "v_Purchase")
If ds.Tables("v_Purchase").Rows.Count <> 0 Then
IsFind = True
With dgvPurchase
.AllowNavigation = False
.ReadOnly = True
.DataSource = ds.Tables("v_Purchase")
End With
Else
IsFind = False
dgvPurchase.DataSource = Nothing
End If
End Sub
Private Sub FormatDataGridWithData()
Dim grdTS As DataGridTableStyle = New DataGridTableStyle()
With grdTS
.AlternatingBackColor = Color.White
.BackColor = Color.White
.MappingName = "v_Purchase"
End With
Dim cs1 As New DataGridTextBoxColumn()
With cs1
.HeaderText = "รหัสสั่งซื้อ"
.MappingName = "Purchase_ID"
.ReadOnly = True
.Alignment = HorizontalAlignment.Center
.Width = 40
End With
grdTS.GridColumnStyles.Add(cs1)
Dim cs2 As New DataGridTextBoxColumn()
With cs2
.HeaderText = "วัน/เดือน/ปี"
.MappingName = "DateTime"
.ReadOnly = True
.Width = 120
End With
grdTS.GridColumnStyles.Add(cs2)
Dim cs3 As New DataGridTextBoxColumn()
With cs3
.HeaderText = "รหัสพนักงาน"
.MappingName = "Employee_ID"
.Alignment = HorizontalAlignment.Center
.ReadOnly = True
.Width = 40
End With
grdTS.GridColumnStyles.Add(cs3)
Dim cs4 As New DataGridTextBoxColumn()
With cs4
.HeaderText = "ชื่อผู้จำหน่าย"
.MappingName = "Shop_Name"
.Alignment = HorizontalAlignment.Left
.ReadOnly = True
.Width = 200
End With
grdTS.GridColumnStyles.Add(cs4)
Dim cs5 As New DataGridTextBoxColumn()
With cs5
.HeaderText = "รหัววัตถุดิบ"
.MappingName = "Material_ID"
.Alignment = HorizontalAlignment.Center
.ReadOnly = True
.Width = 40
End With
grdTS.GridColumnStyles.Add(cs5)
Dim cs6 As New DataGridTextBoxColumn()
With cs6
.HeaderText = "ปริมาณ"
.MappingName = "Purchase_Vulom"
.ReadOnly = True
.Format = "0"
.Alignment = HorizontalAlignment.Center
.Width = 80
End With
grdTS.GridColumnStyles.Add(cs6)
Dim cs7 As New DataGridTextBoxColumn()
With cs7
.HeaderText = "อีเมลล์"
.MappingName = "email"
.Alignment = HorizontalAlignment.Left
.ReadOnly = True
.Width = 120
End With
grdTS.GridColumnStyles.Add(cs7)
Dim cs8 As New DataGridTextBoxColumn()
With cs8
.HeaderText = "รหัสผ่าน"
.MappingName = "password"
.Alignment = HorizontalAlignment.Center
.ReadOnly = True
.Width = 70
End With
grdTS.GridColumnStyles.Add(cs8)
Dim cs9 As New DataGridTextBoxColumn()
With cs9
.HeaderText = "ตำแหน่ง"
.MappingName = "Position_Name"
.Alignment = HorizontalAlignment.Left
.ReadOnly = True
.Width = 120
End With
grdTS.GridColumnStyles.Add(cs9)
With dgvPurchase
.TableStyles.Clear()
.CaptionFont = New Font("MS Sans Serif", 10.0, FontStyle.Regular)
.CaptionText = "รายการสั่งซื้อวัตถุดิบ"
.TableStyles.Add(grdTS)
.FlatMode = True
End With
End Sub
Private Sub ClearAllData()
txtPurchase_ID.Text = ""
txtDateTime_Purchase.Text = ""
txtEmployee_ID_Purchase.Text = ""
txtSupplier_ID_Purchase.Text = ""
txtMaterial_ID_Purchase.Text = ""
txtVulom_Purchase.Text = ""
'dgvPurchase.DataSource = Nothing
End Sub
Private Sub LockAllCtrl() 'เข้ามาหน้าแรก
txtPurchase_ID.Enabled = False
txtDateTime_Purchase.Enabled = False
txtEmployee_ID_Purchase.Enabled = False
txtSupplier_ID_Purchase.Enabled = False
txtMaterial_ID_Purchase.Enabled = False
txtVulom_Purchase.Enabled = False
btnInsert.Enabled = True
btnDelete.Enabled = False
btnUpdate.Enabled = False
btnSave.Enabled = False
btnCancel.Enabled = False
End Sub
Private Sub UnLockAllCtrl()
txtPurchase_ID.Enabled = True
txtDateTime_Purchase.Enabled = True
txtEmployee_ID_Purchase.Enabled = True
txtSupplier_ID_Purchase.Enabled = True
txtMaterial_ID_Purchase.Enabled = True
txtVulom_Purchase.Enabled = True
End Sub
Private Sub ClearBinding()
txtPurchase_ID.DataBindings.Clear()
txtDateTime_Purchase.DataBindings.Clear()
txtEmployee_ID_Purchase.DataBindings.Clear()
txtSupplier_ID_Purchase.DataBindings.Clear()
txtMaterial_ID_Purchase.DataBindings.Clear()
txtVulom_Purchase.DataBindings.Clear()
End Sub
Private Sub Search()
Dim sqlSearch As String = ""
If txtSearch.Text = "" Then Exit Sub
sqlSearch = "SELECT * FROM v_Purchase"
Select Case cboSearch.SelectedIndex
Case 0
sqlSearch &= " WHERE (Name='" & txtSearch.Text & "')"
Case 1
sqlSearch &= " WHERE (Material_Name LIKE '%" & txtSearch.Text & "%')"
End Select
sqlSearch &= " ORDER BY Purchase_ID"
connection() 'เรียกใช้ connection
If IsFind = True Then
ds.Tables("v_Purchase").Clear()
End If
ClearBinding() 'เรียกใช้ฟังก์ชั่น ClearBinding
da.SelectCommand.CommandText = sqlSearch
da.Fill(ds, "v_Purchase")
If ds.Tables("v_Purchase").Rows.Count <> 0 Then
IsFind = True
ClearBinding() 'เรียกใช้ฟังก์ชั่น ClearBinding
txtPurchase_ID.DataBindings.Add("Text", ds, "v_Purchase.Purchase_ID")
txtDateTime_Purchase.DataBindings.Add("Text", ds, "v_Purchase.DateTime")
txtEmployee_ID_Purchase.DataBindings.Add("Text", ds, "v_Purchase.Employee_ID")
txtSupplier_ID_Purchase.DataBindings.Add("Text", ds, "v_Purchase.Shop_Name")
txtMaterial_ID_Purchase.DataBindings.Add("Text", ds, "v_Purchase.Material_ID")
txtVulom_Purchase.DataBindings.Add("Text", ds, "v_Purchase.Purchase_Vulom")
dt = ds.Tables("v_Purchase")
btnInsert.Enabled = False
btnUpdate.Enabled = True
btnCancel.Enabled = True
LockAllCtrl()
Else
IsFind = False
MessageBox.Show("ไม่พบข้อมูลพนักงานที่คุณต้องการ", "ผลการตรวจสอบ", MessageBoxButtons.OK, MessageBoxIcon.Information)
ClearAllData()
btnInsert.Enabled = True
btnUpdate.Enabled = False
LockAllCtrl()
txtSearch.Focus()
txtSearch.SelectAll()
ShowData()
FormatDataGridWithData()
End If
End Sub
Private Sub btnSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearch.Click
Search()
End Sub
Private Sub btnInsert_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnInsert.Click
ActionFlag = "ADD"
UnLockAllCtrl()
ClearAllData()
txtPurchase_ID.Focus()
btnInsert.Enabled = False
btnDelete.Enabled = False
btnUpdate.Enabled = False
btnSave.Enabled = True
btnCancel.Enabled = True
End Sub
Private Sub dgvPurchase_MouseUp(ByVal sender As System.Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles dgvPurchase.MouseUp
Dim UserSelectRow As Integer = 0 'gidที่โช
If IsFind = True Then
UserSelectRow = dgvPurchase.CurrentRowIndex
btnInsert.Enabled = True
btnDelete.Enabled = True
btnUpdate.Enabled = True
btnSave.Enabled = False
btnCancel.Enabled = False
txtPurchase_ID.Text = CStr(dgvPurchase.Item(UserSelectRow, 0))
txtDateTime_Purchase.Text = CStr(dgvPurchase.Item(UserSelectRow, 1))
txtEmployee_ID_Purchase.Text = CStr(dgvPurchase.Item(UserSelectRow, 2))
txtSupplier_ID_Purchase.Text = CStr(dgvPurchase.Item(UserSelectRow, 3))
txtMaterial_ID_Purchase.Text = CStr(dgvPurchase.Item(UserSelectRow, 4))
txtMaterial_ID_Purchase.Text = CStr(dgvPurchase.Item(UserSelectRow, 5))
txtPurchase_ID.Focus()
End If
btnDelete.Enabled = True
End Sub
Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
ActionFlag = "EDIT"
UnLockAllCtrl()
txtPurchase_ID.ReadOnly = True
txtPurchase_ID.Focus()
btnDelete.Enabled = False
btnInsert.Enabled = False
btnUpdate.Enabled = False
btnSave.Enabled = True
btnCancel.Enabled = True
End Sub
Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
If txtPurchase_ID.Text = "" Or txtDateTime_Purchase.Text = "" Or txtEmployee_ID_Purchase.Text = "" Or txtSupplier_ID_Purchase.Text = "" Or txtMaterial_ID_Purchase.Text = "" Or txtVulom_Purchase.Text = "" Then
MessageBox.Show("กรุณาป้อนข้อมูลให้ครบ", "คำเตือน", MessageBoxButtons.OK, MessageBoxIcon.Warning)
Else
Dim sqlProduct As String = ""
Select Case ActionFlag
Case "ADD"
sqlProduct = "INSERT INTO Purchase(Purchase_ID,DateTime,Employee_ID,Supplier_ID,Material_ID,Purchase_Vulom)"
sqlProduct &= " VALUES('" & txtPurchase_ID.Text & "',"
sqlProduct &= "'" & txtDateTime_Purchase.Text & "',"
sqlProduct &= "'" & txtEmployee_ID_Purchase.Text & "',"
sqlProduct &= "'" & CStr(txtSupplier_ID_Purchase.SelectedValue) & "',"
sqlProduct &= "'" & txtMaterial_ID_Purchase.Text & "',"
sqlProduct &= "'" & txtVulom_Purchase.Text & "')"
Case "EDIT"
sqlProduct = "UPDATE Purchase"
sqlProduct &= " SET DateTime='" & txtDateTime_Purchase.Text & "',"
sqlProduct &= " Employee_ID='" & txtEmployee_ID_Purchase.Text & "',"
sqlProduct &= " Supplier_ID='" & CStr(txtSupplier_ID_Purchase.SelectedValue) & "',"
sqlProduct &= " Material_ID='" & txtMaterial_ID_Purchase.Text & "',"
sqlProduct &= " Purchase_Vulom='" & txtVulom_Purchase.Text & "'"
sqlProduct &= " WHERE(Purchase_ID ='" & txtPurchase_ID.Text & "')"
End Select
connection()
Dim tmpMSG As String = ""
If ActionFlag = "ADD" Then
tmpMSG = "คุณต้องการบันทึกการเพิ่มข้อมูล ใช่หรือไม่ ?"
ElseIf ActionFlag = "EDIT" Then
tmpMSG = "คุณต้องการบันทึกการแก้ไขข้อมูล ใช่หรือไม่ ?"
End If
Dim tr As SqlTransaction
Dim comCD As SqlCommand = New SqlCommand
tr = cn.BeginTransaction()
Try
With comCD
.CommandType = CommandType.Text
.CommandText = sqlProduct
.Connection = cn
.Transaction = tr
.ExecuteNonQuery()
End With
Catch ErrorToEdit As Exception
'MessageBox.Show("คุณป้อนรหัสพนักงานซ้ำ !!!", "ข้อผิดพลาด", MessageBoxButtons.OK, MessageBoxIcon.Warning)
MessageBox.Show("ERROR " & ErrorToEdit.Message, "ข้อผิดพลาด", MessageBoxButtons.OK, MessageBoxIcon.Error)
tr.Rollback()
txtPurchase_ID.Focus()
Exit Sub
End Try
If MessageBox.Show(tmpMSG, "คำยืนยัน", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.Yes Then
tr.Commit()
MessageBox.Show("บันทึกข้อมูลเรียบร้อยแล้ว", "ผลการทำงาน", MessageBoxButtons.OK, MessageBoxIcon.Information)
Else
tr.Rollback()
End If
btnInsert.Enabled = True
btnUpdate.Enabled = True
btnSave.Enabled = True
btnCancel.Enabled = True
ClearBinding()
ClearAllData()
LockAllCtrl()
ShowData()
FormatDataGridWithData()
txtSearch.Focus()
txtSearch.SelectAll()
End If
End Sub
Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click
Dim sqlDel As String = ""
Dim comDel As New SqlCommand
If MessageBox.Show("การลบข้อมูลนี้จะมีผลต่อการเชื่อมโยงข้อมูล คุณต้องการลบข้อมูลนี้ใช่หรือไม่", "คำยืนยัน", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.Yes Then
If MessageBox.Show("โปรดยืนยันการลบข้อมูลอีกครั้ง", "คำยืนยัน", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.Yes Then
With cn
If .State = ConnectionState.Open Then .Close()
.ConnectionString = strCn
.Open()
End With
Try
sqlDel = "DELETE FROM Purchase WHERE Purchase.Purchase_ID = '" & Trim(txtPurchase_ID.Text) & "'"
With comDel
.CommandType = CommandType.Text
.CommandText = sqlDel
.Connection = cn
.ExecuteNonQuery()
End With
Catch ex As Exception
MessageBox.Show(ex.Message, "ผลการทำงาน", MessageBoxButtons.OK, MessageBoxIcon.Information)
End Try
ClearAllData()
ShowData()
FormatDataGridWithData()
MessageBox.Show("ลบข้อมูลเรียบร้อยแล้ว !!!", "ผลการทำงาน", MessageBoxButtons.OK, MessageBoxIcon.Information)
End If
End If
btnInsert.Enabled = True
btnDelete.Enabled = False
btnUpdate.Enabled = False
btnSave.Enabled = False
btnCancel.Enabled = False
End Sub
Private Sub btnCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCancel.Click
btnInsert.Enabled = True
btnDelete.Enabled = True
btnUpdate.Enabled = True
btnSave.Enabled = True
btnCancel.Enabled = True
ShowData()
FormatDataGridWithData()
ClearAllData()
LockAllCtrl()
End Sub
End Class
Load balance : Server 03
