form6.vb

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

    End Sub

#End Region

    Private Sub Form6_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(500, 2) 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(Qq1)
            Qq1.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, 0) = DataReader.Item(0)
                    abc(i, 1) = DataReader.Item(1)
                    abc(i, 2) = DataReader.Item(2)
                    i += 1
                End While
                DataReader.Close()
                Conn.Close()

                For i = 0 To 500
                    If abc(i, 0) = "" Or abc(i, 1) = "" Then
                        Exit For
                    End If
                    a = abc(i, 0)
                    b = abc(i, 1)
                    c = abc(i, 2)
                    d = 0
                    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 栋号,房号,Count(*) from 住宿登记  where 栋号=" & "'" & abc(i, 0) & "' and 房号=" & "'" & abc(i, 1) & "'" & "group by 栋号,房号"
                        Cmd2.Connection = Conn2
                        DataReader2 = Cmd2.ExecuteReader
                        While DataReader2.Read()
                            d = CDbl(DataReader2.Item(2))
                        End While
                        DataReader2.Close()
                        Conn2.Close()
                    Catch ex As Exception

                    End Try






                    '把统计数据写入社区文明分表中()
                    f = c - d
                    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 = Qq1.Tables("住宿统计").NewRow
                    DRow.Item(0) = a
                    DRow.Item(1) = b
                    DRow.Item(2) = c
                    DRow.Item(3) = d
                    DRow.Item(4) = f

                    Qq1.Tables("住宿统计").Rows.Add(DRow)
                    OleDbDA.Update(Qq1, "住宿统计")

                Next





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


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



        '析构连接对象,命令对象,数据对象
        OleDbCon.Dispose()
        OleDbCom.Dispose()
        OleDbDA.Dispose()
        Qq1.Dispose()
    End Sub
End Class