选课系统 - VB6论坛

得分:0

我又改了一下，但是操作只能进行一次，到底是什么问题啊！
Dim Con As New ADODB.Connection             '建立connection变量,连接数据库
Dim Res As New ADODB.Recordset

Dim Sqln As String, Sqlv As String
Dim Sql1 As String, Sql2 As String
Dim Sqld As String
Dim i As Long
Dim j As Integer
Private Sub Command1_Click()                '添加记录，可要可不要
    For i = 0 To Res.Fields.Count - 1
        Text1(i).Text = ""
    Next i

End Sub

Private Sub Command2_Click()                '删除记录，有问题，当我数据库为空时，不能给出提示，而是出错，而且不能连续保存

    For i = 0 To Res.Fields.Count - 1
        Sqld = Sqld & Res.Fields(i).Name & "='" & Res.Fields(i).Value & "'and "
    Next i

    Sqld = Left(Sqld, Len(Sqld) - 4)
    Con.Execute "DELETE FROM 选课表 WHERE " & Sqld

    CloseConn                               '关闭数据库
End Sub

Private Sub Command3_Click()                '修改记录,不能连续修改
        For i = 0 To Res.Fields.Count - 1
            Sql1 = Sql1 & Res.Fields(i).Name & "='" & Text1(i).Text & "',"
            Sql2 = Sql2 & Res.Fields(i).Name & "='" & Res.Fields(i).Value & "'and "
        Next i
        Sql1 = Left(Sql1, Len(Sql1) - 1)            '返回 Variant (String)，其中包含字符串中从左边算起指定数量的字符。
        Sql2 = Left(Sql2, Len(Sql2) - 4)
        Con.Execute "UPDATE 选课表 SET " & Sql1 & "WHERE " & Sql2
        CloseConn                           '关闭数据库
End Sub

Private Sub Command4_Click()                '保存记录,有问题，不能连续保存
    If Text1(0).Text = "" Then
        MsgBox "姓名不能为空！", , Form1.Caption
    Exit Sub

    End If
    For i = 1 To Res.Fields.Count - 1
        Sqln = Sqln & "," & Res.Fields(i).Name
        Sqlv = Sqlv & ",'" & Text1(i).Text & "'"
    Next i

    Con.Execute "INSERT INTO 选课表 (姓名" & Sqln & ") VALUES('" & Text1(0).Text & "'" & Sqlv & ")"
    MsgBox "添加完成。", , Form1.Caption
    CloseConn                               '关闭数据库


End Sub

Private Sub Command5_Click()                '按学号查询选课情况,有问题，当记录不存在时，依旧显示出结果原因是MSHFlexGrid1_Click函数
    Sqln = InputBox("输入你想查询的学号:")

    Con.Execute "select * from 选课表 where 学号='" & Sqln & "'"
    MSHFlexGrid1_Click
    CloseConn                               '关闭数据库
End Sub

Private Sub Command6_Click()                '退出记录
    End
End Sub

Private Sub Command7_Click()                '显示全部记录
    Set MSHFlexGrid1.DataSource = Res
    CloseConn                               '关闭数据库
End Sub

Private Sub Form_Load()                    '连接数据库
    Con.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=学生选课表;Data Source=20090828-1511"
    '服务器名称，根据自己的服务器名称进行修改

    Res.Open "select * from 选课表", Con, adOpenKeyset, adLockOptimistic
    CloseConn

    For i = 0 To Res.Fields.Count - 1       '代码重复
        Text1(i).Text = ""
    Next i
End Sub

Private Sub CloseConn()                        '关闭数据库
    Res.Close
    Res.Open

   'If Res.RecordCount > 0 Then
        'MSHFlexGrid1_Click
    'End If
End Sub

Private Sub MSHFlexGrid1_Click()               '在表里点击，在Text上面显示

    For i = 0 To Text1.UBound
        If Res.Fields(i).Value <> "" Then
            Text1(i).Text = Res.Fields(i).Value
        Else
            Text1(i).Text = ""
        End If
    Next i

End Sub