机房收费系统---多表操作

在进行机房收费系统的时候，里面有不少的操作。需要同时操作多个数据库。

比如，用户的注册操作。在用户注册的同时，分别执行两个操作。

1.把用户的信息写入到学生信息表

2.把用户的充值信息写入到数据库的充值记录表中

面对同一个操作，进行多步数据库的操作。有两种可行方案：

第一：

进行充值操作的时候，需要把充值信息写入到充值记录表，也要把学生信息的金额数进行更改。

Private Sub cmdSave_Click()
    Dim txtSQL As String
    Dim MsgText As String
    Dim mrc As ADODB.Recordset
 
    If testNull(txtCardNo.Text) Then
        MsgBox "卡号不允许为空！", vbOKOnly + vbExclamation, "警告"
        txtCardNo.SetFocus
        Exit Sub
    End If
    
    If Not IsNumeric(Trim(txtCardNo.Text)) Then
        MsgBox "卡号请输入数字！", vbOKOnly + vbExclamation, "警告"
        txtCardNo.SetFocus
        Exit Sub
    End If
    
    '检验卡号是否重复
    txtSQL = "select * from student_Info where cardno='" & txtCardNo.Text & "'" & "and status='使用'"
    Set mrc = ExecuteSQL(txtSQL, MsgText)
    If mrc.BOF = False Then
        MsgBox "卡号已经存在，请重新输入！", vbOKOnly + vbExclamation, "警告"
        txtCardNo.SetFocus
        Exit Sub
    End If
    
    
    If testNull(txtStudentNo.Text) Then
        MsgBox "学号不允许为空！", vbOKOnly + vbExclamation, "警告"
        txtStudentNo.SetFocus
        Exit Sub
    End If
     
    If Not IsNumeric(Trim(txtStudentNo.Text)) Then
        MsgBox "学号必须为数字！", vbOKOnly + vbExclamation, "警告"
        txtStudentNo.SetFocus
        Exit Sub
    End If
    
    '检验 学号 是否重复
    txtSQL = "select * from student_Info where studentno='" & txtStudentNo.Text & "'"
    Set mrc = ExecuteSQL(txtSQL, MsgText)
    
    If mrc.EOF = False Then
        MsgBox "此学号已被使用！", vbOKOnly + vbExclamation, "警告"
        txtStudentNo.SetFocus
        Exit Sub
    End If
    
    
    '以下 为验证 其他信息的限制
    If testNull(txtName.Text) Then
        MsgBox "姓名不能为空！", vbOKOnly + vbExclamation, "警告"
        txtName.SetFocus
        Exit Sub
    End If
    
    If testNull(txtCollege.Text) Then
        MsgBox "学院不能为空！", vbOKOnly + vbExclamation, "警告"
        txtCollege.SetFocus
        Exit Sub
    End If
    
    If testNull(txtMajor.Text) Then
        MsgBox "专业不能为空！", vbOKOnly + vbExclamation, "警告"
        txtMajor.SetFocus
        Exit Sub
    End If
    
    If testNull(txtGrade.Text) Then
        MsgBox "年级不能为空！", vbOKOnly + vbExclamation, "警告"
        txtGrade.SetFocus
        Exit Sub
    End If
    
    If testNull(txtClass.Text) Then
        MsgBox "班级不能为空！", vbOKOnly + vbExclamation, "警告"
        txtClass.SetFocus
        Exit Sub
    End If
    
    If testNull(txtCash.Text) Then
        MsgBox "请输入金额！", vbOKOnly + vbExclamation, "警告"
        txtCash.SetFocus
        Exit Sub
    End If
    
    If Val(txtCash.Text) < 5 Then
        MsgBox "注册用户最少需要5元", vbOKOnly + vbExclamation, "警告"
        txtCash.SetFocus
        Exit Sub
    End If
    
    '用户填写的信息检查  没有错误，现在可以进行登记信息了
    txtSQL = "select * from student_Info"
    Set mrc = ExecuteSQL(txtSQL, MsgText)
    
    
    mrc.AddNew
    mrc.Fields(0) = Trim(txtCardNo.Text)
    mrc.Fields(1) = Trim(txtStudentNo.Text)
    mrc.Fields(2) = Trim(txtName.Text)
    mrc.Fields(3) = Trim(cboSex.Text)
    mrc.Fields(4) = Trim(txtCollege.Text)
    mrc.Fields(5) = Trim(txtMajor.Text)
    mrc.Fields(6) = Trim(txtGrade.Text)
    mrc.Fields(7) = Trim(txtClass.Text)
    mrc.Fields(8) = Trim(txtCash.Text)
    mrc.Fields(9) = Trim(txtExplain.Text)
    mrc.Fields(10) = UserID
    mrc.Fields(11) = Trim(cboStatus.Text)
    mrc.Fields(12) = "未结账"
    mrc.Fields(13) = Trim(Date)
    mrc.Fields(14) = Trim(Time)
    
    mrc.Update
    mrc.Close
    
    '用户注册的时候同时也进行了充值。所以需要把充值信息  保存到 充值信息Recharge表中
    txtSQL = "select * from Recharge_Info"
    Set mrc = ExecuteSQL(txtSQL, MsgText)
    
    mrc.AddNew
    mrc.Fields(1) = Trim(txtStudentNo.Text)
    mrc.Fields(2) = Trim(txtCardNo.Text)
    mrc.Fields(3) = Trim(txtCash.Text)
    mrc.Fields(4) = Trim(Date)
    mrc.Fields(5) = Trim(Time)
    mrc.Fields(6) = UserID
    mrc.Fields(7) = "未结账"
    
    mrc.Update
    mrc.Close
    
    MsgBox "学生注册成功！", vbOKOnly + vbExclamation, "注册成功"
    
    Unload Me
End Sub

 

第二：

进行登记的时候，把用户的信息，直接写入一张数据表中，然后再把数据表分别写入到其他的表中。

Private Sub cmdOk_Click()
    Dim txtSQL As String
    Dim MsgText As String
    Dim mrc As ADODB.Recordset          '用于记录学生信息表的结果集
    Dim mrcc As ADODB.Recordset         '用于记录充值记录表 的结果集
    Dim oldCash As Variant
    Dim newCash As Variant
    
    If testNull(txtCardNo.Text) Then
        MsgBox "请输入卡号！", vbOKOnly + vbExclamation, "警告"
        Exit Sub
    End If
    
    '首先检查，输入的卡号是否  注册
    txtSQL = "select * from student_Info where cardNo='" & txtCardNo.Text & "'"
    Set mrc = ExecuteSQL(txtSQL, MsgText)
    If mrc.BOF Then
        MsgBox "这个卡号未被注册！", vbOKOnly + vbExclamation, "警告"
        Exit Sub
    End If
    
    '对充值金额的要求 限定
    If testNull(txtCash.Text) Then
        MsgBox "请输入充值金额！", vbOKOnly + vbExclamation, "警告"
        Exit Sub
    End If
    
    If Val(txtCash.Text) < 5 Then
        MsgBox "充值金额最少为5元！", vbOKOnly + vbExclamation, "警告"
        Exit Sub
    End If
    
    txtSQL = "select * from Recharge_Info"
    Set mrcc = ExecuteSQL(txtSQL, MsgText)
    
    '把充值的信息 写入 充值信息表中。用于做记录
    mrcc.AddNew
    mrcc.Fields(1) = mrc.Fields(1)
    mrcc.Fields(2) = mrc.Fields(0)
    mrcc.Fields(3) = Trim(txtCash.Text)
    mrcc.Fields(4) = Trim(Date)
    mrcc.Fields(5) = Trim(Time)
    mrcc.Fields(6) = UserID           ’操作人的姓名
    mrcc.Fields(7) = "未结账"
    mrcc.Update
    mrcc.Close
    
    '更新student表，把学生 卡上的钱数更改
    oldCash = mrc.Fields(8)             '把学生充值前的  钱取出来
    mrc.Fields(8) = oldCash + txtCash.Text
    newCash = mrc.Fields(8)             '把学生充值后的  钱取出来
    mrc.Update
    mrc.Close
    
    
    '把冲值后的信息显示出来
    rcCardNo.Text = txtCardNo.Text
    rcOldCash.Text = oldCash
    rcNewCash.Text = newCash
    rcDate.Text = Date
    rcTime.Text = Time
    rcTeacher.Text = UserID
    
    MsgBox "充值成功！", vbOKOnly + vbExclamation, "提示"
End Sub

对于这两种操作的效率问题，有待深入研究。