frmcx.frm

学生成绩管理系统,适合与管理学生成绩的程序

            Charset         =   134
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Height          =   375
         Left            =   360
         TabIndex        =   16
         Top             =   480
         Width           =   1095
      End
      Begin VB.Label Label2 
         Alignment       =   1  'Right Justify
         BackStyle       =   0  'Transparent
         Caption         =   "姓  名"
         BeginProperty Font 
            Name            =   "宋体"
            Size            =   12
            Charset         =   134
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Height          =   375
         Left            =   360
         TabIndex        =   15
         Top             =   960
         Width           =   1095
      End
   End
   Begin VB.Frame Frame2 
      BackColor       =   &H00E0F8DE&
      Height          =   615
      Left            =   0
      TabIndex        =   24
      Top             =   5400
      Width           =   9015
      Begin VB.ComboBox Combo3 
         Height          =   300
         ItemData        =   "frmCx.frx":09CA
         Left            =   4800
         List            =   "frmCx.frx":09D7
         TabIndex        =   33
         Text            =   "Combo1"
         Top             =   270
         Width           =   975
      End
      Begin VB.TextBox TxtCx3 
         BeginProperty Font 
            Name            =   "宋体"
            Size            =   12
            Charset         =   134
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Height          =   375
         Left            =   6720
         MaxLength       =   5
         TabIndex        =   32
         Text            =   "TxtCx"
         Top             =   240
         Width           =   735
      End
      Begin VB.ComboBox Combo4 
         Height          =   300
         ItemData        =   "frmCx.frx":09ED
         Left            =   5880
         List            =   "frmCx.frx":09FA
         TabIndex        =   31
         Text            =   "Combo1"
         Top             =   270
         Width           =   735
      End
      Begin VB.ComboBox Combo2 
         Height          =   300
         ItemData        =   "frmCx.frx":0A07
         Left            =   2400
         List            =   "frmCx.frx":0A17
         TabIndex        =   29
         Text            =   "Combo2"
         Top             =   240
         Width           =   975
      End
      Begin VB.TextBox TxtCx2 
         Height          =   300
         Left            =   3480
         TabIndex        =   28
         Top             =   255
         Width           =   855
      End
      Begin VB.CommandButton Command1 
         BackColor       =   &H00E0F8DE&
         Caption         =   "查询"
         BeginProperty Font 
            Name            =   "宋体"
            Size            =   12
            Charset         =   134
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Height          =   375
         Left            =   7560
         Style           =   1  'Graphical
         TabIndex        =   27
         Top             =   160
         Width           =   1335
      End
      Begin VB.TextBox TxtCx1 
         Height          =   300
         Left            =   1200
         TabIndex        =   26
         Top             =   240
         Width           =   855
      End
      Begin VB.ComboBox Combo1 
         Height          =   300
         ItemData        =   "frmCx.frx":0A33
         Left            =   120
         List            =   "frmCx.frx":0A43
         TabIndex        =   25
         Text            =   "Combo1"
         Top             =   200
         Width           =   975
      End
      Begin VB.Label Label12 
         BackStyle       =   0  'Transparent
         Caption         =   "and"
         Height          =   255
         Left            =   4440
         TabIndex        =   34
         Top             =   315
         Width           =   375
      End
      Begin VB.Label Label11 
         BackStyle       =   0  'Transparent
         Caption         =   "and"
         Height          =   255
         Left            =   2040
         TabIndex        =   30
         Top             =   240
         Width           =   375
      End
   End
   Begin VB.Label Label1 
      Alignment       =   2  'Center
      BackStyle       =   0  'Transparent
      Caption         =   "学生管理系统"
      BeginProperty Font 
         Name            =   "隶书"
         Size            =   21.75
         Charset         =   134
         Weight          =   400
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      ForeColor       =   &H00FF8080&
      Height          =   495
      Left            =   2400
      TabIndex        =   12
      Top             =   120
      Width           =   4215
   End
End
Attribute VB_Name = "frmCx"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Option Explicit
Dim xsjl As New ADODB.Recordset
Dim jfm As New ADODB.Recordset
Dim Cj As New ADODB.Recordset
Dim SQL As String

Private Sub Clear()
    TxtXH.Text = ""
    TxtXM.Text = ""
    TxtXB.Text = ""
    TxtBJ.Text = ""
    TxtYw.Text = ""
    TxtSx.Text = ""
    TxtYy.Text = ""
    Combo1.Text = "学号"
    TxtCx1.Text = ""
    Combo2.Text = "姓名"
    TxtCx2.Text = ""
    Combo3.Text = "语文"
    Combo4.Text = ">"
    TxtCx3.Text = "0"
End Sub

Private Sub CmdClear_Click()
    Call Clear
End Sub

Private Sub CmdDelete_Click()
    If TxtXH.Text = "" Then
        MsgBox "学号不能为空！", vbOKOnly, "information"
        TxtXH.SetFocus
        Exit Sub
    End If
    SQL = "delete from 学生档案 where 学号='" & TxtXH.Text & "'"
    cnn.Execute (SQL)
    Call Data_Display
    Call Clear
End Sub

Private Sub CmdExit_Click()
    Unload Me
End Sub

Private Sub CmdSave_Click()
    'On Error Resume Next
    Dim Msg As String
    Dim Sid As Integer
    
    '检查是否存在该学生生数据
    If TxtXH.Text = "" Then
        MsgBox "学号不能为空！", vbOKOnly, "information"
        TxtXH.SetFocus
        Exit Sub
    End If
    If IsNumeric(TxtYw.Text) = False Then
        MsgBox "语文成绩为数字，请重新输入！", vbOKOnly, "information"
        TxtYw.SetFocus
        Exit Sub
    End If
    If IsNumeric(TxtSx.Text) = False Then
        MsgBox "数学成绩为数字，请重新输入！", vbOKOnly, "information"
        TxtSx.SetFocus
        Exit Sub
    End If
    If IsNumeric(TxtYy.Text) = False Then
        MsgBox "英语成绩为数字，请重新输入！", vbOKOnly, "information"
        TxtYy.SetFocus
        Exit Sub
    End If
        
    SQL = "select 学号 from 学生档案 where 学号='" & TxtXH.Text & "'"
    Set xsjl = cnn.Execute(SQL)
    If xsjl.BOF And xsjl.EOF Then
        SQL = "insert into 学生档案 (学号,姓名,性别,班级) "
        SQL = SQL & "values ('" & TxtXH.Text & "','" & TxtXM.Text & "','" & TxtXB.Text & "','" & TxtBJ.Text & "')"
        cnn.Execute (SQL)
        SQL = "select max(s_id) from 学生档案"
        Set jfm = cnn.Execute(SQL)
            
        SQL = "insert into 学生成绩 (s_id,学习科目,成绩) values (" & jfm(0) & ",'语文','" & TxtYw.Text & "')"
        cnn.Execute (SQL)
        SQL = "insert into 学生成绩 (s_id,学习科目,成绩) values (" & jfm(0) & ",'数学','" & TxtSx.Text & "')"
        cnn.Execute (SQL)
        SQL = "insert into 学生成绩 (s_id,学习科目,成绩) values (" & jfm(0) & ",'英语','" & TxtYy.Text & "')"
        cnn.Execute (SQL)
            
        MsgBox "存盘成功！", vbOKOnly, "information"
    Else
        SQL = "update 学生档案 set 姓名='" & TxtXM.Text & "',性别='" & TxtXB.Text & "',班级='" & TxtBJ.Text & "' where 学号='" & TxtXH.Text & "'"
        cnn.Execute (SQL)
        SQL = "select s_id from 学生档案 where 学号='" & TxtXH.Text & "'"
        Set jfm = cnn.Execute(SQL)
        SQL = "update 学生成绩 set 成绩='" & TxtYw.Text & "' where s_id=" & jfm(0) & " and 学习科目='语文'"
        cnn.Execute (SQL)
        SQL = "update 学生成绩 set 成绩='" & TxtSx.Text & "' where s_id=" & jfm(0) & " and 学习科目='数学'"
        cnn.Execute (SQL)
        SQL = "update 学生成绩 set 成绩='" & TxtYy.Text & "' where s_id=" & jfm(0) & " and 学习科目='英语'"
        cnn.Execute (SQL)
        
        MsgBox "修改成功！", vbOKOnly, "information"
    End If
    
    Call Data_Display
End Sub

Private Sub Command1_Click()
    TxtCx1.Text = Trim(TxtCx1.Text)
    TxtCx2.Text = Trim(TxtCx2.Text)
    
    If TxtCx1.Text <> "" And TxtCx2.Text <> "" And TxtCx3.Text <> "" Then
        SQL = "select * from VIEW1 where " & Combo1.Text & " like '" & TxtCx1.Text & "' and " & Combo2.Text & " like '" & TxtCx2.Text & "' and 学习科目='" & Combo3.Text & "' and 成绩" & Combo4.Text & TxtCx3.Text
        GoTo cont1
    End If
    If TxtCx1.Text <> "" And TxtCx2.Text <> "" Then
        SQL = "select * from VIEW1 where " & Combo1.Text & " like '" & TxtCx1.Text & "' and " & Combo2.Text & " like '" & TxtCx2.Text & "'"
        GoTo cont1
    End If
    If TxtCx1.Text <> "" Then
        SQL = "select * from VIEW1 where " & Combo1.Text & " like '" & TxtCx1.Text & "'"
        GoTo cont1
    End If
    If TxtCx2.Text <> "" Then
        SQL = "select * from VIEW1 where " & Combo2.Text & " like '" & TxtCx2.Text & "'"
        GoTo cont1
    End If
    If TxtCx3.Text > 0 Then
        If IsNumeric(TxtCx3.Text) = False Then
            MsgBox "成绩为数字，请重新输入！", vbOKOnly, "information"
            TxtCx3.SetFocus
            Exit Sub
        End If
        SQL = "select * from VIEW1 where 学习科目='" & Combo3.Text & "' and 成绩" & Combo4.Text & TxtCx3.Text
        GoTo cont1
    End If
    
    SQL = "select * from VIEW1"
    
cont1:
    Set jfm = cnn.Execute(SQL)
    Call Data_Display
End Sub

Private Sub Form_Load()
    TextJls.Text = ""
    'cnn.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;User ID=sa;Initial Catalog=student;Data Source=."
    'cnn.Open
    Call Clear
    Call MSFG_Clear
    
  '  SQL = "select * from VIEW1"
  '  Set jfm = cnn.Execute(SQL)
  '  Call Data_Display
End Sub

Private Sub MSFG_Clear()
    MSFlexGrid1.ColWidth(0) = 800
    MSFlexGrid1.ColAlignment(0) = flexAlignCenterCenter
    MSFlexGrid1.ColWidth(1) = 800
    MSFlexGrid1.ColWidth(2) = 1400
    MSFlexGrid1.ColWidth(3) = 800
    MSFlexGrid1.ColWidth(4) = 1300
    MSFlexGrid1.ColWidth(5) = 1200
    MSFlexGrid1.ColWidth(6) = 1200
    MSFlexGrid1.ColWidth(7) = 1200
    MSFlexGrid1.Rows = 1
End Sub

Private Sub Data_Display()
    Dim i As Integer
    
    MSFlexGrid1.Rows = 1 '清空表
    If (Not jfm.BOF) And (Not jfm.EOF) Then
        i = 0
        jfm.MoveFirst
        Do While Not jfm.EOF
            MSFlexGrid1.Rows = MSFlexGrid1.Rows + 1
            i = i + 1
            MSFlexGrid1.TextMatrix(MSFlexGrid1.Rows - 1, 0) = i
            MSFlexGrid1.TextMatrix(MSFlexGrid1.Rows - 1, 1) = Trim(jfm("学号"))
            MSFlexGrid1.TextMatrix(MSFlexGrid1.Rows - 1, 2) = Trim(jfm("姓名"))
            MSFlexGrid1.TextMatrix(MSFlexGrid1.Rows - 1, 3) = Trim(jfm("性别"))
            MSFlexGrid1.TextMatrix(MSFlexGrid1.Rows - 1, 4) = Trim(jfm("班级"))
    
            Do While Not jfm.EOF
                If MSFlexGrid1.TextMatrix(MSFlexGrid1.Rows - 1, 1) = Trim(jfm("学号")) Then
                    If Trim(jfm("学习科目")) = "语文" Then
                        MSFlexGrid1.TextMatrix(MSFlexGrid1.Rows - 1, 5) = Trim(jfm("学习成绩"))
                    End If
                    If Trim(jfm("学习科目")) = "数学" Then
                        MSFlexGrid1.TextMatrix(MSFlexGrid1.Rows - 1, 6) = Trim(jfm("学习成绩"))
                    End If
                    If Trim(jfm("学习科目")) = "英语" Then
                        MSFlexGrid1.TextMatrix(MSFlexGrid1.Rows - 1, 7) = Trim(jfm("学习成绩"))
                    End If
                Else
                    Exit Do
                End If
                jfm.MoveNext
            Loop
        Loop
        TextJls.Text = i
    End If
End Sub

Private Sub MSFlexGrid1_Click()
    TxtXH.Text = MSFlexGrid1.TextMatrix(MSFlexGrid1.Row, 1)
    TxtXM.Text = MSFlexGrid1.TextMatrix(MSFlexGrid1.Row, 2)
    TxtXB.Text = MSFlexGrid1.TextMatrix(MSFlexGrid1.Row, 3)
    TxtBJ.Text = MSFlexGrid1.TextMatrix(MSFlexGrid1.Row, 4)
    TxtYw.Text = MSFlexGrid1.TextMatrix(MSFlexGrid1.Row, 5)
    TxtSx.Text = MSFlexGrid1.TextMatrix(MSFlexGrid1.Row, 6)
    TxtYy.Text = MSFlexGrid1.TextMatrix(MSFlexGrid1.Row, 7)

End Sub

Private Sub MSFlexGrid1_RowColChange()
    Call MSFlexGrid1_Click
End Sub