• <ins id="pjuwb"></ins>
    <blockquote id="pjuwb"><pre id="pjuwb"></pre></blockquote>
    <noscript id="pjuwb"></noscript>
          <sup id="pjuwb"><pre id="pjuwb"></pre></sup>
            <dd id="pjuwb"></dd>
            <abbr id="pjuwb"></abbr>
            隨筆-9  評論-6  文章-0  trackbacks-0

            Microsoft Corporation

            April 2, 1998
            Updated March 30, 2004

            Applies to:
            ??? Microsoft? ActiveX? Data Objects (ADO)

            Summary: This is the first in a series of columns that will explore the individual objects in Microsoft ActiveX Data Objects (ADO).

            Contents
            What Is a Connection Object?
            What Are the Connection Object's Methods and Properties?
            How Do I Use the Connection Object to Connect to a Data Store?
            How Do I Use the Connection Object to Execute a Command?

            What Is a Connection Object?
            A Connection object represents a physical connection to a data store. To create a Connection object, you will supply the name of either an ODBC data store or an OLE DB provider. When you open the Connection object, you attempt to connect to the data store. The State property of the Connection object tells you whether you succeeded or failed. You can send SQL statements or run stored procedures by using the Execute method of the Connection object. If the command you send to the data store returns records, a Recordset object will be created automatically. You close the Connection object when you are through with it.

            What Are the Connection Object's Methods and Properties?
            The following table lists some of the more commonly used methods of the Connection object.

            The following table lists some of the more commonly used methods of the Connection object.

            Method Description
            Open Opens a connection to a data store.
            Close Closes a connection and any dependent objects.
            Execute Executes the specified query, SQL statement, stored procedure, or provider-specific text.
            BeginTrans Begins a new transaction.
            CommitTrans Saves any changes and ends the current transaction. It may also start a new transaction.
            RollbackTrans Cancels any changes made during the current transaction and ends the transaction. It may also start a new transaction.

            The following table lists some of the more commonly used properties of the Connection object.

            Property Description
            ConnectionString Contains the information used to establish a connection to a data store.
            ConnectionTimeout Indicates how long to wait while establishing a connection before terminating the attempt and generating an error.
            CommandTimeout Indicates how long to wait while executing a command before terminating the attempt and generating an error.
            State Indicates whether a connection is currently open, closed, or connecting.
            Provider Indicates the name of the provider used by the connection.
            Version Indicates the ADO version number.
            CursorLocation Sets or returns a value determining who provides cursor functionality.

            How Do I Use the Connection Object to Connect to a Data Store?
            To use a Connection object, simply specify a connection string, which identifies the data store you want to work with, and then call the Open method to connect.

            The easiest way to open a connection is to pass the connection string information to the Open method. To determine whether the Connection object worked, you can use the State property of the Connection object. State returns adStateOpen if the Connection object is open and adStateClosed if it isn't. Here is an example of connecting to SQL Server by using an ODBC data store:

            Sub ?ConnectionExample1()
            ???
            Dim ?cnn? As ?ADODB.Connection
            ???
            Set ?cnn? = ? New ?ADODB.Connection

            ???
            ' ?Open?a?Connection?using?an?ODBC?DSN?named?"Pubs".
            ???cnn.Open? " Pubs " ,? " MyUserName " ,? " MyPassword "

            ???
            ' ?Find?out?if?the?attempt?to?connect?worked.
            ??? If ?cnn.State? = ?adStateOpen? Then
            ??????
            MsgBox ? " Welcome?to?Pubs! "
            ???
            Else
            ??????
            MsgBox ? " Sorry.?No?Pubs?today. "
            ???
            End ? If

            ???
            ' ?Close?the?connection.
            ???cnn.Close

            End?Sub

            If you need to connect to only one data store, the procedure followed in the above code is the easiest way. Alternatively, you can create a Connection object and set the ConnectionString property before calling the Open method. This approach allows you to connect to one data store and then reuse the Connection object to connect to another data store.

            This method also gives you the opportunity to set other properties of the Connection object before connecting. For instance, you might want to set the connection time-out:

            Sub ?ConnectionExample2()
            ???
            Dim ?cnn? As ?ADODB.Connection
            ???
            Set ?cnn? = ? New ?ADODB.Connection?

            ???
            ' ?Open?a?connection?using?an?ODBC?DSN?"Pubs".
            ???cnn.ConnectionString? = ? " DSN=Pubs;UID=MyUserName;PWD=MyPassword; "
            ???cnn.Open?

            ???
            ' ?Find?out?if?the?attempt?to?connect?worked.
            ??? If ?cnn.State? = ?adStateOpen? Then
            ??????
            MsgBox ? " Welcome?to?Pubs! "
            ???
            Else
            ??????
            MsgBox ? " Sorry.?No?Pubs?today. "
            ???
            End ? If ?

            ???
            ' ?Close?the?connection.
            ???cnn.Close?

            End?Sub
            ?


            ?

            Sub ?ConnectionExample3()
            ???
            Dim ?cnn? As ?ADODB.Connection
            ???
            Set ?cnn? = ? New ?ADODB.Connection?

            ???
            ' ?Set?properties?of?the?Connection.
            ???cnn.ConnectionString? = ? " DSN=Pubs;UID=MyUserName;PWD=MyPassword; "
            ???cnn.ConnectionTimeout?
            = ? 30 ?

            ???
            ' ?Open?the?connection.
            ???cnn.Open?

            ???
            ' ?Find?out?if?the?attempt?to?connect?worked.
            ??? If ?cnn.State? = ?adStateOpen? Then
            ??????
            MsgBox ? " Welcome?to?Pubs! "
            ???
            Else
            ??????
            MsgBox ? " Sorry.?No?Pubs?today. "
            ???
            End ? If ?

            ???
            ' ?Close?the?connection.
            ???cnn.Close?

            End?Sub
            ?

            ?

            This syntax for the ConnectionString property assumes that the data store has already been created by using the ODBC Administrator (or in code). It is becoming increasingly popular to not have to rely on existing ODBC data stores. This eases the setup burden. The next example shows an alternative method for connecting to SQL Server, relying merely on the existence of the ODBC driver itself:

            ?

            Sub ?ConnectionExample4()
            ???
            Dim ?cnn? As ?ADODB.Connection
            ???
            Set ?cnn? = ? New ?ADODB.Connection?

            ???
            ' ?Open?a?connection?by?referencing?the?ODBC?driver.
            ???cnn.ConnectionString? = ? " driver={SQL?Server}; " ? & ?_
            ??????
            " server=MySqlServer;uid=MyUserName;pwd=MyPassword;database=pubs "
            ???cnn.Open?

            ???
            ' ?Find?out?if?the?attempt?to?connect?worked.
            ??? If ?cnn.State? = ?adStateOpen? Then
            ??????
            MsgBox ? " Welcome?to?Pubs! "
            ???
            Else
            ??????
            MsgBox ? " Sorry.?No?Pubs?today. "
            ???
            End ? If ?

            ???
            ' ?Close?the?connection.
            ???cnn.Close?

            End?Sub
            ?

            Today there are a wide variety of ODBC drivers you can use with ADO to talk to data. In the future, there will be more OLE DB providers available to connect to data stores. The Microsoft? OLE DB Provider for ODBC is currently the default provider for ADO. You can use a different provider by setting the Provider property of the Connection object.

            ?

            Sub ?ConnectionExample5()
            ???
            Dim ?cnn? As ?ADODB.Connection
            ???
            Set ?cnn? = ? New ?ADODB.Connection?

            ???
            ' Set?the?provider?property?to?the?OLE?DB?Provider?for?ODBC.
            ???cnn.Provider? = ? " MSDASQL " ?

            ???
            ' ?Open?a?connection?using?an?ODBC?DSN.
            ???cnn.ConnectionString? = ? " driver={SQL?Server}; " ? & ?_
            ??????
            " server=MySqlServer;uid=MyUserName;pwd=MyPassword;database=pubs "
            ???cnn.Open?

            ???
            ' ?Find?out?if?the?attempt?to?connect?worked.
            ??? If ?cnn.State? = ?adStateOpen? Then
            ??????
            MsgBox ? " Welcome?to?Pubs! "
            ???
            Else
            ??????
            MsgBox ? " Sorry.?No?Pubs?today. "
            ???
            End ? If ?

            ???
            ' ?Close?the?connection.
            ???cnn.Close?

            End?Sub


            ?

            Sub ?ConnectionExample5()
            ???
            Dim ?cnn? As ?ADODB.Connection
            ???
            Set ?cnn? = ? New ?ADODB.Connection?

            ???
            ' Set?the?provider?property?to?the?OLE?DB?Provider?for?ODBC.
            ???cnn.Provider? = ? " MSDASQL " ?

            ???
            ' ?Open?a?connection?using?an?ODBC?DSN.
            ???cnn.ConnectionString? = ? " driver={SQL?Server}; " ? & ?_
            ??????
            " server=MySqlServer;uid=MyUserName;pwd=MyPassword;database=pubs "
            ???cnn.Open?

            ???
            ' ?Find?out?if?the?attempt?to?connect?worked.
            ??? If ?cnn.State? = ?adStateOpen? Then
            ??????
            MsgBox ? " Welcome?to?Pubs! "
            ???
            Else
            ??????
            MsgBox ? " Sorry.?No?Pubs?today. "
            ???
            End ? If ?

            ???
            ' ?Close?the?connection.
            ???cnn.Close?

            End?Sub
            ?


            In the code above, setting the Provider property is not necessary because the OLE DB Provider for ODBC is the default provider for ADO. However, this shows you how you would change the provider when you want to use other OLE DB providers.

            How Do I Use the Connection Object to Execute a Command?
            The Execute method is used to send a command (an SQL statement or some other text) to the data store. If the SQL statement returns rows, a Recordset object is created. (The Execute method always returns a Recordset object, but it is a closed Recordset if the command doesn't return results.)

            Sub ?ConnectionExample6()
            ???
            Dim ?cnn? As ?ADODB.Connection
            ???
            Dim ?rs? As ?ADODB.Recordset?

            ???
            Set ?cnn? = ? New ?ADODB.Connection?

            ???
            ' ?Open?a?connection?by?referencing?the?ODBC?driver.
            ???cnn.ConnectionString? = ? " driver={SQL?Server}; " ? & ?_
            ??????
            " server=MySqlServer;uid=MyUserName;pwd=MyPassword;database=pubs "
            ???cnn.Open?

            ???
            ' ?Create?a?Recordset?by?executing?an?SQL?statement.
            ??? Set ?rs? = ?cnn.Execute( " Select?*?From?authors " )?

            ???
            ' ?Show?the?first?author.
            ??? MsgBox ?rs( " au_fname " )? & ? " ? " ? & ?rs( " au_lname " )?

            ???
            ' ?Close?the?connection.
            ???rs.Close?

            End?Sub
            ?

            Remember that the returned Recordset object from connection.execute is always a read-only, forward-only cursor. If you need a Recordset object with more functionality, you should first create a Recordset object with the desired property settings and then use the Recordset object's Open method to execute the query and return the desired cursor type.

            In the following example, the command passed to the data source is a Delete statement. Because no rows are returned, you do not need to explicitly use a Recordset object. How many rows were deleted? You can use the recordsAffected parameter to find out.

            ?

            Sub ?ConnectionExample7()
            ???
            Dim ?cnn? As ?ADODB.Connection
            ???
            Dim ?rs? As ?ADODB.Recordset?

            ???
            Set ?cnn? = ? New ?ADODB.Connection?

            ???
            ' ?Open?a?connection?by?referencing?the?ODBC?driver.
            ???cnn.ConnectionString? = ? " driver={SQL?Server}; " ? & ?_
            ??????
            " server=MySqlServer;uid=MyUserName;pwd=MyPassword;database=pubs "
            ???cnn.Open?

            ???
            ' ?Send?a?Delete?statement?to?the?database.
            ???cnn.Execute?( " Delete?From?authors?Where?au_id?=?'011-01-0111' " )?

            ???
            ' ?Find?out?how?many?rows?were?affected?by?the?Delete.
            ??? Set ?rs? = ?cnn.Execute( " Select?@@rowcount " )
            ???
            ' ?Display?the?first?field?in?the?recordset.
            ??? MsgBox ?rs( 0 )? & ? " ?rows?deleted " ?

            ???
            ' ?Close?the?connection.
            ???rs.Close?

            End?Sub
            ?

            In the next example, the command passed to the data store specifies the name of a stored procedure to run. Because rows are returned, you do need to use a Recordset object.

            ?

            Sub ?ConnectionExample8()
            ???
            Dim ?cnn? As ?ADODB.Connection
            ???
            Dim ?rs? As ?ADODB.Recordset?

            ???
            Set ?cnn? = ? New ?ADODB.Connection?

            ???
            ' ?Open?a?connection?by?referencing?the?ODBC?driver.
            ???cnn.ConnectionString? = ? " driver={SQL?Server}; " ? & ?_
            ??????
            " server=MySqlServer;uid=MyUserName;pwd=MyPassword;database=pubs "
            ???cnn.Open?

            ???
            ' ?Create?a?recordset?by?running?a?stored?procedure.
            ??? Set ?rs? = ?cnn.Execute( " Exec?byroyalty?50 " )?

            ???
            ' ?Loop?through?the?recordset?and?show?the?author's?ID.
            ??? Do ? While ? Not ?rs.EOF
            ??????
            MsgBox ?rs( " au_id " )
            ??????rs.MoveNext
            ???
            Loop ?

            ???
            ' ?Close?the?connection.
            ???rs.Close?

            End?Sub
            ?

            posted on 2006-06-06 09:41 小石頭 閱讀(379) 評論(0)  編輯 收藏 引用

            只有注冊用戶登錄后才能發(fā)表評論。
            網(wǎng)站導(dǎo)航: 博客園   IT新聞   BlogJava   博問   Chat2DB   管理


            综合久久精品色| 日本一区精品久久久久影院| 久久午夜无码鲁丝片秋霞| 亚洲人成网亚洲欧洲无码久久| 久久亚洲日韩精品一区二区三区| 国产亚洲婷婷香蕉久久精品| 久久精品无码免费不卡| 日韩av无码久久精品免费| 国产精品伦理久久久久久| 国内精品久久久久伊人av| 内射无码专区久久亚洲| 久久线看观看精品香蕉国产| 久久亚洲精品国产亚洲老地址| 91精品观看91久久久久久| 久久精品国产亚洲AV嫖农村妇女 | 久久综合狠狠色综合伊人| 亚洲欧洲久久久精品| 国产精品热久久无码av| 国内精品久久人妻互换| 国内精品伊人久久久影院| 久久精品国产精品亚洲人人| 国产成人久久激情91| 午夜视频久久久久一区| 一级做a爰片久久毛片16| 久久免费国产精品一区二区| 久久99国产精品尤物| 久久SE精品一区二区| 久久久久亚洲AV成人网人人网站 | 精品久久久久久国产三级| 久久99久久99精品免视看动漫| 久久人人青草97香蕉| 一本色道久久88综合日韩精品 | 亚洲午夜久久久久妓女影院| 亚洲国产成人久久笫一页| 精品久久人人爽天天玩人人妻| 久久被窝电影亚洲爽爽爽| 97久久超碰成人精品网站| 97超级碰碰碰久久久久| 久久99久久99小草精品免视看| 久久亚洲综合色一区二区三区| 99久久精品免费看国产免费|