年级课程表查询窗体.frm

智能排课系统

      Width           =   6015
   End
End
Attribute VB_Name = "Frmgradefind"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Option Explicit
Dim xlapp As Excel.Application
Dim xlbook As Excel.Workbook
Dim xlsheet As Excel.Worksheet
Public db As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim temp As New ADODB.Recordset
Dim classtemp As New ADODB.Recordset
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 Command2_Click()

Unload Me
frmmain.Show vbModal
End Sub


Private Sub Command1_Click()
    Dim strCourseID As String
    Dim strClassRoomID As String
    Dim i As Integer, j As Integer
    If Text1.Text = "" Then
        MsgBox "请输入要查询的班级编号！"
        Exit Sub
    End If
    
    
    
    strSQL = "SELECT * FROM bTempTableA where classid= " & Text1.Text & " order by ttime"
    strtempsql = "SELECT courseID,courseName FROM bCourse"
    strclassroomsql = "SELECT ClassRoomID,ClassRoomName FROM bclassroom"
    ConenctToDatabase
    rst.Open strSQL, db, adOpenKeyset, adLockOptimistic
    temp.Open strtempsql, db, adOpenKeyset, adLockReadOnly
    classtemp.Open strclassroomsql, db, adOpenKeyset, adLockReadOnly
    If rst.RecordCount() <> 0 Then
        i = rst.RecordCount()
    Else
        MsgBox "无此信息，请重新输入！"
        rst.Close
        temp.Close
        classtemp.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) = Text1.Text & "级"
    xlsheet.Cells(5, 6) = Date
    
    While i <> 0
                strCourseID = rst.Fields("courseID")
                temp.Filter = "courseID = '" & strCourseID & "'"
                strClassRoomID = rst.Fields("classroomID")
                classtemp.Filter = "classroomID = '" & strClassRoomID & "'"
                
        Select Case rst.Fields("Ttime")
            Case Is = 1
                xlsheet.Cells(9, 3) = temp.Fields("coursename")
                xlsheet.Cells(11, 3) = classtemp.Fields("classroomName")
            Case Is = 2
                xlsheet.Cells(13, 3) = temp.Fields("coursename")
                xlsheet.Cells(15, 3) = classtemp.Fields("classroomName")
            Case Is = 3
                xlsheet.Cells(17, 3) = temp.Fields("coursename")
                xlsheet.Cells(19, 3) = classtemp.Fields("classroomName")
            Case Is = 4
                xlsheet.Cells(21, 3) = temp.Fields("coursename")
                xlsheet.Cells(23, 3) = classtemp.Fields("classroomName")
            Case Is = 5
                xlsheet.Cells(9, 4) = temp.Fields("coursename")
                xlsheet.Cells(11, 4) = classtemp.Fields("classroomName")
            Case Is = 6
                xlsheet.Cells(13, 4) = temp.Fields("coursename")
                xlsheet.Cells(15, 4) = classtemp.Fields("classroomName")
            Case Is = 7
                xlsheet.Cells(17, 4) = temp.Fields("coursename")
                xlsheet.Cells(19, 4) = classtemp.Fields("classroomName")
            Case Is = 8
                xlsheet.Cells(21, 4) = temp.Fields("coursename")
                xlsheet.Cells(23, 4) = classtemp.Fields("classroomName")
            Case Is = 9
                xlsheet.Cells(9, 5) = temp.Fields("coursename")
                xlsheet.Cells(11, 5) = classtemp.Fields("classroomName")
            Case Is = 10
                xlsheet.Cells(13, 5) = temp.Fields("coursename")
                xlsheet.Cells(15, 5) = classtemp.Fields("classroomName")
            Case Is = 11
                xlsheet.Cells(17, 5) = temp.Fields("coursename")
                xlsheet.Cells(19, 5) = classtemp.Fields("classroomName")
            Case Is = 12
                xlsheet.Cells(21, 5) = temp.Fields("coursename")
                xlsheet.Cells(23, 5) = classtemp.Fields("classroomName")
            Case Is = 13
                xlsheet.Cells(9, 6) = temp.Fields("coursename")
                xlsheet.Cells(11, 6) = classtemp.Fields("classroomName")
            Case Is = 14
                xlsheet.Cells(13, 6) = temp.Fields("coursename")
                xlsheet.Cells(15, 6) = classtemp.Fields("classroomName")
            Case Is = 15
                xlsheet.Cells(17, 6) = temp.Fields("coursename")
                xlsheet.Cells(19, 6) = classtemp.Fields("classroomName")
            Case Is = 16
                xlsheet.Cells(21, 6) = temp.Fields("coursename")
                xlsheet.Cells(23, 6) = classtemp.Fields("classroomName")
            Case Is = 17
                xlsheet.Cells(9, 7) = temp.Fields("coursename")
                xlsheet.Cells(11, 7) = classtemp.Fields("classroomName")
            Case Is = 18
                xlsheet.Cells(13, 7) = temp.Fields("coursename")
                xlsheet.Cells(15, 7) = classtemp.Fields("classroomName")
            Case Is = 19
                xlsheet.Cells(17, 7) = temp.Fields("coursename")
                xlsheet.Cells(19, 7) = classtemp.Fields("classroomName")
            Case Is = 20
                xlsheet.Cells(21, 7) = temp.Fields("coursename")
                xlsheet.Cells(23, 7) = classtemp.Fields("classroomName")
            Case Is = 21
                xlsheet.Cells(9, 8) = temp.Fields("coursename")
                xlsheet.Cells(11, 8) = classtemp.Fields("classroomName")
            Case Is = 22
                xlsheet.Cells(13, 8) = temp.Fields("coursename")
                xlsheet.Cells(15, 8) = classtemp.Fields("classroomName")
            Case Is = 23
                xlsheet.Cells(17, 8) = temp.Fields("coursename")
                xlsheet.Cells(19, 8) = classtemp.Fields("classroomName")
            Case Is = 24
                xlsheet.Cells(21, 8) = temp.Fields("coursename")
                xlsheet.Cells(23, 8) = classtemp.Fields("classroomName")
            Case Is = 25
                xlsheet.Cells(9, 9) = temp.Fields("coursename")
                xlsheet.Cells(11, 9) = classtemp.Fields("classroomName")
            Case Is = 26
                xlsheet.Cells(13, 9) = temp.Fields("coursename")
                xlsheet.Cells(15, 9) = classtemp.Fields("classroomName")
            Case Is = 27
                xlsheet.Cells(17, 9) = temp.Fields("coursename")
                xlsheet.Cells(19, 9) = classtemp.Fields("classroomName")
            Case Is = 28
                xlsheet.Cells(21, 9) = temp.Fields("coursename")
                xlsheet.Cells(23, 9) = classtemp.Fields("classroomName")
            
            Case Else
                MsgBox "数据溢出，请检查系统！"
            End Select
        i = i - 1
        rst.MoveNext
        Wend
        rst.Close
        temp.Close
        classtemp.Close
        
            
            
            
                
        
    

End Sub


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