首先,定义相关的变量
Dim txtsql As String
Dim msgText As String
Dim mrc As ADODB.Recordset
2.定义msgText使用来返回查询状态,查询出来的结果是失败了,还是成功了
3.定义mrc为一个记录集用来保存结果。
@H_403_48@其次,验证数据的合法性
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
@H_403_48@第三,判断输入的数据(卡号)是否重复
txtsql = "select * from student_info where cardno= '" & Trim$(txtCardno.Text) & "'" Set mrc = Executesql(txtsql,msgText) If mrc.EOF = False Then MsgBox "卡号重复,请输入新的卡号","卡号" mrc.Close txtCardno.SetFocus
@H_403_48@第四,向数据库添加数据,跟学生管理信息系统直接用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