form3.vb

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

        '
        'Form3
        '
        Me.AutoScaleBaseSize = New System.Drawing.Size(6, 14)
        Me.ClientSize = New System.Drawing.Size(776, 430)
        Me.Controls.Add(Me.Panel1)
        Me.Controls.Add(Me.Panel2)
        Me.Controls.Add(Me.DataGrid1)
        Me.Name = "Form3"
        Me.StartPosition = System.Windows.Forms.FormStartPosition.Manual
        Me.Text = "住宿登记与查询"
        CType(Me.DataGrid1, System.ComponentModel.ISupportInitialize).EndInit()
        CType(Me.Ds1, System.ComponentModel.ISupportInitialize).EndInit()
        Me.Panel2.ResumeLayout(False)
        Me.Panel1.ResumeLayout(False)
        Me.ResumeLayout(False)

    End Sub

#End Region

    Private Sub Form3_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        '加载数据到Ds1
        Try
            OleDbDataAdapter1.SelectCommand.CommandText = "Select 住宿登记.栋号,住宿登记.房号,住宿登记.床号,房源库.类别,住宿登记.学号,住宿登记.姓名,学生库.院系,学生库.专业,学生库.班级,房源库.宿舍电话 From 住宿登记,学生库,房源库 Where 住宿登记.学号=学生库.学号 AND 住宿登记.栋号=房源库.栋号 AND 住宿登记.房号=房源库.房号"
            Ds1.Clear()
            OleDbDataAdapter1.Fill(Ds1)
            Me.DataGrid1.Select(DataGrid1.CurrentRowIndex)
        Catch ex As Exception
            MsgBox("表中没有数据!")

        End Try

        '加载栋号到ComboBox中
        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()
                ComboBox5.Items.Add(DataReader.Item(0))
            End While
            DataReader.Close()
            Conn.Close()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

     
        Try
            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()
                ComboBox7.Items.Add(DataReader.Item(0))
            End While
            DataReader.Close()
            Conn.Close()
        Catch ex As Exception
            MsgBox(Ex.Message)
        End Try
    End Sub



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

        Dim i As Integer = 1
        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 * from 院系专业 Where 院系=" & "'" & ComboBox7.Text & "'"

        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()
            ComboBox8.Items.Clear()
            For i = 1 To 10
                ComboBox8.Items.Add(DataReader.Item(i))
            Next
            DataReader.Close()
            Conn.Close()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

    Private Sub ComboBox5_Validating(ByVal sender As Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles ComboBox5.Validating
        If ComboBox5.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 栋号=" & "'" & ComboBox5.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()
            ComboBox6.Items.Clear()
            While DataReader.Read()
                ComboBox6.Items.Add(DataReader.Item(0))
            End While
            DataReader.Close()
            Conn.Close()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

    Private Sub DataGrid1_MouseUp(ByVal sender As Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles DataGrid1.MouseUp
        Try
            DataGrid1.Select(DataGrid1.CurrentRowIndex)
        Catch
            If Err.Number = 9 Then
                MsgBox("表中没有数据!")
            End If

        End Try
    End Sub

    Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        Try
            OleDbDataAdapter1.SelectCommand.CommandText = "insert into 住宿登记 (栋号,房号,学号,姓名,床号) values (Trim(ComboBox1.text),Trim(ComboBox2.text),Trim(TextBox1.text),Trim(TextBox2.text),Trim(ComboBox3.text))"
            Ds1.Clear()
            OleDbDataAdapter1.Fill(Ds1)
            Me.BindingContext(Ds1, "住宿登记").AddNew()

            Me.BindingContext(Ds1, "住宿登记").EndCurrentEdit()
        Catch ex As Exception
            MsgBox(ex.Message)

        End Try

    End Sub

    Private Sub Button7_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        Try
            Me.BindingContext(Ds1, "住宿登记").EndCurrentEdit()
            If Ds1.HasChanges(DataRowState.Modified) Then
                OleDbDataAdapter1.Update(Ds1)
                MsgBox("更改成功!")

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

    Private Sub Button9_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        Try
            OleDbDataAdapter1.SelectCommand.CommandText = "Select 住宿登记.栋号,住宿登记.房号,房源库.类别,住宿登记.学号,住宿登记.姓名,学生库.院系,学生库.专业,学生库.班级,房源库.宿舍电话 From 住宿登记,学生库,房源库 Where 住宿登记.学号=学生库.学号 AND 住宿登记.栋号=房源库.栋号 AND 住宿登记.房号=房源库.房号"
            Ds1.Clear()
            OleDbDataAdapter1.Fill(Ds1)
            Me.DataGrid1.Select(DataGrid1.CurrentRowIndex)
        Catch ex As Exception
            MsgBox(ex.Message)

        End Try
    End Sub

    Private Sub Button13_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button13.Click
        '加载数据到Ds1
        Try
            OleDbDataAdapter1.SelectCommand.CommandText = "Select 住宿登记.栋号,住宿登记.房号,住宿登记.床号,房源库.类别,住宿登记.学号,住宿登记.姓名,学生库.院系,学生库.专业,学生库.班级,房源库.宿舍电话 From 住宿登记,学生库,房源库 Where 住宿登记.学号=学生库.学号 AND 住宿登记.栋号=房源库.栋号 AND 住宿登记.房号=房源库.房号"
            Ds1.Clear()
            OleDbDataAdapter1.Fill(Ds1)
            Me.DataGrid1.Select(DataGrid1.CurrentRowIndex)
        Catch ex As Exception
            MsgBox("表中没有数据!")

        End Try
    End Sub

    Private Sub Button12_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button12.Click
        Dim ComStr As String
        ComStr = "Select 住宿登记.栋号,住宿登记.房号,住宿登记.床号,房源库.类别,住宿登记.学号,住宿登记.姓名,学生库.院系,学生库.专业,学生库.班级,房源库.宿舍电话  From 住宿登记,学生库,房源库 Where 住宿登记.学号=学生库.学号 AND 住宿登记.栋号=房源库.栋号 AND 住宿登记.房号=房源库.房号 "
        ComStr &= "and 住宿登记.学号 like '" & Trim(TextBox8.Text) & "%'"
        ComStr &= "and 住宿登记.栋号 like '%" & Trim(ComboBox5.Text) & "%'"
        ComStr &= "and 住宿登记.房号 like '%" & Trim(ComboBox6.Text) & "%'"
        ComStr &= "and 住宿登记.床号 like '" & Trim(ComboBox11.Text) & "%'"
        ComStr &= "and 住宿登记.姓名 like '%" & Trim(TextBox9.Text) & "%'"
        ComStr &= "and 学生库.院系 like '%" & Trim(ComboBox7.Text) & "%'"
        ComStr &= "and 学生库.专业 like '%" & Trim(ComboBox8.Text) & "%'"
        ComStr &= "and 学生库.班级 like " & "'" & Trim(ComboBox9.Text) & "%" & "'"
        ComStr &= "and 房源库.宿舍电话 like " & "'" & Trim(TextBox10.Text) & "%" & "'"
        ComStr &= "and 房源库.类别 like " & "'" & Trim(ComboBox1.Text) & "%" & "'"
        Try
            OleDbDataAdapter1.SelectCommand.CommandText = ComStr
            Ds1.Clear()
            OleDbDataAdapter1.Fill(Ds1)
            Me.DataGrid1.Select(DataGrid1.CurrentRowIndex)
        Catch ex As Exception
            MsgBox("数据库中无您所需数据!")

        End Try
    End Sub

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

    Private Sub Panel1_Paint(ByVal sender As System.Object, ByVal e As System.Windows.Forms.PaintEventArgs) Handles Panel1.Paint

    End Sub

    Private Sub Panel2_Paint(ByVal sender As System.Object, ByVal e As System.Windows.Forms.PaintEventArgs) Handles Panel2.Paint

    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim F As New Form4
        F.Show()
    End Sub
End Class