1. Database Connection Method
The SQL connection method is used here.
ConnectString="provider=sqloledb.1;persist security info=false;User ID=sa;PassWord=hanhan12121; Initial Catalog=student;Data Source=.
One thing to mention here. If it is a local connection, data source can be written into the User Name of the Local Machine, such as: Data Source = Lenovo-PC or directly replaced by a point in the English state
If the user name of the local machine is not allowed to be written remotely, the IP address of the local machine must be changed, and persist Security info = false cannot appear in the connection string, this security mechanism is only available for local Windows login.
2 open a table
First define the variable
Dim MRC as ADODB. recordset 'defines the record set dim txsqlt as string' defines the connected string variable dim msgtext as strng txtsql = "select * From student_info where student_id = '" & trim (txtsid. text) & "'" set MRC = executesql (txtsql, msgtext)
The preceding two statements are used to open the student table and query by the student ID number. Executesql is a self-defined execution function.
3. Determine text box input
A prevent null
If not testtxt (txtsid. Text) then' prevents empty row msgbox from being entered. Please enter the student ID! ", Vbokonly + vbecxlamation," warning! "Txtsid. setfocus" text box to get focus txtsid. selstart = 0 'focus before txtsid. selength = Len (txtsid. Text) 'the text in the selected text box exit sub end if
A function defined in the module is called to determine whether it is null.
Public Function testtxt (txt as string) as Boolean 'defines whether the character is empty function if trim (txt) = "" then' if the input in the text box is empty, false is returned, testtxt = false' otherwise, true else testtxt = trueend ifend function end if
B. Prevent the input from being a number.
If not isnumeric (TRIM (txtsid. Text) then' determines whether the input is a number msgbox "student ID, enter a number", vbokonly + vbexclamation, "warning! "'Exit sub 'in the prompt box is indispensable, because this can directly exit the process to avoid the following problem: txtsid. setfocus 'get the focus txtsid. selstart = 0' focus before txtsid. selength = Len (txtsid. text) 'text in the selected text box
'This effect is equivalent to Automatically Selecting All texts, and the focus is on the first place. This prevents the mouse from being used again.
C. Call the isdate function to determine whether it is in the Date input format.
If not isdate (txtborndate. text) then' determine the date of birth msgbox "Date of Birth input format (yyyy-mm-dd)", vbokonly + vbexclamation, "warning" txtborndate. setfocus else txtborndate = format (txtborndate, "yyyy-mm-dd") end if
4. Data Operations
Record set search
As shown in
Click the first record to go to the first record.
MRC. movefirst call Resource: displays the source data MRC. moveprevious. The previous record if MRC. bof then MRC. movelast end if call Resource
The custom rosorce function is called here, which displays the original data.
Data Update and Deletion
Data Update first determines whether to modify the data. If no modification is made, a prompt is prompted for modification. Here, a Boolean variable is cleverly used.
The public Boolean variable mrcclean is added to the update operation. If the value of mrcclean is set to false, the value of mrcclean is set to true. Therefore, clicking update will prompt you to modify it first.
Update operation
First, make sure that the input content cannot be blank.
Next, open the table. Also stated
Add content for it again
MRC. addnew' cursor updates data MRC in the new record. fields (0) = trim (txtsid. text) MRC. fields (1) = trim (txtname. text) MRC. fields (2) = trim (combosex. text) MRC. fields (3) = trim (txtborndate. text) MRC. update 'Update record msgbox "Student Status information modified successfully', vbokonly + vbexclamation, '" Modify student status information "Call viewdata' display original data
It also involves whether the navigation bar and text box are valid.
For example, when loading
Make sure that the navigation bar is valid and the input text box is invalid.
Delete record
The automatic redirect record is used here.
For example
Dim mybookmark as varianton error GOTO 1 mybookmark = MRC. bookmark' flag str2 = msgbox ("delete current record", vbokonly + vbexclamation + vbokcancel, "warning") if str2 = vbok then MRC. movenext 'goes to the next record if MRC. EOF then' if the path ends with MRC. movefirst 'redirects to the first record mybookmark = MRC. bookmark' sets the flag MRC. movelast 'to the last record MRC. delete 'delete record MRC. bookmark = mybookmark 'unmark call viewdata' displays the original data else mybookmark = MRC. bookmark MRC. moveprevious 'goes to the previous record MRC. delete MRC. bookmark = mybookmark call viewdata end if else 'If you cancel the deletion, the tag is canceled and the original data MRC is displayed. bookmark = mybookmark call viewdata end if 1: