แปลง คศ เป็น พศ ยังไงคะช่วยหนูที หนูทามไม่เป็นจริงๆๆนะคะ

Option Explicit On
Option Strict On
Imports System.Data
Imports System.Data.SqlClient
Imports System.Text
Imports System.Globalization
Public Class frmExp
    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 = ""
    'Dim d As Date
    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 frmExp_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 Position_ID,Position_Name "
        'sqlPosition &= " FROM Position "
        'sqlPosition &= " ORDER BY Position_ID"

        'da = New SqlDataAdapter(sqlPosition, cn)
        'da.Fill(ds, "Position")

        'If ds.Tables("Position").Rows.Count <> 0 Then
        '    With txtPID
        '        .DataSource = ds.Tables("Position")
        '        .DisplayMember = "Position_Name"
        '        .ValueMember = "Position_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_Exp"    'v_Exp เป็น view ใน sqlserver อ่ะเชื่อม 2 ตาราง
        sqlDepartment &= " ORDER BY Exp_ID"

        If IsFind = True Then
            ds.Tables("v_Exp").Clear()
        End If

        da = New SqlDataAdapter(sqlDepartment, cn)
        da.Fill(ds, "v_Exp")

        If ds.Tables("v_Exp").Rows.Count <> 0 Then
            IsFind = True
            With dgvExp
                .AllowNavigation = False
                .ReadOnly = True
                .DataSource = ds.Tables("v_Exp")
            End With
        Else
            IsFind = False
            dgvExp.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_Exp"
        End With

        Dim cs1 As New DataGridTextBoxColumn()
        With cs1
            .HeaderText = "รหัสวันหมดอายุ"
            .MappingName = "Exp_ID"
            .ReadOnly = True
            .Alignment = HorizontalAlignment.Center
            .Width = 70
        End With
        grdTS.GridColumnStyles.Add(cs1)

        Dim cs2 As New DataGridTextBoxColumn()
        With cs2
            .HeaderText = "รหัสวัตถุดิบ"
            .MappingName = "Material_ID"
            .ReadOnly = True
            .Alignment = HorizontalAlignment.Center
            .Width = 70
        End With
        grdTS.GridColumnStyles.Add(cs2)

        Dim cs3 As New DataGridTextBoxColumn()
        With cs3
            .HeaderText = "วันหมดอายุ"
            .MappingName = "Exp"
            .ReadOnly = True
            .Width = 120
        End With
        grdTS.GridColumnStyles.Add(cs3)

        Dim cs4 As New DataGridTextBoxColumn()
        With cs4
            .HeaderText = "ราคา(บาท)"
            .MappingName = "Price"
            .Alignment = HorizontalAlignment.Center
            .ReadOnly = True
            .Format = "0.00"
            .Width = 120
        End With
        grdTS.GridColumnStyles.Add(cs4)


        Dim cs5 As New DataGridTextBoxColumn()
        With cs5
            .HeaderText = "จำนวนปัจจุบัน(กรัม)"
            .MappingName = "Balance"
            .Alignment = HorizontalAlignment.Left
            .ReadOnly = True
            .Format = "0"
            .Width = 120
        End With
        grdTS.GridColumnStyles.Add(cs5)

        Dim cs6 As New DataGridTextBoxColumn()
        With cs6
            .HeaderText = "ชื่อวัตถุดิบ"
            .MappingName = "Material_Name"
            .Alignment = HorizontalAlignment.Center
            .ReadOnly = True
            .Width = 100
        End With
        grdTS.GridColumnStyles.Add(cs6)


        With dgvExp
            .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()
        txtExp_ID.Text = ""
        txtMaterial_ID.Text = ""
        txtExp.Text = ""
        txtPrice.Text = ""
        txtBalance.Text = ""
        'dgvExp.DataSource = Nothing
    End Sub
    Private Sub LockAllCtrl() 'เข้ามาหน้าแรก
        txtExp_ID.Enabled = False
        txtMaterial_ID.Enabled = False
        txtExp.Enabled = False
        txtPrice.Enabled = False
        txtBalance.Enabled = False
       
        btnInsert.Enabled = True
        btnDelete.Enabled = False
        btnUpdate.Enabled = False
        btnSave.Enabled = False
        btnCancel.Enabled = False
    End Sub
    Private Sub UnLockAllCtrl()
        txtExp_ID.Enabled = True
        txtMaterial_ID.Enabled = True
        txtExp.Enabled = True
        txtPrice.Enabled = True
        txtBalance.Enabled = True
    End Sub
    Private Sub ClearBinding()
        txtExp_ID.DataBindings.Clear()
        txtMaterial_ID.DataBindings.Clear()
        txtExp.DataBindings.Clear()
        txtPrice.DataBindings.Clear()
        txtBalance.DataBindings.Clear()
    End Sub
    Private Sub Search()
        Dim sqlSearch As String = ""
        If txtSearch.Text = "" Then Exit Sub
        sqlSearch = "SELECT * FROM v_Exp"
        Select Case cboSearch.SelectedIndex
            Case 0
                sqlSearch &= " WHERE (Exp_ID='" & txtSearch.Text & "')"
            Case 1
                sqlSearch &= " WHERE (Material_Name LIKE '%" & txtSearch.Text & "%')"

        End Select

        sqlSearch &= " ORDER BY Exp_ID"
        connection() 'เรียกใช้ connection

        If IsFind = True Then
            ds.Tables("v_Exp").Clear()
        End If
        ClearBinding() 'เรียกใช้ฟังก์ชั่น  ClearBinding

        da.SelectCommand.CommandText = sqlSearch
        da.Fill(ds, "v_Exp")

        If ds.Tables("v_Exp").Rows.Count <> 0 Then
            IsFind = True

            ClearBinding() 'เรียกใช้ฟังก์ชั่น  ClearBinding
            txtExp_ID.DataBindings.Add("Text", ds, "v_Exp.Exp_ID")
            txtMaterial_ID.DataBindings.Add("Text", ds, "v_Exp.Material_ID")
            txtExp.DataBindings.Add("Text", ds, "v_Exp.Exp")
            txtPrice.DataBindings.Add("Text", ds, "v_Exp.Price")
            txtBalance.DataBindings.Add("Text", ds, "v_Exp.Balance")

            dt = ds.Tables("v_Exp")
            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()
        txtExp_ID.Focus()

        btnInsert.Enabled = False
        btnDelete.Enabled = False
        btnUpdate.Enabled = False
        btnSave.Enabled = True
        btnCancel.Enabled = True
    End Sub
    Private Sub dgvExp_MouseUp(ByVal sender As System.Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles dgvExp.MouseUp
        Dim UserSelectRow As Integer = 0 'gidที่โช
        If IsFind = True Then
            UserSelectRow = dgvExp.CurrentRowIndex

            btnInsert.Enabled = True
            btnDelete.Enabled = True
            btnUpdate.Enabled = True
            btnSave.Enabled = False
            btnCancel.Enabled = False

            txtExp_ID.Text = CStr(dgvExp.Item(UserSelectRow, 0))
            txtMaterial_ID.Text = CStr(dgvExp.Item(UserSelectRow, 1))
            txtExp.Text = CStr(dgvExp.Item(UserSelectRow, 2))
            txtPrice.Text = CStr(dgvExp.Item(UserSelectRow, 3))
            txtBalance.Text = CStr(dgvExp.Item(UserSelectRow, 4))
           
            txtExp_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()
        txtExp_ID.ReadOnly = True
        txtExp_ID.Focus()

        btnDelete.Enabled = False
        btnInsert.Enabled = False
        btnUpdate.Enabled = False
        btnSave.Enabled = True
        btnCancel.Enabled = True
    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 Expire WHERE Expire.Exp_ID = '" & Trim(txtExp_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
    Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
        If txtExp_ID.Text = "" Or txtMaterial_ID.Text = "" Or txtExp.Text = "" Or txtPrice.Text = "" Or txtBalance.Text = "" Then
            MessageBox.Show("กรุณาป้อนข้อมูลให้ครบ", "คำเตือน", MessageBoxButtons.OK, MessageBoxIcon.Warning)
        Else
            Dim sqlProduct As String = ""
        

            Select Case ActionFlag
                Case "ADD"
                    sqlProduct = "INSERT INTO Expire(Exp_ID,Material_ID,Exp,Price,Balance)"
                    sqlProduct &= " VALUES('" & txtExp_ID.Text & "',"
                    sqlProduct &= "'" & txtMaterial_ID.Text & "',"
                    [font=Verdana]sqlProduct &= "'" & txtExp.Text & "',"[/font]
                    sqlProduct &= "'" & txtPrice.Text & "',"
                    sqlProduct &= "'" & txtBalance.Text & "')"
                   
                    txtMaterial_ID.Enabled = True
                    txtExp.Enabled = True
                    txtPrice.Enabled = True
                    txtBalance.Enabled = True

                Case "EDIT"
                    sqlProduct = "UPDATE Expire"
                    sqlProduct &= " SET Material_ID='" & txtMaterial_ID.Text & "',"
                    sqlProduct &= " Exp='" & txtExp.Text & "',"
                    sqlProduct &= " Price='" & txtPrice.Text & "',"
                    sqlProduct &= " Balance='" & txtBalance.Text & "'"
                    sqlProduct &= " WHERE(Exp_ID ='" & txtExp_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()
                txtExp_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
End Class