住宿统计主界面.vb

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

        Me.Label1.Location = New System.Drawing.Point(8, 32)
        Me.Label1.Name = "Label1"
        Me.Label1.Size = New System.Drawing.Size(39, 22)
        Me.Label1.TabIndex = 0
        Me.Label1.Text = "栋号"
        '
        'DataGrid1
        '
        Me.DataGrid1.DataMember = "住宿统计"
        Me.DataGrid1.DataSource = Me.De1
        Me.DataGrid1.HeaderForeColor = System.Drawing.SystemColors.ControlText
        Me.DataGrid1.Location = New System.Drawing.Point(8, 0)
        Me.DataGrid1.Name = "DataGrid1"
        Me.DataGrid1.ReadOnly = True
        Me.DataGrid1.Size = New System.Drawing.Size(608, 320)
        Me.DataGrid1.TabIndex = 0
        '
        'De1
        '
        Me.De1.DataSetName = "De"
        Me.De1.Locale = New System.Globalization.CultureInfo("zh-CN")
        '
        'OleDbDataAdapter1
        '
        Me.OleDbDataAdapter1.DeleteCommand = Me.OleDbDeleteCommand1
        Me.OleDbDataAdapter1.InsertCommand = Me.OleDbInsertCommand1
        Me.OleDbDataAdapter1.SelectCommand = Me.OleDbSelectCommand1
        Me.OleDbDataAdapter1.TableMappings.AddRange(New System.Data.Common.DataTableMapping() {New System.Data.Common.DataTableMapping("Table", "住宿统计", New System.Data.Common.DataColumnMapping() {New System.Data.Common.DataColumnMapping("栋号", "栋号"), New System.Data.Common.DataColumnMapping("房号", "房号"), New System.Data.Common.DataColumnMapping("总床位", "总床位"), New System.Data.Common.DataColumnMapping("已住床位", "已住床位"), New System.Data.Common.DataColumnMapping("剩余床位", "剩余床位")})})
        Me.OleDbDataAdapter1.UpdateCommand = Me.OleDbUpdateCommand1
        '
        'OleDbDeleteCommand1
        '
        Me.OleDbDeleteCommand1.CommandText = "DELETE FROM 住宿统计 WHERE (房号 = ?) AND (栋号 = ?) AND (剩余床位 = ? OR ? IS NULL AND 剩余床位 " & _
        "IS NULL) AND (已住床位 = ? OR ? IS NULL AND 已住床位 IS NULL) AND (总床位 = ? OR ? IS NULL " & _
        "AND 总床位 IS NULL)"
        Me.OleDbDeleteCommand1.Connection = Me.OleDbConnection1
        Me.OleDbDeleteCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Original_房号", System.Data.OleDb.OleDbType.VarWChar, 50, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "房号", System.Data.DataRowVersion.Original, Nothing))
        Me.OleDbDeleteCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Original_栋号", System.Data.OleDb.OleDbType.VarWChar, 50, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "栋号", System.Data.DataRowVersion.Original, Nothing))
        Me.OleDbDeleteCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Original_剩余床位", System.Data.OleDb.OleDbType.Integer, 0, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "剩余床位", System.Data.DataRowVersion.Original, Nothing))
        Me.OleDbDeleteCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Original_剩余床位1", System.Data.OleDb.OleDbType.Integer, 0, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "剩余床位", System.Data.DataRowVersion.Original, Nothing))
        Me.OleDbDeleteCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Original_已住床位", System.Data.OleDb.OleDbType.Integer, 0, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "已住床位", System.Data.DataRowVersion.Original, Nothing))
        Me.OleDbDeleteCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Original_已住床位1", System.Data.OleDb.OleDbType.Integer, 0, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "已住床位", System.Data.DataRowVersion.Original, Nothing))
        Me.OleDbDeleteCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Original_总床位", System.Data.OleDb.OleDbType.Integer, 0, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "总床位", System.Data.DataRowVersion.Original, Nothing))
        Me.OleDbDeleteCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Original_总床位1", System.Data.OleDb.OleDbType.Integer, 0, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "总床位", System.Data.DataRowVersion.Original, Nothing))
        '
        'OleDbConnection1
        '
        Me.OleDbConnection1.ConnectionString = "Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Registry Path=;Jet OLEDB:Database L" & _
        "ocking Mode=1;Data Source=""学生公寓管理系统.mdb"";Jet OLEDB:Engine Type=5;Provider=""Micro" & _
        "soft.Jet.OLEDB.4.0"";Jet OLEDB:System database=;Jet OLEDB:SFP=False;persist secur" & _
        "ity info=False;Extended Properties=;Mode=Share Deny None;Jet OLEDB:Encrypt Datab" & _
        "ase=False;Jet OLEDB:Create System Database=False;Jet OLEDB:Don't Copy Locale on " & _
        "Compact=False;Jet OLEDB:Compact Without Replica Repair=False;User ID=Admin;Jet O" & _
        "LEDB:Global Bulk Transactions=1"
        '
        'OleDbInsertCommand1
        '
        Me.OleDbInsertCommand1.CommandText = "INSERT INTO 住宿统计(栋号, 房号, 总床位, 已住床位, 剩余床位) VALUES (?, ?, ?, ?, ?)"
        Me.OleDbInsertCommand1.Connection = Me.OleDbConnection1
        Me.OleDbInsertCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("栋号", System.Data.OleDb.OleDbType.VarWChar, 50, "栋号"))
        Me.OleDbInsertCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("房号", System.Data.OleDb.OleDbType.VarWChar, 50, "房号"))
        Me.OleDbInsertCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("总床位", System.Data.OleDb.OleDbType.Integer, 0, "总床位"))
        Me.OleDbInsertCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("已住床位", System.Data.OleDb.OleDbType.Integer, 0, "已住床位"))
        Me.OleDbInsertCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("剩余床位", System.Data.OleDb.OleDbType.Integer, 0, "剩余床位"))
        '
        'OleDbSelectCommand1
        '
        Me.OleDbSelectCommand1.CommandText = "SELECT 栋号, 房号, 总床位, 已住床位, 剩余床位 FROM 住宿统计 ORDER BY 栋号, 房号"
        Me.OleDbSelectCommand1.Connection = Me.OleDbConnection1
        '
        'OleDbUpdateCommand1
        '
        Me.OleDbUpdateCommand1.CommandText = "UPDATE 住宿统计 SET 栋号 = ?, 房号 = ?, 总床位 = ?, 已住床位 = ?, 剩余床位 = ? WHERE (房号 = ?) AND (栋" & _
        "号 = ?) AND (剩余床位 = ? OR ? IS NULL AND 剩余床位 IS NULL) AND (已住床位 = ? OR ? IS NULL A" & _
        "ND 已住床位 IS NULL) AND (总床位 = ? OR ? IS NULL AND 总床位 IS NULL)"
        Me.OleDbUpdateCommand1.Connection = Me.OleDbConnection1
        Me.OleDbUpdateCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("栋号", System.Data.OleDb.OleDbType.VarWChar, 50, "栋号"))
        Me.OleDbUpdateCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("房号", System.Data.OleDb.OleDbType.VarWChar, 50, "房号"))
        Me.OleDbUpdateCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("总床位", System.Data.OleDb.OleDbType.Integer, 0, "总床位"))
        Me.OleDbUpdateCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("已住床位", System.Data.OleDb.OleDbType.Integer, 0, "已住床位"))
        Me.OleDbUpdateCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("剩余床位", System.Data.OleDb.OleDbType.Integer, 0, "剩余床位"))
        Me.OleDbUpdateCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Original_房号", System.Data.OleDb.OleDbType.VarWChar, 50, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "房号", System.Data.DataRowVersion.Original, Nothing))
        Me.OleDbUpdateCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Original_栋号", System.Data.OleDb.OleDbType.VarWChar, 50, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "栋号", System.Data.DataRowVersion.Original, Nothing))
        Me.OleDbUpdateCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Original_剩余床位", System.Data.OleDb.OleDbType.Integer, 0, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "剩余床位", System.Data.DataRowVersion.Original, Nothing))
        Me.OleDbUpdateCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Original_剩余床位1", System.Data.OleDb.OleDbType.Integer, 0, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "剩余床位", System.Data.DataRowVersion.Original, Nothing))
        Me.OleDbUpdateCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Original_已住床位", System.Data.OleDb.OleDbType.Integer, 0, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "已住床位", System.Data.DataRowVersion.Original, Nothing))
        Me.OleDbUpdateCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Original_已住床位1", System.Data.OleDb.OleDbType.Integer, 0, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "已住床位", System.Data.DataRowVersion.Original, Nothing))
        Me.OleDbUpdateCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Original_总床位", System.Data.OleDb.OleDbType.Integer, 0, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "总床位", System.Data.DataRowVersion.Original, Nothing))
        Me.OleDbUpdateCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Original_总床位1", System.Data.OleDb.OleDbType.Integer, 0, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "总床位", System.Data.DataRowVersion.Original, Nothing))
        '
        '住宿统计主界面
        '
        Me.AutoScaleBaseSize = New System.Drawing.Size(6, 14)
        Me.BackColor = System.Drawing.Color.Silver
        Me.ClientSize = New System.Drawing.Size(624, 441)
        Me.Controls.Add(Me.Panel1)
        Me.Controls.Add(Me.DataGrid1)
        Me.MaximizeBox = False
        Me.Name = "住宿统计主界面"
        Me.StartPosition = System.Windows.Forms.FormStartPosition.Manual
        Me.Text = "住宿统计主界面"
        Me.Panel1.ResumeLayout(False)
        Me.GroupBox1.ResumeLayout(False)
        CType(Me.DataGrid1, System.ComponentModel.ISupportInitialize).EndInit()
        CType(Me.De1, System.ComponentModel.ISupportInitialize).EndInit()
        Me.ResumeLayout(False)

    End Sub

#End Region
  
    Private Sub 住宿统计主界面_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Try
            OleDbDataAdapter1.SelectCommand.CommandText = "Select * from 住宿统计"
            De1.Clear()
            OleDbDataAdapter1.Fill(De1)
            DataGrid1.Select(DataGrid1.CurrentRowIndex)
        Catch ex As Exception
            MsgBox("表中没有数据!")
        End Try

        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=学生公寓管理系统.mdb"
            Conn.Open()
            Cmd.CommandText = "Select Distinct 栋号 from 房源库 order by 栋号"
            Cmd.Connection = Conn
            DataReader = Cmd.ExecuteReader
            While DataReader.Read()
                ComboBox1.Items.Add(DataReader.Item(0))
                ComboBox5.Items.Add(DataReader.Item(0))
            End While
            DataReader.Close()
            Conn.Close()
        Catch ex As Exception

        End Try
    End Sub

    Private Sub 住宿统计主界面_Closed(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Closed
        Me.Hide()
        Dim FR As New 提示
        FR.Show()
        Try
            OleDbDataAdapter1.SelectCommand.CommandText = "select * from 住宿统计"
            De1.Clear()
            OleDbDataAdapter1.Fill(De1)
            Me.DataGrid1.Select(DataGrid1.CurrentRowIndex)
        Catch ex As Exception

        End Try

        While Me.BindingContext(De1, "住宿统计").Count > 0
            Try
                De1.住宿统计.Rows(Me.BindingContext(De1, "住宿统计").Position).Delete()
                OleDbDataAdapter1.Update(De1)
            Catch ex As Exception

            End Try
        End While
        FR.Close()
    End Sub

    Private Sub GroupBox1_Enter(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles GroupBox1.Enter

    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Try
            Dim ComStr As String
            ComStr = "Select * from 住宿统计 where 栋号 like '" & Trim(ComboBox1.Text) & "%'"
            If ComboBox2.Text <> "" Then
                ComStr &= "and 房号 like '" & Trim(ComboBox2.Text) & "%'"
            End If

            If ComboBox3.Text <> "" And TextBox3.Text <> "" Then
                ComStr &= "and 总床位" & Trim(ComboBox3.Text) & Trim(TextBox3.Text)
            End If

            If ComboBox4.Text <> "" And TextBox1.Text <> "" Then
                ComStr &= "and 剩余床位" & Trim(ComboBox4.Text) & Trim(TextBox1.Text)
            End If

            If ComboBox5.Text <> "" And TextBox2.Text <> "" Then
                ComStr &= "and 已住床位" & Trim(ComboBox5.Text) & Trim(TextBox2.Text)
            End If

            OleDbDataAdapter1.SelectCommand.CommandText = ComStr
            De1.Clear()
            OleDbDataAdapter1.Fill(De1)
            DataGrid1.Select(DataGrid1.CurrentRowIndex)
        Catch ex As Exception
            MsgBox("表中没有数据!")
        End Try
    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Try
            OleDbDataAdapter1.SelectCommand.CommandText = "Select * from 住宿统计"
            De1.Clear()
            OleDbDataAdapter1.Fill(De1)
            DataGrid1.Select(DataGrid1.CurrentRowIndex)
        Catch ex As Exception
            MsgBox("表中没有数据!")
        End Try
        ComboBox1.Text = ""
        ComboBox2.Text = ""
        ComboBox3.Text = ""
        ComboBox4.Text = ""
        ComboBox5.Text = ""
        TextBox1.Text = ""
        TextBox2.Text = ""
        TextBox3.Text = ""
    End Sub

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        Me.Close()
    End Sub

    Private Sub ComboBox1_Validating(ByVal sender As Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles ComboBox1.Validating
        If ComboBox1.Text = "" Then
            Exit Sub
        End If

        Dim Conn As New System.Data.OleDb.OleDbConnection
        Dim Cmd As New System.Data.OleDb.OleDbCommand
        Dim DataReader As System.Data.OleDb.OleDbDataReader
        Dim ComString As String
        ComString = "Select Distinct 房号 from 房源库 Where 栋号=" & "'" & ComboBox1.Text & "'" & "order by 房号"

        Try
            Conn.ConnectionString = "Provider=Microsoft.Jet.Oledb.4.0;Data Source=学生公寓管理系统.mdb"
            Conn.Open()

            Cmd.CommandText = ComString
            Cmd.Connection = Conn
            DataReader = Cmd.ExecuteReader
            DataReader.Read()
            ComboBox2.Items.Clear()
            While DataReader.Read()
                ComboBox2.Items.Add(DataReader.Item(0))
            End While
            DataReader.Close()
            Conn.Close()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub
End Class