การสร้างฟอร์มด้วย DataGridView เบื้องต้นแบบ Step by Step [ VB.NET (Windows App+SQL Server) ]

Public Class TestDataGrid

    Private Sub MExpenseBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MExpenseBindingNavigatorSaveItem.Click
        Me.Validate()
        Me.bsMExpense.EndEdit()
        Me.TableAdapterManager.UpdateAll(Me.dsMain) 

    End Sub

    Private Sub TestDataGrid_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'TODO: This line of code loads data into the 'MyDataSet.MExpense' table. You can move, or remove it, as needed.
        Me.ExpTypeTableAdapter.Fill(Me.dsMain.ExpType)

        'TODO: This line of code loads data into the 'MyDataSet.MExpense' table. You can move, or remove it, as needed.
        Me.taMExpense.Fill(Me.dsMain.MExpense)

    End Sub
End Class

Public Class TestDataGrid

    Private Sub MExpenseBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MExpenseBindingNavigatorSaveItem.Click
        Me.Validate()
        Me.bsMExpense.EndEdit()
        '---------------------------------------------
        ' ใส่ข้อมูลผู้ใช้งาน และวันที่แก้ไขตรงนี้
        '---------------------------------------------
        Dim stUser As String
        Dim SysDate As Date = Date.Now
        stUser = My.User.Name
        stUser = "CurrentUser"    ' ของจริงบรรทัดนี้ไม่ต้องใส่
        For Each dr As DataRow In Me.dsMain.MExpense.Rows
            If (dr.RowState And 20) > 0 Then   ' เช็คกับ 20 ถ้าเป็นจริง แสดงว่าเป็นการเพิ่มหรือไม่ก็แก้ไข
                If dr.RowState = DataRowState.Added Then
                    dr("usrInsert") = stUser
                    dr("dtInsert") = SysDate
                End If
                dr("usrModify") = stUser
                dr("dtModify") = SysDate
            End If
        Next

        '---------------------------------------------
        ' แก้ไขตรงนี้ 
        '---------------------------------------------
        ' Me.TableAdapterManager.UpdateAll(Me.dsMain) 
        Me.taMExpense.Update(Me.dsMain.MExpense) 
    End Sub

    Private Sub TestDataGrid_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        ' เนื่องจากใน Combo ExpTypeID ต้องการแสดงรายการที่ ExpTypeID>0 จึงต้องแก้ไขคำสั่ง
        ' Me.ExpTypeTableAdapter.Fill(Me.dsMain.ExpType)
        Dim SQL As String = "SELECT ExpTypeID, ExpTypeName FROM ExpType WHERE ExpTypeID>0"
        Dim da As New SqlClient.SqlDataAdapter(SQL, My.Settings.BudgetConnectionString)
        da.Fill(Me.dsMain.ExpType)

        'TODO: This line of code loads data into the 'MyDataSet.MExpense' table. You can move, or remove it, as needed.
        Me.taMExpense.Fill(Me.dsMain.MExpense)

    End Sub
End Class

Private Sub BtnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnSave.Click
        connection = New SqlConnection(conStr)
        If connection.State = ConnectionState.Closed Then
            connection.Open()
        End If
        BtnSearch.Enabled = False
        BtnSave.Enabled = False
        BtnExit.Enabled = True

        Dim CusID As String = txtID.Text
        Dim processID As String = Process_ID.Text
        Dim POID As String = PO_ID.Text
        Dim Remarks As String = Remark.Text


        Dim MSG As String = ""
        If status = "S" Then
            MSG = "คุณต้องการเพิ่มข้อมูล ใช่หรือไม่?"
        ElseIf status = "E" Then
            MSG = "คุณต้องการแก้ไขข้อมูล ใช่หรือไม่?"
        End If

        If MessageBox.Show(MSG, "คำยืนยัน", MessageBoxButtons.YesNo, MessageBoxIcon.Information) = Windows.Forms.DialogResult.Yes Then

            Select Case UCase(status)
                Case "S"
                    Dim sql As String = "INSERT INTO Process_Product(Process_ID,PO_ID,Cus_ID,Date_Process,Date_Receiving_Pro, Remark) values ('" & processID & "','" & POID & "','" & CusID & "','" & DateTimeProcess.Value.AddYears(-543).ToString("MM/dd/yyyy") & "','" & DateTimeReceProcess.Value.AddYears(-543).ToString("MM/dd/yyyy") & "','" & Remarks & "')"
                    Dim cmd2 As New SqlCommand(sql, connection)
                    Dim adapter As New SqlDataAdapter(cmd2)
                    Dim data As New DataSet()
                    cmd2.Parameters.Clear()
                    cmd2.ExecuteNonQuery()
                    Dim data3 As New DataSet()
                    Dim sql3 As String = "Insert into Process_Product_ Lists(Process_ID, Product_ID, Qty, Unit) values (@Process_ID, @Product_ID, @Qty, @Unit)"
                    'MessageBox.Show(sql3)
                    Dim cmd3 As New SqlCommand(sql3, connection)
                    For i As Integer = 0 To MyDataGridView1.RowCount - 2
                        With cmd3
                            .Parameters.AddWithValue("processID", (Process_ID.Text))
                            .Parameters.AddWithValue("Product_ID", MyDataGridView1.Rows(i).Cells(0).Value)
                            '.Parameters.AddWithValue("Price", MyDataGridView1.Rows(i).Cells(2).Value)
                            .Parameters.AddWithValue("Qty", CDbl(MyDataGridView1.Rows(i).Cells(2).Value))
                            .Parameters.AddWithValue("Unit", MyDataGridView1.Rows(i).Cells(3).Value)
                        End With

                        cmd3.ExecuteNonQuery()
                        cmd3.Parameters.Clear()
                    Next i
                    MessageBox.Show("บันทึกข้อมูลเรียบร้อยแล้ว", "ข้อความ", MessageBoxButtons.OK, MessageBoxIcon.None)
                    '    End If
                    'End If
                    clear()
                    MyDataGridView1.Columns.Clear()
                    MyDataGridView1.Rows.Clear()
                    MyDataGridView1.DataSource = Nothing
                    DataGrid1()
                    BtnSave.Enabled = False
                    BtnSearch.Enabled = False
                    BtnDelete.Enabled = False
                    MyDataGridView1.Enabled = False
                    Process_ID.Enabled = True
                    PO_ID.Enabled = False
                    locktext()
                Case "E"
                    'Dim sql As String = "Update Process_Product set Date_Process ='" & DateTimeProcess.Value.AddYears(-543).ToString("MM/dd/yyyy") & "',"
                    'sql &= "Cus_ID ='" & txtID.Text & "',"
                    ''sql &= "Amount1 ='" & Amount1.Text & "',"
                    ''sql &= "Discount ='" & Discount.Text & "',"
                    ''sql &= "SumDis ='" & SumDiscount.Text & "',"
                    ''sql &= "Amount2 ='" & Amount2.Text & "',"
                    ''sql &= "Vat ='" & Vat.Text & "',"
                    ''sql &= "SumVat ='" & SumVat.Text & "',"
                    ''sql &= "Total ='" & Total.Text & "',"
                    ''sql &= "Remark ='" & Remark.Text & "'WHERE Accounts_ID ='" & Account_ID.Text & "'"
                    'Dim cmd As New SqlCommand(sql, connection)
                    'Dim cmd1 As New SqlCommand(sql, connection)
                    ''For i As Integer = 0 To DataGridView2.RowCount - 2
                    'With cmd1
                    '    Dim sql2 As String = "Delete From Process_Product_ Lists Where Process_ID='" & Process_ID.Text & "'"
                    '    With cmd1
                    '        .CommandType = CommandType.Text
                    '        .CommandText = sql2
                    '        .Connection = connection
                    '        .ExecuteNonQuery()
                    '    End With
                    '    cmd1.ExecuteNonQuery()
                    '    cmd1.Parameters.Clear()
                    'End With
                    ''Next i
                    'Dim sql3 As String = "Insert into Process_Product_ Lists(Process_ID,Product_ID, Qty, Unit) values (@ProcessID,@Product_ID,@Qty, @Unit)"
                    'Dim cmd3 As New SqlCommand(sql3, connection)
                    'For i As Integer = 0 To MyDataGridView1.RowCount - 2
                    '    With cmd3
                    '        .Parameters.AddWithValue("Process_ID", (Process_ID.Text))
                    '        .Parameters.AddWithValue("Product_ID", MyDataGridView1.Rows(i).Cells(0).Value)
                    '        '.Parameters.AddWithValue("Price", MyDataGridView1.Rows(i).Cells(2).Value)
                    '        .Parameters.AddWithValue("Qty", CDbl(MyDataGridView1.Rows(i).Cells(2).Value))
                    '        .Parameters.AddWithValue("Unit", CDbl(MyDataGridView1.Rows(i).Cells(3).Value))
                    '    End With

                    '   cmd3.ExecuteNonQuery()
                    '    cmd3.Parameters.Clear()
                    'Next i
                    'With cmd
                    '    .CommandType = CommandType.Text
                    '    .CommandText = sql
                    '    .Connection = connection
                    '    .ExecuteNonQuery()
                    'End With
                    'cmd.ExecuteNonQuery()
                    'cmd.Parameters.Clear()
                    'MessageBox.Show("แก้ไขข้อมูลเรียบร้อยแล้ว", "ข้อความ", MessageBoxButtons.OK, MessageBoxIcon.None)
                    'clear()
                    'MyDataGridView1.Columns.Clear()
                    'MyDataGridView1.Rows.Clear()
                    'MyDataGridView1.DataSource = Nothing
                    'DataGrid1()
                    'BtnSave.Enabled = False
                    'BtnAdd.Enabled = True
                    'BtnSearch.Enabled = False
                    'BtnDelete.Enabled = False
                    'MyDataGridView1.Enabled = False
                    'PO_ID.Enabled = True
                    'Process_ID.Enabled = False
                    'locktext()
            End Select
        End If
    End Sub