文明分统计.vb

一个功能强大的学生公寓管理系统 界面非常友好 十分强大

Public Class 文明分统计
    Inherits System.Windows.Forms.Form

#Region " Windows 窗体设计器生成的代码 "

    Public Sub New()
        MyBase.New()

        '该调用是 Windows 窗体设计器所必需的。
        InitializeComponent()

        '在 InitializeComponent() 调用之后添加任何初始化

    End Sub

    '窗体重写 dispose 以清理组件列表。
    Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
        If disposing Then
            If Not (components Is Nothing) Then
                components.Dispose()
            End If
        End If
        MyBase.Dispose(disposing)
    End Sub

    'Windows 窗体设计器所必需的
    Private components As System.ComponentModel.IContainer

    '注意: 以下过程是 Windows 窗体设计器所必需的
    '可以使用 Windows 窗体设计器修改此过程。
    '不要使用代码编辑器修改它。
    Friend WithEvents OleDbSelectCommand1 As System.Data.OleDb.OleDbCommand
    Friend WithEvents OleDbInsertCommand1 As System.Data.OleDb.OleDbCommand
    Friend WithEvents OleDbUpdateCommand1 As System.Data.OleDb.OleDbCommand
    Friend WithEvents OleDbDeleteCommand1 As System.Data.OleDb.OleDbCommand
    Friend WithEvents OleDbCom As System.Data.OleDb.OleDbCommand
    Friend WithEvents OleDbDA As System.Data.OleDb.OleDbDataAdapter
    Friend WithEvents OleDbCon As System.Data.OleDb.OleDbConnection
    Friend WithEvents Yx1 As 学生公寓管理系统.yx
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
        Me.OleDbSelectCommand1 = New System.Data.OleDb.OleDbCommand
        Me.OleDbInsertCommand1 = New System.Data.OleDb.OleDbCommand
        Me.OleDbUpdateCommand1 = New System.Data.OleDb.OleDbCommand
        Me.OleDbDeleteCommand1 = New System.Data.OleDb.OleDbCommand
        Me.OleDbDA = New System.Data.OleDb.OleDbDataAdapter
        Me.OleDbCom = New System.Data.OleDb.OleDbCommand
        Me.OleDbCon = New System.Data.OleDb.OleDbConnection
        Me.Yx1 = New 学生公寓管理系统.yx
        CType(Me.Yx1, System.ComponentModel.ISupportInitialize).BeginInit()
        '
        'OleDbDA
        '
        Me.OleDbDA.DeleteCommand = Me.OleDbDeleteCommand1
        Me.OleDbDA.InsertCommand = Me.OleDbInsertCommand1
        Me.OleDbDA.SelectCommand = Me.OleDbSelectCommand1
        Me.OleDbDA.UpdateCommand = Me.OleDbUpdateCommand1
        '
        'Yx1
        '
        Me.Yx1.DataSetName = "yx"
        Me.Yx1.Locale = New System.Globalization.CultureInfo("zh-CN")
        '
        '文明分统计
        '
        Me.AutoScaleBaseSize = New System.Drawing.Size(6, 14)
        Me.ClientSize = New System.Drawing.Size(256, 157)
        Me.Name = "文明分统计"
        Me.Opacity = 0
        Me.Text = "文明分统计"
        CType(Me.Yx1, System.ComponentModel.ISupportInitialize).EndInit()

    End Sub

#End Region

    Private Sub 文明分统计_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim a As String
        Dim b, c, d, f As Double
        Dim abc(200) As String
        Dim s As String
        Dim i As Integer = 0

        Try
            OleDbCon.ConnectionString = "Provider=Microsoft.Jet.Oledb.4.0;Data Source=学生公寓管理系统.mdb"
            OleDbCon.Open()
            OleDbCom.Connection = OleDbCon
            OleDbCom.CommandType = CommandType.TableDirect
            OleDbCom.CommandText = "文明分统计"
            OleDbDA.SelectCommand = OleDbCom
            OleDbDA.Fill(Yx1)
            Yx1.Tables(0).TableName = "文明分统计"

            OleDbCom.CommandType = CommandType.Text

            '从住宿登记表中统计已住人数写入已住床位中


            Try
                Dim Conn As New System.Data.OleDb.OleDbConnection
                Dim Cmd As New System.Data.OleDb.OleDbCommand
                Dim DataReader As System.Data.OleDb.OleDbDataReader

                Conn.ConnectionString = "Provider=Microsoft.Jet.Oledb.4.0;Data Source=学生公寓管理系统.mdb"
                Conn.Open()
                Cmd.CommandText = "Select 学号 from 学生库"
                Cmd.Connection = Conn
                DataReader = Cmd.ExecuteReader

                While DataReader.Read  '学生库
                    abc(i) = DataReader.Item(0)
                    s = abc(i)
                    i += 1
                End While  '学生库
                DataReader.Close()
                Conn.Close()

                For i = 0 To 200
                    If abc(i) = "" Then
                        Exit For
                    End If
                    a = abc(i)
                    b = 0
                    c = 0
                    d = 100
                    f = 0

                    Try

                        Dim Conn2 As New System.Data.OleDb.OleDbConnection
                        Dim Cmd2 As New System.Data.OleDb.OleDbCommand
                        Dim DataReader2 As System.Data.OleDb.OleDbDataReader
                        Conn2.ConnectionString = "Provider=Microsoft.Jet.Oledb.4.0;Data Source=学生公寓管理系统.mdb"
                        Conn2.Open()

                        Cmd2.CommandText = "Select 学号,sum(扣分) from 个人纪律  where 学号=" & "'" & abc(i) & "'" & "group by 学号"
                        Cmd2.Connection = Conn2
                        DataReader2 = Cmd2.ExecuteReader
                        While DataReader2.Read()  '个人纪律
                            b += CDbl(DataReader2.Item(1))
                        End While  '个人纪律
                        DataReader2.Close()
                        Conn2.Close()
                    Catch ex As Exception

                    End Try



                    Try

                        Dim Conn3 As New System.Data.OleDb.OleDbConnection
                        Dim Cmd3 As New System.Data.OleDb.OleDbCommand
                        Dim DataReader3 As System.Data.OleDb.OleDbDataReader
                        Conn3.ConnectionString = "Provider=Microsoft.Jet.Oledb.4.0;Data Source=学生公寓管理系统.mdb"
                        Conn3.Open()

                        Cmd3.CommandText = "Select 学号,sum(扣分) from 晚归记录 where 学号=" & "'" & abc(i) & "'" & "group by 学号"
                        Cmd3.Connection = Conn3
                        DataReader3 = Cmd3.ExecuteReader
                        While DataReader3.Read()
                            b += CDbl(DataReader3.Item(1))
                        End While
                        DataReader3.Close()
                        Conn3.Close()
                    Catch ex As Exception

                    End Try



                    Try

                        Dim Conn4 As New System.Data.OleDb.OleDbConnection
                        Dim Cmd4 As New System.Data.OleDb.OleDbCommand
                        Dim DataReader4 As System.Data.OleDb.OleDbDataReader
                        Conn4.ConnectionString = "Provider=Microsoft.Jet.Oledb.4.0;Data Source=学生公寓管理系统.mdb"
                        Conn4.Open()

                        Cmd4.CommandText = "Select 学号,sum(加分) from 公益活动 where 学号=" & "'" & abc(i) & "'" & "group by 学号"
                        Cmd4.Connection = Conn4
                        DataReader4 = Cmd4.ExecuteReader
                        While DataReader4.Read()
                            c = CDbl(DataReader4.Item(1))
                        End While
                        DataReader4.Close()
                        Conn4.Close()
                    Catch ex As Exception

                    End Try


                    '把统计数据写入社区文明分表中()
                    f = d + c - b
                    OleDbCom.CommandText = "Insert Into 文明分统计 values(?,?,?,?,?)"
                    OleDbCom.Parameters.Clear()
                    OleDbCom.Parameters.Add("a", a)
                    OleDbCom.Parameters.Add("b", b)
                    OleDbCom.Parameters.Add("c", c)
                    OleDbCom.Parameters.Add("d", d)
                    OleDbCom.Parameters.Add("f", f)


                    OleDbDA.InsertCommand = OleDbCom
                    Dim DRow As DataRow
                    DRow = Yx1.Tables("文明分统计").NewRow
                    DRow.Item(0) = a
                    DRow.Item(1) = b
                    DRow.Item(2) = c
                    DRow.Item(3) = d
                    DRow.Item(4) = f

                    Yx1.Tables("文明分统计").Rows.Add(DRow)
                    OleDbDA.Update(Yx1, "文明分统计")
                    abc(i) = 0
                Next





            Catch ex As Exception

            End Try


        Catch ex As Exception

        End Try



        '析构连接对象,命令对象,数据对象
        OleDbCon.Dispose()
        OleDbCom.Dispose()
        OleDbDA.Dispose()
        Yx1.Dispose()

  
      
    End Sub

    Private Sub 文明分统计_Closing(ByVal sender As Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles MyBase.Closing

    End Sub
End Class