Common Operations on ADO objects

1.
Establish a connection

1) Define a connection object and instantiate it through new

2) Assign values to the connection string commmandstring

3) Call the open method of the connection to establish a connection.

4) When no connection is used, call the close method to close the connection.

Dim CNN as connection 'defines a connection object

Set CNN = new connection 'instantiate the connection object

CNN. connectionstring =
"Provider = sqloledb.1; Integrated Security = sspi; persist Security
Info = false; initial catalog = instance 1; Data Source = XDP-PC"

CNN. open' open the connection

......

CNN. Close 'Close the connection

2.
Create a data record set

1) . Define A recordset record set and instantiate it through new

2) . Set the value of cursorlocation

3) . Call the recordset open method. An open connection is required at this time. If not, an error is returned.

Dim RS as recordset 'defines a record rs

Set RX = new recordset
'Instantiate record set

Rs. cursorlocation = aduseclient
'Specify as the client record set

'The open record set requires an open connection CNN to open the specified table in the database

Rs. Open "select * from system user", CNN, adopendynamic,
Adlockbatchoptimistic

 

3.
Recorded operations

1) Add, use the addnew method, and then update

Rs. addnew' Add a new record

Rs. Fields ("student ID") = txtstuno. Text

Rs. Fields ("name") = txtstuname. Text

Rs. Update

2) Modify or delete a record. Check the corresponding record before performing the corresponding operation.

Rs. Find ("student ID = '000000 '")

Rs. Fields ("name") = txtstuname. Text

Rs. Fields ("Address") = txtstuaddr. Text

Rs. Update

 

4.
Data Query

1) Create a command object, use? Set the commandtext and commandtype attributes of the command object as query parameters in the SELECT statement.

2) Create a parameter using the createparameter method of the command object and add it to the parameters set of the command object.

3) Set the query parameter value

4) Execute the command object to complete the query

'Instantiate a command object

Dim
Objcmd as command

Set objcmd = new command

Set objcmd. activeconnection = CNN
'COMMAND object connection to the specified database

With
Objcmd

. Commandtext = "select * from system user where username like? And identity like? "

. Commandtype = ad1_text

End

'Create parameters for the command object,

Dim parm as New Parameter

Set parm = objcmd. createparameter ("User Name", advarchar, adparaminput,
10)

Objcmd. Parameters. append parm

Set parm = objcmd. createparameter ("Identity", advarchar, adparaminput,
10)

Objcmd. Parameters. append parm

'Obtain the query parameters

Objcmd ("username") = "%" & txtuser & "%"

Objcmd ("Identity") = "%" & txtstatus & "%"

'Execute the query to obtain the query result record set.

Set objrs = objcmd. Execute ()