frmtongji2.frm

用VB连接ORalce数据库的学生管理系统

        Adodc8.RecordSource = sql
        Adodc8.Refresh
        frmtongji.md8 = Adodc8.Recordset.Fields(0)
        Adodc9.ConnectionString = "Provider=MSDAORA.1;User ID=he;Persist Security Info=False"
        sql = "select count(*) from choose where grade in(select grade from choose where cno in(select cno from course where cname='English') and grade between 80 and 90)"
        Adodc9.CommandType = adCmdText
        Adodc9.RecordSource = sql
        Adodc9.Refresh
        frmtongji.md9 = Adodc9.Recordset.Fields(0)
        Adodc10.ConnectionString = "Provider=MSDAORA.1;User ID=he;Persist Security Info=False"
        sql = "select count(*) from choose where grade in(select grade from choose where cno in(select cno from course where cname='English') and grade>90)"
        Adodc10.CommandType = adCmdText
        Adodc10.RecordSource = sql
        Adodc10.Refresh
        frmtongji.md10 = Adodc10.Recordset.Fields(0)
        frmtongji.Show
        frmtongji2.Hide
    End If
    If Option10.Value = True Then
        Adodc1.ConnectionString = "Provider=MSDAORA.1;User ID=he;Persist Security Info=False"
        sql = "select * from choose where grade < 60 and sno in (select sno from choose where grade < 60 group by sno having count(*)>1)"
        Adodc1.CommandType = adCmdText
        Adodc1.RecordSource = sql
        Adodc1.Refresh
        Set MSF1.DataSource = Adodc1
        If Adodc1.Recordset.EOF = True Then
            MsgBox "对不起，没有此课程的档案记录！", vbOKOnly, "查询"
            frmtongji2.ZOrder (0)
            Exit Sub
        End If
        printstr = sql
    End If
    If Option11.Value = True Then
        Adodc1.ConnectionString = "Provider=MSDAORA.1;User ID=he;Persist Security Info=False"
        sql = "select * from course where cno in (select cno from choose where grade<60 group by cno having count(*)=(select max(count(*)) from choose where grade<60 group by cno))"
        Adodc1.CommandType = adCmdText
        Adodc1.RecordSource = sql
        Adodc1.Refresh
        Set MSF1.DataSource = Adodc1
        If Adodc1.Recordset.EOF = True Then
            MsgBox "对不起，没有符合要求的记录！", vbOKOnly, "查询"
            frmtongji2.ZOrder (0)
            Exit Sub
        End If
        printstr = sql
    End If
    If Option12.Value = True Then
        Adodc1.ConnectionString = "Provider=MSDAORA.1;User ID=he;Persist Security Info=False"
        sql = "select * from course where cno in (select cno from choose  group by cno having avg(grade)=(select max(avg(grade)) from choose  group by cno))"
        Adodc1.CommandType = adCmdText
        Adodc1.RecordSource = sql
        Adodc1.Refresh
        Set MSF1.DataSource = Adodc1
        If Adodc1.Recordset.EOF = True Then
            MsgBox "对不起，没有此课程的档案记录！", vbOKOnly, "查询"
            frmtongji2.ZOrder (0)
            Exit Sub
        End If
        printstr = sql
    End If
End Sub

Private Sub Command1_Click()
If Text1.Text = "" Then
    MsgBox "系别不能为空！", vbExclamation + vbOKOnly, "警告"
    Text1.SetFocus
    Exit Sub
End If
str1 = "student.dept = '" & Trim(Text1.Text) & "'"
Select Case Combo1.Text
    Case "20岁以下"
        Select Case Combo2.Text
            Case ""
                str = "select count(*) from student where student.age<20"
            Case "男"
                str = "select count(*) from student where student.age<20 and student.sex='man'"
            Case "女"
                 str = "select count(*) from student where student.age<20 and student.sex='woman'"
        End Select
    Case "20岁-25岁之间"
        Select Case Combo2.Text
            Case ""
                str = "select count(*) from student where student.age between 20 and 25"
            Case "男"
                str = "select count(*) from student where student.age between 20 and 25 and student.sex='man'"
            Case "女"
                 str = "select count(*) from student where student.age between 20 and 25 and student.sex='woman'"
        End Select
    Case "25岁以上"
        Select Case Combo2.Text
            Case ""
                str = "select count(*) from student where student.age>25"
            Case "男"
                str = "select count(*) from student where student.age>25 and student.sex='man'"
            Case "女"
                 str = "select count(*) from student where student.age>25 and student.sex='woman'"
        End Select
    Case ""
        Select Case Combo2.Text
            Case ""
                MsgBox "你至少要输入一个条件以上才能查询！", vbExclamation + vbOKOnly, " 警告"
                Combo1.SetFocus
                Exit Sub
            Case "男"
                str = "select count(*) from student where student.sex='man'"
            Case "女"
                 str = "select count(*) from student where student.sex='woman'"
        End Select
End Select
    Adodc1.ConnectionString = "Provider=MSDAORA.1;User ID=he;Persist Security Info=False"
    sql = str & " and " & str1
    Adodc1.CommandType = adCmdText
    Adodc1.RecordSource = sql
    Adodc1.Refresh
    If Adodc1.Recordset.EOF = True Then
        MsgBox "对不起，没有此课程的档案记录！", vbOKOnly, "查询"
        frmtongji2.ZOrder (0)
        Exit Sub
    End If
    msql = "select student.sno,student.name,student.sex,student.age,student.dept,student.class from student where " & str1 & "order by student.sno desc"
    frmtongji2.printstr = msql
    tongji2find = True
    frmtongji2.tongjishowtitle
    frmtongji2.tongjishowdata
End Sub

Private Sub Command2_Click()
    If Trim(printstr) = "" Then
        MsgBox "没有当前记录！", vbOKOnly + vbExclamation, "警告"
        Exit Sub
    End If
    If DataEnv1.rsCommand1.State = adStateOpen Then
        DataEnv1.rsCommand1.Close
    End If
    DataEnv1.rsCommand1.Open printstr
    If DataEnv1.rsCommand1.EOF = True Then
        MsgBox "没有当前记录！", vbOKOnly + vbExclamation, "警告"
        Exit Sub
    End If
    DataReportstudent.Show 1
End Sub

Private Sub Command3_Click()
If Text2.Text = "" Then
    MsgBox "任课教师不能为空！", vbExclamation + vbOKOnly, "警告"
    Text2.SetFocus
    Exit Sub
End If
    str1 = "course.teacher = '" & Trim(Text2.Text) & "'"
If Text4.Text = "" Then
    MsgBox "课程不能为空！", vbExclamation + vbOKOnly, " 警告"
    Text4.SetFocus
    Exit Sub
End If
    str = "course.cname = '" & Trim(Text4.Text) & "'"
    Adodc1.ConnectionString = "Provider=MSDAORA.1;User ID=he;Persist Security Info=False"
    sql = "select course.snum from course where " & str & " and " & str1
    Adodc1.CommandType = adCmdText
    Adodc1.RecordSource = sql
    Adodc1.Refresh
    If Adodc1.Recordset.EOF = True Then
        MsgBox "对不起，没有记录！", vbOKOnly, "查询"
        Exit Sub
    End If
    msql = "select course.cno,course.cname,course.teacher,course.profession,course.snum,course.course from course where " & str & " and " & str1 & "order by course.cno desc"
    frmtongji2.printstr = msql
    tongji2find = True
    frmtongji2.tongjishowtitle
    frmtongji2.tongjishowdata
End Sub

Private Sub Command31_Click()
If Text2.Text = "" Then
    MsgBox "任课教师不能为空！", vbExclamation + vbOKOnly, "警告"
    Text2.SetFocus
    Exit Sub
End If
    str1 = "course.teacher = '" & Trim(Text2.Text) & "'"
If Text4.Text = "" Then
    MsgBox "课程不能为空！", vbExclamation + vbOKOnly, " 警告"
    Text4.SetFocus
    Exit Sub
End If
    str = "course.cname = '" & Trim(Text4.Text) & "'"
    Adodc1.ConnectionString = "Provider=MSDAORA.1;User ID=he;Persist Security Info=False"
    sql = "select * from course where " & str & " and " & str1
    Adodc1.CommandType = adCmdText
    Adodc1.RecordSource = sql
    Adodc1.Refresh
    If Adodc1.Recordset.EOF = True Then
        MsgBox "对不起，没有记录！", vbOKOnly, "查询"
        Exit Sub
    End If
    msql = "select course.cno,course.cname,course.teacher,course.profession,course.snum,course.course from course where " & str & " and " & str1 & "order by course.cno desc"
    frmtongji2.printstr = msql
    tongji2find = True
    frmtongji2.tongjishowtitle
    frmtongji2.tongjishowdata
End Sub

Private Sub Command32_Click()
    If Trim(printstr) = "" Then
        MsgBox "没有当前记录！", vbOKOnly + vbExclamation, "警告"
        Exit Sub
    End If
    If DataEnv1.rsCommand4.State = adStateOpen Then
        DataEnv1.rsCommand4.Close
    End If
    DataEnv1.rsCommand4.Open printstr
    If DataEnv1.rsCommand4.EOF = True Then
        MsgBox "没有当前记录！", vbOKOnly + vbExclamation, "警告"
        Exit Sub
    End If
    DataReportcourse.Show 1
End Sub

Private Sub Command4_Click()
    If Trim(printstr) = "" Then
        MsgBox "没有当前记录！", vbOKOnly + vbExclamation, "警告"
        Exit Sub
    End If
    If DataEnv1.rsCommand1.State = adStateOpen Then
        DataEnv1.rsCommand1.Close
    End If
    DataEnv1.rsCommand1.Open printstr
    If DataEnv1.rsCommand1.EOF = True Then
        MsgBox "没有当前记录！", vbOKOnly + vbExclamation, "警告"
        Exit Sub
    End If
    DataReportstudent.Show 1
End Sub

Private Sub Command5_Click()
    If Text3.Text = "" Then
        MsgBox "学生姓名不能为空！", vbExclamation + vbOKOnly, "警告"
        Text3.SetFocus
        Exit Sub
    End If
        str1 = "student.name = '" & Trim(Text3.Text) & "'"
        Adodc1.ConnectionString = "Provider=MSDAORA.1;User ID=he;Persist Security Info=False"
        sql = "select * from course where cno in (select cno from choose where sno=(select sno from student where " & str1 & "))"
        Adodc1.CommandType = adCmdText
        Adodc1.RecordSource = sql
        Adodc1.Refresh
        Set MSF1.DataSource = Adodc1
        If Adodc1.Recordset.EOF = True Then
            MsgBox "对不起，没有记录！", vbExclamation + vbOKOnly, "查询"
            Exit Sub
        End If
    msql = "select student.sno,student.name,student.sex,student.age,student.dept,student.class from student where " & str1 & "order by student.sno desc"
    frmtongji2.printstr = msql
    tongji2find = True
    frmtongji2.tongjishowtitle
    frmtongji2.tongjishowdata
End Sub

Private Sub Form_Activate()
    jibenfind = True
    If tongjifind = True Then
        tongji2.ZOrder 0
    End If
    If tongjifind = True Then
        Exit Sub
    ElseIf tongjimodi = True Then
        tongjishowdata
        tongjishowtitle
    End If
End Sub

Private Sub Form_Load()
    With Combo1
            .AddItem "20岁以下"
            .AddItem "20岁-25岁之间"
            .AddItem "25岁以上"
    End With
     With Combo2
        .AddItem "男"
        .AddItem "女"
     End With
End Sub

Private Sub Form_LostFocus()
    Unload Me
End Sub

Private Sub Form_Unload(Cancel As Integer)
    tongjifind = False
End Sub
Public Sub tongjishowtitle()
Dim i As Integer
MSF1.Clear
  With MSF1
    .Cols = 10
    .TextMatrix(0, 1) = "总数"
    .ColWidth(0) = 200
    .FixedRows = 1
    .FillStyle = flexFillSingle
    .Col = 0
    .Row = 0
    .RowSel = 1
    .ColSel = .Cols - 1
    .CellAlignment = 4
    .Row = 1
    End With
End Sub

Public Sub tongjishowdata()
    MSF1.Clear
    Dim j As Integer
    Dim i As Integer
'    msql = ql()
    Adodc1.ConnectionString = "Provider=MSDAORA.1;User ID=he;Persist Security Info=False"
    Adodc1.CommandType = adCmdText
    Adodc1.RecordSource = sql
    Adodc1.Refresh
    If Adodc1.Recordset.EOF = False Then
    Adodc1.Recordset.MoveFirst
    With MSF1
        .Rows = 10
        .Row = 1
        Do While Not Adodc1.Recordset.EOF
        .Rows = .Rows + 1
        For i = 1 To Adodc1.Recordset.Fields.Count
        .TextMatrix(.Row, i) = Adodc1.Recordset.Fields(i - 1)
        Next i
        .Row = .Row + 1
        Adodc1.Recordset.MoveNext
        Loop
    End With
    Else
        If tongjifind = True Then
            MsgBox "对不起，没有此课程的记录！", vbOKOnly, "查询"
            frmtongji1.ZOrder 0
        End If
    End If
End Sub