Chapter 1
ActiveX Data Objects (ADO) – I
Universal Data Access (UDA):
Universal Data Access (UDA) is term which is used when describing Microsoft’s architecture for accessing data. The key technologies of Universal Data Access are ADO and OLE DB. Any data access technology must be reliable, fast and it must have broad support for all kinds of data. The reason from moving ODBC is that ADO and OLE DB offer much more, both in terms of functionality and in terms of supported data sources. Through Universal Data Access, we’ve given you access to data you couldn’t access in the past and maybe some data stores you’ve never even considered like accessing data from Email. Now it is possible to do context searches over a Web site using that same API. ADO and OLE DB offer that also. The first key is it will work with just about any tool you might want to use. This includes Visual Basic, Visual J++, Visual C++, Visual InterDev and Visual FoxPro. ADO also is programmable from Microsoft Office. You can even use ADO with Internet Information Server via Active Server Pages, and Internet Explorer via Dynamic HTML and Client Scripting. This is Universal Data Access. Use ADO and OLE DB to get whatever data you need from whatever tool you’re working with. What’s more, Visual Basic and Visual C++ both implement tools to help you write your own OLE DB providers. You can take your proprietary data format, expose it through a custom OLE DB provider and then just use that data just like any other data source. When designing OLE DB, MS the best of the data access interfaces to create Universal Data Access. ADO takes the best ideas from DAO and RDO and combines them into a single interface. Many of the ADO objects have the same names as their corresponding DAO counterparts. And much of the functionality, such as disconnected recordsets and events, were first exposed in RDO. Microsoft’s focus is Universal Data Access.
To work on ADO, client applications or middle tier components that use ADO, should be created. ADO, in turn, will communicate with any of several data providers, including the ODBC provider as well as providers for VSAM, email stores and directory services. A business object can be created also.
Another benefit of Universal Data Access is that all of these data access components are designed to participate in a Microsoft transaction server application.
Understanding OLE DB
OLE DB is Microsoft’s strategic, system-level programming interface to access data across an organization. Whereas Open Database Connectivity (ODBC) is designed to allow access to relational data, OLE DB is an open standard designed to allow access to all kinds of data. Conceptually, OLE DB has three types of components: data providers, data consumers, and service components.
Data Provider:
A data provider is the component that exposes data via the OLE DB interfaces. Data providers include the OLE DB provider for SQL server or a provider for the email store, for example. Data providers are applications, such as Microsoft SQL Server or Exchange, or operating system components, such as a file system, that have data that other applications may need to access. These data providers expose OLE DB interfaces that service components or data consumers can access directly. There is also an OLE DB provider for ODBC. This provider makes any ODBC data available to OLE DB data consumers.
Data Consumers:
Data consumers are applications that use the data exposed by data providers. ADO is the programmatic interface for using OLE DB data. Any application that uses ADO is an OLE DB data consumer. The data consumer is a component that uses data. This could be a COM object, a control or a stand alone app. Data consumers use ADO to get data from a data provider.
Service Components :
Sitting between the data consumer and the data provider are service components. OLE DB is based on COM, which gives it the capability to separate these data services into components. An example of a service component is a query processor. If you have some complex proprietary data, in the past to query that data, you would have needed to create your own query engine. Now, with OLE DB, you need only to expose the data as an OLE DB provider and then invoke a query processor service component over that data. Another example of a service component is a cursor engine, giving you scrolability over a forward only data source. Service components are components of OLE DB that process and transport data. These components include query processors and cursor engines. Architecturally, OLE DB is separated into components so data providers do not need to have the innate ability to provide data in a way that ADO can understand. These service components give ADO the ability to consume OLE DB data from providers that don’t inherently offer handling of result sets or interpretation of SQL queries.
Visual Data Access Tools :
Visual Basic 6 includes the Data Environment designer and the integrated Visual Database Tools to make it easier for you to create data access applications. When Visual Basic 6.0 was designed, one of r main goals of Microsoft was to make it easier than ever to develop database applications. One of the results of this design goal is that the Microsoft Visual Database Tools have been integrated directly into the Visual Basic environment. From within the IDE, now you can create and maintain tables, views, queries, and diagrams of your database. With the new SQL Editor, you can create and edit stored procedures, triggers, and Oracle functions and synonyms. You can also right click on a SQL Server stored procedure and debug it directly within the IDE. Moreover, ADO is the single object model you’ll need to access SQL Server, Jet, Oracle, AS/400, or any other data in your enterprise. Another thing which has improved is the enhancing VB’s data binding capabilities. Now a user can bind controls on forms to any data source: such as user – authored controls, classes, or even the Data Environment.
Data Environment Designer :
The Data Environment designer is a design-time tool that lets you create objects that access data. You use the Data Environment designer to:
Add a Data Environment to your project.
Add connections to databases.
Add commands to retrieve and manipulate data.
Data View Window :
The Data View Window can be used to:
List and browse any data source.
View and modify (if permitted) the structure of a database.
Query Builder:
You use the Query Builder to create queries that retrieve specific sets of data from a database.
Data Form Wizard:
You use the Data Form Wizard to generate Visual Basic forms that display and manage information from database tables and queries.
Data Report Designer :
You use the Data Report Designer to create reports from a recordset.
Accessing Data from Visual Basic
Although applications that use Data Access Objects (DAO) and Remote Data Objects (RDO) will run correctly in Visual Basic 6, ADO is the data-access method Microsoft recommends for new applications.
Advantages of Using ADO :
ADO is an evolution of the RDO and DAO architectures. ADO combines the best features of RDO and DAO, and replaces them with one robust, easy-to-use interface. RDO and DAO limited you to using ODBC and Jet compliant data providers. ADO, however, provides quick, high-performance access to all of the types of data and information that are available through OLE DB, while maintaining a low overhead in terms of memory and disk space.
For simple applications, you can use the Data Environment designer to add ADO objects to your project at design time. This technique requires little or no code to interact with a data source. In more sophisticated applications, you can add code to work with ADO objects and their properties, methods, and events.
When you work with ADO programmatically, you typically use the following three ADO objects:
Connection
An ADO Connection object is used to create a connection to a data source.
Command
An ADO Command object is used to return data from a connection. Command objects can also manipulate data in a data source or call a SQL Server stored procedure.
Recordset
An ADO Recordset object is used to store the result set of a query on the data source.
OLE DB Data Providers :
Applications using ADO consume OLE DB data by using the appropriate data provider. For example, you use the OLE DB provider for SQL Server to access data in a SQL Server database. Because there is an OLE DB provider for ODBC, you can write ADO code to access data in your existing ODBC data sources that do not have a native OLE DB provider.
Use the Data Environment Designer :
The Data Environment designer is an object you can add to your Visual Basic project that provides an interactive, design-time environment for creating objects that access data.
Adding Connections and Commands :
When you create a Data Environment, you add Connection and Command objects, and set property values for them. To see an illustration that shows an example of a project that contains a Data Environment with a connection and several commands.
Organizing Data :
You can use the Data Environment designer to relate multiple Command objects. This relation is called a command hierarchy. Within a hierarchy, you can create aggregate fields whose values are automatically calculated based on the hierarchy. For more information about relating commands, see Organizing Data in this chapter.
Data Environment Events :
In the code module associated with a Data Environment, you can add code to events generated by the Data Environment or by Connection objects and Command objects within a Data Environment. For the Data Environment itself, you can write code to its Initialize and Terminate events. The events generated by connections and commands in a Data Environment are ADO events.
Creating a Connection :
When you add a Data Environment to your project, a Connection object called Connection1 is included by default. You can also create new connections at any time by choosing Add Connection from the Data Environment designer toolbar. For more information about connections, see Setting Connection Properties in this chapter.
To create a new Data Environment
1. If the Add Data Environment command is not present on the Project menu, then on the Project menu, click Components.
2. In the Components dialog box, click the Designers tab and select the Data Environment check box.
3. On the Project menu, click Add Data Environment.
4. Use the Properties window to set the Name property of the Data Environment to something appropriate for your application.
This is the name you will use to refer to the Data Environment programmatically.
Setting Connection Properties :
When you create a connection in the Data Environment you need to specify details about the data source that you are connecting to. The details about this connection are collectively referred to as the data link properties.
Use the Data Link Properties dialog box to select the data provider and set the properties for a connection. To display the Data Link Properties dialog box, in the Data Environment window, right-click a connection name, and then click Properties.
Use the Provider tab of the Data Link Properties dialog box to select the OLE DB provider you want to use to connect to a data source. Use the Connection tab to provide the necessary connection information. To see an illustration of the Connection tab in the Data Link Properties dialog box for the Microsoft OLE DB provider for SQL Server, click this icon.
Connection properties vary depending on the data provider you select.
Practice: Connecting To a Data Source :
In this practice exercise, you will create a new Visual Basic project and add a Data Environment to it. You will then create an OLE DB connection to the Northwind database.
Add a Data Environment to a Visual Basic project
1. Open Visual Basic and create a Standard EXE project named Practice.
2. On the Project menu, click Add Data Environment.
3. In the Data Environment window, right-click Connection1 and choose Properties.
4. On the Provider tab of the Data Link Properties dialog box, select the Microsoft Jet 3.51 OLE DB Provider from the provider list, and then click Next.
5. On the Connection tab of the Data Link Properties dialog box,
a. In the Select or enter a database name text box, select or type the path of Nwind.mdb.
b. Accept the default information to log on to the database.
c. Click the Test Connection button to verify the connection to the data source and click OK.
6. Click OK to save the data link properties.
7. Use the Properties window to change the name of the Data Environment to dePractice and to change the name of the connection to Northwind.
Using the Data View Window :
Once you have added a connection to a data source to a Data Environment in your project, you can use the Data View window to examine database structure.
To view the structure of a database
1. On the Visual Basic View menu, click Data View Window, or use the Data View Window button on the Visual Basic toolbar.
2. Click to expand the parts of the database structure you want to view.
Getting Data from a Connection :
Creating a Command :
A Command object can be used to define what data to retrieve from a connection. The command can be based on a table, view, stored procedure, or SQL statement.
To Add A Command :
- Right-Click A Connection In The Data Environment Window And Select Add Command.
To Set The Properties Of A Command :
1. In the Data Environment window, right-click the command that just has been added, and choose Properties.
2. On the General tab in the Command Properties dialog box,
a. Type a meaningful name for your command into the Command Name text box.
b. From the Connection list box, choose the Connection object that you want to base the command on.
c. In the Source of Data group box, click SQL Statement, and then click the SQL Builder button to open the Query Builder. Visually create a query by dragging and dropping objects from the Data View window to the Query Builder Design window and choose the fields you want returned in the recordset. If you want to create a query that retrieves information from multiple tables, you can drag a field from one table to the related field in another table to create a join. The Query Builder will then generate a SQL statement for use by the Command object.
d. Right-click anywhere in the Query Builder Design window and select Run to see the results of the command.
Accessing the Result of Command :
You can access the records returned by a Command object in code by using an ADO Recordset object, available through the Data Environment. To access the Recordset object for a particular command, use the name of the Command object preceded by “rs”. The following example code shows how to move to the next record in a recordset returned from a command called AllBooks in a Data Environment called deStateUBookstore:
deStateUBookstore.rsAllBooks.MoveNext
Note : The Command object must be open in order to use the recordset. If the command is bound to a field on a form, it will automatically be opened and available. Otherwise, you’ll need to declare an instance of the Data Environment and then execute the appropriate method of the Command object, as shown in the example code above.
Organizing Data:
Grouping Records:
The SQL language provides syntax for grouping similar records. Alternatively, you can use the Command Properties dialog box in the Data Environment designer to group records.
Note : Grouping only makes sense if there are multiple records in the set with a like value.
To group records within a Command
1. In the Data Environment window, right-click the command for which you want to group records and select Properties.
2. Select the Grouping tab of the Command Properties dialog box.
3. Click the Group Command Object check box, and choose the fields you want to use for grouping.
Relating Commands :
You can relate several commands to create a hierarchical relationship of data. For example, you can relate commands to create a hierarchical display of all publishers and the books produced by each publisher.
To relate commands, you specify a common field in each recordset. For example, you can relate publishers and books based on the field pub_id.
After you relate commands, they form a command hierarchy. The command hierarchy is based upon a parent-child, or one-to-many, relationship in the data. For example, each publisher can publish many books.
To relate two command objects
1. In the Data Environment window, right-click the child command (the “many” side of the one-to-many relationship) and select Properties.
2. On the Relation tab of the Command Properties dialog box,
a. Check the Relate to a Parent Command Object check box.
b. Enter the name of the related parent command (the “one” side of the one-to-many relationship) in the Parent Command box.
c. Make sure that the correct parent field and child field are selected in their respective drop down boxes, and click Add.
3. Click OK in the Properties dialog box.
You will see the commands as related in the Data Environment.
The hierarchy created by relating two commands is created by using the SHAPE syntax. To view the SQL syntax, right-click the parent in the command hierarchy and click Hierarchy Info.
Using Aggregates :
You can use aggregates to display data that is calculated from a recordset. For example, if you want to find out how many students are in each class, you can use the aggregate Count function to calculate the number of students in each class and display the count in a separate field for each class.
To use aggregate functions, on the Aggregates tab in the Command Properties dialog box specify the type of calculation you want performed. Choices include calculations such as sum, count, and average. Visual Basic will generate the SQL statement to produce the results.
Chapter 2
Using ActiveX Data Objects
In Chapter ActiveX Data Objects ( ADO ), you learned how to use the visual data access tools to create solutions that access data without writing much code yourself. In this chapter, you will learn how to write code that uses ActiveX Data Objects (ADO) to connect to a data source, retrieve and manipulate data, and disconnect from a data source. You will also learn how to build and manage disconnected recordsets.
ADO a Quick Revision :
ADO allows you to access and manipulate data from a data source. It also provides a universal data access interface. Writing code to ADO provides access to object properties, methods, and events, allowing for more flexibility than that provided by the visual data access tools. In addition, ADO is supported by a variety of development platforms. For example, using ADO, you can add code to Active Server Pages to return data from a database to a Web page. Using the same code, you can also return those records to a Visual Basic program.
The following development platforms can implement solutions using ADO:
Microsoft Visual Basic
Microsoft Visual InterDev
Microsoft Visual C++
Microsoft Visual J++
Microsoft Visual FoxPro
Microsoft VBScript
Microsoft Visual Basic for Applications
Understanding the ADO object Model :
ADO objects provide you with fast and easy access to all types of data. The ADO object model has three main components: the Connection object, the Command object, and the Recordset object.
The ADO object model differs from the RDO and DAO object models in that many of the objects can be created independently of one another. For example, you can create a Recordset object without first explicitly creating a Connection object. ADO
ADO Objects :
The three main components of the ADO object model are the Connection object, the Command object, and the Recordset object.
Connection Object :
The Connection object is the highest-level object in the ADO object model. It is used to make a connection between your application and an external data source, such as Microsoft SQL Server.
Command Object :
The Command object is used to build queries, including user-specific parameters, to access records from a data source. Typically, these records are returned in a Recordset object.
Recordset Object :
The Recordset object is used to access records returned from a SQL query. Using this object, you can navigate returned records, modify existing records, add new records, or delete specific records.
ADO Collections :
ADO supports three collections: the Errors collection, the Parameters collection, and the Fields collection. While these collections can provide additional functionality to an application, they are not required to build ADO solutions.
Errors Collection :
The Errors collection is used to return detailed information about run-time errors or other messages returned from a data source.
Parameters Collection :
The Parameters collection is used to pass specific data to a parameterized query or stored procedures in a SQL Server database.
Fields Collection :
The Fields collection is used to access specific fields in an existing Recordset object.
Handling Data Access Errors :
Trapping ADO errors is similar to trapping errors in most Visual Basic applications. You enable an error trap and write error-handling code to contend with errors that may occur. When you use error trapping the Err object is cleared for you. If you use inline error handling to handle errors without an error trap, you should use the Clear method to remove any existing error information before proceeding with code execution. However, unlike other Visual Basic applications where you only rely on the Err object for error information, when you write ADO code you also need to use the ADO Errors collection to get more detailed information about an error or a group of errors from the data source.
When an error occurs or the data source returns a message, an Error object is created and added to the Errors collection. Error traps or inline code can interrogate the Errors collection and each specific Error object for more detailed information.
Note : Some data sources return non-critical information to the ADO Errors collection in the form of a message.
Sub StartConnection()
‘ Declare a connection and error object
Dim cn As Connection
Dim adoErr As Error
Set cn = New Connection
‘ Enable the error trap
On Error Goto StartConnection_Handler
‘ Establish a connection to the data source
With cn
.Provider = “SQLOLEDB”
.ConnectionString = “User ID=sa;” & _
“Data Source=MSERIES1;” & _
“Initial Catalog=StateUBookstore”
.Open
End With
‘ If the connection succeeds, exit the procedure
Exit Sub
StartConnection_Handler:
‘ If an error occurs, loop through the collection
‘ There can be more than one error code for a single
‘ Run time error
For Each adoErr in cn.Errors
‘ Show each error description to the user
MsgBox adoErr.Description
Next
End Sub
Note : For the previous sample code to work correctly, the developer must have set a reference in the project to the Microsoft ActiveX Data Objects 2.0 Library.
Connecting to a Data Source :
The Connection object establishes a connection to a data source. It allows your application to pass client information, such as username and password, to the database for validation.
To use ADO to establish a connection to a database
1. Set a reference to the ADO Object Library.
2. Declare a Connection object.
3. Specify an OLE DB data provider.
4. Pass connection information.
Once you have completed these steps, you will be ready to establish a connection using the Open method.
Setting a Reference to ADO :
Before you can use ADO in your Visual Basic application, you must first set a reference to the Microsoft ActiveX Data Objects 2.0 Library.
To create a reference to the ADO Object Library
1. On the Project menu, click References.
2. Select Microsoft ActiveX Data Objects 2.0 Library, and then click OK.
Declaring a Connection Object :
Once you have made a reference to the ADO object library, you can declare a Connection object in your application. Using the Connection object, you can then create a Command object or Recordset object.
Note : Unless you are using both ADO and DAO in the same application, you do not need to use the prefix “ADODB” before ADO data types.
The following example code declares and instantiates a new Connection object:
Dim cn As Connection
Set cn = New Connection
Specifying a Data Provider :
Once you have instantiated a Connection object, you must specify an OLE DB data source provider. You do this by setting the Provider property.
The following example code specifies the Microsoft SQL Server OLE DB data provider:
cn.Provider = “SQLOLEDB”
Passing Connection Information :
The final step before establishing a connection to a data source is to specify the connection information. You do this by setting the Connection object’s ConnectionString property. Connection string arguments are provider specific, are passed directly to the provider, and are not processed by ADO.
The following connection string arguments are used with the SQL Server OLE DB provider.
Connection argument Description
User ID Valid user name
Password Valid user password
Data Source Name of the remote server
Initial Catalog Database name in the external data source
The following example code specifies a Microsoft SQL Server data provider and supplies connection information in the ConnectionString property:
With cn
.Provider = “SQLOLEDB”
.ConnectionString = “User ID=sa;Password=; Data Source=MSERIES1;” & _
“Initial Catalog=StateUBookstore”
End With
The following table describes some of the OLE DB providers currently available.
OLE DB data provider Description
SQLOLEDB OLE DB provider for Microsoft SQL Server
MSDASQL OLE DB provider for ODBC
Microsoft.Jet.OLEDB.3.51 OLE DB provider for Microsoft Jet
MSIDXS OLE DB provider for Microsoft Index Server
ADSDSOObject OLE DB provider for Microsoft Active Directory Service
MSDAORA OLE DB provider for Oracle
Connecting To And Disconnecting From A Data Source :
Proper connection management is critical for efficient use of both client and server resources. Although ADO will disconnect automatically when an object goes out of scope, it is proper coding technique to explicitly close the connections your application opens. This also ensures that any server-side resources are released.
Connecting to a Data Source :
Once you have specified an OLE DB data provider and have passed the ConnectionString information, you use the Open method to establish a connection to the data source.
The following example code creates a connection to a Microsoft SQL Server database called StateUBookstore on the server called MSERIES1 using the SQL Server OLE DB data provider:
Sub cmdConnect_Click()
‘ The connection object variable cn
‘ was declared at Module level
‘ Instantiate the connection object variable
Set cn = New Connection
‘ Establish a connection
With cn
.Provider = “SQLOLEDB”
.ConnectionString = “User ID=sa; Data Source=MSERIES1;” & _
“Initial Catalog=StateUBookstore”
‘ Open the connection
.Open
End With
End Sub
Disconnecting from a Data Source :
Once you have finished with the connection, you use the Close method to disconnect from a data source. In the case of a SQL Server, any server-side resources that were in use under this active connection will be released. It is proper coding technique for all open connections to be closed before the application is terminated.
The following example code closes an active connection to a data source and releases the Connection object variable:
Sub cmdClose_Click()
cn.Close
Set cn = Nothing
End Sub
Using Connection Object Events :
The Connection object supports a number of events that allow your application to execute custom code. These events are associated with connecting to a data source, executing SQL commands, and managing transactions at the connection level.
The following table describes the events associated with the Connection Object.
Event Description
AbortTransaction Fires after the RollbackTrans method is called.
BeginTransaction Fires after the BeginTrans method is called.
CommitTransaction Fires after the CommitTrans method is called.
ConnectComplete Fires when a connection attempt has completed successfully, failed, or timed out.
Disconnect Fires when an active connection is closed.
ExecuteComplete Fires after the Execute method is called.
InfoMessage Fires when a message is returned from OLE DB or the data source.
WillConnect Fires after the Open method is called, but before the connection is established.
WillExecute Fires after the Execute method is called, but before the command is completed.
Enabling ADO Events :
When you declare an ADO object, you can use the WithEvents keyword to expose the object’s events to your application. If you declare a Connection object with its events exposed, the Connection object appears in the Visual Basic Object box list and all the available events for the object appear in the Procedures/Events box list.
The following example code declares a Connection object and exposes its events:
Public WithEvents cn As Connection
You can now use the Connection object to execute statements or create Command objects or Recordset objects.
Adding Code to Connection Object Events :
When you declare a Connection object using the WithEvents keyword, code is added to any of its associated events. The following example code displays a message box to the user when a connection attempt has successfully completed:
Sub cn_ConnectComplete(ByVal pError As ADODB.Error, _
adStatus As ADODB.EventStatusEnum, _
ByVal pConnection As ADODB.Connection)
If adStatus = adStatusOK Then
MsgBox “The connection has been established with the data source.”
End If
End Sub
Note : There are additional values that can be returned in the EventStatusEnum object.
Using Multiple connection on a Datasbase :
While it is possible to open multiple active connections to the same data source, it is not advised. Each active connection consumes resources on both the client and the server. For example, if your application opens two active connections to a SQL Server, the server treats each connection as a separate request, even though they originated from the same application. Therefore, each connection is given equal access to the server resources. One reason that your application might open more than one connection to the same data source is that you need to use separate transaction spaces.
If your application requires multiple recordsets built against the same data source, ADO supports the creation of independent recordsets from the same active connection.
Once independent connections have been established, you can execute SQL statements or build recordsets from each of the Connection objects.
Retrieving Data From A Data Sources :
Using the Command Object :
The ADO Command object accesses and builds queries that are executed against a data source. You can also use Command objects to access stored procedures in an external database.
Using ADO, you don’t have to create a Connection object before using the Command object. However, if you do not associate the new Command object with an active connection, a new implicit Connection object will be created automatically, using additional server resources.
Property Description
ActiveConnection Sets or returns the active connection used by the object.
CommandText SQL command, stored procedure name, or table name that will be used by the object.
CommandType Indicates if the CommandText property is an SQL command, stored procedure, or a table name.
Prepared Indicates whether the SQL command should be created as a temporary stored procedure.
State Indicates whether the command is currently opened, closed, or executing.
Note : If you do not use an active connection when you create the Command object, you must pass the required connection string to the ActiveConnection property.
Command Object Methods :
The following table describes methods of the Command object.
Method Description
Cancel Cancels the currently executing command.
CreateParameter Creates a parameter object (for use with stored procedures).
Execute Executes a SQL command.
Creating a Command from a Connection:
Although an existing active connection is not required, it is more efficient to create Command objects from established connections. Once the Command object has been created, it can be used to execute the specified command or build a recordset.
The following example code uses a Connection object and a Command object to increase the book price for all records in the Books table by 10 percent:
Dim comPriceUpdate As Command
Set comPriceUpdate = New Command
With comPriceUpdate
‘ An existing Connection Object is referenced
.ActiveConnection = cn
.CommandText = “UPDATE Books SET Price = Price * 1.1″
‘ call the Execute method to update the prices
.Execute
End With
Creating a Stand-Alone Command Object :
Since ADO provides a flat object model, you do not have to explicitly create a Connection object. Instead, you can pass the required connection information to the ActiveConnection property of the Command object. Then, when you use the Execute method to run the SQL command, a connection is established for you. However, using this technique, you cannot access the Connection object from your Visual Basic code.
The following example code uses a Command object to increase the book price for all records in the Books table by 10 percent:
Dim comPriceUpdate As Command
Set comPriceUpdate = New Command
With comPriceUpdate
‘ No connection object is used
.ActiveConnection = “Provider=SQLOLEDB;” & _
“User ID=sa;” & _
“Data Source=MSERIES1;” & _
“Initial Catalog=StateUBookstore”
.CommandText = “UPDATE Books SET Price = Price * 1.1″
‘ call the Execute method to update the prices
.Execute
End With
Using the Recordset Object :
The Recordset object allows your application to access data returned from a SQL query. This query can be created by the application, or it can reside on the server as a stored procedure. Using the Recordset object, you can navigate the records that have been returned, or edit their values.
Recordset Object Properties:
Property Description
ActiveCommand Returns the active command for the recordset.
ActiveConnection Sets or returns the active connection for the recordset.
CursorLocation Sets or returns the location of the cursor. The default is adUseServer.
CursorType Sets or returns the cursor type. The default is adOpenForward.
LockType Sets or returns the type of record locking. The default is adLockReadOnly.
MaxRecords Sets or returns the maximum number of records to return.
PersistFormat Determines the format in which the recordset data is saved when calling the Save method.
RecordCount Returns the number of records in the recordset.
State Returns the current state of the recordset.
Recordset Object Methods :
Method Description
Open Executes a SQL command and opens a cursor.
Close Closes the recordset.
Requery Re-executes a SQL command and rebuilds the recordset.
Resync Refreshes cached records in a recordset.
Save Saves an open recordset to a file that can be re-opened later.
Creating a Recordset :
You can build a Recordset object based on an active connection to a data source. This limits the number of connections and can reduce the amount of client and server resources used by your application. Depending on the needs of the recordset, you may need to build an explicit Connection object or Command object first. If you do not explicitly use a Connection object or a Command object, a stand-alone recordset is automatically created.
The functionality of the recordset you create is determined by the values specified for the CursorLocation and CursorType properties.
Using a Connection Object and a Command Object :
You can create a new recordset from an existing Command object. Open the connection and create the Command object. Then use the Command object’s Execute method to build the recordset.
Sub cmdConnect_Click()
‘ Declare the object variables
Dim cn As Connection
Dim comPriceUpdate As Command
Dim rs As Recordset
‘ Instantiate the variables
Set cn = New Connection
Set comPriceUpdate = New Command
Set rs = New Recordset
‘ Establish a connection
With cn
.Provider = “SQLOLEDB”
.ConnectionString = “User ID=sa;” & _
“Data Source=MSERIES1;” & _
“Initial Catalog=StateUBookstore”
.Open
End With
‘ Create a Command object
With comPriceUpdate
.ActiveConnection = cn
.CommandText = “SELECT StudentID FROM Students”
End With
‘ Build the recordset
Set rs = comPriceUpdate.Execute
End Sub
Using the Open Method :
You can create a new recordset directly from an existing active connection. Open the connection normally, and pass the Connection object to the recordset using the Open method.
Sub cmdOpenRecordset_Click()
‘ Declare and instantiate the object variables
Dim cn As Connection
Dim rs As Recordset
Set cn = New Connection
Set rs = New Recordset
‘ Establish a connection
With cn
.Provider = “SQLOLEDB”
.ConnectionString = “User ID=sa;” & _
“Data Source=MSERIES1;” & _
“Initial Catalog=StateUBookstore”
.Open
End With
‘ Open a recordset using the Connection object variable
rs.Open “SELECT StudentID FROM Students”, cn
End Sub
Using the Execute Method :
You can also create a recordset using the Execute method of the Connection object. The Execute method does not support the same arguments as the Open method and therefore your recordset assumes the properties set originally on the Connection object. Using this technique, you are limited in the features that can be associated with the recordset.
Sub cmdOpenRecordset_Click()
‘ Declare and instantiate the object variables
Dim cn As Connection
Dim rs As Recordset
Set cn = New Connection
Set rs = New Recordset
‘ Establish a connection
With cn
.Provider = “SQLOLEDB”
.ConnectionString = “User ID=sa;” & _
“Data Source=MSERIES1;” & _
“Initial Catalog=StateUBookstore”
.Open
End With
‘ Open a recordset using the Connection object variable
rs.Open “SELECT StudentID FROM Students”, cn
End Sub
Creating a Stand-Alone Recordset :
When you create a stand-alone Recordset object, a Connection object does not have to already exist. A Connection object will be created automatically, but it cannot be accessed from Visual Basic. When the Open method is used, the required connection information must be passed to ADO.
The following example code shows how to create a Recordset object as a stand-alone object:
Dim rs As Recordset
Set rs = New Recordset
rs.Open “SELECT StudentID FROM Students”, _
“Provider=SQLOLEDB;” & _
“User ID=sa;” & _
“Data Source=MSERIES1;Initial Catalog=StateUBookstore”
Presenting Data To The User :
There are two ways to present data to the user. You can manually place the contents of the current record’s fields in appropriate controls, such as a text box, or you can bind the controls to the Recordset object.
Referencing Fields in a Recordset :
In order to show a record’s value to the user, you must reference the fields of the recordset. The most efficient technique is to reference the field name directly. You can use the Fields collection, which is more explicit code, but it is less efficient. The following example code directly references the First_Name field of a recordset:
‘ most efficient
txtFirstName.Text = rs!First_Name
‘ explicit, but less efficient
txtFirstName.Text = rs.Fields(“First_Name”).Value
Note : Some databases such as Microsoft Access allow spaces in field names. VB supports this syntax, but square brackets must be placed around field names that contain a space.
Populating Controls Manually :
After a recordset has been created, you can manually reference fields of the recordset in order to present its values to the user. For example, if the Visual Basic form you are using has two text boxes, one to display the student’s first name and a second to display the student’s last name, you can manually populate the text boxes. This process must occur each time the user navigates to a new record. The following example code populates the text boxes on a form:
Sub FillControls()
txtFirstName.Text = rs!First_Name
txtLastName.Text = rs!Last_Name
End Sub
Binding Controls to a Recordset :
New to Visual Basic is the ability to bind controls to objects. This is similar to the technique that you use to bind controls to an ActiveX Data control. Using the Recordset object, you can bind any data-aware control to any field in a recordset. As the user navigates from record to record, the text box automatically shows the record’s values.
The following example code binds a text box to a field of an existing Recordset object:
Set txtFirstName.DataSource = rs
txtFirstName.DataField = “First_Name”
Set txtLastName.DataSource = rs
txtLastName.DataField = “Last_Name”
Note : Use the binding technique as an alternative to writing a general procedure that manually populates controls on a form.
Navigating through a Recordset :
Of the ADO objects, only the Recordset object allows users to navigate through a group of records. Only one record within a recordset can be current at a given time. Therefore, the Recordset object supports a number of properties and methods that allow users to navigate through the recordset.
Recordset Navigation Properties :
Property Description
AbsolutePage Sets or returns the absolute page in which the current record exists.
AbsolutePosition Sets or returns the absolute position of the current record (this can be affected by record additions or deletions).
BOF Indicates if the pointer has moved before the first record.
Bookmark Returns a unique identifier for the current record. This property can also be used to move the pointer to a specified record.
EOF Indicates if the pointer has moved past the last record.
Recordset Navigation Methods:
Method Description
Move Moves a specified number of records forward or backward.
MoveFirst Moves to the first record.
MoveLast Moves to the last record.
MoveNext Moves to the next record.
MovePrevious Moves to the previous record.
Sub cmdFirst_click()
rs.MoveFirst
End Sub
Sub cmdLast_Click()
rs.MoveLast
End Sub
Sub cmdPrevious_Click()
rs.MovePrevious
‘ Check to see if tried to move prior to the first record
If rs.BOF then
‘ Moved prior to the first record
‘ Set the user back to the first
rs.MoveFirst
End If
End Sub
Sub cmdNext_Click()
rs.MoveNext
‘ Check to see if tried to move beyond the last record
If rs.EOF then
‘ Moved beyond the last record
‘ Set the user back to the last
rs.MoveLast
End If
End Sub
Sorting Searching Data:
Using the Sort property of a recordset, you can specify the order in which the records appear in an existing recordset. This eliminates the need to return to the data source, perhaps over a network, to get a new recordset of sorted data. Depending on the client’s computer and size of the recordset, this may be more efficient than re-creating the recordset.
Note : To use the Sort property with SQL Server you must use a client-side cursor. This is because, depending on the provider, a server-side cursor may or may not support sorting and SQL server does not.
When applying a sort, you can choose between the ASC and DESC keyword. To sort the records in ascending order, use ASC. To sort the records in descending order, use DESC.
The following example code sorts an existing recordset based on the LastName field of a recordset in ascending order:
rs.Sort = “LastName ASC”
Disabling a Sort:
In order to return a recordset to its original order, set the Sort property to an empty string. The following example code disables a sort:
rs.Sort = “”
Filtering Records:
Using the Filter property, you can limit the records that are presented. The original records are still available in memory, but only those that meet the filter’s requirements will be displayed as part of the recordset.
The following example code shows only students that have an account balance greater than $1,000:
rs.Filter = “AccountBalance > 1000″
Disabling a Filter:
In order to return a recordset to its original contents, set the Filter property to the adFilterNone constant. The following example code disables a filter, making all original records available:
rs.Filter = adFilterNone
Searching Records:
Although the Filter property can be used to return a group of specific records from a recordset, you can also search for a specific record. Use the Find method to search on a record that matches your criteria.
The following example code moves the recordset pointer to the first record that has “Gray” as a last name:
rs.Find “Last_Name = ‘Gray’”
Note: When specifying a string value, use single quotes around the text. When specifying a date, use the # symbol around the value.
The following table describes the parameters supported by the Find method.
Parameter Description
Criteria Expression stating the field to search and the value to find.
SkipRows Number indicating how many rows to skip when starting the search. Set this value to zero (default) to include the current row in the search. Set it to 1 to skip the current row.
Direction Specifies which direction from the current record the search should progress ( adSearchForward or adSearchBackward ). The default is adSearchForward.
Start Bookmark to use as the starting position.
The following table describes the search clauses to use when specifying criteria.
Clause Description
FieldName Name of the field being searched
Operator =, <, >, “like”
Value Date, String, Number
Note : You can use the Find method together with the Sort and Filter properties to refine your search.
Updating Data:
Using the execute Method :
When your application needs to update data in an external data source, you can either execute direct SQL statements or use a Recordset object (and its various methods for modifying data). Using SQL statements works best when a large number of records need to be updated. Using SQL statements is also more efficient for both the client and the server.
You can use either a Connection object or a Command object to execute SQL statements directly. It is preferable to use a Connection object when the SQL command will be issued only once. If your application needs to send the same SQL command to the data source more than once, it is more efficient to use a Command object. Both objects use the Execute method to send the SQL command to the data source.
Inserting New Records :
You can use the SQL Insert statement to issue a command that will add a new record (or group of records) in a data source.
Sub cmdAddRecord_Click()
‘ Declare and instantiate the object variable
Dim cn As Connection
Dim sSQL As String
Set cn = New Connection
‘ Establish a connection
With cn
.Provider = “SQLOLEDB”
.ConnectionString = “User ID=sa; Data Source=MSERIES1;” & _
“Initial Catalog=StateUBookstore”
.Open
End With
‘ Build the SQL command
sSQL = “INSERT INTO Students(First_Name, Last_Name) ” & “VALUES (‘Lani’, ‘Ota’)”
‘ Execute the SQL command
cn.Execute sSQL
End Sub
Updating Records :
You can use the SQL Update statement to issue a command that changes a record or group of records.
Sub cmdUpdateRecord_Click()
‘ Declare and instantiate the object variable
Dim cn As Connection
Dim sSQL As String
Set cn = New Connection
‘ Establish a connection
With cn
.Provider = “SQLOLEDB”
.ConnectionString = “User ID=sa; Data Source=MSERIES1;” & _
“Initial Catalog=StateUBookstore”
.Open
End With
‘ Build the SQL command
sSQL = “UPDATE Students SET MajorID = 4 WHERE StudentID = 3″
‘ Execute the SQL command
cn.Execute sSQL
End Sub
Deleting Records :
You can use the SQL Delete statement to issue a command that deletes a record or group of records in a data source.
Sub cmdDeleteRecord_Click()
‘ Declare and instantiate the object variable
Dim cn As Connection
Dim sSQL As String
Set cn = New Connection
With cn
.Provider = “SQLOLEDB”
.ConnectionString = “User ID=sa; Data Source=MSERVER;” & _
“Initial Catalog=StateUBookstore”
.Open
End With
‘ Build the SQL command
sSQL = “DELETE FROM Books WHERE BookID = 3″
‘ Execute the SQL command
cn.Execute sSQL
End Sub
Using A Recordset :
If your application has already opened a recordset, you can modify data using the recordset’s methods. Modifying records with a Recordset object is limited to a single addition, deletion, or update at a time. Consider using the Connection object’s Execute method for performing multiple updates at once.
Adding a New Record :
Adding a new record to a recordset is a two-step process. Your application must first create a new record to be added, and then it must use the Update method to send the change to the data source. To create a new record entry, use the AddNew method.
Note: The CursorType and LockType properties of the Recordset object must be set to the appropriate values in order to be able to call the AddNew, Update, or Delete methods.
The following example code creates a recordset and adds a new customer record:
Dim rs As Recordset
Set rs = New Recordset
‘ open a recordset
rs.CursorType = adOpenKeyset
rs.LockType = adLockOptimistic
rs.Open “SELECT First_Name, Last_Name FROM Students“, cn
‘ add a new record
rs.AddNew
Once the user has entered the new record’s information, you must use the Update method to send the changes to the data source. The changes will then be made permanent. New records will be added to the end of the current Recordset object, regardless of the original SQL command used to create the recordset. The following example code updates a recordset:
rs.Update
Deleting a Record:
To delete the current record from the recordset and the data source, use the Delete method. Unlike adding or changing a record, there is only one step to cause a deletion; you do not need to use the Update method.
The following example code deletes the current record from the recordset:
rs.Delete
After deleting a record, you should add code to ensure that there will be no attempts to reference data belonging to a record that has been deleted and is, therefore, invalid. The following example code shows one technique for setting the current record to one that is valid.
rs.MoveNext
If rs.EOF Then
rs.MoveLast
End If
Changing a Record
To edit records in an ADO recordset, specify the field to change and the new value. Then use the Update method as you would to add a new record to save the changes to the data source. These changes are reflected in the current recordset without refreshing.
Note: Unlike DAO and RDO, there is no Edit method in ADO.
The next example code creates a new Recordset object. The application navigates to the last record and changes the student’s major:
Dim rs as Recordset
Set rs = New Recordset
rs.Open “SELECT MajorID FROM Students”
rs.MoveLast
rs!MajorID = 2
rs.Update
Using Disconnected Data :
Creating A Disconnected Recordset Object :
Using disconnected recordsets, ADO allows your application to create a recordset, disconnect from the data source, and let the user view and edit the recordset offline. When the user has made the desired changes, your application can reconnect to the data source and update the database.
Note : To support a disconnected recordset, you must specify that the recordset will be built on the client.
Creating a Client-Side Recordset :
To specify that the recordset should be built on the client, use the adUseClient parameter with the Recordset object’s Open method.
Sub cmdConnect_Click()
‘ Declare and instantiate object variables
Dim cn As Connection
Dim rs As Recordset
Set cn = New Connection
Set rs = New Recordset
‘ Establish a connection
With cn
.Provider = “SQLOLEDB”
.ConnectionString = “User ID=sa; Data Source=MSERIES1;” & _
“Initial Catalog=StateUBookstore”
.Open
End With
‘ Build a recordset
With rs
‘ Specify the cursor’s location
.CursorLocation = adUseClient
.LockType = adLockBatchOptimistic
.Open “SELECT First_Name, Last_Name FROM Students”, cn
End With
End Sub
Disconnecting from the Data Source :
Setting the recordset’s ActiveConnection property to Nothing disconnects the recordset from the active connection. Your application can then close the active Connection object using the Close method. If the adUseClient parameter was used with the Open method, the client will have a copy of the recordset data and can begin navigating or updating the records. The following example code closes the connection and disconnects from the data source:
Set rs.ActiveConnection = Nothing
cn.Close
Making Offline Changes :
Once a recordset has been disconnected from the data source, your application can use any of the data update and navigation techniques. New records can be added using the AddNew method, records can be deleted using the Delete method, and existing records can be changed using the Update method. However, all changes are cached on the client until the connection is re-established.
Note : Offline changes made to the same record by more than one user will cause a conflict when the data is saved back to the data source.
Persisting a Recordset :
Using the Save method, a recordset’s contents can be saved to a file. This process is called persisting data. This is particularly useful if the user wishes to close the application and return to it later without reconnecting to the data source.
Persisting data is also useful for a disconnected recordset, since the connection and the application can be closed while the recordset is still available on the client computer.
Saving Data to a File :
To save the contents of the current recordset to a file, use the Save method. The Save method allows you to specify the format of the data and whether an existing file should be overwritten. By default, the Save method fails if a file with the same name already exists.
Note: If a filter is currently active, only the records that are visible through the filter will be saved to the file.
The following example code saves the recordset’s contents to a file on the local computer:
rs.Save “c:\studentinfo.dat”, adPersistADTG
Note : Once a recordset has been saved, it is static. Any additional changes made to the recordset after calling the Save method will not be reflected in the persisted data when it is retrieved.
Retrieving Persisted Data :
To rebuild the recordset from data that was saved, use the Open method and refer to the file name. The following example code reopens a persisted recordset:
rs.Open “c:\studentinfo.dat”
Reconnecting to A Data Source :
After a connection has been closed, it can be reopened and any changes the user made offline can be saved to the data source. The original connection information, such as the data source provider, is retained as long as the Connection object was disconnected using the Close method. If the Connection object is set to Nothing, all connection information must be re-established.
The following example code reconnects to a data source and associates the Recordset object with the connection:
Sub cmdReconnect_Click()
cn.Open
rs.ActiveConnection = cn
End Sub
Submitting Changes To A Data Sources :
One use of a disconnected recordset is to provide offline updating. A client can spend as much time as needed reviewing and editing data while disconnected from the data source.
Creating Dynamic Data Sources :
When you use ADO, you do not have to create recordsets from external data sources. Instead, your application can build dynamic recordsets to manage data internal to your application.
To create a dynamic recordset, declare and instantiate a normal Recordset object variable. However, since there is no data source, you will not specify connection information or use the Open method.
Adding Fields to the Recordset :
Use the Fields collection and the Append method to create new fields on the recordset. When you create a field, you must specify a name and a data type for the field. In addition, if you specify a string data type, you must also pass the length of the string.
The following example code creates a dynamic recordset and adds two fields:
Dim rsFileInfo As Recordset
Set rsFileInfo = New Recordset
rsFileInfo.Fields.Append “ID”, adInteger
rsFileInfo.Fields.Append “FileName”, adBSTR, 255
Using a Dynamic Recordset
Adding Fields to the Recordset :
Use the Fields collection and the Append method to create new fields on the recordset. When you create a field, you must specify a name and a data type for the field. In addition, if you specify a string data type, you must also pass the length of the string.
The following example code creates a dynamic recordset and adds two fields:
Dim rsFileInfo As Recordset
Set rsFileInfo = New Recordset
rsFileInfo.Fields.Append “ID”, adInteger
rsFileInfo.Fields.Append “FileName”, adBSTR, 255
Using a Dynamic Recordset :
Sub cmdLoadFiles_Click()
Dim sFileDir As String
Dim Idx As Integer
Set rsFileInfo = New Recordset
‘ add new fields to the recordset
rsFileInfo.Fields.Append “ID”, adInteger
rsFileInfo.Fields.Append “FileName”, adBSTR, 255
‘ open the recordset
rsFileInfo.Open
‘ use the Dir command to return the files in the C:\ directory
sFileDir = Dir(“C:\”)
‘ if there is a valid file name
Do While sFileDir <> “”
If sFileDir <> “.” and sFileDir <> “..” Then
Idx = Idx + 1
rsFileInfo.AddNew ‘ add a new record for this file
rsFileInfo!ID = Idx
rsFileInfo!FileName = sFileDir
rsFileInfo.Update
‘ get the next file in the directory
sFileDir = Dir
End If
Loop
rsFileInfo.MoveFirst
End Sub
Sub cmdDisplayResults_Click()
Do Until rsFileInfo.EOF
MsgBox “File Number: ” & rsFileInfo!ID & vbCrLf & “File Name: ” & rsFileInfo!FileName
rsFileInfo.MoveNext
Loop
End Sub
Chapter 3
ADO & SQL SERVER
The behavior of the application when connecting to a SQL Server database depends upon the type of security on the server. To develop a client application that establishes a connection to a data source, the way in which the connection is made, must be planned. This includes determining the security mode of the designated data source, and whether it requires a user ID and password.
SQL Server Security Modes :
Security is necessary to protect the information contained in the database. There are two primary security options:
Standard security mode
Integrated security mode
Standard Mode :
Standard security mode is the default security mode for SQL Server. Standard mode uses the SQL Server security model for every connection to the database. It supports non-trusted environments, such as the Internet. For example, if you implement a solution that allows users to connect to a SQL Server over the Internet, users will not necessarily first connect to a Windows NT server for authentication. SQL Server will perform its own authentication in this situation. The system administrator or database owner can create user IDs, aliases, user names, and groups for each database on the server. When this mode is in use, the user must enter a user ID and password combination that has been established for the database.
When using standard mode, security information to a data source can be passed in one of the following three ways:
Hard-code all connection information.
Prompt the user for some information and hard-code the rest.
Prompt the user for all information.
Integrated Mode :
Integrated security mode allows SQL Server to use Microsoft Windows NT authentication mechanisms to validate users for all connections to the database. You can use integrated security in network environments in which all clients support trusted connections. A trusted connection is one that recognizes users who have been granted system administrator status, have valid Windows NT accounts, or otherwise have access to the database.
Integrated security allows applications to take advantage of Windows NT security capabilities, which include encrypted passwords, password aging, domain-wide user accounts, and Windows-based user administration.
With integrated security, users maintain a single user ID and password for both Windows NT and SQL Server.
SQL Server Security and ADO :
A user ID value & a password value must be supplied to connect to a SQL Server Database.. The user ID and password are passed through ADO directly to the server for validation.
Note : In the event that SQL Server is using Integrated Security, the values of user ID and password are ignored.
If an invalid user ID or password is provided, the connection will fail and a run-time error will result. If no password is required for the user ID, the password argument can be left out of the connection information.
Connecting to a Secure SQL Server:
Unless your application hard-codes the user ID and password values, you must prompt the user for these values before connecting to the database. The following example code prompts the user for a user name and password:
Sub cmdConnect_Click()
Dim sUser As string
Dim sPassword As string
Dim cn As Connection
Set cn = New Connection
sUser = InputBox(“Please enter your user name:”)
sPassword = InputBox(“Please enter your password:”)
With cn
.Provider = “SQLOLEDB”
.ConnectionString = “User ID=” & sUser & _
“;Password=” & sPassword & _
“;Data Source=MSSERVER;” & _
“Initial Catalog=Cse”
.Open
End With
End Sub
Using Cursors :
When a Recordset object is created, specific records based on a SQL query are returned. This grouping of records is called a cursor because it indicates the current position in the result set, just as the cursor on a computer display indicates the current position on the screen. There are four types of cursors and two different locations.
Cursor Type :
Cursors are created in a computer’s memory. Using ADO and certain databases, such as Microsoft SQL Server, on which computer the cursor is created can be specified. Depending on their functionality, certain cursors use more system resources than others. Selecting where the cursor should be built (its location) and the functionality of the cursor (its type) occurs when the recordset is first opened. The cursor type affects the performance and overhead of the cursor.
Note : The location and type of the cursor cannot be changed unless the recordset is first closed and then reopened.
There are four types of cursors :
- Forward – Only,
- Static,
- KeySet, and
- Dynamic.
Forward-Only Cursor :
The default recordset cursor type is forward-only. A forward-only cursor provides support exclusively for the MoveNext navigation method. Any other navigation method generates a run-time error. Due to the limited capabilities of a forward-only cursor, it is very efficient and uses the least amount of overhead.
Since forward-only is the default, one does not need to specify a cursor type when opening a recordset. However, it is a recommended practice to specify the cursor type. To explicitly create a forward-only cursor, set the CursorType property of the recordset to adOpenForwardOnly.
Sub cmdOpenRecordset_Click()
Dim rs As Recordset
Set rs = New Recordset
rs.Open “SELECT pub_id FROM Publishers”, cn, adOpenForwardOnly
End Sub
Static Cursors :
A static cursor does not detect changes made to the recordset, the order in which the records are returned by the cursor, or the changes made to the values in each record in the recordset after the cursor is opened. For example, suppose a static cursor fetches a record and then another application updates that record. If the static cursor uses the record again, the values seen by the static cursor are unchanged, in spite of the changes that the other application made. In order for a static cursor to reflect changes made to its records, it must be closed and reopened. It provides navigation in both directions.
To create a static cursor, set the CursorType property of a recordset to adOpenStatic.
Sub cmdOpenRecordset_Click()
Dim rs As Recordset
Set rs = New Recordset
Rs.Open “SELECT pub_id FROM Publishers”, cn, adOpenStatic
End Sub
Keyset Cursors :
A keyset cursor lies between a static and dynamic cursorin the sense that a keyset cursor is used when there is a need to see changes to the data in the recordset but does not need to see additions or deletions of records. Like a static cursor, it does not always detect changes to its records and order of the recordset. For example, if the cursor is fully populated, new records are not included without refreshing the recordset. Like a dynamic cursor, however, it does detect changes to the values of records in the recordset.
To create a keyset cursor, set the CursorType property of the recordset to adOpenKeyset.
Sub cmdOpenRecordset_Click()
Dim rs As Recordset
Set rs = New Recordset
Rs.Open “SELECT pub_id FROM Publishers”, cn, adOpenKeyset
End Sub
Dynamic Cursors :
Dynamic cursors are the most functional of the cursor types, but use the most overhead. A dynamic cursor is used when there is a need a cursor that will always provide live data. A dynamic cursor can detect changes made to records in the recordset and their order. For example, suppose a dynamic cursor fetches two records, and then another application updates one of the records, deletes the other, and adds a new record that satisfies the query criteria. If the dynamic cursor attempts to fetch these records again, it will return the updated value of the first record, it will not return the deleted record, and it will return the new record.
To create a dynamic cursor, set the CursorType property of the recordset to adOpenDynamic.
Sub cmdOpenRecordset_Click()
Dim rs As Recordset
Set rs = New Recordset
Rs.Open “SELECT pub_id FROM Publishers”, cn, adOpenDynamic
End Sub
Cursor Location :
A cursor can be build on the server or on the client. Which location is preferable will depend on the size of the cursor and the capabilities of the server data source.
Server-Side Cursors :
Server-side cursors are the default in ADO. If the data source you are connecting to does not support server-side cursors, a client-side cursor must be created. To explicitly specify the creation of a server-side cursor, set the CursorLocation property of the Recordset object to adUseServer. If a server-side cursor is created, the values of the records contained in the recordset are stored on the server.
Using a server-side cursor can increase an application’s performance since the overhead on the client is limited and the amount of network traffic is reduced. Server-side cursors are specified in the CursorLocation property.
Sub cmdOpenRecordset_Click()
Dim rs As Recordset
Set rs = New Recordset
Rs.CursorLocation = adUseServer
Rs.Open “SELECT StudentID FROM Students”, cn
End Sub
Client-Side Cursors :
Client-side cursors are built by setting the CursorLocation property of the Recordset object to adUseClient. If a client-side cursor is created, the values of the records contained in the recordset are stored on the client’s computer. This allows for the creation and management of disconnected recordsets and can be used to move the overhead of cursor management off the server.
Note : When you set the CursorLocation property to adUseClient, the client cursor engine supports only “static” cursor types, no matter what’s reported by the CursorType property.
Sub cmdOpenRecordset_Click()
Dim rs As Recordset
Set rs = New Recordset
Rs.CursorLocation = adUseClient
rs.Open “SELECT StudentID FROM Students”, cn
End Sub
Enforcing Data integrity :
Ensuring data integrity is a critical element of developing professional applications. Techniques such as record locking, transaction management, implementing data source features, and handling referential integrity errors help maintain data consistency.
Using Record Locking :
When there is a recordset, a locking option is set to manage the user’s access to records in a data source. In a multi-user environment, locking ensures that no two users can change the same record at the same time. Note that the combination of the cursor location, cursor type, and locking option affects the updateability of a recordset.
Use the LockType property to set the locking option for the recordset. The following table describes the options of the LockType property.
Locking Option Description
adLockReadOnly The data is read-only and cannot be altered. This is the default option.
adLockPessimistic The provider ensures successful editing of the records, usually by locking records at the data source immediately upon editing.
adLockOptimistic The provider uses optimistic locking, locking records only when you call the Update method.
adLockBatchOptimistic The records are locked in batch update mode, as opposed to immediate update mode. This option is required for client-side cursors, including disconnected recordsets.
Note : Specifying a forward-only cursor that uses read-only locking is the most efficient implementation of a cursor and should be used whenever possible. This type of cursor is sometimes called a “firehose cursor.”
A locking option is specified when a recordset is opened. Set the LockType property of the recordset to one of the locking constants to control how the data source locks the records.
Sub cmdOpenRecordset_Click()
Dim cn As Connection
Dim rs As Recordset
Set cn = New Connection
With cn
.Provider = “SQLOLEDB”
.ConnectionString = “User ID=sa;” & _
“Data Source=MSSERVER;” & _
“Initial Catalog=Cse”
.Open
End With
Set rs = New Recordset
rs.Open “SELECT StudentID FROM Students”, n, adOpenKeyset, _
adLockPessimistic
End Sub
Transaction :
Transactions help ensure data integrity by grouping one or more SQL statements together. A transaction is an “all or nothing” proposition; either all of the statements are committed, or none of them are. If either command fails, you can roll back both commands, returning the data source to its original state. If both commands are successful, you can commit the changes and make them permanent.
There are two categories of transactions: implicit and explicit.
Implicit Transactions :
Implicit transactions do not allow you to group multiple commands together. Instead a transaction is built around each individual command. Using implicit transactions, you cannot programmatically roll back or commit the changes. However, you can trap for a run-time error if the command were to fail. Your program can then resubmit the individual change.
If you do not explicitly turn on a transaction, implicit transactions are used automatically. SQL Server will use auto-commit mode and build a transaction around each individual command.
Explicit Transactions :
Explicit transactions allow your application to manage multiple SQL statements as if they were a single command. When you use an explicit transaction, your application groups commands into a single action.
For example, you can use an explicit transaction if your application transfers money between bank accounts. The act of transferring money consists of two operations: removing money from one account (a debit) and then adding it to another account (a credit). If there are any network problems or other errors that prevent the credit from occurring, the money will be removed from the first account without being added to the second.
Your application can manage explicit transactions by using one of three transaction methods: BeginTrans, RollbackTrans, or CommitTrans. When you create an explicit transaction with the BeginTrans method, all statements that follow are automatically a part of that transaction. When you use the RollbackTrans or CommitTrans methods, the transaction is closed and a new one can be created.
Creating an Explicit Transaction :
Transactions are managed at the level of the Connection object. Any Recordset objects or Command objects created when a transaction is enabled automatically share the transaction.
Sub cmdMakeChanges_Click()
Dim cn As Connection
Set cn = New Connection
With cn
.Provider = “SQLOLEDB”
.ConnectionString = “User ID=sa;” & _
“Data Source=MSSERVER;” & _
“Initial Catalog=Cse”
.Open
End With
‘ A Transaction space is created
cn.BeginTrans
‘ Turn on the error handler
On Error Goto Error_Handler
‘ SQL Commands are executed in the transaction
cn.Execute “INSERT INTO Authors…”
cn.Execute “DELETE FROM Publishers…”
‘ If all commands are successful, commit them
cn.CommitTrans
Exit Sub
Error_Handler:
cn.RollbackTrans
MsgBox “An error occured changing the records.”, vbExclamation
End Sub
Handling Referential Integrity Error :
Referential integrity preserves defined relationships among tables when you enter and delete records in those tables. For example, assume your database contains two tables, Customers and Orders. Each customer in the Customers table can have multiple records in the Orders table. In the event that a Customer record is deleted, all matching records in the Orders table will be left with an invalid Customer ID. This is an example of a referential integrity violation because the integrity of the database has been violated.
Trapping for Referential Integrity Violations :
If a referential integrity violation occurs, your application will receive a run-time error. Depending on the data source, the command that violates the integrity will fail. Therefore, if this type of error occurs, the data source will retain its original state and your application must respond accordingly.
Note : Referential integrity error codes are specific to the data source your application is using. The errors are also specific to the kind of referential integrity error encountered, such as foriegn key violation, duplicate key, and so forth.
In order to verify the error code a given referential integrity error will generate in your application, force a situation that will generate the error you want to trap for and then check the error value returned by the data source.
Sub cmdDeleteRecords_Click()
Dim cn As Connection
Dim errStateU As Errors
Set cn = New Connection
cn.Provider = “SQLOLEDB”
cn.ConnectionString = “User ID=sa; Data Source=MSSERVER;” & _
“Initial Catalog=Cse”
cn.Open
On Error Goto Error_Handler:
cn.BeginTrans
‘ this command will generate a foreign key violation error
cn.Execute “DELETE FROM Books WHERE BookID = 14″
cn.CommitTrans
Exit Sub
Error_Handler:
If errStateU.Number = -2147217900 Then
cn.RollbackTrans
End If
End Sub
Executing Statement Directly :
Your application can run SQL commands by executing them directly. These commands either return records, in the form of a recordset, or they affect the value of records. To execute commands that affect the value of records, such as updating current data and adding or deleting new records, use the appropriate SQL command and the Execute method of either the Connection object or the Command object.
Executing Directly Using the Connection Object :
If the SQL command being executed will be called only once from your application, you can use the Execute method of the active connection. This is the most efficient technique for one-time execution of SQL commands. If your application will call the same command more than once, consider using a Command object.
Sub cmdUpdateRecords_Click()
Dim cn As Connection
Dim sSQL As String
Set cn = New Connection
With cn
.Provider = “SQLOLEDB”
.ConnectionString = “User ID=sa; Data Source=MSSERVER;” & _
“Initial Catalog=Cse”
.Open
End With
sSQL = “INSERT INTO Authors(First_Name, Last_Name) ” & _
“VALUES (‘Suanne’, ‘Nagata’)”
cn.Execute sSQL
End Sub
Executing Directly Using the Command Object :
For SQL commands that will be called more than once from the same application, use the Command object to build the query. Set the Prepared property to True. This technique improves the performance of your application since the command is prepared and then saved in memory. Initially, this is slower than using the Connection object but improves performance for subsequent calls.
Sub cmdUpdateRecords_Click()
Dim cn As Connection
Dim comBooks As Command
Set cn = New Connection
cn.Provider = “SQLOLEDB”
Cn.ConnectionString = “User ID=sa; Data Source=MSSERVER;” & _
“Initial Catalog=Cse”
cn.Open
Set comBooks = New Command
With comBooks
.Prepared = True
.ActiveConnection = cn
.CommandType = adCmdText
.CommandText = “UPDATE Books SET Price = Price * 1.1″
End With
comBooks.Execute
End Sub
Note : If the Prepared property of a Command object has been set to True, you can re-execute the statement using the Execute method.
Stored Procedures:
Stored procedures are routines that are stored on the server and can be executed from a single call from a client. They are complex routines that you create using a scripting language called Transact SQL that are compiled and stored in a SQL Server database.
Stored procedures are useful when building a client/server application that accesses a SQL Server database. Stored procedures can be written to validate, modify, insert, and/or delete data. They can encapsulate business rules and integrity rules such that the rules do not need to be addressed on the client or in a middle tier component.
Stored procedures differ from an ordinary SQL statement because they are pre-parsed and pre-normalized. When a stored procedure is created, SQL Server analyzes it and prepares an internal, normalized structure that is stored in a system table. The first time the stored procedure is executed, a query plan is created and compiled. The compiled plan stays in memory on the server until it is forced out by other memory needs. This means that subsequent calls to the stored procedure result in much better performance than re-creating the same SQL command each time it is needed.
Advantages of Stored Procedures :
Stored procedures can streamline the execution of a query because of their ability to:
Accept parameters.
Return values of parameters to a calling procedure or client.
Return a status value that indicates success or failure, as well as the reason for the failure.
Call other stored procedures.
Encapsulate business functionality so that all applications can use and perform the procedures consistently.
Make execution faster and more efficient. Once they are compiled no syntax checking is required when they are executed.
Store the compiled version on the server and use it for subsequent calls, thus reducing network traffic.
Be called from different client applications.
Calling a Stored Procedure :
You call stored procedures by using the ADO Command object. In order to call a stored procedure use the following main steps:
- Create an ADO Command object and set its ActiveConnection property to use a connection to the database containing the stored procedure you want to call.
- Specify that the command is a stored procedure by setting the CommandType property to adCmdStoredProc.
- Specify which stored procedure you want to call by setting the CommandText property to the name of the stored procedure.
- If you need to send data to or receive data from the stored procedure you can use the Parameters collection of the Command object.
- Use the Append and CreateParameter methods to create parameters to hold the data.
- Use the Direction argument of the CreateParameter method to specify the type of parameter you want to create.
- To run the stored procedure, call the Command object’s Execute method.
- If the stored procedure returns data, you can receive the output either in a recordset or in parameters. More complex stored procedures can return a combination of recordsets and output parameters in a single call.
Chapter 4
Using Class Modules
Class Modules are one of the most striking features of Visual Basic. It is used to implement several key Object – oriented concepts in Visual Basic. It defines an Object’s behavior and attributes and provides a method to use those Objects ( Instantiation ). Class Modules give Visual Basic Programmers to use another important concept of Object – Oriented Programming : – Abstraction. Abstraction defines a set of rules to encapsulate member functions and member data at one place ( Object ). It hides the essential details of the Object from outside world.
To create a COM component in Visual Basic, a Class Module must be defined within a project. The component can then be used within the Visual Basic project as part of a larger application, or it can be exposed to other applications.
There are three important features in Object – Oriented Programming – Class & Objects, Inheritance and Polymorphism.
Class & Objects :
Class is practically implemented by a Class Module in Visual Basic. A Class Module is a type of Visual Basic code Module. A Class Module (.cls file) is similar to a standard code Module (.bas file) in that it contains functionality that can be used by other Modules within the application. The difference is that a Class Module provides functionality in the form of an Object. Each Class Module defines one type of Object. There may be several Class Modules in an application.
Each Object in the Object Model and Data Services Layer will be implemented from a Visual Basic Class Module. This includes the top – level Object s, Collection, items in the Collection, and every other Object.
Proper use of Class Modules will give several advantages to an application…
· Class Modules allow the Programmers to use Object – Oriented Programming Techniques.
· Class Modules make it easier to manage multiple instances of an Object.
· The Class Modules make it easier to separate the user interface and the business rules.
· Class Modules are required to create COM in Visual Basic.
· It is required for the implementation of Add – Ins in Visual Basic.
To use a Class Module in an application, an instance of the Class is created. Then a user gets access to the properties, methods, and events of the Object defined by the Class. For example, there might be an Employee Class that has properties such as LastName and FirstName, and methods such as Hire. Then an instance of the Employee Object can be created and used whenever there is a need to process employee information in the application.
Inheritance And Polymorphism :
Inheritance and Polymorphism, both are introduced in Visual Basic 5.0. Using Inheritance, one Class is derived from another and the Derived Class will have same set of properties and methods as the base Class has. With Inheritance, a Class hierarchy can be created.
In Polymorphism, an Object declared of a given base Class can take the form of the base Class or any other Class Derived from that base Class at run – time.
So, Polymorphism is the ability of an Object to respond differently in different circumstances. Polymorphism makes compilers smart in the sense that they can decide which method to execute, out of given set of methods.
Visual Basic neither supports function overloading nor supports operator overloading. Visual Basic supports Interface Inheritance rather. Conceptually, there are two types of Inheritance : Interface Inheritance and implementation Inheritance.
Implementation Inheritance :
If a language supports implementation Inheritance, as C++ or Java does, a Class can be Derived from a base Class. Nothing more needs to be done by the Programmer to incorporate all the functions and Data of the base Class.
With interface Inheritance, a Derived Class only inherits the interface of the base Class. Here the implementation is not provided automatically and all the functions and procedures of the base Class have to be defined in the Derived Class.
Interface :
An interface Basically, is a list of semantically related properties and methods. An interface has no implementation, and the Class provides the implementation. So, the interface can not be used directly from code. A Class is implementation of one or more interfaces. A Class provides code and data to implement interfaces. Thus a Class is a template and it can not be used directly either. An Object is an instance of Class and can be used directly from the code.
Interface Inheritance is used in Visual Basic to enable communication between the Components. If an already developed Component needs to invoke code in another Component, the existing Component can define an interface to be implemented by the Component.
To create an Object – Oriented application in VB, following steps are needed :
- Identifying Objects,
- Identifying the Properties of the Objects,
- Identifying the methods of the Objects,
- Implementing the Object s using Visual Basic Class Modules.
Designing An Application :
With Visual Basic, one can create components that range from code libraries to automation-enabled applications. COM lets us to assemble reusable components into applications and services.
Using Object And Component :
COM components are self-contained units of code that provide specific functionality. Using COM, several different components that work together as a single application, can be developed. Separating user’s code into components gives the ability to develop and test small, encapsulated pieces of the application independently. It also enables multiple developers to work on a project together by allowing tasks to be distributed among the members of the development team. Designing an application using COM components also enables the Programmer to use multiple instances of an Object within one application.
COM components can be either internal components, which are compiled into a project and are available only to that project, or external components, which are compiled into executable files or dynamic-link libraries. External components can be used by any client application that supports COM. A COM component is used in exactly the same way, regardless of whether they are internal or external components.
Advantage Of Using COM Components :
There are many reasons to use COM components in an application:
Reusability :
Once you create a COM component, other developers can use it. This enables easy access to your component’s features in other applications without requiring developers to write extensive code. A developer can use the Object Browser to get information about the properties, methods, and events exposed by your COM component. For more information, see Using the Object Browser in this chapter.
Reduced complexity :
You can create a COM component to hide programming complexity from other programmers. Other programmers need only know what information to provide to your component, and what information to retrieve.
Easier updating :
Components make it easier for you to revise and update your applications. For example, you can create a COM component that encapsulates business rules. If the business rules change, you update just the component, and not all the applications that use the component.
Windows Distributed Internet Architecture :
Windows Distributed InterNet Applications Architecture (Windows DNA) is a development framework for building scalable, distributed applications based on the Windows platform. With Windows DNA, application developers can build and extend solutions that combine the most desirable aspects of personal computer applications, C/S applications, and Internet applications.
These integrated applications and components can be developed by writing COM components and by accessing the Windows application services exposed through Microsoft Transaction Server and other enabling technologies.
Creating Class Modules Using The Class Builder :
You can create a Class Module and add methods, properties, and events to it manually, or you can use the Class Builder Add-In. The Class Builder automates the process of adding properties, methods, and events to a Class. With the Class Builder, you can Visually define a Class and its interface, as shown in the following illustration.
To add the Class Builder to a project
1. On the Add-Ins menu, click Add-In Manager.
2. In the list of available add-ins, select VB 6 Class Builder Utility.
3. Under Load Behavior, select the Loaded/Unloaded check box, and then click OK.
Adding A New Class :
To manually add a new Class Module to a project, click Add Class Module on the Project menu. However, if you manually add a Class Module to your project, you will not be able to use all of the features of the Class Builder.
You can add new Classes to your project with the Class Builder.
To use the Class Builder to add a Class
1. On the Add-Ins menu, click Class Builder Utility.
2. In the Class Builder, on the File menu, point to New, and click Class.
3. In the Class Module Builder dialog box, enter a name for the Class.
Creating The Class Interface :
Once a Class is created, properties, methods, and events of the Class should be defined. Collectively, these are called the interface of the Class.
To add properties, methods, and events, select the Class in the Class Builder, and then on the File menu, point to New and click Property, Method, or Event.
You can also add descriptive text and associate a Help context ID number for each property, method, and event by selecting the Attributes tab and
After you’ve defined all the Classes in your project, click Update Project on the File menu, and then exit the Class Builder.
Note : Although you can define the properties, methods, and events for a Class by using the Class Builder, you’ll still need to write code to add the functionality.
Class Module Events :
Class Modules have two built-in events: Initialize and Terminate.
To add code to the Class Module events, open the code window for the Class, and click Class in the Object drop-down list box.
Initialize Event :
The Initialize event occurs when an instance of a Class is created, but before any properties have been set. When you write a Class Module, you use the Initialize event to initialize any data used by the Class, as shown in the following example code:
Private Sub Class_Initialize()
‘Initialize data.
iDept = 5
End Sub
You can also use the Initialize event to load forms used by the Class.
Terminate Event :
The Terminate event occurs when the Object variable goes out of scope or is set to Nothing. When you write a Class Module, you use the Terminate event to save information, unload forms, or perform tasks that should occur when the Class terminates.
Private Sub Class_Terminate()
‘Any termination code.
End Sub
To set help information for a Class
1. Open the project containing the Class Module.
2. Open the code window for the Class Module.
3. On the Tools menu, click Procedure Attributes.
4. Fill in the Description and Help Context ID fields for each procedure.
Note : You cannot set descriptions for properties defined as Public variables, but you can set descriptions for properties defined by using property procedures.
The name of your project’s help file will be displayed in the Project Help File field in the Procedure Attributes dialog box. If you have not already done so, you can specify a help file for your project in the Project Properties dialog box.
To specify a help file for the project :
1. On the Project menu, click Project Properties.
2. Enter a file name in the Help File Name field, and then click OK.
Using The Object Browser :
To view the properties, methods, and events you’ve created for a Class Module, you can use the Object Browser. To run the Object Browser, on the View menu, click Object Browser. As you select the different properties, methods, and events you’ve created, the Object Browser will display the help information you added. The Object Browser displays information about Objects in all the type libraries referenced by the project including components intrinsic to the project. For information about setting a reference to a type library, see Using Components in Chapter 1: Visual Basic Essentials.
The Project/Library list box shows that the Object Browser is viewing the StaffingManager project. The Classes pane shows that there are two components available in the StaffingManager project: CEmployee and frmNewEmployee. The Members of pane shows that CEmployee contains three properties (Salary, FirstName, and LastName) as well as the private member variables for these properties and one method (Hire).
Practice Using The Class Builder :
In this practice exercise, you will use the Class Builder Utility to create an employee component Class with one property and one method. You will then use that component in a Visual Basic application.
Create a Class Module
1. Start a new Standard EXE project.
2. Run the Class Builder Utility Add-In.
a. Create a new Class named Student.
b. Create a public property named stName that is a String data type.
c. Create a method called Show that takes no arguments and does not return a value.
d. Exit the Class Builder Utility and update the project.
3. In the code Module for the Student Class, add code to the Show method that displays the value of the stName property:
Msgbox “Student Name: ” & mvarstName
4. Name the project ClassModulePractice and save your work.
Add component information
1. On the Tools menu, click Procedure Attributes.
2. In the Procedure Attributes dialog box, select the procedure name Show.
3. Add description information in the Description text box.
Use the Object Browser
1. On the View menu, click Object Browser.
2. In the Project/Library box, choose the current project name.
3. Select Student from the list of Classes.
4. Select the Show method in the list of Members of ‘Student’.
The method description should appear in the Details pane.
5. Save your work.
Adding Properties, Methods, And Events :
You have already seen how to use the Class Builder to add properties, methods, and events. Although Class Builder creates procedure templates, you’ll need to write code to create the functionality for your properties, methods, and events.
Creating Properties :
Properties define the data or attributes of a Class. For example, an Employee Class may have properties such as FirstName, LastName, and HireDate.
You can define a property for your Class in two ways: You can define public variables, or you can create public property procedures within your Class Module. Public variables provide a variable to hold a property value. Property procedures enable you to run code when a property is set or retrieved.
Using Public Variables to Create Properties :
If you don’t need to run any additional code when a property is set or retrieved, you can simply create a Public variable to hold the value of the property instead of a Private variable.
The following example code defines the string property FirstName:
Public FirstName As String
Users have no way of knowing whether your component uses property procedures or public variables; they get and set properties in the same way. However, with public variables, your component cannot determine when a property is being set or retrieved, nor can it tell what the property is being set to, until it is already set.
Using Property Procedures to Create Properties :
Use property procedures if you want to execute code when a property is set or retrieved. With property procedures, you can perform the following tasks:
Run a procedure when a property value is changed or read.
Constrain a property to a set of valid values.
Expose a property that is read-only.
You create property procedures in pairs. For example, you can create a Set or Let procedure to assign a value to the property, and then create a Get procedure to return the value. You give the two procedures the same name, which is also the name of the property. When you read the property, the Property Get procedure runs. When you set the property, the Property Set or Property Let procedure runs.
Define the Property :
The first step in defining a property is to define storage for that property in the Class Module. You can do this by declaring a private variable of some data type. The following example code declares a String variable to hold a property:
Private mvarFirstName As String
Define the Interface :
The Private variable is only visible to the Class Module itself, so you need to create a Public interface to get and set the value of the variable. Property procedures create this interface. The following example code creates Property Let and Property Get procedures that assign a string value and return the value for the FirstName property:
Public Property Let FirstName(passedName As String)
mvarFirstName = UCase(passedName)
End Property
Public Property Get FirstName() As String
FirstName = mvarFirstName
End Property
To create a property that returns a standard data type, define a Property Get procedure and a Property Let procedure. To create a property that is an Object data type, define a Property Get procedure and a Property Set procedure. To create a read-only property, define a Property Get procedure without a matching Property Let or Property Set procedure.
Creating a Default Property :
You can also create a default property for an Object. The default property is the property that is set if the user doesn’t provide a property name when working with the Object. For example, the Text property is the default property for a TextBox control. As a user, you can set the default property of a component either explicitly or implicitly, as shown in the following example code:
txtEmpFirstName.Text = “Bill”
txtEmpFirstName = “Bill”
To create a default property
1. Open the Class Module containing the property.
2. On the Tools menu, click Procedure Attributes.
3. In the Procedure Attributes dialog box, click the Advanced button.
4. In the Name drop-down list box, select the property you want to set as the default.
5. In the Procedure ID drop-down list box, select (Default), and then click OK.
Creating Methods :
Methods represent the functionality your Class provides. For example, an Employee Class may have methods such as Hire or PayIncrease.
To create a method for an Object, you create Public Sub or Function procedures within a Class Module.
The following example code creates a method that adds an employee record to a database:
Public Function Hire() as Boolean
‘ add employee record to database here
MsgBox “Employee was added to the database”
‘ if successful
Hire = True
End Sub
The following example code creates a method that increases the employee’s salary:
Public Function PayIncrease(dPercent As Double) As Integer
mvarSalary = mvarSalary * (1 + dPercent)
End Function
Using Named Constants:
A named constant is an item that retains a constant value throughout the execution of a program, and can be used in place of literal values. You can use named constants as property values and method arguments, and as return values. By using a named constant, you also make your code easier to read and maintain.
For example, if you want to create a message box that includes Yes and No buttons, you can use the named constant vbYesNo for the argument instead of a literal value of 4.
Creating Named Constants :
To define your own set of named constants, you create an enumeration and define the set of values available in it.
The following example code shows how to define an enumeration called JobLevel that has three values:
Public Enum JobLevel
jExecutive = 1
jManagement = 2
jStaff = 3
End Enum
You can make the members of your enumeration available to users of a component by marking the enumeration Public, and including it in a Class Module. Users of your Class can then use the JobLevel enumeration with a simple method, as shown in the following example code:
Public Sub CheckExecutiveStatus(iLevel as JobLevel)
If iLevel = jExecutive Then
MsgBox “Executive Level Status Approved”
Else
MsgBox “Executive Status Denied”
End If
End Sub
Visual Basic context-sensitive help will list the values of the enumeration when you are setting a variable of that type. Although the enumeration appears in a Module that defines a Class, it has global scope in the type library.
Note : To avoid enumeration member name conflicts, prefix the member name with lowercase characters that identify the type and the component to which it belongs. For example, the built-in enumeration VbDayofWeek contains numeric constants with the names vbMonday, vbTuesday, and so on.
Adding Events to A Class :
Through the use of events, a Class provides notification that some action has occurred. Visual Basic provides two built-in events for Class Modules by default: the Initialize event and the Terminate event. You can also declare custom events for your Class. You define the event and then write code to cause the event to occur. When a developer works with an Object that exposes an event, the developer can write code in an event handler to take action when the event occurs.
For example, if you create an order component called COrder, you can raise a Status event when the status of the order changes. A developer using an instance of the COrder component can write an event handler for the Status event that reacts to the new status.
Use the following steps to raise an event from a code component:
1. Declare the event.
2. Raise the event.
Declaring an Event :
You declare an event in the General Declarations section of a Class Module by using the Event keyword. The following example code shows how to declare an event for a Class:
Public Event Status(ByVal StatusText As String)
Raising an Event :
When you want an event to be raised by the Class, you call the RaiseEvent statement, and pass the event name and any arguments that the event takes.
The following example code raises the Status event to provide status information during a method that takes a long time to process:
Public Sub SubmitOrder()
‘code to submit an order goes here
RaiseEvent Status(“Checking credit…”)
‘code to check credit goes here
RaiseEvent Status(“Processing Order…”)
‘code to process an order goes here
End Sub
Communicating With A Component :
If a Class defines an event that uses ByRef arguments, the arguments can be changed when the event is handled. The Class can check the arguments after the event has been handled to determine if changes occurred. In this way the Class can pass information and get a response using the values of the event’s arguments.
In the following example code, a Class has an event LimitChanged in which it passes a new credit limit by reference. The Class can evaluate the new limit value after the event has been handled.
Dim iLimit as Integer
iLimit = 400
‘ raise the event to be handled by the client
RaiseEvent LimitChanged(iLimit)
‘ now check to see if the client changed the limit
If iLimit <> 400 Then
‘client didn’t accept the new limit
End If
Raising Errors From A Class :
You can create a Class Module that raises error messages. To pass an error back to the code using your Class, you use the Raise method of the Err Object. The code using your Class will need to handle the error.
To raise an error, use the following syntax:
ERR.Raise Number, Source, Description, HelpFile, HelpContext
Specify the error number by adding the intrinsic constant vbObjectError to the error number you want to raise. This ensures that your error numbers do not conflict with the built-in Visual Basic error numbers. The following example code raises an error that will be handled in the code using this Class:
Err.Raise vbObjectError + 100, “CEmployee”, “Employee could not be added”
Creating Instances of Class :
Class Modules serve as templates for Objects. To use a Class Module, you must first create an instance of the Class Module. Then, you can access the properties, methods, and events of that instance.
Class Modules differ from standard Modules in two ways:
You must explicitly create an instance of a Class before you can use it.
You can create multiple instances of a Class Module.
In the project that contains the Class Module, you can create an instance of a Class with the Dim, Set, and Dim As New statements, as shown in the following example code:
Dim empCurrent As CEmployee
Set empCurrent = New CEmployee
You can also use more compact syntax:
Dim empCurrent As New CEmployee
It is more efficient to use the Dim and Set statements separately to clearly show where the instance of the Class is instantiated. With the Dim As New statement, the Object is created the first time the Object variable is used, but with each subsequent use Visual Basic has to first determine if the Object has already been instantiated.
Releasing the Class :
When writing code that uses Objects, it is good practice to release the memory used by your Objects when you are finished with them. Once you have finished using a Class, use the Set statement to assign Nothing to the variable for the Object. The following example code releases the memory for the empCurrent Object:
Set empCurrent = Nothing
Note : When an Object variable is set to reference an Object, the Object’s reference count is incremented automatically. The Object count is decremented automatically whenever the reference variable goes out of scope, or set to nothing. If an Object variable is set to reference a different Object, the new object’s reference count is incremented after the first object‘s reference count is decremented. When an object’s reference count reaches to zero, the Object destroys itself.
Using a Class :
Once you’ve created an instance of a Class Module, you can test the methods and properties of the Class. You can use the Object Browser to view the properties, methods, and events that are defined for a Class.
Setting and Retrieving Properties :
The following example code creates an instance of the CEmployee Class, and sets and retrieves the FirstName property:
Dim empCurrent As CEmployee
Set empCurrent = New CEmployee
empCurrent.FirstName = “Bill” ‘Calls Let procedure.
MsgBox empCurrent.FirstName ‘Calls Get procedure.
Calling Methods :
The following example code creates an instance of the CEmployee Class and calls the Hire method:
Dim empCurrent As CEmployee
Dim bHired as Boolean
Set empCurrent = New CEmployee
‘Call the Hire method
bHired = empCurrent.Hire
If bHired Then
MsgBox empCurrent.FirstName & ” was hired!”
End If
Handling Events :
Follow these steps to handle an event that is provided by a COM component:
1. Declare an Object variable for the Class that raises the event using the WithEvents keyword.
2. Create an event procedure to handle the event.
The Object variable name will show up in the Object list, and the event for the Object will show up in the list of available events for that Object.
3. Create an instance of the Class so that it can fire the event.
Declaring a Variable Using WithEvents :
When you declare a variable using the WithEvents keyword, it contains a reference to the Object that will provide the notification events. To do this, you add a standard Dim statement and the WithEvents keyword, as shown in the following example code:
Dim WithEvents ordCurrent As COrder
Note : You must declare a WithEvents variable in the general declarations section. You cannot declare a WithEvents variable as a local variable.
Creating an Instance of the Object :
When you create an instance of an Object that provides events, and store that instance in the variable you dimensioned by using the WithEvents keyword, any events fired by the Object will automatically be passed to the appropriate event procedure.
The following example code creates an instance of the Order Object, and calls the SubmitOrder method, which then raises an event:
Private Sub cmdSubmitOrder_Click()
Dim ordCurrent As COrder
Set ordCurrent = New COrder
ordCurrent.SubmitOrder
End Sub
