How to Use the connection object in ado?

How to Use the connection object in ado?

What is a connection object?

A connection object describes the physical connection to the data source. You can use ODBC or ole db to connect to the data source. When you open a connectiont object, you attempt to connect to the database. The state attribute of the connection object tells us whether the connection is successful. Use the execute method of the connection object to send SQL statements to the data source or run the stored procedure. If the command you send to the data source requires the data source to return the record set, the record set object will be automatically created. After you connect to the database, you can close the connection object.

What are the methods and attributes of the connection object?

The following table lists some common connection methods.

Method
Description
 
Open
Open a data source connection
 
Close
Close the connection to the data source and related objects
 
Execute
Execute a query (SQL language name, stored procedure, or data provider-specific text)
 
Begintrans
Start a new transaction
 
Committrans
.

Save some changes or the current transaction to start a new transaction
 
Rollbacktrans
Cancels some changes in the current transaction and ends the transaction to start a new transaction.
 

The following table lists the attributes of some common connection objects.

Attribute
Description
 
Connectionstring
Contains information about establishing a connection to the data source.
 
Connectiontimeout
Displays the time spent trying to establish a connection to the data source and generating errors.
 
Commandtimeout
Display the time taken to execute this command before an attempt is interrupted or an error is returned.
 
State
Indicates whether it is connected to the data source, closed, or in connection
 
Provider
Display the name of the connection provider
 
Version
Show ADO version number
 
Cursorlocation
Sets or returns the value of a provider's cursor function.

 
 

How to connect to the data source using the connection object?

To use a connnection object, you only need to specify a connection string to specify the data source you want to join and then call the open method to establish a connection.

The information provided by connection string can be easily connected to data through the open method. If you decide to use the connection object for work, you can use its state attribute. If the connection object is opened, the returned value is adstateopen. If not, the returned value is adstateclosed. The following example uses ODBC to establish a connection with SQL.

Sub connectionexample1 ()
Dim CNN as ADODB. Connection
Set CNN = new ADODB. Connection
'Open the connection with ODBC.
CNN. Open "pubs", "sa ",""
'Check whether the connection is complete
If CNN. State = adstateopen then
Msgbox "Welcome to pubs! "
Else
Msgbox "sorry. No pubs today ."
End if
Close connection object
CNN. Close
End sub
If you only need to connect to a data source. The following code is simpler than the above Code. You can create a connection object. You can set the connectionstring Attribute before calling the open method. This method allows you to connect to a data source and then use this object to connect to another data source.
Sub connectionexample2 ()
Dim CNN as ADODB. Connection
Set CNN = new ADODB. Connection
'Establish a connection with ODBC DSN
CNN. connectionstring = "DSN = pubs; uid = sa; Pwd = ;"
CNN. Open
'Check whether a connection is established with the data source.
If CNN. State = adstateopen then
Msgbox "Welcome to pubs! "
Else
Msgbox "sorry. No pubs today ."
End if
'Close the connection object
CNN. Close
End sub
You can set other attributes of a connection object before establishing a connection. For example, you can set connection timeout.

Sub connectionexample3 ()
Dim CNN as ADODB. Connection
Set CNN = new ADODB. Connection
'Set the connection Property
CNN. connectionstring = "DSN = pubs; uid = sa; Pwd = ;"
CNN. connectiontimeout = 30
'Open the connection object
CNN. Open
'Check whether the data source has been connected
If CNN. State = adstateopen then
Msgbox "Welcome to pubs! "
Else
Msgbox "sorry. No pubs today ."
End if
'Close the connection object
CNN. Close
End sub
The syntax structure of the connectionstring attribute assumes that the data source has been established or uses ODBC as the system administrator. This reduces the installation burden. The following example shows an optional method for connecting to SQL Server.

. Sub connectionexample4 ()
Dim CNN as ADODB. Connection
Set CNN = new ADODB. Connection
'Use ODBC driver to open the connection object
CNN. connectionstring = "driver = {SQL Server };"&_
"Server = rgreennt; uid = sa; Pwd =; database = pubs"
CNN. Open
'Find out if the attempt to connect worked.
'Check whether a connection has been established
If CNN. State = adstateopen then
Msgbox "Welcome to pubs! "
Else
Msgbox "sorry. No pubs today ."
End if
'Close the connection object
CNN. Close
End sub
Now ODBC driver has a wider change. You can use ADO to talk to the data source. Soon, more ole db providers will be connected to the data source. The Microsoft? Ole db provider for ODBC is the default provider of the current ADO. You can use different providers to set the provider attribute of the connection object.

Sub connectionexample5 ()
Dim CNN as ADODB. Connection
Set CNN = new ADODB. Connection
'Set the provider property to use ole db provider for ODBC
CNN. provider = "msdasql"
'Open the connection object with ODBC DSN
CNN. connectionstring = "driver = {SQL Server };"&_
"Server = rgreennt; uid = sa; Pwd =; database = pubs"
CNN. Open
 
'Check whether the data source is connected
If CNN. State = adstateopen then
Msgbox "Welcome to pubs! "
Else
Msgbox "sorry. No pubs today ."
End if
 
'Close the connection object
CNN. Close
 
End sub
The code above sets the provider attribute not to be necessary because the default provider of ADO is ole db provider for ODBC. Here, you only know how to set it when you use other ole db providers.

How can I use the connection object to execute command?

The execute method of connnetion is used to send a command (an SQL command or other text information) to the data source. If several lines of records are required to be returned in the SQL command, A recordset object will be automatically created.

Sub connectionexample6 ()
Dim CNN as ADODB. Connection
Dim RS as ADODB. recordset
Set CNN = new ADODB. Connection
'Reference ODBC driver to establish a connection.
CNN. connectionstring = "driver = {SQL Server };"&_
"Server = rgreennt; uid = sa; Pwd =; database = pubs"
CNN. Open
'Execute the SQL statement to create A recordset object.
Set rs = CNN. Execute ("select * from authors ")
'Display the first author.
Msgbox RS ("au_fname") & "" & RS ("au_lname ")
'Disconnect
Rs. Close
End sub
Remember that the recordset returned by execute is read-only and only the forward cursor is used. If you need to use more functions of the recordset object, you must first create a recordset object and set the attributes you want to set, then open it using the open method to query and return the desired cursor type.

In the following example, the command object executes the DELETE command. Because no data set needs to be returned, you do not need to use the recordset object. How many rows of data have been deleted? You can know it through the recordsaffected parameter.

Sub connectionexample7 ()
Dim CNN as ADODB. Connection
Dim RS as ADODB. recordset
Set CNN = new ADODB. Connection
'Reference ODBC driver to establish a connection
CNN. connectionstring = "driver = {SQL Server };"&_
"Server = rgreennt; uid = sa; Pwd =; database = pubs"
CNN. Open
'Send the deletion instruction to the data source
CNN. Execute ("delete from authors where au_id = '011-01-0111 '")
'Check how many rows of data are deleted
Set rs = CNN. Execute ("select @ rowcount ")
'Display the first field
Msgbox RS (0) & "rows deleted"
'Close the connection
Rs. Close
End sub
In the following example, the command runs the stored procedure of the data source using the name of the specified stored procedure. To return data, you must create a recordset object.

Sub connectionexample8 ()
Dim CNN as ADODB. Connection
Dim RS as ADODB. recordset
Set CNN = new ADODB. Connection
'Reference ODBC driver to establish a connection
CNN. connectionstring = "driver = {SQL Server };"&_
"Server = rgreennt; uid = sa; Pwd =; database = pubs"
CNN. Open
'Create A recordset object to run the Stored Procedure
Set rs = CNN. Execute ("Exec byroyalty 50 ")
'Show author IDs through Loops
Do while not Rs. EOF
Msgbox RS ("au_id ")
Rs. movenext
Loop
'Disconnect
Rs. Close
End sub
For the first time, mistakes are inevitable. I hope the experts can give me some corrections and get some help from beginners.

Http://msdn.microsoft.com/library/default.asp? Url =/library/en-US/dnado/html/msdn_workshp2.asp