cnn.Execute (SQL)
       ' 刪除學生選課信息
        SQL = "delete from jc_sc where sno='" & TxtXH.Text & "'"
        On Error Resume Next
        cnn.Execute (SQL)
    
    End If
    
    Call getAllRecords
End Sub

Private Sub cmdExit_Click()
    Unload Me
End Sub


Private Sub cmdSave_Click()
    
    
    '檢查是否存在該學生生數據
    If TxtXH.Text = "" Then
        Call showMsg("學號不能為空！", 1)
        Exit Sub
    End If
    
        
        SQL = "select sno from jc_s where sno='" & TxtXH.Text & "'"
        On Error Resume Next
        Set xsjl = cnn.Execute(SQL)
        
        On Error Resume Next
        If xsjl.BOF And xsjl.EOF Then
    
       SQL = "insert into jc_c(sno,sname,sex,age,dept) "
       SQL = SQL & " values ('" & TxtXH.Text & "','" & TxtXM.Text & "','" & cobXB.Text & "','" & cobNL.Text & "','" & TxtZY.Text & "')"
       
       On Error Resume Next
       cnn.Execute (SQL)

       Call showMsg("添加 " & TxtXM.Text & " 的信息成功！", 1)
       
    Else
        SQL = "update jc_s set sname='" & TxtXM.Text & "',sex='" & cobXB.Text & "',age='" & cobNL.Text & "',dept='" & TxtZY.Text & "' where sno='" & TxtXH.Text & "'"
        
        On Error Resume Next
        cnn.Execute (SQL)
        SQL = "select sno from jc_s where sno='" & TxtXH.Text & "'"
        
        On Error Resume Next
        Set xsjl = cnn.Execute(SQL)
        
        Call showMsg("修改 " & TxtXM.Text & "的信息成功！", 1)
    End If
    
    Call getAllRecords
End Sub

Private Sub cmdFind_Click()

     Dim strKeyword As String
    strKeyword = Trim(TxtKeyword.Text)
    
   
    If cobField.Text = "" Then
        Call showMsg("請選擇查詢方式！", 1)
        cobField.SetFocus
        Exit Sub
    ElseIf strKeyword = "" Then
        Call showMsg("請輸入關鍵字！", 1)
        TxtKeyword.Enabled = True
        TxtKeyword.SetFocus
        Exit Sub
    Else
    
        If strFieldName <> "" Then
        SQL = "select sno,sname,sex,age,dept from jc_s where " & strFieldName & " like '%" & strKeyword & "%'"
        
        On Error Resume Next
        Set xsjl = cnn.Execute(SQL)
        
        Call Data_Display
        SQL = "select count(sno) as snums from jc_s where " & strFieldName & " like '%" & strKeyword & "%'"
        getRecordsNums SQL
        End If
    End If

End Sub
Function getRecordsNums(ByVal TmpSql As String) As String
    Dim rs As ADODB.Recordset
    On Error Resume Next
    Set rs = cnn.Execute(TmpSql)
    lbScounts.Caption = "當前共" & rs("snums") & "個學生信息"
    getRecordsNums = lbScounts.Caption
End Function

Private Sub getAllRecords()
    
   SQL = "select count(sno) as snums from jc_s"
   getRecordsNums SQL

    SQL = "select sno,sname,sex,age,dept from jc_s order by sno desc "
    On Error Resume Next
    Set xsjl = cnn.Execute(SQL)
    
    Call Data_Display
    
End Sub
Private Sub getFieldOrd()
    If strFieldName <> "" Then
        SQL = "select sno,sname,sex,age,dept from jc_s order by " & strFieldName
            If StrOrd <> "" Then
            SQL = SQL & " " & StrOrd
            End If
        On Error Resume Next
        Set xsjl = cnn.Execute(SQL)
        Call Data_Display
    End If
End Sub




Private Sub cobField_Change()
Call cobField_Click
End Sub

Private Sub cobField_Click()
    '得到所選擇的排序方式并轉化為字段名
    Select Case cobField.ListIndex
        Case 0: strFieldName = "sno"
        Case 1: strFieldName = "sname"
        Case 2: strFieldName = "sex"
        Case 3: strFieldName = "age"
        Case 4: strFieldName = "dept"
        Case Else: strFieldName = "sno"
    End Select
    TxtKeyword.Enabled = True
End Sub

Private Sub cobOrd_Click()

    '得到所選擇的排列升降序方式
     Select Case cobOrd.ListIndex
        Case 0: StrOrd = "asc"
        Case 1: StrOrd = "desc"
        Case Else: StrOrd = "asc"
    End Select
    
    Call getFieldOrd
End Sub

Private Sub cobOrdField_Click()

    '得到所選擇的排序方式并轉化為字段名
    Select Case cobOrdField.ListIndex
        Case 0: strFieldName = "sno"
        Case 1: strFieldName = "sname"
        Case 2: strFieldName = "sex"
        Case 3: strFieldName = "age"
        Case 4: strFieldName = "dept"
        Case Else: strFieldName = "sno"
    End Select
    
    Call getFieldOrd
End Sub

Private Sub Form_Activate()

    '初始化下拉列表,Style均為 2 -Dropdown List
    cobXB.AddItem ("男")
    cobXB.AddItem ("女")
    
    cobNL.AddItem ("18")
    cobNL.AddItem ("19")
    cobNL.AddItem ("20")
    cobNL.AddItem ("21")
    cobNL.AddItem ("22")
    cobNL.AddItem ("23")
    cobNL.AddItem ("24")
    cobNL.AddItem ("25")
    
    cobField.AddItem "學號", 0
    cobField.AddItem "姓名", 1
    cobField.AddItem "性別", 2
    cobField.AddItem "年齡", 3
    cobField.AddItem "系別", 4
    
    cobOrdField.AddItem "學號", 0
    cobOrdField.AddItem "姓名", 1
    cobOrdField.AddItem "性別", 2
    cobOrdField.AddItem "年齡", 3
    cobOrdField.AddItem "系別", 4
    
    
    cobOrd.AddItem "升序", 0
    cobOrd.AddItem "降序", 1
End Sub

Private Sub Form_Load()
    Dim strErr As String
  
    If cnn.State = 0 Then
  
       On Error GoTo errNotable:
       cnn.Open connStr
       
    End If
    
    '調用Clear過程，清空所有文本
    Call Clear
    '調用getAllRecords過程，顯示所有記錄
    Call getAllRecords
    
    Exit Sub

errNotable:
    strErr = "沒有找到相應數據，請確認所選擇的數據源指向的是MS SQL Server數據庫" & _
         vbCrLf & "并且數據庫中已經存在學生信息表。" & _
         vbCrLf & "您可以選擇初始化重新設置數據表。" & vbCrLf
    Call showMsg(strErr, 0)
End Sub

Private Sub MSFG_Clear()

    With MSFlexGrid1
        .Cols = 0
        .Rows = 0
    End With
    
End Sub

Private Sub Data_Display()
    Dim i As Integer
    '清空表
    Call MSFG_Clear
    With MSFlexGrid1
        On Error Resume Next
        If (xsjl.BOF) Or (xsjl.EOF) Then
        
            Call showMsg("沒有找到任何記錄 ！", 1)
            
            .Cols = 0
            .Rows = 0
            Frame3.Enabled = False
        Else
            .Cols = 5
            .Rows = 1
            Frame3.Enabled = True
        
            '得到每一個列的列名
           ' For i = 0 To 4
           '     .TextMatrix(0, i) = xsjl.Fields(i).Name
           ' Next i
            
            .TextMatrix(0, 0) = "學號"
            .TextMatrix(0, 1) = "姓名"
            .TextMatrix(0, 2) = "性別"
            .TextMatrix(0, 3) = "年齡"
            .TextMatrix(0, 4) = "所在系"
            
            .ColWidth(0) = 1500
            .ColWidth(1) = 1500
            .ColWidth(2) = 1500
            .ColWidth(3) = 800
            .ColWidth(4) = 2500
            
            
            On Error GoTo rsErr
            
            Do While Not xsjl.EOF
                .Rows = .Rows + 1
                '顯示一條記錄
                For i = 0 To 4
                .TextMatrix(MSFlexGrid1.Rows - 1, i) = Trim(xsjl.Fields(i))
                Next i
                '移動游標到下一條記錄
                xsjl.MoveNext
            Loop
            
            
        End If
    End With
    Exit Sub
     
rsErr:
     Call showMsg(" ", 0)
End Sub


Private Sub Form_Resize()
'使所有框架居中

    With Me
        Frame4.Left = (.ScaleWidth - Frame4.Width) / 2
        Frame4.Top = (.ScaleHeight - Frame4.Height) / 2
    End With
End Sub

Private Sub Form_Unload(Cancel As Integer)
    mdiFrm.stb.SimpleText = "就緒"
    Unload Me
End Sub

Private Sub MSFlexGrid1_Click()
    gRow = MSFlexGrid1.Row
    With MSFlexGrid1
    
        '得到一條記錄
        On Error GoTo exitThis
       ' TxtXH.Text = .TextMatrix(MSFlexGrid1.Rows - 1, 0)
       '如果為零，則說明所選擇的是第一行標題
        Call Clear
        If gRow <> 0 Then
            TxtXH.Text = .TextMatrix(gRow, 0)
            TxtXM.Text = .TextMatrix(gRow, 1)
            cobXB.Text = .TextMatrix(gRow, 2)
            cobNL.Text = .TextMatrix(gRow, 3)
            TxtZY.Text = .TextMatrix(gRow, 4)
        End If
        
    End With
    
exitThis:
    
End Sub

Private Sub MSFlexGrid1_RowColChange()
    Call MSFlexGrid1_Click
End Sub