tailieunhanh - Microsoft SQL Server 2005 Developer’s Guide- P34

Microsoft SQL Server 2005 Developer’s Guide- P34:SQL Server 2005 is a feature-rich release that provides a host of new tools and technologies for the database developer. This book is written to help database developers and DBAs become productive immediately with the new features and capabilities found in SQL Server 2005. | Chapter 8 Developing Database Applications with ADO 309 result set. The following code illustrates how you can add rows to a Recordset object that was created using a keyset cursor Private Sub CursorAdd cn As Dim rs As New Dim i As Integer Pass in the SQL Connection Cursor type lock type and source type Select Dep_ID Dep_Name From _ cn adOpenKeyset adLockOptimistic adCmdText Add 50 rows to the table Note that the Bang notation is used to specify column names For i 1 To 50 rs Dep_ID i rs Dep_Name Department CStr i Next Display the new rows in a grid DisplayKeysetGrid rs Grid 1 End Sub The first parameter of the Recordset object s Open method accepts a string containing a SQL statement that defines the result set. In this case the result set consists of the Dep_ID and Dep_Name columns from the table created in the earlier dynamic SQL example. The second parameter of the Open method contains the name of an active Connection object named cn. The third parameter uses the constant adOpenKeyset to specify that the Recordset object will use a keyset cursor. The fourth parameter contains the value adLockOptimistic. These two parameters indicate this Recordset object set is updatable and will use optimistic record locking. After the result set has been opened a For Next loop is used to add 50 rows to the Recordset object. Within the For Next loop the AddNew method is called to create a row buffer that will contain the new row values. Unlike the earlier examples in this chapter that accessed columns by iterating through the Fields collection this example illustrates how to access individual columns using the column name and Bang notation. 310 Microsoft SQL Server 2005 Developer s Guide The value of the Dep_ID column is set using a unique integer value obtained by using the loop counter. The Dep_Name column is set using the string formed by .