• <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  評(píng)論-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 小石頭 閱讀(380) 評(píng)論(0)  編輯 收藏 引用

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


            亚洲狠狠婷婷综合久久蜜芽 | 无码人妻久久久一区二区三区| 9191精品国产免费久久| 国产精品狼人久久久久影院| 久久人人爽人人爽人人片AV东京热| 亚洲乱码日产精品a级毛片久久| 中文字幕久久精品| 国产三级久久久精品麻豆三级| 久久久久久狠狠丁香| 久久久久久久综合狠狠综合| 人妻丰满AV无码久久不卡| 日本三级久久网| 国产免费久久精品99re丫y| 99久久99这里只有免费费精品 | 蜜桃麻豆WWW久久囤产精品| 人妻久久久一区二区三区| 国产一久久香蕉国产线看观看| 国内精品久久久久久久久| 久久精品国产免费观看三人同眠| 久久精品成人免费网站| 久久久久久国产精品无码下载| 日韩一区二区久久久久久| 国产A级毛片久久久精品毛片| 青青草原综合久久| 久久久久亚洲精品无码蜜桃| 久久亚洲精品无码观看不卡| 国内精品九九久久久精品| 亚洲综合精品香蕉久久网| 久久www免费人成看国产片| 99久久99久久| 999久久久免费精品国产| 中文字幕乱码人妻无码久久| 久久中文字幕无码专区| 国产免费久久久久久无码| 久久影院综合精品| 日本欧美久久久久免费播放网| 欧美性猛交xxxx免费看久久久| 国产亚州精品女人久久久久久 | 亚洲性久久久影院| 精品无码久久久久久国产| 草草久久久无码国产专区|