frmserach2.frm

一个vb的mis系统

            strobj = stuobj2.Text
        End If
        If stuobj3.Visible = True Then
            strobj = stuobj3.Text
        End If
        
        '定义科目代码
        Dim obj As Integer
        Select Case strobj
            Case "计算机基础", "C语言", "SQL数据库", "VB可视化编程", "HTML网页编程"
                obj = 1
            Case "中级SQL", "JAVA编程", "ASP网络编程", "软件测试与保证", "C++编程"
                obj = 2
            Case "OOAD分析设计", "Oracle数据库", "J2EE开发", "WinForm开发", ".NET & C#"
                obj = 3
        End Select
        
        Select Case obj
            Case 1
                DataEnvironment1.rsCommand2.Open showtsql.Text
                DataReport2.PrintReport True
                DataEnvironment1.rsCommand2.Close
                Exit Sub
            Case 2
                DataEnvironment1.rsCommand3.Open showtsql.Text
                DataReport3.PrintReport True
                DataEnvironment1.rsCommand3.Close
                Exit Sub
            Case 3
                DataEnvironment1.rsCommand4.Open showtsql.Text
                DataReport4.PrintReport True
                DataEnvironment1.rsCommand4.Close
                Exit Sub
        End Select
    End If
End Sub
'>>>>>>>>>打印功能<<<<<<<<<

'>>>>>>>>>保存功能<<<<<<<<<
Private Sub pic_save_Click()
    '检查数据
    If chk = False Then
        Exit Sub
    End If
    
    '刷新预览屏
    Call comserach_Click
    
    '两种情况：保存学员信息表；保存学员成绩表
    '保存学员信息表：
    If Check5.Value = 0 Then
        DataEnvironment1.rsCommand1.Open showtsql.Text
        DataReport1.ExportReport rptKeyText, "C:\ACCP学员管理系统模糊查询资料（学员信息）.txt", True, False
        If frmmain.check_have.Value = 0 Then
        Set publicmbox.Picture = publicmbox.picsave.Picture: publicmbox.Show vbModal
        End If
        DataEnvironment1.rsCommand1.Close
        Exit Sub
    Else
    '保存学员成绩表（分为三个学期）
        '取得成绩科目的值
        Dim strobj As String
        If stuobj0.Visible = True Then
            strobj = stuobj0.Text
        End If
        If stuobj1.Visible = True Then
            strobj = stuobj1.Text
        End If
        If stuobj2.Visible = True Then
            strobj = stuobj2.Text
        End If
        If stuobj3.Visible = True Then
            strobj = stuobj3.Text
        End If
        
        '定义科目代码
        Dim obj As Integer
        Select Case strobj
            Case "计算机基础", "C语言", "SQL数据库", "VB可视化编程", "HTML网页编程"
                obj = 1
            Case "中级SQL", "JAVA编程", "ASP网络编程", "软件测试与保证", "C++编程"
                obj = 2
            Case "OOAD分析设计", "Oracle数据库", "J2EE开发", "WinForm开发", ".NET & C#"
                obj = 3
        End Select
        
        Select Case obj
            Case 1
                DataEnvironment1.rsCommand2.Open showtsql.Text
                DataReport2.ExportReport rptKeyText, "C:\ACCP学员管理系统模糊查询资料（一学期成绩信息）.txt", True, False
                If frmmain.check_have.Value = 0 Then
                Set publicmbox.Picture = publicmbox.picsave.Picture: publicmbox.Show vbModal
                End If
                DataEnvironment1.rsCommand2.Close
                Exit Sub
            Case 2
                DataEnvironment1.rsCommand3.Open showtsql.Text
                DataReport3.ExportReport rptKeyText, "C:\ACCP学员管理系统模糊查询资料（二学期成绩信息）.txt", True, False
                If frmmain.check_have.Value = 0 Then
                Set publicmbox.Picture = publicmbox.picsave.Picture: publicmbox.Show vbModal
                End If
                DataEnvironment1.rsCommand3.Close
                Exit Sub
            Case 3
                DataEnvironment1.rsCommand4.Open showtsql.Text
                DataReport4.ExportReport rptKeyText, "C:\ACCP学员管理系统模糊查询资料（三学期成绩信息）.txt", True, False
                If frmmain.check_have.Value = 0 Then
                Set publicmbox.Picture = publicmbox.picsave.Picture: publicmbox.Show vbModal
                End If
                DataEnvironment1.rsCommand4.Close
                Exit Sub
        End Select
    End If
End Sub
'>>>>>>>>>保存功能<<<<<<<<<




'>>>>>>>>>>>>>>已经选好科目时再选择其他学期时及时切换相对应的科目以避免查询失败<<<<<<<<<<<<<<<<<<<<<<<<<
Private Sub stuterm_click()
    Call Check5_Click: Call Check5_Click
End Sub
'>>>>>>>>>>>>>>已经选好科目时再选择其他学期时及时切换相对应的科目以避免查询失败<<<<<<<<<<<<<<<<<<<<<<<<<



'产生可用的T－SQL查询语句
Private Sub comserach_Click()
    If chk = False Then
        Exit Sub
    End If
    
    '初始化表格
    Adodc1.RecordSource = "select * from stuinfo"
    Adodc1.Refresh

    Dim sqltri As String  '用于最终的合并语句
    Dim sqltri1 As String  '用于开头语句
    Dim sqltri2 As String  '用于第一种情况的语句
    Dim sqltri3 As String  '用于第二种情况的语句
    
    sqltri1 = "select * from "
    sqltri2 = "between " & stumak1.Text & " and " & stumak2.Text
    
    
    '三种情况：一是当只有最后一个选中时只搜索相关的学期成绩表，二是当最后一项没选中时，只搜索STUINFO表，三是当最后一项和其他某项或某些项被选中时需要同时搜索STUINFO和相关的成绩表。
    '>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>.情况一：只有成绩限定项选中（即搜索只在三个学员成绩表中进行）<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
    If Check5.Value = 1 And Check1.Value = 0 And Check2.Value = 0 And Check3.Value = 0 And Check4.Value = 0 Then
        
        Select Case Trim(stuobj0.Text)
            '第一学期的处理
            Case "计算机基础", "C语言", "SQL数据库", "VB可视化编程", "HTML网页编程"
                sqltri = sqltri1 & "stumak1 where " & stuobj0.Text & " " & sqltri2
                Adodc1.RecordSource = sqltri
                Adodc1.Refresh
                Call formatstumak1
                
            '第二学期的处理
            Case "C++编程"   'C++编程科目由于名称特殊，必须单独提出来加入"[]"后导入T－SQL
                sqltri = sqltri1 & "stumak2 where [C++编程] " & sqltri2
                Adodc1.RecordSource = sqltri
                Adodc1.Refresh
                Call formatstumak2
            Case "中级SQL", "JAVA编程", "ASP网络编程", "软件测试与保证"
                sqltri = sqltri1 & "stumak2 where " & stuobj0.Text & " " & sqltri2
                Adodc1.RecordSource = sqltri
                Adodc1.Refresh
                Call formatstumak2
                
            '第三学期的处理
            Case ".NET & C#"   '.NET & C#科目由于名称特殊，必须单独提出来加入"[]"后导入T－SQL
                sqltri = sqltri & "stumak3 where [.NET & C#] " & sqltri2
                Adodc1.RecordSource = sqltri
                Adodc1.Refresh
                Call formatstumak3
            Case "OOAD分析设计", "Oracle数据库", "J2EE开发", "WinForm开发"
                sqltri = sqltri & "stumak3 where " & stuobj0.Text & " " & sqltri2
                Adodc1.RecordSource = sqltri
                Adodc1.Refresh
                Call formatstumak3
        End Select
        showtsql.Text = sqltri
        Exit Sub
    End If
    '>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>.情况一结束<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
    
    '>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>.情况二：成绩限定项未选中（即搜索只在学员信息表中进行）<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
    If Check5.Value = 0 Then
        sqltri1 = sqltri1 & "stuinfo where 1=1 "   '初始化sqltri1使之适合用于查询stuinfo表
        '定义每一个字段的分T-SQL语句
        Dim sqltri_term As String
        Dim sqltri_sex As String
        Dim sqltri_name As String
        Dim sqltri_age As String
            
            If Check1.Value = 1 Then                           '学期字段的T-SQL片断
                sqltri_term = "and Stu_term='" & stuterm.Text & "'"
            End If
            
            If Check2.Value = 1 Then                           '性别字段的T-SQL片断
                sqltri_sex = "and Stu_sex='" & stusex.Text & "'"
            End If
            
            If Check3.Value = 1 Then                           '姓名字段的T-SQL片断
                sqltri_name = "and Stu_name LIKE '%" & stuname.Text & "%'"
            End If
            
            If Check4.Value = 1 Then                           '年龄字段的T-SQL片断
                sqltri_age = "and Stu_age between " & stuage1.Text & " and " & stuage2.Text
            End If
        '最后合并所有字段
        sqltri = sqltri1 & sqltri_term & sqltri_sex & sqltri_name & sqltri_age
        
        Adodc1.RecordSource = sqltri
        Adodc1.Refresh
        Call formatstuinfo
        showtsql.Text = sqltri
        Exit Sub
    End If
    '>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>.情况二结束<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
    '第三种情况：成绩限定被选中和成绩限定上面中有一项或多项被选中（即搜索需要在三个学员成绩表中的任意一个与学员信息表一起进行）
    If Check5.Value = 1 Then
    If Check1.Value = 1 Or Check2.Value = 1 Or Check3.Value = 1 Or Check4.Value = 1 Then
    
    '取得成绩科目的值
    Dim strobj As String
    If stuobj0.Visible = True Then
        strobj = stuobj0.Text
    End If
    If stuobj1.Visible = True Then
        strobj = stuobj1.Text
    End If
    If stuobj2.Visible = True Then
        strobj = stuobj2.Text
    End If
    If stuobj3.Visible = True Then
        strobj = stuobj3.Text
    End If
    
    '取得其他限定条件的值
    If Check2.Value = 1 Then                           '性别字段的T-SQL片断
        sqltri_sex = "and stuinfo.Stu_sex='" & stusex.Text & "'"
    End If
    If Check3.Value = 1 Then                           '姓名字段的T-SQL片断
        sqltri_name = "and stuinfo.Stu_name LIKE '%" & stuname.Text & "%'"
    End If
    If Check4.Value = 1 Then                           '年龄字段的T-SQL片断
        sqltri_age = "and stuinfo.Stu_age between " & stuage1.Text & " and " & stuage2.Text
    End If
    
    '以下开始合并T-sql
    Select Case Trim(strobj)
        '第一学期成绩表和学员信息表一起查询
        Case "计算机基础", "C语言", "SQL数据库", "VB可视化编程", "HTML网页编程"
            '加成绩限定
            sqltri = "select stumak1.* from stuinfo ,stumak1 where 1=1 and stuinfo.stu_id=stumak1.学生ID and " & Trim(strobj) & " between " & Trim(stumak1.Text) & " and " & Trim(stumak2.Text)
            sqltri = sqltri & sqltri_sex & sqltri_name & sqltri_age
            Adodc1.RecordSource = sqltri
            Adodc1.Refresh
            Call formatstumak1
            
        '第二学期成绩表和学员信息表一起查询
        Case "中级SQL", "JAVA编程", "ASP网络编程", "软件测试与保证"
            '加成绩限定
            sqltri = "select stumak2.* from stuinfo ,stumak2 where 1=1 and stuinfo.stu_id=stumak2.学生ID and " & Trim(strobj) & " between " & Trim(stumak1.Text) & " and " & Trim(stumak2.Text)
            sqltri = sqltri & sqltri_sex & sqltri_name & sqltri_age
            Adodc1.RecordSource = sqltri
            Adodc1.Refresh
            Call formatstumak2
        Case "C++编程"   'C++编程科目由于名称特殊，必须单独提出来加入"[]"后导入T－SQL
            '加成绩限定
            sqltri = "select stumak2.* from stuinfo ,stumak2 where 1=1 and stuinfo.stu_id=stumak2.学生ID and [C++编程] between " & Trim(stumak1.Text) & " and " & Trim(stumak2.Text)
            sqltri = sqltri & sqltri_sex & sqltri_name & sqltri_age
            Adodc1.RecordSource = sqltri
            Adodc1.Refresh
            Call formatstumak2
        
        '第三学期成绩表和学员信息表一起查询
        Case "OOAD分析设计", "Oracle数据库", "J2EE开发", "WinForm开发"
            '加成绩限定
            sqltri = "select stumak3.* from stuinfo ,stumak3 where 1=1 and stuinfo.stu_id=stumak3.学生ID and " & Trim(strobj) & " between " & Trim(stumak1.Text) & " and " & Trim(stumak2.Text)
            sqltri = sqltri & sqltri_sex & sqltri_name & sqltri_age
            Adodc1.RecordSource = sqltri
            Adodc1.Refresh
            Call formatstumak3
        Case ".NET & C#"   'C++编程科目由于名称特殊，必须单独提出来加入"[]"后导入T－SQL
            '加成绩限定
            sqltri = "select stumak3.* from stuinfo ,stumak3 where 1=1 and stuinfo.stu_id=stumak3.学生ID and [.NET & C#] between " & Trim(stumak1.Text) & " and " & Trim(stumak2.Text)
            sqltri = sqltri & sqltri_sex & sqltri_name & sqltri_age
            Adodc1.RecordSource = sqltri
            Adodc1.Refresh
            Call formatstumak3
    End Select
    showtsql.Text = sqltri
    End If
    End If
End Sub






'>>>>>>>>>检查功能<<<<<<<<<
Public Function chk() As Boolean
    chk = True
    
    If Check1.Value = 0 And Check2.Value = 0 And Check3.Value = 0 And Check4.Value = 0 And Check5.Value = 0 Then
        If frmmain.check_have.Value = 0 Then
        Set publicmbox.Picture = publicmbox.pic16.Picture: publicmbox.Show vbModal
        End If
        chk = False
    End If
    
    '关于名字的检查
    If pictxt6.Visible = True And Trim(stuname.Text) = "" Then
        If frmmain.check_have.Value = 0 Then
        Set publicmbox.Picture = publicmbox.pic17.Picture: publicmbox.Show vbModal
        End If
        chk = False
        Exit Function
    End If
    
    '关于年龄的检查
    If fstuage.Visible = True Then
        '年龄至少其中一个为空的处理
        If Trim(stuage1.Text) = "" Or Trim(stuage2.Text) = "" Then
            If frmmain.check_have.Value = 0 Then
            Set publicmbox.Picture = publicmbox.pic18.Picture: publicmbox.Show vbModal
            End If
            chk = False
            Exit Function
        End If
        '年龄其中一个有非数字字符的处理
        If Not IsNumeric(Trim(stuage1.Text)) Or Not IsNumeric(Trim(stuage2.Text)) Then
            If frmmain.check_have.Value = 0 Then
            Set publicmbox.Picture = publicmbox.pic19.Picture: publicmbox.Show vbModal
            End If
            chk = False
            Exit Function
        End If
        '年龄不在18～60之间的处理
        If CInt(Trim(stuage1.Text)) < 18 Or CInt(Trim(stuage1.Text)) > 59 Then
            If frmmain.check_have.Value = 0 Then
            Set publicmbox.Picture = publicmbox.pic20.Picture: publicmbox.Show vbModal
            End If
            chk = False
            Exit Function
        End If
        If CInt(Trim(stuage2.Text)) < 18 Or CInt(Trim(stuage2.Text)) > 59 Then
            If frmmain.check_have.Value = 0 Then
            Set publicmbox.Picture = publicmbox.pic21.Picture: publicmbox.Show vbModal
            End If
            chk = False
            Exit Function
        End If
        '年龄大小顺序颠倒的处理
        If CInt(Trim(stuage1.Text)) > CInt(Trim(stuage2.Text)) Then
            If frmmain.check_have.Value = 0 Then
            Set publicmbox.Picture = publicmbox.pic21.Picture: publicmbox.Show vbModal
            End If
            chk = False
            Exit Function
        End If
    End If
    
    '关于成绩的检查
    If fstumak.Visible = True Then
        '成绩至少其中一个为空的处理
        If Trim(stuage1.Text) = "" Or Trim(stuage2.Text) = "" Then
            If frmmain.check_have.Value = 0 Then
            Set publicmbox.Picture = publicmbox.pic22.Picture: publicmbox.Show vbModal
            End If
            chk = False
            Exit Function
        End If
        '成绩中有非数字字符的处理
        If Not IsNumeric(Trim(stumak1.Text)) Or Not IsNumeric(Trim(stumak2.Text)) Then
            If frmmain.check_have.Value = 0 Then
            Set publicmbox.Picture = publicmbox.pic23.Picture: publicmbox.Show vbModal
            End If
            chk = False
            Exit Function
        End If
        '成绩不在0～100之间的处理
        If CInt(Trim(stumak1.Text)) < 0 Or CInt(Trim(stumak1.Text)) > 100 Then
            If frmmain.check_have.Value = 0 Then
            Set publicmbox.Picture = publicmbox.pic24.Picture: publicmbox.Show vbModal
            End If
            chk = False
            Exit Function
        End If
        If CInt(Trim(stumak2.Text)) < 0 Or CInt(Trim(stumak2.Text)) > 100 Then
            If frmmain.check_have.Value = 0 Then
            Set publicmbox.Picture = publicmbox.pic24.Picture: publicmbox.Show vbModal
            End If
            chk = False
            Exit Function
        End If
        '成绩大小顺序颠倒的处理
        If CInt(Trim(stumak1.Text)) > CInt(Trim(stumak2.Text)) Then
            If frmmain.check_have.Value = 0 Then
            Set publicmbox.Picture = publicmbox.pic25.Picture: publicmbox.Show vbModal
            End If
            chk = False
            Exit Function
        End If
    End If
End Function
'>>>>>>>>>检查功能结束<<<<<<<<<