首先,定义相关的变量
Dim txtSQL As String
Dim msgText As String
Dim mrc As ADODB.Recordset
1.定义txtSQL 是用来保存执行到的SQL语句
2.定义msgText使用来返回查询状态,查询出来的结果是失败了,还是成功了
3.定义mrc为一个记录集用来保存结果。
其次,验证数据的合法性
1.利用For Each遍历查询控件,判断是否输入空字符
Dim Ctl As Control
For Each Ctl In Me.Controls
If TypeOf Ctl Is TextBox Or TypeOf Ctl Is ComboBox Then
If Len(Ctl) = 0 Then
Ctl.SetFocus
MsgBox Ctl.Tag + "未录入!", vbOKOnly + vbCritical, "提示"
Exit Sub
End If
End If
Next
2.判断卡号等是否输入数字
If Not IsNumeric(txtCardno.Text) Then
MsgBox "输入的信息不合法", vbOKOnly + vbExclamation, "卡号"
txtCardno.Text = ""
txtCardno.SetFocus
Exit Function
End If
第三,判断输入的数据(卡号)是否重复
txtSQL = "select * from student_info where cardno= '" & Trim$(txtCardno.Text) & "'"
Set mrc = ExecuteSQL(txtSQL, msgText)
If mrc.EOF = False Then
MsgBox "卡号重复,请输入新的卡号", vbOKOnly + vbExclamation, "卡号"
mrc.Close
txtCardno.SetFocus
第四,向数据库添加数据,跟学生管理信息系统直接用mrc.Addnew直接添加不同,下面是用sql语句添加数据。这样大大减少了代码的冗余度。
txtSQL = "Insert into student_info(studentname,sex,studentno,class,grade,cardno,comment,status,sum,department)" & _
" values ('" & txtStudentname.Text & "','" & comboSex.Text & "','" & txtStudentno.Text & _
"','" & txtClass.Text & "','" & txtGrade.Text & "','" & txtCardno.Text & "','" & _
txtComment.Text & " ','" & comboStatu.Text & "','" & txtSum.Text & "','" & txtDepartment.Text & "')"
ExecuteSQL txtSQL, msgText
mrc.Close
MsgBox "注册成功!", vbOKOnly + vbExclamation
Unload Me