frmauallborrowlist.vb

The Management Information System of Library using .NET

        Me.Label6.TabIndex = 40
        Me.Label6.Text = "读者类别:"
        '
        'txtAu_id
        '
        Me.txtAu_id.BackColor = System.Drawing.SystemColors.Info
        Me.txtAu_id.BorderStyle = System.Windows.Forms.BorderStyle.FixedSingle
        Me.txtAu_id.Location = New System.Drawing.Point(88, 16)
        Me.txtAu_id.Name = "txtAu_id"
        Me.txtAu_id.Size = New System.Drawing.Size(128, 21)
        Me.txtAu_id.TabIndex = 0
        Me.txtAu_id.Text = ""
        '
        'Label5
        '
        Me.Label5.Location = New System.Drawing.Point(432, 40)
        Me.Label5.Name = "Label5"
        Me.Label5.Size = New System.Drawing.Size(64, 16)
        Me.Label5.TabIndex = 37
        Me.Label5.Text = "办证日期:"
        '
        'Label4
        '
        Me.Label4.Location = New System.Drawing.Point(432, 16)
        Me.Label4.Name = "Label4"
        Me.Label4.Size = New System.Drawing.Size(64, 16)
        Me.Label4.TabIndex = 36
        Me.Label4.Text = "读者地址:"
        '
        'Label3
        '
        Me.Label3.Location = New System.Drawing.Point(16, 40)
        Me.Label3.Name = "Label3"
        Me.Label3.Size = New System.Drawing.Size(64, 16)
        Me.Label3.TabIndex = 34
        Me.Label3.Text = "读者性别:"
        '
        'Label2
        '
        Me.Label2.Location = New System.Drawing.Point(224, 16)
        Me.Label2.Name = "Label2"
        Me.Label2.Size = New System.Drawing.Size(64, 16)
        Me.Label2.TabIndex = 32
        Me.Label2.Text = "读者姓名:"
        '
        'Label1
        '
        Me.Label1.ForeColor = System.Drawing.Color.Red
        Me.Label1.Location = New System.Drawing.Point(16, 16)
        Me.Label1.Name = "Label1"
        Me.Label1.Size = New System.Drawing.Size(64, 16)
        Me.Label1.TabIndex = 29
        Me.Label1.Text = "图书证号:"
        '
        'frmAuAllBorrowList
        '
        Me.AutoScaleBaseSize = New System.Drawing.Size(6, 14)
        Me.ClientSize = New System.Drawing.Size(714, 535)
        Me.Controls.Add(Me.GroupBox1)
        Me.Controls.Add(Me.dbgBorrowList)
        Me.Name = "frmAuAllBorrowList"
        Me.Text = "读者借阅情况查询"
        CType(Me.dbgBorrowList, System.ComponentModel.ISupportInitialize).EndInit()
        Me.GroupBox1.ResumeLayout(False)
        Me.ResumeLayout(False)

    End Sub

#End Region

    Private Sub txtAu_id_KeyPress(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtAu_id.KeyPress
        If e.KeyChar.Equals(Chr(13)) Then
            databind(sender.text)
            AuTextBind(sender.text)
            If Me.PubAu_id <> "" Then
                Me.setBorrowState(sender.text)
            End If
        End If
    End Sub

    Private Sub frmAuAllBorrowList_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load
        With Me.dbgBorrowList
            .Anchor = CType((((System.Windows.Forms.AnchorStyles.Top Or System.Windows.Forms.AnchorStyles.Bottom) _
                Or System.Windows.Forms.AnchorStyles.Left) _
                Or System.Windows.Forms.AnchorStyles.Right), System.Windows.Forms.AnchorStyles)
            .BackColor = System.Drawing.Color.Snow
            .BackgroundColor = System.Drawing.SystemColors.ActiveCaptionText
            .CaptionVisible = False
            .HeaderForeColor = System.Drawing.SystemColors.ControlText
            .ReadOnly = True
        End With
    End Sub

    '绑定数据表,显示某读者 所有的借书记录
    Function databind(ByVal strAu_id As String)
        Dim cnlib2004 As SqlConnection
        Dim cmdAuBorrowList As SqlCommand
        Dim daAuBorrowList As SqlDataAdapter
        Dim dsAuBorrowList As New DataSet

        cnlib2004 = New SqlConnection(cnstr)
        cmdAuBorrowList = New SqlCommand
        With cmdAuBorrowList
            .CommandType = CommandType.StoredProcedure
            .CommandText = "SelectAuAllBorrowList"
            .Connection = cnlib2004
        End With
        Dim mAu_id As New SqlParameter("@Au_id", SqlDbType.NVarChar, 50) '1
        cmdAuBorrowList.Parameters.Add(mAu_id)
        mAu_id.Value = strAu_id
        daAuBorrowList = New SqlDataAdapter(cmdAuBorrowList)

        Try
            daAuBorrowList.Fill(dsAuBorrowList)
            Me.dbgBorrowList.DataSource = dsAuBorrowList.Tables(0)
            '以下代码目的是获取服务器时间,使判断图书超期不以本机时间为准,而以服务器时间为准
            '代码可以避免时间的不一致性
            cmdAuBorrowList.CommandType = CommandType.Text
            cmdAuBorrowList.CommandText = "select getdate()"
            cnlib2004.Open()
            Dim currdate As DateTime
            currdate = cmdAuBorrowList.ExecuteScalar
            'MsgBox(currdate)
            '以下代码加亮显示已经超期的图书
            Dim row As DataRow
            With dsAuBorrowList.Tables(0)
                Dim n As Int16 = 0
                For Each row In .Rows
                    ' MsgBox(row.Item("归还日期").GetType.ToString)
                    If row.Item("应归还日期") < currdate And row.Item("归还日期") Is System.DBNull.Value Then
                        Me.dbgBorrowList.Select(n)
                    End If
                    n += 1
                Next
            End With
            '代码结束
        Catch ex As SqlException
            MessageBox.Show(ex.Message, "error", MessageBoxButtons.OK, MessageBoxIcon.Stop)
        Finally
            cnlib2004.Close()
        End Try
    End Function
    '显示某读者基本情况
    Function AuTextBind(ByVal strAu_id As String)
        Dim cnlib2004 As SqlConnection
        Dim cmdAuBorrowList As SqlCommand
        Dim drAuBorrowList As SqlDataReader

        cnlib2004 = New SqlConnection(cnstr)
        cmdAuBorrowList = New SqlCommand
        With cmdAuBorrowList
            .CommandType = CommandType.StoredProcedure
            .CommandText = "Select1Au"
            .Connection = cnlib2004
        End With
        Dim mAu_id As New SqlParameter("@Au_id", SqlDbType.NVarChar, 50) '1
        cmdAuBorrowList.Parameters.Add(mAu_id)
        mAu_id.Value = strAu_id
        Try
            cnlib2004.Open()
            drAuBorrowList = cmdAuBorrowList.ExecuteReader(CommandBehavior.SingleRow)
            With drAuBorrowList
                If .Read() Then
                    Me.txtAu_adddate.Text = CType(.Item("Au_adddate"), String)
                    Me.txtAu_adr.Text = .Item("Au_adr")
                    Me.txtAu_name.Text = .Item("Au_name")
                    Me.txtAu_sex.Text = .Item("Au_sex")
                    Me.txtAu_sort.Text = .Item("Au_sort")
                    Me.PubAu_id = strAu_id
                Else
                    MessageBox.Show("无此读者!", "信息", 0, MessageBoxIcon.Exclamation)
                    Me.PubAu_id = ""
                    clearAu()
                End If
            End With

        Catch ex As Exception
            MessageBox.Show(ex.Message, "error", MessageBoxButtons.OK, MessageBoxIcon.Stop)
        Finally
            cnlib2004.Close()
        End Try
    End Function

    '显示读者借书状态 最大借书册书,当前借书册数 等
    Function setBorrowState(ByVal strAu_id As String)
        Dim cnlib2004 As SqlConnection
        Dim cmd As SqlCommand
        Dim dr As SqlDataReader

        cnlib2004 = New SqlConnection(cnstr)
        cmd = New SqlCommand
        With cmd
            .CommandType = CommandType.StoredProcedure
            .CommandText = "SelectAuCurrentBorrowNo"
            .Connection = cnlib2004
        End With
        Dim mAu_id As New SqlParameter("@Au_id", SqlDbType.NVarChar, 50) '1
        cmd.Parameters.Add(mAu_id)
        mAu_id.Value = strAu_id

        Try
            cnlib2004.Open()
            CurBorrowNo = CType(cmd.ExecuteScalar, Int16)

            cmd.CommandText = "SelectAuAllBorrowNo"
            AllborrowNo = CType(cmd.ExecuteScalar, Int16)

            cmd.CommandText = "SelectAuSortInfo"
            dr = cmd.ExecuteReader(CommandBehavior.SingleRow)

            While dr.Read()
                BorrowDays = dr.Item("Au_borrowdays")
                BorrowBooks = dr.Item("Au_borrowbooks")
            End While

            Me.txtTotalBorrowNo.Text = "共借书" + AllborrowNo.ToString + "册"
            Me.txtMaxBorrowDays.Text = BorrowDays.ToString + "天"

            Me.txtBorrowQinkuang.Text = "[" + CurBorrowNo.ToString + "/"
            Me.txtBorrowQinkuang.Text += BorrowBooks.ToString + "]"
        Catch ex As Exception
            MessageBox.Show(ex.Message, "error", MessageBoxButtons.OK, MessageBoxIcon.Stop)
        Finally
            cnlib2004.Close()
        End Try
    End Function
    Sub clearAu()
        Me.txtAu_adddate.Text = ""
        Me.txtAu_adr.Text = ""
        Me.txtAu_name.Text = ""
        Me.txtAu_sex.Text = ""
        Me.txtAu_sort.Text = ""
        Me.PubAu_id = ""

        Me.txtTotalBorrowNo.Clear()
        Me.txtBorrowQinkuang.Clear()
        Me.txtMaxBorrowDays.Clear()
    End Sub
End Class