frmaddbook.vb

The Management Information System of Library using .NET

        Try
            cnLib2004.Open()
            drBooksort = cmdbooksort.ExecuteReader
            Dim myarr As New ArrayList
            While drBooksort.Read
                myarr.Add(drBooksort.Item(0))
            End While
            Me.cboBook_sort.DataSource = myarr


        Catch ex As Exception
            MessageBox.Show(ex.Message)
            Me.Close()
        Finally
            cnLib2004.Close()
        End Try
    End Function
    Function BindComboBox2()
        Dim cnLib2004 As SqlConnection
        Dim cmdPlace As SqlCommand
        Dim drPlace As SqlDataReader

        cnLib2004 = New SqlConnection(cnstr)
        cmdPlace = New SqlCommand
        With cmdPlace
            .CommandType = CommandType.StoredProcedure
            .Connection = cnLib2004
            .CommandText = "selectPlace"
        End With

        Try
            cnLib2004.Open()
            drPlace = cmdPlace.ExecuteReader
            Dim myarr As New ArrayList
            With drPlace
                While .Read
                    myarr.Add(.Item(0))
                End While
            End With
            Me.cboBook_place.DataSource = myarr
        Catch ex As Exception
            MessageBox.Show(ex.Message)
            Me.Close()
        Finally
            cnLib2004.Close()
        End Try
    End Function
    Function Fillfield(ByVal currInt As Integer)
        Dim tableBook As DataTable
        tableBook = CType(dbgBook.DataSource, DataTable)
        With tableBook.Rows(currInt)
            Me.txtBook_id.Text = .Item(0)
            Me.txtBook_code.Text = .Item(1)
            Me.txtBook_name.Text = .Item(2)
            Me.txtBook_pub.Text = .Item(3)
            Me.txtBook_isbn.Text = .Item(4)
            Me.dtpBook_pubdate.Value = .Item(5)
            Me.txtBook_author.Text = .Item(6)
            Me.txtBook_page.Text = CType(.Item(7), String)
            Me.txtBook_price.Text = CType(.Item(8), String)
            Me.dtpBook_adddate.Value = .Item(9)
            Me.cboBook_place.Text = .Item(10)
            Me.cboBook_sort.Text = .Item(11)
            Me.txtBook_remarks.Text = .Item(12)
        End With
    End Function
    Function LocateRecord(ByVal columnIndex As Int16, ByVal keyWord As String) As Boolean
        Dim tableAu As DataTable
        tableAu = CType(dbgBook.DataSource, DataTable)
        Dim mycount As Int16
        With tableAu
            For mycount = 0 To .Rows.Count - 1
                If .Rows(mycount).Item(columnIndex) = keyWord Then
                    With .Rows(mycount)
                        Me.txtBook_id.Text = .Item(0)
                        Me.txtBook_code.Text = .Item(1)
                        Me.txtBook_name.Text = .Item(2)
                        Me.txtBook_pub.Text = .Item(3)
                        Me.txtBook_isbn.Text = .Item(4)
                        Me.dtpBook_pubdate.Value = .Item(5)
                        Me.txtBook_author.Text = .Item(6)
                        Me.txtBook_page.Text = CType(.Item(7), String)
                        Me.txtBook_price.Text = CType(.Item(8), String)
                        Me.dtpBook_adddate.Value = .Item(9)
                        Me.cboBook_place.Text = .Item(10)
                        Me.cboBook_sort.Text = .Item(11)
                        Me.txtBook_remarks.Text = .Item(12)
                    End With
                    'MessageBox.Show("定位成功!", "信息", _
                    '             MessageBoxButtons.OK, MessageBoxIcon.Information)
                    Me.btnDelete1Book.Enabled = True
                    Me.btnEditBook.Enabled = True
                    Me.btnAdd.Enabled = False
                    Me.txtBook_code.Focus()
                    Return True
                    Exit For
                End If
            Next
        End With
        MessageBox.Show("没有这本书!", "信息", _
                  MessageBoxButtons.OK, MessageBoxIcon.Error)
        Return False
    End Function
#End Region
    Private Sub FrmAddBook_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Me.SuspendLayout()
        Dim frmStatusMessage As New frmStatus
        frmStatusMessage.Show("正在连接数据服务器...")
        dbgbind()
        'BindComboBox1()
        'BindComboBox2()
        setEmpty()
        Me.TextBox1.Text = "  双击每行的开头可以进行定位" + vbCrLf + "    单击列标题可以进行排序"
        frmStatusMessage.Close()
        Me.ResumeLayout()
    End Sub

    Private Sub dbgBook_DoubleClick(ByVal sender As Object, ByVal e As System.EventArgs) Handles dbgBook.DoubleClick
        Dim str As String = CType(sender.Item(sender.CurrentRowIndex, 1), String)
        LocateRecord(1, str)
    End Sub

    Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
        Dim cnlib2004 As SqlConnection
        Dim cmdbook As SqlCommand

        cnlib2004 = New SqlConnection
        cnlib2004.ConnectionString = cnstr
        cmdbook = New SqlCommand

        With cmdbook
            .CommandType = CommandType.StoredProcedure
            .Connection = cnlib2004
            .CommandText = "InsertInBook"
        End With

        Dim mBook_code As New SqlParameter("@Book_code", SqlDbType.NVarChar, 50) '1
        Dim mBook_name As New SqlParameter("@Book_name", SqlDbType.NVarChar, 50) '2
        Dim mBook_pub As New SqlParameter("@Book_pub", SqlDbType.NVarChar, 50) '3
        Dim mBook_isbn As New SqlParameter("@Book_isbn", SqlDbType.NVarChar, 50) '4
        Dim mBook_pubdate As New SqlParameter("@Book_pubdate", SqlDbType.SmallDateTime, 4) '5
        Dim mBook_author As New SqlParameter("@Book_author", SqlDbType.NVarChar, 50) '6
        Dim mBook_page As New SqlParameter("@Book_page", SqlDbType.Int, 4) '7
        Dim mBook_price As New SqlParameter("@Book_price", SqlDbType.Money, 8) '8
        Dim mBook_adddate As New SqlParameter("@Book_adddate", SqlDbType.SmallDateTime, 4) '9
        Dim mBook_place As New SqlParameter("@Book_place", SqlDbType.NVarChar, 50) '10
        Dim mBook_sort As New SqlParameter("@Book_sort", SqlDbType.NVarChar, 50) '11
        Dim mBook_remarks As New SqlParameter("@Book_remarks", SqlDbType.NVarChar, 50) '12
        With cmdbook.Parameters
            .Add(mBook_code)
            .Add(mBook_name)
            .Add(mBook_pub)
            .Add(mBook_isbn)
            .Add(mBook_pubdate)
            .Add(mBook_author)
            .Add(mBook_page)
            .Add(mBook_price)
            .Add(mBook_adddate)
            .Add(mBook_place)
            .Add(mBook_sort)
            .Add(mBook_remarks)
        End With
        '赋值
        mBook_code.Value = Me.txtBook_code.Text '1
        mBook_name.Value = Me.txtBook_name.Text '2
        mBook_pub.Value = Me.txtBook_pub.Text '3
        mBook_isbn.Value = Me.txtBook_isbn.Text '4 
        mBook_pubdate.Value = Me.dtpBook_pubdate.Value '5
        mBook_author.Value = Me.txtBook_author.Text  '6
        mBook_page.Value = Val(Me.txtBook_page.Text) '7
        mBook_price.Value = Val(Me.txtBook_price.Text) '8
        mBook_adddate.Value = Me.dtpBook_adddate.Value  '9
        mBook_place.Value = Me.cboBook_place.Text  '10
        mBook_sort.Value = Me.cboBook_sort.Text  '11
        mBook_remarks.Value = Me.txtBook_remarks.Text  '12

        Try
            cnlib2004.Open()
            cmdbook.ExecuteNonQuery()
            dbgbind()
            setEmpty()
            MessageBox.Show("添加成功", "Info", _
             MessageBoxButtons.OK, MessageBoxIcon.Information)
        Catch ex As Exception
            MessageBox.Show(ex.Message, "更新失败", _
            MessageBoxButtons.OK, MessageBoxIcon.Error)
        Finally
            cnlib2004.Close()
            cmdbook.Dispose()
        End Try

    End Sub



    Private Sub btnCreatBookcode_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCreatBookcode.Click
        Dim creatcode As String = System.DateTime.Now.ToUniversalTime
        creatcode = creatcode.Replace("-", "X")
        creatcode = creatcode.Replace(" ", "")
        creatcode = creatcode.Replace(":", "")
        Me.txtBook_code.Text = creatcode

    End Sub

    Private Sub btnDelete1Book_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete1Book.Click

        Dim cnlib2004 As SqlConnection
        Dim cmdbook As SqlCommand

        cnlib2004 = New SqlConnection
        cnlib2004.ConnectionString = cnstr
        cmdbook = New SqlCommand

        With cmdbook
            .CommandType = CommandType.StoredProcedure
            .Connection = cnlib2004
            .CommandText = "Delete1Book"
        End With
        Dim mBook_code As New SqlParameter("@Book_code", SqlDbType.NVarChar, 50) '1
        cmdbook.Parameters.Add(mBook_code)
        mBook_code.Value = Me.txtBook_code.Text '1

        Try
            cnlib2004.Open()
            cmdbook.ExecuteNonQuery()
            dbgbind()
            MessageBox.Show("删除成功!", "信息", _
            MessageBoxButtons.OK, MessageBoxIcon.Information)
            setEmpty()
        Catch ex As Exception
            MessageBox.Show(ex.Message, "更新失败", _
            MessageBoxButtons.OK, MessageBoxIcon.Error)
        Finally
            cnlib2004.Close()
            cmdbook.Dispose()
        End Try

    End Sub

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

    Private Sub btnEditBook_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnEditBook.Click
        Dim cnlib2004 As SqlConnection
        Dim cmdbook As SqlCommand

        cnlib2004 = New SqlConnection
        cnlib2004.ConnectionString = cnstr

        cmdbook = New SqlCommand

        With cmdbook
            .CommandType = CommandType.StoredProcedure
            .Connection = cnlib2004
            .CommandText = "UpdateBook"
        End With
        Dim mBook_id As New SqlParameter("@Book_id", SqlDbType.Int, 4) '0
        Dim mBook_code As New SqlParameter("@Book_code", SqlDbType.NVarChar, 50) '1
        Dim mBook_name As New SqlParameter("@Book_name", SqlDbType.NVarChar, 50) '2
        Dim mBook_pub As New SqlParameter("@Book_pub", SqlDbType.NVarChar, 50) '3
        Dim mBook_isbn As New SqlParameter("@Book_isbn", SqlDbType.NVarChar, 50) '4
        Dim mBook_pubdate As New SqlParameter("@Book_pubdate", SqlDbType.SmallDateTime, 4) '5
        Dim mBook_author As New SqlParameter("@Book_author", SqlDbType.NVarChar, 50) '6
        Dim mBook_page As New SqlParameter("@Book_page", SqlDbType.Int, 4) '7
        Dim mBook_price As New SqlParameter("@Book_price", SqlDbType.Money, 8) '8
        Dim mBook_adddate As New SqlParameter("@Book_adddate", SqlDbType.SmallDateTime, 4) '9
        Dim mBook_place As New SqlParameter("@Book_place", SqlDbType.NVarChar, 50) '10
        Dim mBook_sort As New SqlParameter("@Book_sort", SqlDbType.NVarChar, 50) '11
        Dim mBook_remarks As New SqlParameter("@Book_remarks", SqlDbType.NVarChar, 50) '12
        With cmdbook.Parameters
            .Add(mBook_id)
            .Add(mBook_code)
            .Add(mBook_name)
            .Add(mBook_pub)
            .Add(mBook_isbn)
            .Add(mBook_pubdate)
            .Add(mBook_author)
            .Add(mBook_page)
            .Add(mBook_price)
            .Add(mBook_adddate)
            .Add(mBook_place)
            .Add(mBook_sort)
            .Add(mBook_remarks)
        End With
        '赋值
        mBook_id.Value = CType(Me.txtBook_id.Text, Int32)
        mBook_code.Value = Me.txtBook_code.Text '1
        mBook_name.Value = Me.txtBook_name.Text '2
        mBook_pub.Value = Me.txtBook_pub.Text '3
        mBook_isbn.Value = Me.txtBook_isbn.Text '4 
        mBook_pubdate.Value = Me.dtpBook_pubdate.Value '5
        mBook_author.Value = Me.txtBook_author.Text  '6
        mBook_page.Value = Val(Me.txtBook_page.Text) '7
        mBook_price.Value = Val(Me.txtBook_price.Text) '8
        mBook_adddate.Value = Me.dtpBook_adddate.Value  '9
        mBook_place.Value = Me.cboBook_place.Text  '10
        mBook_sort.Value = Me.cboBook_sort.Text  '11
        mBook_remarks.Value = Me.txtBook_remarks.Text  '12

        Try
            cnlib2004.Open()
            cmdbook.ExecuteNonQuery()
            dbgbind()
            MessageBox.Show("修改成功", "信息", _
            MessageBoxButtons.OK, MessageBoxIcon.Information)
            'setEmpty()
        Catch ex As Exception
            MessageBox.Show(ex.Message, "更新失败", _
            MessageBoxButtons.OK, MessageBoxIcon.Error)
        Finally
            cnlib2004.Close()
            cmdbook.Dispose()
        End Try
    End Sub

    Private Sub btnReset_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnReset.Click
        setEmpty()
    End Sub

    'Private Sub txtBookcodeFind_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtBookcodeFind.KeyPress
    '    If e.KeyChar.Equals(Chr(13)) Then
    '        Me.LocateRecord(1, sender.text.trim)
    '    End If

    'End Sub
    'Private Sub txtBook_idFind_KeyPress(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtBook_idFind.KeyPress
    '    If e.KeyChar.Equals(Chr(13)) Then
    '        Me.LocateRecord(0, sender.text.trim)
    '    End If
    'End Sub

    Private Sub txtBook_idFind_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles txtBook_idFind.KeyDown
        If e.KeyCode = Keys.Enter Then
            Me.LocateRecord(0, sender.text.trim)
        End If
    End Sub

    Private Sub txtBook_code_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles txtBook_code.KeyDown
        If e.KeyCode = Keys.Enter Then
            Me.LocateRecord(1, sender.text.trim)
        End If
    End Sub

End Class