สอบถามเรื่องการเขียนโปรแกรม Vb.net และการสร้างฐานข้อมูลเพื่อเก็บข้อมูลในด้านการหักลบเวลาเพื่อให้ได้เวลาที่แท้จริง

Imports System.Data
Imports System.Data.SqlClient
Public Class Form3
    Private conStr As String = "Data Source=MAKEVB\SQLEXPRESS;Initial Catalog=NewNew;Integrated Security=True;uid=sa; pwd=matco1234; "
    Private mycon As New SqlConnection(conStr)
    Private bindingSrc As New BindingSource

    Private Sub Form3_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
       
        If mycon.State = ConnectionState.Closed Then
            mycon.Open()
        End If

        btnSave.Enabled = False
        btnSaveEdit.Enabled = False
        btnSaveProblem.Enabled = False
        btnSaveEditProblem.Enabled = False

    End Sub
    Sub template()
        Dim cmd As New SqlCommand
        Dim da As New SqlDataAdapter
        Dim ds As New DataSet
        Try
            With cmd
                .Connection = mycon
                .CommandType = CommandType.Text
                .CommandText = ""

            End With
            da.SelectCommand = cmd
            da.Fill(ds, "")
        Catch exsql As SqlException
            MsgBox(exsql.Message, MsgBoxStyle.Exclamation)
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Exclamation)
        End Try
    End Sub

    Sub InitialPartName()
        Dim cmd As New SqlCommand
        Dim da As New SqlDataAdapter
        Dim ds As New DataSet
        Try
            With cmd
                .Connection = mycon
                .CommandType = CommandType.Text
                .CommandText = "select PartName from T_Part"
            End With

            da.SelectCommand = cmd
            da.Fill(ds, "Part") 'Keep data to Dataset

            cboPartName.DataSource = ds.Tables("Part")
            cboPartName.DisplayMember = "PartName"
            cboPartName.ValueMember = "PartName"


        Catch exsql As SqlException
            MsgBox(exsql.Message, MsgBoxStyle.Exclamation)
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Exclamation)
        End Try

    End Sub

    Sub InitialMachine()
        Dim cmd As New SqlCommand
        Dim da As New SqlDataAdapter
        Dim ds As New DataSet
        Try
            With cmd
                .Connection = mycon
                .CommandType = CommandType.Text
                .CommandText = "select MachineName from T_Machine "
            End With

            da.SelectCommand = cmd
            da.Fill(ds, "Matco1_Machine") 'Keep data to Dataset
            cboMachineName.DataSource = ds.Tables("Matco1_Machine")
            cboMachineName.DisplayMember = "MachineName"
            cboMachineName.ValueMember = "MachineName"

        Catch exsql As SqlException
            MsgBox(exsql.Message, MsgBoxStyle.Exclamation)
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Exclamation)
        End Try

    End Sub
  
    
    Sub InitialSchType()
        Dim cmd As New SqlCommand
        Dim da As New SqlDataAdapter
        Dim ds As New DataSet
        Try
            With cmd
                .Connection = mycon
                .CommandType = CommandType.Text
                .CommandText = "select  distinct SchType from T_SCH  "
            End With

            da.SelectCommand = cmd
            da.Fill(ds, "Sch") 'Keep data to Dataset
            cboSchType.DataSource = ds.Tables("Sch")
            cboSchType.DisplayMember = "SchType"
            cboSchType.ValueMember = "SchType"

        Catch exsql As SqlException
            MsgBox(exsql.Message, MsgBoxStyle.Exclamation)
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Exclamation)
        End Try
    End Sub
    Sub refreshCboSchDetail(ByVal tmpSchType As String)
        Dim cmd As New SqlCommand
        Dim da As New SqlDataAdapter
        Dim ds As New DataSet
        Try
            With cmd
                .Connection = mycon
                .CommandType = CommandType.Text
                .CommandText = "select SchDetail from T_SCH where SchType = '" & tmpSchType & "' group by SchDetail order by SchDetail"
            End With

            da.SelectCommand = cmd
            da.Fill(ds, "SCH_Detail") 'Keep data to Dataset

            cboSchDetail.DataSource = ds.Tables("SCH_Detail")
            cboSchDetail.DisplayMember = "SchDetail"
            cboSchDetail.ValueMember = "SchDetail"
            cboSchDetail.Refresh()

        Catch exsql As SqlException
            MsgBox(exsql.Message, MsgBoxStyle.Exclamation)
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Exclamation)
        End Try

    End Sub

  
    Private Sub ShowDetailPartName()
        Dim cmd As New SqlCommand
        Dim da As New SqlDataAdapter
        Dim ds As New DataSet
        Dim headerText() As String = {"รหัส", "ชื่อชิ้นงาน", "ชื่อเครื่อง", "เวลาเริ่มฉีด", "เวลาฉีดเสร็จ", "รวมเวลาฉีด", "รหัสฉีด"}
        Dim i As Integer

        If Not (ds.Tables("AddPart") Is Nothing) Then
            ds.Tables("AddPart").Clear()
        End If

        Try
            With cmd
                .Connection = mycon
                .CommandType = CommandType.Text
                .CommandText = "select RunNo, PartID, MachineID, PartTimeStart, " & _
                  "PartTimeEnd , datediff(minute ,PartTimeStart , PartTimeEnd) as total, IDInject " & _
                    "from T_addPart order by RunNo desc"
            End With

            da.SelectCommand = cmd
            da.Fill(ds, "ShowDetailPartName") 'Keep data to Dataset
            grdAddPart.DataSource = ds.Tables("ShowDetailPartName")

            For i = 0 To headerText.Length - 1
                grdAddPart.Columns(i).HeaderText = headerText(i)
            Next
            grdAddPart.RowsDefaultCellStyle.BackColor = Color.White
            grdAddPart.AlternatingRowsDefaultCellStyle.BackColor = Color.PowderBlue
            ds.Dispose()
        Catch exsql As SqlException
            MsgBox(exsql.Message, MsgBoxStyle.Exclamation)
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Exclamation)
        End Try

    End Sub
    Private Sub ShowDetailSchedule()
        Dim cmd As New SqlCommand
        Dim da As New SqlDataAdapter
        Dim ds As New DataSet

        Dim headerText() As String = {"รหัสฉีด", "ประเภทปัญหา", "รายละเอียดปัญหา", "เวลาของปัญหา"}
        Dim i As Integer

        If Not (ds.Tables("AddPart") Is Nothing) Then
            ds.Tables("AddPart").Clear()

        End If

        Try
            With cmd
                .Connection = mycon
                .CommandType = CommandType.Text
                .CommandText = "select   IDInject, schid, schDetail, schproblemtime  from t_AddSchTime order by IDInject desc "
              
            End With

            da.SelectCommand = cmd

            da.Fill(ds, "ShowDetailSchedule") 'Keep data to Dataset
        
            grdAddSchTime.DataSource = ds.Tables("ShowDetailSchedule")

            For i = 0 To headerText.Length - 1
                grdAddSchTime.Columns(i).HeaderText = headerText(i)
            Next
            grdAddSchTime.RowsDefaultCellStyle.BackColor = Color.White
            grdAddSchTime.AlternatingRowsDefaultCellStyle.BackColor = Color.PowderBlue
            ds.Dispose()
        Catch exsql As SqlException
            MsgBox(exsql.Message, MsgBoxStyle.Exclamation)
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Exclamation)
        End Try

    End Sub

    Private Function IsDataComplete()
        'txtProblemTime.Text.Trim()
        If DateTimeEnd.Text = "" Or DateTimeStart.Text = "" Or txtIDInject.Text = "" Then
            MessageBox.Show("กรุณาใส่ข้อมูลให้ครบ")
            Return False
        Else
            Return True
        End If
    End Function
    Private Function IsDataCompleteSchedule()
        If txtIDInjectProblem.Text = "" Or txtProblemTime.Text = "" Then
            MsgBox("กรุณาใส่ข้อมูลให้ครบ")
            Return False
        Else
            Return True
        End If
    End Function

    Private Sub btnShow_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnShow.Click
        Call ShowDetailPartName()
    End Sub

    Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
        If btnAdd.Text = "เพิ่มข้อมูล" Then
            cboPartName.Focus()
            btnAdd.Text = "ยกเลิก"
            btnSave.Enabled = True
            btnEdit.Enabled = False
            btnDelete.Enabled = False
            txtRunNo.Text = ""
            DateTimeStart.Text = ""
            DateTimeEnd.Text = ""
            txtIDInject.Text = ""
            Call InitialMachine()
            Call InitialPartName()
        Else
            btnAdd.Text = "เพิ่มข้อมูล"
            btnSave.Enabled = False
            btnEdit.Enabled = True
            btnDelete.Enabled = True
        End If
    End Sub

    Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
        Dim cmd As New SqlCommand
        Dim da As New SqlDataAdapter
        Dim ds As New DataSet
        If IsDataComplete() = False Then
            Exit Sub
        End If
        Try
            With cmd
                .Connection = mycon
                .CommandType = CommandType.Text
                .CommandText = "insert into T_AddPart (PartID, MachineID, PartTimeStart, PartTimeEnd, IDInject)" & _
                                 " values (@PartID,  @MachineID, @PartTimeStart, @PartTimeEnd, @IDInject)"
                .Parameters.Clear()
                da.SelectCommand = cmd
                .Parameters.AddWithValue("@PartID", cboPartName.Text)
                .Parameters.AddWithValue("@MachineID", cboMachineName.Text)
                .Parameters.AddWithValue("@PartTimeStart", DateTimeStart.Value)
                .Parameters.AddWithValue("@PartTimeEnd", DateTimeEnd.Value)
                .Parameters.AddWithValue("@IDInject", txtIDInject.Text)
                Dim result As Integer = .ExecuteNonQuery
                If result = -1 Then
                    MessageBox.Show("เกิดข้อผิดพลาดไม่สามารถเพิ่มข้อมูลได้")
                Else
                    MessageBox.Show("บันทึกข้อมูลแล้ว")
                    btnAdd.PerformClick()
                End If
                Call ShowDetailPartName()
            End With
        Catch exsql As SqlException
            MsgBox(exsql.Message, MsgBoxStyle.Exclamation)
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Exclamation)
        End Try
    End Sub

    Private Sub btnEdit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnEdit.Click
        If btnEdit.Text = "แก้ไข" Then
            btnEdit.Text = "ยกเลิกการแก้ไข"
            btnDelete.Enabled = False
            btnSaveEdit.Enabled = True
            btnAdd.Enabled = False
            ShowDetailPartName()
        Else
            btnEdit.Text = "แก้ไข"
            btnSaveEdit.Enabled = False
            btnAdd.Enabled = True
            btnDelete.Enabled = True
            Exit Sub
        End If

    End Sub
    Private Sub grdAddPart_CellContentDoubleClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles grdAddPart.CellClick
        txtRunNo.Text = grdAddPart.Rows.Item(e.RowIndex).Cells(0).Value
        cboPartName.Text = grdAddPart.Rows.Item(e.RowIndex).Cells(1).Value
        cboMachineName.Text = grdAddPart.Rows.Item(e.RowIndex).Cells(2).Value
        DateTimeStart.Value = grdAddPart.Rows.Item(e.RowIndex).Cells(3).Value
        DateTimeEnd.Value = grdAddPart.Rows.Item(e.RowIndex).Cells(4).Value
        txtIDInject.Text = grdAddPart.Rows.Item(e.RowIndex).Cells(5).Value

    End Sub
    Private Sub btnSaveEdit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSaveEdit.Click


        Dim cmd As New SqlCommand
        Dim da As New SqlDataAdapter
        Dim ds As New DataSet
        Try
            With cmd
                .Connection = mycon
                .CommandType = CommandType.Text
                .CommandText = " update T_AddPart set PartID=@PartID, MachineId=@MachineID, PartTimeStart=@PartTimeStart,PartTimeEnd=@PartTimeEnd, IDInject=@IDInject " & _
                                "where RunNo=@Runno"
                .Parameters.Clear()
                da.SelectCommand = cmd

                .Parameters.AddWithValue("@RunNo", txtRunNo.Text)
                .Parameters.AddWithValue("@PartID", cboPartName.Text)
                .Parameters.AddWithValue("@MachineID", cboMachineName.Text)
                .Parameters.AddWithValue("@PartTimeStart", DateTimeStart.Value)
                .Parameters.AddWithValue("@PartTimeEnd", DateTimeEnd.Value)
                .Parameters.AddWithValue("@IDInject", txtIDInject.Text)
            End With




            If cmd.ExecuteNonQuery = 1 Then
                MessageBox.Show("บันทึกการแก้ไขแล้ว")
                btnEdit.Text = "แก้ไข"
                btnSaveEdit.Enabled = False
                btnDelete.Enabled = True

            Else
                MessageBox.Show("เกิดข้อผิดพลาดในการแก้ไขเนื่องจากคุณยังไม่ได้เลือกข้อมูลที่ต้องการแก้ไข")
                Exit Sub
            End If

            btnAdd.Enabled = True
            ShowDetailPartName()
        Catch exsql As SqlException
            MsgBox(exsql.Message, MsgBoxStyle.Exclamation)
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Exclamation)
        End Try
    End Sub

    Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click
        If grdAddPart.SelectedRows.Count = 0 Then
            MessageBox.Show("ท่านยังไม่ได้เลือกแถวที่ต้องการ")
            Exit Sub
        End If
        Dim msgResult As DialogResult = _
        MessageBox.Show("ท่านต้องการลบข้อมูลแถวที่เลือกจริงหรือไม่", "ยืนยันการลบ", _
        MessageBoxButtons.OKCancel)
        If msgResult = Windows.Forms.DialogResult.Cancel Then
            Exit Sub
        End If

        Dim dss As New DataSet
        Dim adapter As New SqlDataAdapter
        Dim command As New SqlCommand

        Dim key As Integer = _
        grdAddPart.SelectedRows.Item(0).Cells("runno").Value

        With command
            .Connection = mycon
            .CommandType = CommandType.Text
            .CommandText = "DELETE FROM T_AddPart where runno = " & key
            adapter.SelectCommand = command
            .ExecuteNonQuery()

        End With
        Call ShowDetailPartName()
    End Sub

    Private Sub StatusStrip1_ItemClicked(ByVal sender As System.Object, ByVal e As System.Windows.Forms.ToolStripItemClickedEventArgs)

    End Sub
    Private Sub btnCal_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        Dim cmd As New SqlCommand
        Dim da As New SqlDataAdapter
        Dim ds As New DataSet
        Try
            With cmd
                .Connection = mycon
                .CommandType = CommandType.Text
                .CommandText = " select  datediff(minute ,PartTimeStart , PartTimeEnd) from t_addPart"

                da.SelectCommand = cmd
                da.Fill(ds, "ShowTime") 'Keep data to Dataset
                ' TextBox1.Text = ds.Tables("ShowTime")
                ' TextBox1.DataSource = ds.Tables("ShowTime")
            End With
            ' textbox1.Show=
        Catch exsql As SqlException
            MsgBox(exsql.Message, MsgBoxStyle.Exclamation)
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Exclamation)
        End Try
    End Sub

    Private Sub btnInputbox_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        ' MessageBox.Show("กรุณาใส่", InputBox)
        InputBox(txtRunNo.Text, "ใส่")

    End Sub
    Private Sub btnAddProblem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAddProblem.Click
        If btnAddProblem.Text = "เพิ่มข้อมูล" Then
            cboSchType.Focus()
            btnAddProblem.Text = "ยกเลิก"
            btnSaveProblem.Enabled = True
            btnEditProblem.Enabled = False
            btnDeleteProblem.Enabled = False
            txtIDInjectProblem.Text = ""
            txtProblemTime.Text = ""
            Call InitialSchType()

        Else
            btnAddProblem.Text = "เพิ่มข้อมูล"
            btnSaveProblem.Enabled = False
            btnEditProblem.Enabled = True
            btnDeleteProblem.Enabled = True
        End If
    End Sub


    Private Sub cboSchType_DropDownClosed1(ByVal sender As Object, ByVal e As System.EventArgs) Handles cboSchType.DropDownClosed
        refreshCboSchDetail(cboSchType.SelectedValue)
    End Sub

    Private Sub btnSaveProblem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSaveProblem.Click
        Dim cmd As New SqlCommand
        Dim da As New SqlDataAdapter
        Dim ds As New DataSet
        If IsDataCompleteSchedule() = False Then
            Exit Sub
        End If
        Try
            With cmd
                .Connection = mycon
                .CommandType = CommandType.Text
                .CommandText = "insert into T_AddSchTime (IDInject, SchID, SchDetail, SchProblemTime)" & _
                                 " values (@IDInject,  @SchID, @SchDetail, @SchProblemTime)"

                .Parameters.Clear()
                da.SelectCommand = cmd

                .Parameters.AddWithValue("@IDInject", txtIDInjectProblem.Text)
                .Parameters.AddWithValue("@SchID", cboSchType.Text)
                .Parameters.AddWithValue("@SchDetail", cboSchDetail.Text)
                .Parameters.AddWithValue("@SchProblemTime", txtProblemTime.Text)


                Dim result As Integer = .ExecuteNonQuery
                If result = -1 Then
                    MessageBox.Show("เกิดข้อผิดพลาดไม่สามารถเพิ่มข้อมูลได้")
                Else
                    MessageBox.Show("บันทึกข้อมูลแล้ว")
                    btnAddProblem.PerformClick()
                End If
                Call ShowDetailSchedule()
            End With
        Catch exsql As SqlException
            MsgBox(exsql.Message, MsgBoxStyle.Exclamation)
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Exclamation)
        End Try
    End Sub

    Private Sub btnShowSchProblemTime_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnShowSchProblemTime.Click
        Call ShowDetailSchedule()
    End Sub

    Private Sub btnDeleteProblem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDeleteProblem.Click
        If grdAddSchTime.SelectedRows.Count = 0 Then
            MessageBox.Show("ท่านยังไม่ได้เลือกแถวที่ต้องการ")
            Exit Sub
        End If
        Dim msgResult As DialogResult = _
        MessageBox.Show("ท่านต้องการลบข้อมูลแถวที่เลือกจริงหรือไม่", "ยืนยันการลบ", _
        MessageBoxButtons.OKCancel)
        If msgResult = Windows.Forms.DialogResult.Cancel Then
            Exit Sub
        End If

        Dim dss As New DataSet
        Dim adapter As New SqlDataAdapter
        Dim command As New SqlCommand

        Dim key As Integer = _
        grdAddSchTime.SelectedRows.Item(0).Cells("IDInject").Value

        With command
            .Connection = mycon
            .CommandType = CommandType.Text
            .CommandText = "DELETE FROM T_AddSchTime where IDInject = " & key
            adapter.SelectCommand = command
            .ExecuteNonQuery()

        End With
        Call ShowDetailSchedule()
    End Sub

    Private Sub btnEditProblem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnEditProblem.Click
        If btnEditProblem.Text = "แก้ไข" Then
            btnEditProblem.Text = "ยกเลิกการแก้ไข"
            btnDeleteProblem.Enabled = False
            btnSaveEditProblem.Enabled = True
            btnAddProblem.Enabled = False
            ShowDetailSchedule()
          
        Else
            btnEditProblem.Text = "แก้ไข"
            btnSaveEditProblem.Enabled = False
            btnAddProblem.Enabled = True
            btnDeleteProblem.Enabled = True
            Exit Sub
        End If
    End Sub

    Private Sub btnSaveEditProblem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSaveEditProblem.Click
        Dim cmd As New SqlCommand
        Dim da As New SqlDataAdapter
        Dim ds As New DataSet
        Try
            With cmd
                .Connection = mycon
                .CommandType = CommandType.Text
                .CommandText = " update T_AddSchTime set IDInject=@IDInject, SchId=@SchID, SchDetail=@SchDetail,SchProblemTime=@SchProblemTime " & _
                                "where IDInject=@IDInject"

                .Parameters.Clear()
                da.SelectCommand = cmd

                .Parameters.AddWithValue("@IDInject", txtIDInjectProblem.Text)
                .Parameters.AddWithValue("@SchID", cboSchType.Text)
                .Parameters.AddWithValue("@SchDetail", cboSchDetail.Text)
                .Parameters.AddWithValue("@SchProblemTime ", txtProblemTime.Text)

            End With
            If cmd.ExecuteNonQuery = 1 Then
                MessageBox.Show("บันทึกการเปลี่ยนแปลงแล้ว")
                btnEditProblem.Text = "แก้ไข"
                btnAddProblem.Enabled = True
                btnSaveEditProblem.Enabled = False
                btnDeleteProblem.Enabled = True

            Else
                MessageBox.Show("เกิดข้อผิดพลาดในการแก้ไขข้อมูลเนื่องจากคุณยังไม่ได้เลือกแถวที่ต้องการแก้ไข")
            End If
            btnAdd.Enabled = True
            ShowDetailSchedule()
        Catch exsql As SqlException
            MsgBox(exsql.Message, MsgBoxStyle.Exclamation)
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Exclamation)
        End Try
    End Sub

    Private Sub grdAddSchTime_CellClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles grdAddSchTime.CellClick
        txtIDInjectProblem.Text = grdAddSchTime.Rows.Item(e.RowIndex).Cells(0).Value
        cboSchType.Text = grdAddSchTime.Rows.Item(e.RowIndex).Cells(1).Value
        cboSchDetail.Text = grdAddSchTime.Rows.Item(e.RowIndex).Cells(2).Value
        txtProblemTime.Text = grdAddSchTime.Rows.Item(e.RowIndex).Cells(3).Value
    End Sub
End Class