frm_overdue.vb

图书管理系统 VB加SQL 数据库在文件里的DATA里 进到SQL里附加进去就可以使用了

        'Button9
        '
        Me.Button9.Location = New System.Drawing.Point(8, 152)
        Me.Button9.Name = "Button9"
        Me.Button9.Size = New System.Drawing.Size(72, 24)
        Me.Button9.TabIndex = 0
        Me.Button9.Text = "尾记录"
        '
        'Frm_Overdue
        '
        Me.AutoScaleBaseSize = New System.Drawing.Size(6, 14)
        Me.ClientSize = New System.Drawing.Size(616, 422)
        Me.Controls.Add(Me.GroupBox2)
        Me.Controls.Add(Me.DataGrid1)
        Me.Controls.Add(Me.GroupBox1)
        Me.Name = "Frm_Overdue"
        Me.Text = "逾期管理"
        Me.GroupBox1.ResumeLayout(False)
        CType(Me.DataGrid1, System.ComponentModel.ISupportInitialize).EndInit()
        Me.GroupBox2.ResumeLayout(False)
        Me.ResumeLayout(False)

    End Sub

#End Region

    Private db As New DataBase
    Public bmData As BindingManagerBase
    
    '加载
    Private Sub Frm_Overdue_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        jfk()
        Dim ds As New DataSet
        ds = db.RunSelectDataSet("select * from 借阅总表 where 应还日期 < '" & System.DateTime.Now.Today & "'")
        '定义一个DataGrid表的样式
        Dim MyTable As New DataGridTableStyle
        '设置DataGrid1中奇数行的背景颜色
        MyTable.AlternatingBackColor = Color.LightGray
        '不允许单击列标头对网格进行重新排序
        MyTable.AllowSorting = False
        '将表样式MyTable添加到DataGrid1中
        DataGrid1.TableStyles.Add(MyTable)
        MyTable.MappingName = ds.Tables(0).TableName
        DataGrid1.DataSource = ds.Tables(0)
        bmData = Me.BindingContext(ds.Tables(0))

    End Sub
    '首记录
    Private Sub Button6_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button6.Click
        If bmData.Position <> 0 Then
            bmData.Position = 0
        End If
    End Sub
    '上一条
    Private Sub Button7_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button7.Click
        If bmData.Position <> 0 Then
            bmData.Position -= 1
        End If
    End Sub
    '下一条
    Private Sub Button8_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button8.Click
        If bmData.Position <> bmData.Count - 1 Then
            bmData.Position += 1
        End If
    End Sub
    '尾记录
    Private Sub Button9_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button9.Click
        If bmData.Position <> bmData.Count - 1 Then
            bmData.Position = bmData.Count - 1
        End If
    End Sub

    '查询
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        If ComboBox1.Text = "" Then
            MessageBox.Show("请选择要查询的字段！", "提示", MessageBoxButtons.OK)
            Exit Sub
        End If
        If TextBox1.Text = "" Then
            MessageBox.Show("请输入要查询的数值！", "提示", MessageBoxButtons.OK)
            Exit Sub
        End If
        Dim sqlstr1 As String
        sqlstr1 = "select * from 借阅总表 where " & ComboBox1.Text.Trim & " like '%" & TextBox1.Text.Trim & "%'" & " and 应还日期 < '" & System.DateTime.Now.Today & "'"
        Try
            Dim dv As New DataView
            dv = db.RunSelectSQL(sqlstr1)
            DataGrid1.DataSource = dv
        Catch ex As Exception
            MessageBox.Show("无此记录！", "提示", MessageBoxButtons.OK)
        End Try

    End Sub
    '罚款+归还
    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        If MessageBox.Show("确认已罚款并且归还图书吗？", "提示", MessageBoxButtons.OKCancel) = DialogResult.OK Then

        Else
            Exit Sub
        End If
        Dim rid As Integer = DataGrid1.CurrentRowIndex
        '定义一个变量保存学号
        Dim xh As String = DataGrid1.Item(rid, 1)
        '定义一个变量保存书号
        Dim sh As String = DataGrid1.Item(rid, 2)
        Dim sqlstr As String
        sqlstr = "select 库存,已借出 from 书籍总表 where 书号='" & sh & "'"
        Dim dv As New DataView
        dv = db.RunSelectSQL(sqlstr)
        '定义一个变量保存库存
        Dim kc As Integer
        '定义一个变量保存已借出
        Dim yjc As Integer
        kc = dv.Item(0)("库存")
        yjc = dv.Item(0)("已借出")

        Dim sqlstr4 As String
        sqlstr4 = "Insert into 归还总表(学号,归还书号,归还日期) values('" & xh & "','" & sh & "','" & System.DateTime.Today & "')"
        Try
            db.RunDelOrInsSQL(sqlstr4)
            Dim sqlstr5 As String
            sqlstr5 = "update 书籍总表 set 库存='" & kc + 1 & "',已借出='" & yjc - 1 & "' where 书号='" & sh & "'"
            db.RunDelOrInsSQL(sqlstr5)
            Dim sqlstr6 As String
            sqlstr6 = "delete from 借阅总表 where 学号='" & xh & "' and 所借书号='" & sh & "'"
            db.RunDelOrInsSQL(sqlstr6)
            MessageBox.Show("归还操作成功！", "提示", MessageBoxButtons.OK)
            Exit Sub
        Catch ex As Exception
            MessageBox.Show("归还操作失败！", "提示", MessageBoxButtons.OK)
        End Try
    End Sub
    '罚款+续借
    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        If MessageBox.Show("确认已罚款并且续借图书吗？", "提示", MessageBoxButtons.OKCancel) = DialogResult.OK Then

        Else
            Exit Sub
        End If
        Try
            Dim rid As Integer = DataGrid1.CurrentRowIndex
            '定义一个变量保存ID
            Dim id As Integer = DataGrid1.Item(rid, 0)
            Dim sqlstr3 As String
            sqlstr3 = "update 借阅总表 set 应还日期 = '" & System.DateTime.Today.AddDays(30) & "',应罚款='0' where ID='" & id & "'"
            db.RunDelOrInsSQL(sqlstr3)
            MessageBox.Show("续借成功！", "提示", MessageBoxButtons.OK)
        Catch ex As Exception
            MessageBox.Show("续借失败！", "提示", MessageBoxButtons.OK)
            Exit Sub
        End Try

    End Sub
    '刷新
    Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
        Dim sqlstr4 As String
        sqlstr4 = "select * from 借阅总表 where 应还日期 < '" & System.DateTime.Now.Today & "'"
        Dim dv As New DataView
        dv = db.RunSelectSQL(sqlstr4)
        DataGrid1.DataSource = dv
    End Sub
    '退出
    Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click
        Me.Close()
    End Sub
    '定义一个函数获取费率
    Private Function fl() As Single
        Dim sqlstr1 As String
        Dim StrCon As String = "Data Source=" & compname & ";Integrated Security=SSPI;Initial Catalog=tushugl"
        Dim SqlConn As New SqlConnection(StrCon)
        sqlstr1 = "select 费率 from 费率总表"
        Dim cmd As New SqlCommand(sqlstr1, SqlConn)
        Dim dr As SqlDataReader
        SqlConn.Open()
        dr = cmd.ExecuteReader()
        Dim fl1 As Single
        While dr.Read()
            fl1 = dr("费率")
        End While
        fl = fl1
        dr.Close()
        SqlConn.Close()
    End Function
    '定义一个函数计算应罚款
    Private Sub jfk()
        Dim fl2 As Single
        fl2 = fl()

        Dim sqlstr2 As String
        sqlstr2 = "select * from 借阅总表 where 应还日期 < '" & System.DateTime.Now.Today & "'"
        Dim ds1 As New DataSet
        ds1 = db.RunSelectDataSet(sqlstr2)
        Dim dt As DateTime
        Dim i As Integer
        For i = 0 To ds1.Tables(0).Rows.Count - 1
            dt = ds1.Tables(0).Rows(i).Item("应还日期")
            Dim jg As Single
            jg = System.DateTime.Now.Today.Day - dt.Day
            If jg > 0 Then
                Dim sqlstr3 As String
                Dim jg1 As Single
                jg1 = jg * fl2
                sqlstr3 = "update 借阅总表 set 应罚款='" & jg1 & "' where 应还日期 = '" & dt & "'"
                db.RunDelOrInsSQL(sqlstr3)
            End If
        Next i
        ds1.Clear()
        ds1.Dispose()
    End Sub

End Class