住宿统计.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 OleDbDA As System.Data.OleDb.OleDbDataAdapter
    Friend WithEvents OleDbCom As System.Data.OleDb.OleDbCommand
    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 OleDbCon As System.Data.OleDb.OleDbConnection
    Friend WithEvents Ds1 As 学生公寓管理系统.Ds
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
        '
        '住宿统计
        '
        Me.AutoScaleBaseSize = New System.Drawing.Size(6, 14)
        Me.ClientSize = New System.Drawing.Size(544, 266)
        Me.Name = "住宿统计"

    End Sub

#End Region

    Private Sub 住宿统计_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Try
            OleDbCon.ConnectionString = "Provider=Microsoft.Jet.Oledb.4.0;Data Source=D:\学生公寓管理系统\学生公寓管理系统.mdb"
            OleDbCon.Open()
            OleDbCom.Connection = OleDbCon
            OleDbCom.CommandType = CommandType.TableDirect
            OleDbCom.CommandText = "已住床位"
            OleDbDA.SelectCommand = OleDbCom
            OleDbDA.Fill(Ds1)
            Ds1.Tables(0).TableName = "已住床位"

            OleDbCom.CommandType = CommandType.Text

            '从住宿登记表中统计已住人数写入已住床位中
            Dim Conn As New System.Data.OleDb.OleDbConnection
            Dim Cmd As New System.Data.OleDb.OleDbCommand
            Dim DataReader As System.Data.OleDb.OleDbDataReader
            Try
                Conn.ConnectionString = "Provider=Microsoft.Jet.Oledb.4.0;Data Source=D:\学生公寓管理系统\学生公寓管理系统.mdb"
                Conn.Open()
                Cmd.CommandText = "Select 栋号,房号,count(*) As 已住床位 from 住宿登记 Group by 栋号,房号"
                Cmd.Connection = Conn
                DataReader = Cmd.ExecuteReader
                While DataReader.Read()
                    Dim a, b, c As String
                    a = DataReader.Item(0)
                    b = DataReader.Item(1)
                    c = DataReader.Item(2)

                    '把栋号,房号,已住床位 写入到已住床位表中
                    OleDbCom.CommandText = "Insert Into 已住床位 values(?,?,?)"
                    OleDbCom.Parameters.Clear()
                    OleDbCom.Parameters.Add("a", a)
                    OleDbCom.Parameters.Add("b", b)
                    OleDbCom.Parameters.Add("c", c)
                    OleDbDA.InsertCommand = OleDbCom
                    Dim DRow As DataRow
                    DRow = Ds1.Tables("已住床位").NewRow
                    DRow.Item(0) = a
                    DRow.Item(1) = b
                    DRow.Item(2) = c
                    Ds1.Tables("已住床位").Rows.Add(DRow)
                    OleDbDA.Update(Ds1, "已住床位")
                End While
                DataReader.Close()
                Conn.Close()
            Catch ex As Exception
                MsgBox(ex.Message)
            End Try

        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

        '把宿舍统计写到住宿统计表中
        Ds1.Clear()
        Try
            OleDbCom.Connection = OleDbCon
            OleDbCom.CommandType = CommandType.TableDirect
            OleDbCom.CommandText = "住宿统计"
            OleDbDA.SelectCommand = OleDbCom
            OleDbDA.Fill(Ds1)
            Ds1.Tables(1).TableName = "住宿统计2"

            OleDbCom.CommandType = CommandType.Text

            '把已住人的宿舍统计写到住宿统计表中
            Dim Conn2 As New System.Data.OleDb.OleDbConnection
            Dim Cmd2 As New System.Data.OleDb.OleDbCommand
            Dim DataReader2 As System.Data.OleDb.OleDbDataReader
            Try
                Conn2.ConnectionString = "Provider=Microsoft.Jet.Oledb.4.0;Data Source=D:\学生公寓管理系统\学生公寓管理系统.mdb"
                Conn2.Open()
                Cmd2.CommandText = "SELECT 房源库.栋号, 房源库.房号, 房源库.总床位, 已住床位.已住床位 FROM 房源库, 已住床位 WHERE 房源库.栋号=已住床位.栋号 and 房源库.房号=已住床位.房号"
                Cmd2.Connection = Conn2
                DataReader2 = Cmd2.ExecuteReader
                While DataReader2.Read()
                    Dim a, b, c, d, f As String
                    a = DataReader2.Item(0)
                    b = DataReader2.Item(1)
                    c = DataReader2.Item(2)
                    d = DataReader2.Item(3)
                    f = DataReader2.Item(4)
                    '把栋号,房号,已住床位 写入到已住床位表中
                    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 = Ds1.Tables("住宿统计2").NewRow
                    DRow.Item(0) = a
                    DRow.Item(1) = b
                    DRow.Item(2) = c
                    DRow.Item(3) = d
                    DRow.Item(4) = f
                    Ds1.Tables("住宿统计2").Rows.Add(DRow)
                    OleDbDA.Update(Ds1, "住宿统计2")
                End While
                DataReader2.Close()
                Conn2.Close()
            Catch ex As Exception
                MsgBox(ex.Message)
            End Try
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

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

    End Sub
End Class