I heard a classmate said that his girlfriend was a school, but also learned the database. The self-learning of the computer is still not, always feel a bit of sweat. On the forum of each website, I often see the netizen to the database. Some operations are difficult to grasp. I want to briefly introduce some common sense about ADO objects in VB. In fact, I have just understood this article, I am thinking of this article, I hope that the masters should not laugh, but At the same time, I hope to help beginners.
ADO is an abbreviation for ActiveX Data Objects. It is an access technology for a new database. It has a more simplified object model relative to Dao in VB5.0, whether it is access to local or remote data. Consistent interface. In terms of me, ADO is indeed much better than DAO.
Today, I only talk about ADO's object model. ADO defines a programmable object collection. ADO object model is shown in the figure:
For ADO, I think it is more important to: Command, Connection, RecordSet object.
Although it is strictly that this is inaccurate, it is true based on my actual experience. Let's talk about it.
(1) Connection object Connection object is used to establish a connection with the database. Access the data source from the application via the connection. It saves connection information such as a pointer type, connection string, query timeout, connection timeout, and default database.
(2) Command object After establishing the Connection, you can send a command to operate the data source. In general, the Command object can add, delete, or update data in the database, or perform data query in the table. Command object is defined in the query parameter or execution It is very useful when a stored procedure with output parameters.
(3) Recordset object RecordSet object only represents a recordset, this recordset is a table in a connected database, or a recordset returned by the execution result of the Command object. In the ADO object model, check and modify in the row The most important way to data, all of the operations of data are almost all of the .record objects that are completed in the Recordset object is used to specify rows, mobile lines, add, change, and delete records.
Let's specifically use ADO to access data:
The step of using an typical access data using ADO during actual programming is: (1) Connecting data source (2) Open Record Set Object (3) Using Record Set (4) Disconnect Connection
So, how is it used? See it separately. (I will give a complete use of ADO instance to everyone.)
(1) How to connect the data source to create a connection to a data source using the Connection object. Application method is the OPEN method for the Connection object. Syntax: Connection object .open connectionString, userid, password, openOption]: Connection object for your defined Connection The object's instance; Connectionstring is an option, contains information about the connection database; UserId option, the username of establishing a connection; Password is optional, including the user password to establish a connection; OpenOptions is optional, if set to adConnectasync, Then the connection will open asynchronously.
(2) Open the record set object In fact, the recordset returns a query result set back from the database. So he has two types of open methods: an Open method using record set, and the other is using the Connection object Execute method.
(a) Open method syntax of the record set: RecordSet.Open Source, ActiveConnection, Cursortype, LockType, Options in which: RecordSet is an instance of the defined recordset object. Source option, indicate the opened record source information. Can be Legal commands, object variable names, SQL statements, table names, stored procedures calls, or save record sets. ActiveConnection options, the validated variable name of the open Connection object, or a string containing the Connectionstring parameter. CURSORTYPE Options, determine the pointer type that opens the recordset object. LockType Option Options Open the lock type used by the recordset object. (B) EXECUTE Method for Connection Objects: Set RecordSet = Connection.execute (CommandText, Recordsaffected, Options) Parameter Description: CommandText A string, returns the SQL command, the table name, stored procedure, or specified text. RecordSaffected option, the value of the long type, the number of records that the return operation affects the number of records. Options option, long type value, indicate how Process the CommandText parameter. Introduce how to open the database, let's talk about how to use it.
(3) Use record set
(a) Add a new record: Add a new record in the ADO: addNew its syntax for: RecordSet.AddNew FieldList, VALUES RECORDSET for record set object instance FieldList as a field name, or a field array. VALUES is a value assigned to the field of the information, if the filedlist is a field name, then Values should be a single value, if FileDList is a field array, the values must also be a number, type and FieldList the same array. . After adding the AddNew method to add a new record to the record set, you should use Update to store the added data in the database. However, you'd better use the cancelupdate method before using the Update method to cancel this action.
(b) Modifying the record set actually modifying the data re-assaped in the record set. There is no difference. Just use the SQL statement to find a data to be modified and re-assapled. This is no longer said.
(c) Deleting the method of removing the data in the ADO to delete the record set is: DELETE method, which is the same as the DAO object, but its ability to be enhanced in ADO, can delete a set of records. Its syntax is as follows : RecordSet.delete AffectRecords, the AffECTRecords parameter is the way to determine the Delete method. Its value is as follows: AdAffectCurrent only deletes the current record AdaffectGroup delete those records that meet the Filter property settings. For one set of data, you should delete a set of data Set the Filter property.
(d) Query the method recorded in the ADO is very flexible, there are several queries. ● Execute the SQL command using the EXECUTE method of the connection object, returns the query record set. The SQL command returns the query record set. The specific syntax of the first method has been introduced in front of the data connection. The second method is specifically told. The syntax of the Execute method of the Command object is as follows: Command.execute Recordsaffected, Parameters, Options 'does not return a record set or set Rscordset = cmmnad.Execute (RecordsAffected, Parameters, Options)' returns the record set CommandText syntax: Command.CommandText = stringvariable wherein: stringvariable a string variable that contains a SQL statement, table or memory Procedure. (4) Disconnects before the application ends, you should release resources assigned to the ADO object, the operating system reclaims these resources and can be allocated to other applications. The method used is: Close method. The syntax is as follows: Object. Close 'Object is ADO object
Ok, I said so many theories, let's apply it! Below I give an instance I write, it is a user login, registration, password modification, application applet deleted by users. You can also go to this Download in the download warehouse.
Specific implementation: four forms are included in this program. A user login, name is: formdenglu; a user registration, name is: formzhuce; another is used for users to change the password, name: Formxiugai The last one is used for the user's deletion, the name is: Formshanchu.
The definition and setting of the ADO object is performed below, but you must reference the ADO object in the project before this.
Define ADO objects:
Dim ZhuCe As ADODB.Connection 'ZhuCe into the connector Dim rstZhuCe As ADODB.Recordset' rstZhuCe recordset objects Dim cmmZhuCe As ADODB.Command 'cmmZhuCe command object is to be provided as follows ADO Form_Load procedure: Set ZhuCe = New ADODB .Connection 'Setting the connection object instance Zhuce.cursorLocation = aduseclient' Open data source connection Zhuce.Open "provider = microsoft.jet.oledb.4.0; data source = app.path / registration .mdb" set record set object instance set Rstzhuce = New ADODB.Recordset set rstZhuCe.ActiveConnection = ZhuCe 'call record set rstZhuCe.Open "select * from register", ZhuCe, adOpenStatic, adLockOptimistic' set command object instance set cmmZhuCe = New ADODB.Command set cmmZhuCe.ActiveConnection = ZhuCe added New User Information (Original Code) if TextYonghuming.text = "" Or TextMima.Text = "" OR _ texttishiwenti.text = "" or texttishidaan.text = "" The msgbox "pen add information, please enter ", vbokonly vbexclamation," Tips "else if textjiaoyan.text = textmima.text the Rstzhuce.MovelAst 'records the mobile method, pointer moves to remember End rstZhuCe.AddNew 'record collection to add a new record rstZhuCe! Username = TextYongHuMing.Text rstZhuCe! Password = TextMiMa.Text rstZhuCe! Name = TextXingMing.Text rstZhuCe! Nickname = TextNiCheng.Text rstZhuCe! Address = TextLianXiDiZhi.Text rstZhuCe ! Postal Code = TextYoubian.text Rstzhuce! OICQ = TEXTOICQ.TEXT RSTZhuce! Email = TextMail.Text Rstzhuce! = Texthuji.text Rstzhuce! Mobile = TextShouji.Text Rstzhuce! Password Question =
TextTiShiWenTi.Text rstZhuCe! Question answer = TextTiShiDaAn.Text rstZhuCe! Telephone number = TextDianHua.Text rstZhuCe.Update 'all of the above information stored in the database MsgBox "Registration successful", vbOKOnly vbInformation, "registered successfully" Else MsgBox "school The password is inconsistent with the password, please re-enter ", vbokonly vbexclamation, _" Password error "textmimamima.text =" "textjiaoyan.text =" "Endjiaoyan.Text =" "END IF below is to modify the user password (I didn't use it here The two methods mentioned above, but this method is also very simple, in fact, it should be a first method.
The code is as follows: DIM A AS STRINGHUMING.TEXT = "" or textmimima.text = "" or textxinmimamimamimima.text = "" or textXinmima.text = "" the msgbox "modification information is not complete, please re-re- fill! ", vbOKOnly vbInformation," correct errors "TextYongHuMing.Text =" "TextMiMa.Text =" "TextJiaoYanMiMa.Text =" "TextXinMiMa.Text =" "Else If TextXinMiMa.Text <> TextJiaoYanMiMa.Text Then MsgBox" school The password is inconsistent with the new password, please refill it! " "& _ TextYongHuMing.Text & _" 'AND password =' "& _ TextMiMa.Text &" '; "rstZhuCe.Open a, ZhuCe, adOpenKeyset, adLockOptimistic rstZhuCe password = TextXinMiMa.Text MsgBox!" password change is successful, your The new password is: "& RSTZhuce! Password &"! ", Vbokonly vbinformation," Modify Success "End IF Maybe you seem to be a bit trouble, but you can download all the programs, which look simple! Below is The user's deletion.
Code is as follows: Dim a As String Dim ok As Integer Dim i As Integer rstZhuCe.MoveFirst For i = 0 To rstZhuCe.RecordCount - 1 If rstZhuCe = ComboYongHuMing.Text And rstZhuCe username password = TextMiMa.Text Then ok = MsgBox (!! "Do you determine this user?", VBOKCANCEL VBQUESTION, "Tip") IF OK = 1 THEN RSTZhuce.delete AdaffectCurrent 'Remove the user msgbox "This user has successfully deleted!", Vbokonly vbinformation "Delete success" end if End if rstzhuce.movenext Next I gave you a simple knowledge and basic usage of ADO objects, and assumbreate my experience for your reference.
