books.vb

图书管理系统最新的

        Me.ControlBox = False
        Me.Controls.Add(Me.Label1)
        Me.Controls.Add(Me.GroupBox2)
        Me.Controls.Add(Me.btn_close)
        Me.Controls.Add(Me.GroupBox1)
        Me.Controls.Add(Me.dg_books)
        Me.ForeColor = System.Drawing.Color.Navy
        Me.MaximizeBox = False
        Me.MinimizeBox = False
        Me.Name = "books"
        Me.StartPosition = System.Windows.Forms.FormStartPosition.CenterScreen
        Me.Text = "Add/Remove Books"
        Me.WindowState = System.Windows.Forms.FormWindowState.Maximized
        CType(Me.dg_books, System.ComponentModel.ISupportInitialize).EndInit()
        Me.GroupBox1.ResumeLayout(False)
        Me.GroupBox2.ResumeLayout(False)
        Me.ResumeLayout(False)

    End Sub

#End Region

    Dim cn As New SqlConnection("server=localhost;database=lms;uid=sa;password=;")
    Dim da As SqlDataAdapter
    Dim ds As DataSet
    Dim cmd As SqlCommand
    Dim dv As DataView
    Dim ch As Char
    Dim i As Integer


    Private Sub books_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Try

            cn.Open() 'opening connection
            fillgrid() 'filling book data in grid

        Catch ex As SqlException
            MessageBox.Show(ex.Message, "Exception", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Me.Close()

        Finally
            GroupBox1.Focus()
            txt_bookid.Focus()

        End Try
    End Sub

    'method to fetch books info and display it in the grid
    Sub fillgrid()

        Try

            da = New SqlDataAdapter("select * from book_info", cn)
            ds = New DataSet
            da.Fill(ds, "book_info")
            dv = New DataView(ds.Tables(0))
            dv.Sort = "Book_id"

            dg_books.DataSource = dv


        Catch ex As SqlException
            MessageBox.Show(ex.Message, "Exception", MessageBoxButtons.OK, MessageBoxIcon.Error)

        Catch err As System.Exception
            MessageBox.Show(err.Message, "Exception", MessageBoxButtons.OK, MessageBoxIcon.Error)

        Finally

            'dereferencing objects
            da = Nothing
            ds = Nothing
            dv = Nothing

        End Try

    End Sub

    Private Sub btn_add_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_add.Click

        Try

            If txt_bookid.Text <> "" AndAlso txt_booktitle.Text <> "" _
    AndAlso txt_author.Text <> "" AndAlso txt_pub.Text <> "" _
   AndAlso txt_price.Text <> "" Then

                'inserting record
                cmd = New SqlCommand
                cmd.Connection = cn
                cmd.CommandText = "insert into book_info values('" & txt_bookid.Text & "','" & txt_booktitle.Text & "','" & txt_author.Text & "','" & txt_pub.Text & "'," & txt_price.Text & ")"
                cmd.ExecuteNonQuery()
                MessageBox.Show("Book record added successfully!!", "LMS", MessageBoxButtons.OK, MessageBoxIcon.Information)
                fillgrid()
                clearfields()

            Else 'one or more fields is blank

                MessageBox.Show("All fields are mandatory to fill.", "Exception", MessageBoxButtons.OK, MessageBoxIcon.Warning)
                txt_bookid.Focus()

            End If

        Catch ex As SqlException
            MessageBox.Show(ex.Message, "Exception", MessageBoxButtons.OK, MessageBoxIcon.Error)
            txt_bookid.Focus()

        Catch err As System.Exception
            MessageBox.Show(err.Message, "Exception", MessageBoxButtons.OK, MessageBoxIcon.Error)
            txt_bookid.Focus()

        Finally

            cmd = Nothing

        End Try

    End Sub
    Sub cleardatabind()

        txt_bookid.DataBindings.Clear()
        txt_booktitle.DataBindings.Clear()
        txt_author.DataBindings.Clear()
        txt_pub.DataBindings.Clear()
        txt_price.DataBindings.Clear()


    End Sub
    Sub clearfields()
        txt_bookid.Clear()
        txt_booktitle.Clear()
        txt_author.Clear()
        txt_pub.Clear()
        txt_price.Clear()
        txt_bookid.Focus()
    End Sub

    Sub clear_stud_labels()
        lbl_pname.Text = ""
        lbl_pyear.Text = ""
        lbl_pbranch.Text = ""
    End Sub

    Sub clear_stud_databind()
        lbl_pname.DataBindings.Clear()
        lbl_pyear.DataBindings.Clear()
        lbl_pbranch.DataBindings.Clear()
    End Sub


    Private Sub txt_bookid_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txt_bookid.KeyPress

        ch = e.KeyChar

        If ch.IsSymbol(ch) Or ch.IsPunctuation(ch) Then
            e.Handled = True
            MessageBox.Show("Symbols are not allowed.", "LMS", MessageBoxButtons.OK, MessageBoxIcon.Stop)
        End If


        If Asc(e.KeyChar) = Keys.Enter Then
            fill_on_keypress()
          
        End If

    End Sub

    Private Sub btn_remove_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_remove.Click

        Try

            If txt_bookid.Text <> "" AndAlso txt_booktitle.Text <> "" Then

                'deleting the record
                cmd = New SqlCommand
                cmd.Connection = cn
                cmd.CommandText = "delete from book_info where Book_id='" & txt_bookid.Text & "' and Book_id not in (select Book_Id from trans)"
                i = cmd.ExecuteNonQuery()

                If i > 0 Then
                    MessageBox.Show("Book record deleted successfully", "LMS", MessageBoxButtons.OK, MessageBoxIcon.Information)
                    fillgrid()
                    clearfields()
                Else
                    MessageBox.Show("Can not delete the book record because this book is issued.", "LMS", MessageBoxButtons.OK, MessageBoxIcon.Information)
                    txt_bookid.Focus()
                End If

            Else

                txt_bookid.Focus()

            End If


        Catch ex As SqlException
            MessageBox.Show(ex.Message, "Exception", MessageBoxButtons.OK, MessageBoxIcon.Error)

        Catch err As System.Exception
            MessageBox.Show(err.Message, "Exception", MessageBoxButtons.OK, MessageBoxIcon.Error)

        Finally

            cmd = Nothing

        End Try

    End Sub

    Sub get_from_grid()
        Try
            i = dg_books.CurrentRowIndex
            txt_bookid.Text = dg_books.Item(i, 0)
            txt_booktitle.Text = dg_books.Item(i, 1)
            txt_author.Text = dg_books.Item(i, 2)
            txt_pub.Text = dg_books.Item(i, 3)
            txt_price.Text = dg_books.Item(i, 4)

            get_student_info()

        Catch err As System.Exception

        End Try
    End Sub

    Private Sub dg_books_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles dg_books.Click
        get_from_grid()
    End Sub

    Private Sub dg_books_CurrentCellChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles dg_books.CurrentCellChanged
        get_from_grid()
    End Sub


    Sub get_student_info()

        Try
            clear_stud_databind()
            da = New SqlDataAdapter("select * from stud_info where S_Id=(select S_id from trans where Book_Id='" & txt_bookid.Text & "')", cn)
            ds = New DataSet
            da.Fill(ds, "stud_info")

            If ds.Tables(0).Rows.Count <> 0 Then

                dv = New DataView(ds.Tables(0))

                'binding data to the labels
                lbl_pname.DataBindings.Add("Text", dv, "Name")
                lbl_pyear.DataBindings.Add("Text", dv, "A_year")
                lbl_pbranch.DataBindings.Add("Text", dv, "Branch")

            Else

                clear_stud_labels()

            End If

        Catch ex As SqlException
            MessageBox.Show(ex.Message, "Exception", MessageBoxButtons.OK, MessageBoxIcon.Error)

        Catch err As System.Exception
            MessageBox.Show(err.Message, "Exception", MessageBoxButtons.OK, MessageBoxIcon.Error)

        Finally

            'dereferencing objects
            da = Nothing
            ds = Nothing
            dv = Nothing

        End Try


    End Sub


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

    Private Sub books_Closing(ByVal sender As Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles MyBase.Closing
        cn.Close()
    End Sub

    Private Sub txt_price_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txt_price.KeyPress

        ch = e.KeyChar

        If Not (ch.IsDigit(ch) Or Asc(e.KeyChar) = Keys.Back) Then
            e.Handled = True
        End If


    End Sub

    Sub fill_on_keypress()
        Try
            cleardatabind()

            da = New SqlDataAdapter("select * from book_info where Book_Id='" & txt_bookid.Text & "'", cn)
            ds = New DataSet
            da.Fill(ds, "book_info")

            If ds.Tables("book_info").Rows.Count <> 0 Then 'no rows selected

                dv = New DataView(ds.Tables(0))

                txt_booktitle.DataBindings.Add("Text", dv, "Title")
                txt_author.DataBindings.Add("Text", dv, "Author")
                txt_pub.DataBindings.Add("Text", dv, "Publication")
                txt_price.DataBindings.Add("Text", dv, "Price")

                'displaying student info
                get_student_info()
            Else

                txt_booktitle.Clear()
                txt_author.Clear()
                txt_pub.Clear()
                txt_price.Clear()
                clear_stud_labels()
                'txt_bookid.Focus()

            End If

        Catch ex As SqlException
            MessageBox.Show(ex.Message, "Exception", MessageBoxButtons.OK, MessageBoxIcon.Error)

        Catch err As System.Exception
            MessageBox.Show(err.Message, "Exception", MessageBoxButtons.OK, MessageBoxIcon.Error)

        Finally

            'dereferencing objects
            da = Nothing
            ds = Nothing
            dv = Nothing

        End Try

    End Sub


    Private Sub txt_bookid_LostFocus(ByVal sender As Object, ByVal e As System.EventArgs) Handles txt_bookid.LostFocus
        If txt_bookid.Text <> "" Then
            fill_on_keypress()
        End If
    End Sub

    Private Sub lbl_help_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles lbl_help.Click
        MessageBox.Show("You will have to delete the record to be updated and insert it again with the new information.", "LMS Help", MessageBoxButtons.OK, MessageBoxIcon.Information)
    End Sub
End Class