frm_rentbook.vb

是关于图书管理系统的用vb.net 和access开发的

Imports System.Data.OleDb
Public Class frm_RentBook

    Dim myConnection As New OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0; Data source=D:\library.mdb")
    Dim myCommand As OleDbCommand
    Dim myReader As OleDbDataReader

    Dim period As Integer

    Dim FreezeStatus As Boolean = False

    Dim reserveCanRent As Boolean = False


    Private Sub frm_RentBook_Activated(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Activated
        readerNotxt.Focus()
    End Sub

    Private Sub readerNotxt_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles readerNotxt.KeyPress
        If e.KeyChar = ChrW(System.Windows.Forms.Keys.Enter) Then

            If checkValidReader() Then
                ISBNtxt.Focus()
          
            End If

            FreezeStatus = False
        End If
    End Sub

   Function checkValidReader() As Boolean

        Dim readerNo As String = ""
        Dim name As String = ""
        Dim alrBorr As String = ""
        Dim readerTypeCode As String = ""
        Dim status As String = ""


        myConnection.Open()
        myCommand = New OleDbCommand("select * from ReaderDetails where ReaderNo='" & readerNotxt.Text & "'", myConnection)
        myReader = myCommand.ExecuteReader()


        While myReader.Read

            readerNo = myReader("ReaderNo")
            name = myReader("Name")
            alrBorr = myReader("AlreadyBorrNo")
            readerTypeCode = myReader("ReaderTypeCode")
            status = myReader("status")

        End While

        myConnection.Close()
        myCommand.Dispose()
        myReader.Close()

        If readerNo = "" Then

            MsgBox("Invalid reader ID!")
            readerNotxt.Focus()
            readerNotxt.SelectAll()
            rentbt.Enabled = False
            Return False

        ElseIf status = "freeze" Then
            FreezeStatus = True
            MsgBox("The user is frozen now, can not be serviced!")
            rentbt.Enabled = False
            Return False
        Else
            readerNametxt.Text = name
            alreadyBorrNotxt.Text = alrBorr

            addMaxBorrandRewNo(readerTypeCode)
            setPeriod(readerTypeCode)

            rentbt.Enabled = True

            Return True

        End If
    End Function

    Sub addMaxBorrandRewNo(ByVal readerTypeCode)

        Dim maxBorr As String = ""
        Dim maxRew As String = ""

        myConnection.Open()
        myCommand = New OleDbCommand("select * from ReaderType where ReaderTypeCode='" & readerTypeCode & "'", myConnection)
        myReader = myCommand.ExecuteReader()

        While myReader.Read
            maxBorr = myReader("MaxBorrowNo")
            maxRew = myReader("MaxBorrowNo")

        End While

        myConnection.Close()
        myCommand.Dispose()
        myReader.Close()

        maxBorrNotxt.Text = maxBorr
        maxRewNotxt.Text = maxRew
    End Sub

    Private Sub isbntxt_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles ISBNtxt.KeyPress
        If e.KeyChar = ChrW(System.Windows.Forms.Keys.Enter) Then

            If checkValidBook() Then

                If alrInRentRec() Then

                    MsgBox("The reader has already borrowed this book!")
                    rentbt.Enabled = False

                ElseIf isInReserve() Then

                    If reserveCanRent = False Then

                        MsgBox("The book is already reserved by another one!")
                        rentbt.Enabled = False
                    Else
                        rentbt.Enabled = True
                        deleteReserve()

                        displayDate()
                        admNotxt.Focus()

                    End If

                    reserveCanRent = False
                Else

                    rentbt.Enabled = True

                    displayDate()
                    admNotxt.Focus()
                End If

            Else
                MsgBox("Invalid book ISBN!")
                ISBNtxt.Focus()
                ISBNtxt.SelectAll()
            End If
        End If

    End Sub
    Function isInReserve() As Boolean
        Dim isbn As String = ""
        Dim readerNo As String = ""

        myConnection.Open()
        myCommand = New OleDbCommand("select * from ReserveBook where ISBN='" & ISBNtxt.Text & "'", myConnection)
        myReader = myCommand.ExecuteReader()

        While myReader.Read
            isbn = myReader("ISBN")
            readerno = myReader("ReaderNo")
        End While

        myConnection.Close()
        myCommand.Dispose()
        myReader.Close()


        If isbn = "" Then
            Return False
        Else

            If ISBNtxt.Text = isbn And readerNotxt.Text = readerNo Then
                reserveCanRent = True
            End If

            Return True
        End If

    
    End Function
    Sub deleteReserve()

        myConnection.Open()

        myCommand = New OleDbCommand("Delete from ReserveBook where ISBN='" & ISBNtxt.Text & _
        "' and ReaderNo='" & readerNotxt.Text & "'", myConnection)

        myCommand.ExecuteNonQuery()

        myConnection.Close()
        myCommand.Dispose()
        myReader.Close()

    End Sub
  
    Function alrInRentRec()

        Dim temp As String = ""
        myConnection.Open()
        myCommand = New OleDbCommand("select * from RentBook where ISBN='" & ISBNtxt.Text & _
        "'and ReaderNo='" & readerNotxt.Text & "'", myConnection)
        myReader = myCommand.ExecuteReader()


        While myReader.Read
            temp = myReader("ISBN")
        End While

        myConnection.Close()
        myCommand.Dispose()
        myReader.Close()

        If temp = "" Then
            Return False
        Else

            Return True
        End If
    End Function



    Function checkValidBook() As Boolean

        Dim isbn As String = ""
        Dim name As String = ""
        Dim status As String = ""
        Dim author As String = ""
        Dim edition As String = ""

        myConnection.Open()
        myCommand = New OleDbCommand("select * from BookDetails where ISBN='" & ISBNtxt.Text & "'", myConnection)
        myReader = myCommand.ExecuteReader()


        While myReader.Read

            isbn = myReader("ISBN")
            name = myReader("BookName")
            status = myReader("Status")
            author = myReader("Author")
            edition = myReader("Edition")

        End While

        myConnection.Close()
        myCommand.Dispose()
        myReader.Close()

        If isbn = "" Then
            rentbt.Enabled = False
            Return False
        Else

            ISBNtxt.Text = isbn
            bookNametxt.Text = name
            statustxt.Text = status
            authortxt.Text = author
            editiontxt.Text = edition

            rentbt.Enabled = True

            Return True

        End If
    End Function
    Sub displayDate()
        Dim rentD As Date = Now
        Dim dueD As Date


        rentDatetxt.Text = rentD.ToShortDateString()


        dueD = rentD.AddDays(period)

        dueDatetxt.Text = dueD.ToShortDateString()


    End Sub
    Sub setPeriod(ByVal readerTypeCode)


        myConnection.Open()
        myCommand = New OleDbCommand("select * from ReaderType where ReaderTypeCode='" & readerTypeCode & "'", myConnection)
        myReader = myCommand.ExecuteReader()

        While myReader.Read
            period = Integer.Parse(myReader("BorrowPeriod"))
        End While

        myConnection.Close()
        myCommand.Dispose()
        myReader.Close()

    End Sub
   

  
    Private Sub rentbt_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles rentbt.Click
       
        Try
            If readerNotxt.Text = "" Or ISBNtxt.Text = "" Or admNotxt.Text = "" Or passwordtxt.Text = "" Then
                MsgBox("Information is not enough!")
            ElseIf checkValidAdm() Then
                updateRentBook()
                changeReaderBorrNo()
                changeBookDetailStatus()
            End If
        Catch ex As Exception
            MsgBox("You already insert this record!")
            Exit Try
        End Try
    End Sub
    Sub changeBookDetailStatus()

        myConnection.Open()

        myCommand = New OleDbCommand("Update BookDetails set Status= 'OutLib' where ISBN='" & ISBNtxt.Text & "'", myConnection)

        myCommand.ExecuteNonQuery()

        myConnection.Close()
        myCommand.Dispose()
        myReader.Close()
    End Sub

    Function checkValidAdm() As Boolean

        Dim admNo As String = ""
        Dim password As String = ""

    
        myConnection.Open()
        myCommand = New OleDbCommand("select * from Administrator where AdmNo = '" & admNotxt.Text & _
           "'", myConnection)
        myReader = myCommand.ExecuteReader()

        While myReader.Read
            admNo = myReader("AdmNo")
            password = myReader("Password")
        End While

        myConnection.Close()
        myReader.Close()
        myCommand.Dispose()

        If admNo = "" Then
            MsgBox("Invalid Administrator ID!")
        Else

            If password = passwordtxt.Text Then
                Return True
            Else

                MsgBox("Invalid Administrator Password!")
                Return False

            End If
        End If
    End Function
    Sub updateRentBook()
        If Integer.Parse(alreadyBorrNotxt.Text) >= Integer.Parse(maxBorrNotxt.Text) Then
            MsgBox("Can not borrow any more book!")
            rentbt.Enabled = False
        Else

            myConnection.Open()

            myCommand = New OleDbCommand("INSERT INTO RentBook VALUES( " & _
         "'" & ISBNtxt.Text & "','" & readerNotxt.Text & "','" & rentDatetxt.Text & "','" & admNotxt.Text & "','" & _
            dueDatetxt.Text & "'," & Integer.Parse("0") & ", 'Nreturned')", myConnection)

            myCommand.ExecuteNonQuery()


            myConnection.Close()
            myCommand.Dispose()
            myReader.Close()

            MsgBox("The rent record is added successfully!")
        End If
    End Sub
    Sub changeReaderBorrNo()

        myConnection.Open()

        myCommand = New OleDbCommand("Update ReaderDetails set AlreadyBorrNo= " & (Integer.Parse(alreadyBorrNotxt.Text) + 1) & " where ReaderNo='" & _
          readerNotxt.Text & "'", myConnection)

        myCommand.ExecuteNonQuery()

        myConnection.Close()
        myCommand.Dispose()
        myReader.Close()

    End Sub

    Private Sub exitbt_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles exitbt.Click
        Me.Close()
    End Sub

    Private Sub resetbt_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles resetbt.Click
        readerNotxt.Clear()
        readerNametxt.Clear()
        maxBorrNotxt.Clear()
        alreadyBorrNotxt.Clear()
        maxRewNotxt.Clear()

        ISBNtxt.Clear()
        bookNametxt.Clear()
        statustxt.Clear()
        authortxt.Clear()
        editiontxt.Clear()


        rentDatetxt.Clear()
        dueDatetxt.Clear()
        admNotxt.Clear()
        passwordtxt.Clear()

        readerNotxt.Focus()
        rentbt.Enabled = True
    End Sub
End Class