教师课程表查询窗体.frm

智能排课系统

Dim strSQL As String
Dim strtempsql As String
Dim strclassroomsql As String

'连接到数据库
Private Function ConenctToDatabase() As Boolean
  On Error GoTo ErrorHandler
  Dim DBName As String, ServerAdd As String, UserName As String, UserPwd As String
  '设置连接信息字符串的参数
  ServerAdd = "IMAGE"
  DBName = "Paike"
  UserName = ""
  UserPwd = ""
  '连接数据库
  Set db = New ADODB.Connection
  db.ConnectionTimeout = 10
  db.CursorLocation = adUseServer
  db.ConnectionString = "uid=" & UserName & ";pwd=" & UserPwd & _
                            ";driver={SQL Server};server=" & ServerAdd & _
                            ";database=" & DBName & ";dsn=''"
  db.Open
  '返回值
  ConenctToDatabase = True
  Exit Function
ErrorHandler:
  MsgBox "连接到数据库出错", vbCritical, "出现错误"
  Exit Function
End Function

Private Sub Command1_Click()
    Dim a As String
    Dim i As Integer, j As Integer
    Dim strCourseID As String
    Dim strClassRoomID As String
    Dim strclasssql As String
    Dim strClassID As String
    Dim strTeacher As String
    
    strSQL = "SELECT * FROM bTempTableA where teacherid= " & Text1.Text & " order by ttime"
    strtempsql = "SELECT courseID,courseName FROM bCourse"
    strclassroomsql = "SELECT ClassRoomID,ClassRoomName FROM bclassroom"
    strclasssql = "select classID,classname from bclass"
    strTeacher = "SELECT * FROM bteacher where teacherid= " & Text1.Text & " "
    
    ConenctToDatabase
    rst.Open strSQL, db, adOpenKeyset, adLockOptimistic
    coursetemprst.Open strtempsql, db, adOpenKeyset, adLockReadOnly
    classtemprst.Open strclasssql, db, adOpenKeyset, adLockReadOnly
    classroomrst.Open strclassroomsql, db, adOpenDynamic, adLockReadOnly
    teacherrst.Open strTeacher, db, adOpenDynamic, adLockReadOnly
    If rst.RecordCount() <> 0 Then
        i = rst.RecordCount()
    Else
        MsgBox "无此信息，请重新输入！"
        rst.Close
        coursetemprst.Close
        classtemprst.Close
        classroomrst.Close
        Exit Sub
        
    End If

Set xlapp = New Excel.Application

Set xlbook = xlapp.Workbooks.Open(App.Path & "\课程表模板.xlt")
xlapp.Visible = True
Set xlsheet = xlbook.Worksheets("教师课程表")
xlsheet.Activate
xlsheet.Cells(5, 1) = teacherrst.Fields("teachername")
xlsheet.Cells(5, 6) = Date

 While i <> 0
                strCourseID = rst.Fields("courseID")
                coursetemprst.Filter = "courseID = '" & strCourseID & "'"
                strClassRoomID = rst.Fields("classroomID")
                classroomrst.Filter = "classroomID = '" & strClassRoomID & "'"
                strClassID = rst.Fields("classID")
                classtemprst.Filter = "classID = '" & strClassID & "'"
                
                
                
                
        Select Case rst.Fields("Ttime")
            Case Is = 1
                xlsheet.Cells(9, 3) = coursetemprst.Fields("coursename")
                xlsheet.Cells(10, 3) = classroomrst.Fields("classroomName")
                xlsheet.Cells(12, 3) = classtemprst.Fields("classname")
            Case Is = 2
                xlsheet.Cells(13, 3) = coursetemprst.Fields("coursename")
                xlsheet.Cells(14, 3) = classroomrst.Fields("classroomName")
                xlsheet.Cells(16, 3) = classtemprst.Fields("classname")
            Case Is = 3
                xlsheet.Cells(17, 3) = coursetemprst.Fields("coursename")
                xlsheet.Cells(18, 3) = classroomrst.Fields("classroomName")
                xlsheet.Cells(20, 3) = classtemprst.Fields("classname")
            Case Is = 4
                xlsheet.Cells(21, 3) = coursetemprst.Fields("coursename")
                xlsheet.Cells(22, 3) = classroomrst.Fields("classroomName")
                xlsheet.Cells(24, 3) = classtemprst.Fields("classname")
            Case Is = 5
                xlsheet.Cells(9, 4) = coursetemprst.Fields("coursename")
                xlsheet.Cells(10, 4) = classroomrst.Fields("classroomName")
                xlsheet.Cells(12, 4) = classtemprst.Fields("classname")
            Case Is = 6
                xlsheet.Cells(13, 4) = coursetemprst.Fields("coursename")
                xlsheet.Cells(14, 4) = classroomrst.Fields("classroomName")
                xlsheet.Cells(16, 4) = classtemprst.Fields("classname")
            Case Is = 7
                xlsheet.Cells(17, 4) = coursetemprst.Fields("coursename")
                xlsheet.Cells(18, 4) = classroomrst.Fields("classroomName")
                xlsheet.Cells(20, 4) = classtemprst.Fields("classname")
            Case Is = 8
                xlsheet.Cells(21, 4) = coursetemprst.Fields("coursename")
                xlsheet.Cells(22, 4) = classroomrst.Fields("classroomName")
                xlsheet.Cells(24, 4) = classtemprst.Fields("classname")
            Case Is = 9
                xlsheet.Cells(9, 5) = coursetemprst.Fields("coursename")
                xlsheet.Cells(10, 5) = classroomrst.Fields("classroomName")
                xlsheet.Cells(12, 5) = classtemprst.Fields("classname")
            Case Is = 10
                xlsheet.Cells(13, 5) = coursetemprst.Fields("coursename")
                xlsheet.Cells(14, 5) = classroomrst.Fields("classroomName")
                xlsheet.Cells(16, 5) = classtemprst.Fields("classname")
            Case Is = 11
                xlsheet.Cells(17, 5) = coursetemprst.Fields("coursename")
                xlsheet.Cells(18, 5) = classroomrst.Fields("classroomName")
                xlsheet.Cells(20, 5) = classtemprst.Fields("classname")
            Case Is = 12
                xlsheet.Cells(21, 5) = coursetemprst.Fields("coursename")
                xlsheet.Cells(22, 5) = classroomrst.Fields("classroomName")
                xlsheet.Cells(24, 5) = classtemprst.Fields("classname")
            Case Is = 13
                xlsheet.Cells(9, 6) = coursetemprst.Fields("coursename")
                xlsheet.Cells(10, 6) = classroomrst.Fields("classroomName")
                xlsheet.Cells(12, 6) = classtemprst.Fields("classname")
            Case Is = 14
                xlsheet.Cells(13, 6) = coursetemprst.Fields("coursename")
                xlsheet.Cells(14, 6) = classroomrst.Fields("classroomName")
                xlsheet.Cells(16, 6) = classtemprst.Fields("classname")
            Case Is = 15
                xlsheet.Cells(17, 6) = coursetemprst.Fields("coursename")
                xlsheet.Cells(18, 6) = classroomrst.Fields("classroomName")
                xlsheet.Cells(20, 6) = classtemprst.Fields("classname")
            Case Is = 16
                xlsheet.Cells(21, 6) = coursetemprst.Fields("coursename")
                xlsheet.Cells(22, 6) = classroomrst.Fields("classroomName")
                xlsheet.Cells(24, 6) = classtemprst.Fields("classname")
            Case Is = 17
                xlsheet.Cells(9, 7) = coursetemprst.Fields("coursename")
                xlsheet.Cells(10, 7) = classroomrst.Fields("classroomName")
                xlsheet.Cells(12, 7) = classtemprst.Fields("classname")
            Case Is = 18
                xlsheet.Cells(13, 7) = coursetemprst.Fields("coursename")
                xlsheet.Cells(14, 7) = classroomrst.Fields("classroomName")
                xlsheet.Cells(16, 7) = classtemprst.Fields("classname")
            Case Is = 19
                xlsheet.Cells(17, 7) = coursetemprst.Fields("coursename")
                xlsheet.Cells(18, 7) = classroomrst.Fields("classroomName")
                xlsheet.Cells(20, 7) = classtemprst.Fields("classname")
            Case Is = 20
                xlsheet.Cells(21, 7) = coursetemprst.Fields("coursename")
                xlsheet.Cells(22, 7) = classroomrst.Fields("classroomName")
                xlsheet.Cells(24, 7) = classtemprst.Fields("classname")
            Case Is = 21
                xlsheet.Cells(9, 8) = coursetemprst.Fields("coursename")
                xlsheet.Cells(10, 8) = classroomrst.Fields("classroomName")
                xlsheet.Cells(12, 8) = classtemprst.Fields("classname")
            Case Is = 22
                xlsheet.Cells(13, 8) = coursetemprst.Fields("coursename")
                xlsheet.Cells(14, 8) = classroomrst.Fields("classroomName")
                xlsheet.Cells(16, 8) = classtemprst.Fields("classname")
            Case Is = 23
                xlsheet.Cells(17, 8) = coursetemprst.Fields("coursename")
                xlsheet.Cells(18, 8) = classroomrst.Fields("classroomName")
                xlsheet.Cells(20, 8) = classtemprst.Fields("classname")
            Case Is = 24
                xlsheet.Cells(21, 8) = coursetemprst.Fields("coursename")
                xlsheet.Cells(22, 8) = classroomrst.Fields("classroomName")
                xlsheet.Cells(24, 8) = classtemprst.Fields("classname")
            Case Is = 25
                xlsheet.Cells(9, 9) = coursetemprst.Fields("coursename")
                xlsheet.Cells(10, 9) = classroomrst.Fields("classroomName")
                xlsheet.Cells(12, 9) = classtemprst.Fields("classname")
            Case Is = 26
                xlsheet.Cells(13, 9) = coursetemprst.Fields("coursename")
                xlsheet.Cells(14, 9) = classroomrst.Fields("classroomName")
                xlsheet.Cells(16, 9) = classtemprst.Fields("classname")
            Case Is = 27
                xlsheet.Cells(17, 9) = coursetemprst.Fields("coursename")
                xlsheet.Cells(18, 9) = classroomrst.Fields("classroomName")
                xlsheet.Cells(20, 9) = classtemprst.Fields("classname")
            Case Is = 28
                xlsheet.Cells(21, 9) = coursetemprst.Fields("coursename")
                xlsheet.Cells(22, 9) = classroomrst.Fields("classroomName")
                xlsheet.Cells(24, 9) = classtemprst.Fields("classname")
            
            Case Else
                MsgBox "数据溢出，请检查系统！"
            End Select
        i = i - 1
        rst.MoveNext
        Wend
    rst.Close
    coursetemprst.Close
    classtemprst.Close
    classroomrst.Close
    teacherrst.Close

    
End Sub

Private Sub Command2_Click()
Frmteacherfind.Cls
Unload Me
frmmain.Show vbModal
End Sub

Private Sub DataGrid1_Click()
Text1.Text = DataGrid1.Columns(0).Text
End Sub