Imports System.Data.SqlClient

Public Class frmReturn
    Public ReturnID As Integer

    Dim State As FormState
    Dim conn As New SqlClient.SqlConnection(My.Settings.LibSysConnectionString)

    Public WriteOnly Property getFormState()
        Set(ByVal value)
            State = value
        End Set
    End Property

    Private Sub ReturnBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ReturnBindingNavigatorSaveItem.Click
        For Each row As DataGridViewRow In Me.ReturnDetailsDataGridView.Rows
            If row.Cells(Me.Returned.Index).FormattedValue = False Then
                ReturnDetailsDataGridView.Rows.RemoveAt(row.Index)
            End If
        Next row

        Me.Validate()
        Me.ReturnBindingSource.EndEdit()
        Me.TableAdapterManager.UpdateAll(Me.ReturnDataSet)

        conn.Open()

        For Each row As DataGridViewRow In Me.ReturnDetailsDataGridView.Rows
            Dim intBookID As Integer
            Dim strSQL As String

            If row.Cells(Me.Returned.Index).FormattedValue = True Then
                intBookID = row.Cells(Me.BookID.Index).Value

                strSQL = "UPDATE BorrowDetails " & _
                        "SET BorrowDetails.Returned = 'True' " & _
                        "FROM BorrowDetails " & _
                        "INNER JOIN Borrow ON Borrow.BorrowID = BorrowDetails.BorrowID " & _
                        "WHERE BookID = " & intBookID & " AND MemberID = " & MemberIDComboBox.SelectedValue

                Dim cmd As SqlCommand = New SqlCommand(strSQL, conn)

                cmd.ExecuteNonQuery()
            End If
        Next row

        conn.Close()

        BindingNavigatorAddNewItem.PerformClick()
        Me.txtSearchMemberID.Text = ""
        Me.txtSearchMemberID.Focus()

        MessageBox.Show("Record successfully saved.", "Saving", MessageBoxButtons.OK, MessageBoxIcon.Information)
    End Sub

    Private Sub frmReturn_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Me.BooksTableAdapter.Fill(Me.BooksDataSet.Books)
        Me.MembersTableAdapter.Fill(Me.MembersDataSet.Members)
        Me.ReturnDetailsTableAdapter.Fill(Me.ReturnDataSet.ReturnDetails)
        Me.ReturnTableAdapter.Fill(Me.ReturnDataSet._Return)

        If State = FormState.adStateAddMode Then
            BindingNavigatorAddNewItem.PerformClick()
        End If
    End Sub

    Private Sub txtSearchMemberID_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtSearchMemberID.KeyPress
        If e.KeyChar = Microsoft.VisualBasic.ChrW(Keys.Return) Then
            Try
                If Me.txtSearchMemberID.Text = "" Then Exit Sub

                conn.Open()

                Dim sqlQRY As String = "SELECT * FROM qry_Borrow WHERE MemberID = " & Me.txtSearchMemberID.Text

                Dim cmd As SqlCommand = New SqlCommand(sqlQRY, conn)

                Dim rdr As SqlDataReader = cmd.ExecuteReader

                ReturnDataSet.ReturnDetails.Clear()

                While rdr.Read()
                    MemberIDComboBox.SelectedValue = rdr("MemberID")

                    Dim intBookID As Integer = rdr("BookID")

                    Dim newRow As ReturnDataSet.ReturnDetailsRow = ReturnDataSet.ReturnDetails.NewReturnDetailsRow

                    newRow.ReturnID = ReturnIDTextBox.Text
                    newRow.BookID = intBookID

                    ReturnDataSet.ReturnDetails.Rows.Add(newRow)
                End While

            Catch ex As Exception
                MessageBox.Show(ex.Message, "Error!!!", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Finally
                conn.Close()
            End Try
        End If

    End Sub

    Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
        Me.ReturnDetailsBindingSource.EndEdit()

        ReturnBindingNavigatorSaveItem_Click(sender, e)
    End Sub
End Class