1084-1084

Page 1085

CHAPTER 48

Using Visual Basic

IN THIS CHAPTER

• Third-Party Products That Decrease Development Time and Add Value 1083
• Immediate Benefits to RAF Users 1086
• Rapid Application Development 1086
• RAF Courses 1087
• Connecting to the Database 1087

Page 1086

Microsoft Visual Basic has become the most popular development tool for Windows applications. Its easy-to-use IDE, ANSI standard programming language, and extensive third-party product support have contributed largely to its success.

While Visual Basic lacks many of the object-oriented features of its competitors , the simplicity of the programming language and the development environment make it a frequent choice for rapid prototyping and client/server development. Third-party custom controls, many of which would take months to develop, can be added to a project instantly. Visual Basic provides connectivity to any ODBC data source, as well as support for other common Windows services, including OLE and DDE.

Third-Party Products That Decrease Development
Time and Add Value

Among the most comprehensive third-party applications that can quickly jump-start your project is Rapid Application Foundation (RAF) from Advanced Information Systems. RAF is a structured approach to Rapid Application Development. RAF leverages several of the industry's best development products. These products, coupled with RAF, provide a robust development platform while preserving ease of programming. The RAF foundation offers a development path well suited for Rapid Application Development, while preserving ease of maintenance.

RAF includes a set of source code libraries consisting of predeveloped code and precoded form templates. The RAF libraries simplify the effort of building any application while providing robust features found only in professional software applications.

Immediate Benefits to RAF Users

RAF enables applications to be prototyped in minutes instead of weeks. The benefit begins as the RAF prototype is optimized with predeveloped foundation libraries, enabling development of world-class client/server applications in record time. Built-in functionality enables your prototype to immediately connect to all ODBC-compliant databases. Connections may be made directly through database procedures, ODBC, or other precoded techniques. Inter- form communication allows an unlimited number of screens, pop-up windows, notes, combo list boxes, charts , graphs, and spreadsheets to efficiently communicate between applications automatically.

Rapid Application Development

The RAF approach delivers rapid applications for the Windows environment, simplifying window creation through the use of precoded form templates. RAF provides a framework that unifies application coders into a maintainable programming model. RAF greatly minimizes the complexity of code maintenance and learning to develop under the Windows environment.

Page 1087

RAF Courses

Advanced Information Systems, Inc. provides on-site skills transfer courses. These courses offer classroom sessions, as well as project development. AIS trainers customize the lab exercises to directly build your in-house project applications. AIS can be reached at (800)327-9725.

This chapter focuses on Visual Basic's ODBC interface, which has relatively few features but is extremely easy to use. In addition, the final section includes a brief summary of the Visual Basic environment and language structure, as well as its strengths and weaknesses.

Connecting to the Database

No additional configuration is required to connect to an ODBC data source from Visual Basic. Any drivers and data sources installed and configured using the ODBC administration program are accessible. The Visual Basic Database object is used to establish connections through its OpenDatabase method. The OpenDatabase method takes four arguments, three of which apply only to local desktop databases:

• Database name : An empty string for ODBC data sources
• Open exclusive: Boolean, always set to False for ODBC data sources
• Read only: Boolean, always set to False for ODBC data sources
• Connect string: The ODBC connect string, includes "ODBC;", the data source name, user ID, password, and any additional database-specific parameters

To connect to Oracle, the connect string requires no special parameters. The code fragment in Listing 48.1 uses values from a generic ODBC login form to build a connect string.

Listing 48.1. The Database object is used to connect to the data source.

Dim dbOracle As Database
Dim szConnect As String

szConnect = "ODBC;DSN=" & lstDSNs.Text & ";"
szConnect = szConnect & "UID=" & txtID.Text & ";"
szConnect = szConnect & "PWD=" & txtPassword.Text & ";"

Set dbOracle = OpenDatabase("", False, False, szConnect)

The connect string constructed in Listing 48.1 might look like

"ODBC;DSN=ORACLE;UID=scotty;PWD=tiger;"

The methods of the Database object are used to retrieve results, apply SQL transactions, and call stored procedures. In most cases, the application should declare one Database object globally.

Page 1088

The primary means of retrieving results from an ODBC data source is the Snapshot object. The Snapshot object is created using the CreateSnapshot method of the Database object. The CreateSnapshot method takes two arguments: a SQL SELECT statement and a numeric constant used to control processing of the SQL. Unless the application needs to be portable to different RDBMSs, this numeric constant should be set to DB_SQLPASSTHROUGH (64), which sends the statement directly to the server for processing. This mode allows the developer to use the native syntax of the RDBMS, and prevents the local Microsoft Access engine from attempting to parse and process the SQL. The following code fragment provides a simple example of the use of the CreateSnapshot method:

Dim dsContacts   As Snapshot

Set dsContacts = dbOracle.CreateSnapshot("SELECT a.last_name, a.first_name,
                            b.phone_nbr FROM individual a, phone b
                           WHERE a.ID = b.IndividualID(+) ORDER BY 1, 2",
                           DB_SQLPASSTHROUGH)

The example assumes that the Database object has already connected to the data source. Note that if DB_SQLPASSTHROUGH is not specified, a syntax error results because the local Access engine attempts to parse the SQL and does not recognize the outer join syntax.

After applying the SQL and creating the result set, there are numerous methods that can be applied to position the record pointer in the cursor. The MoveFirst, MoveLast, MoveNext, and MovePrevious methods are the most commonly used, and their purposes should be self-explanatory. Visual Basic provides the additional method FindFirst to position the record pointer at the first record matching specific criteria. For example, assuming that the record pointer is positioned at the first record, the following line would find the first individual with the last name Smith, based on the result set returned by the previous example:

dsContacts.FindFirst("last_name = `Smith'")

The criteria supplied as the argument to FindFirst are syntactically equivalent to a WHERE clause in SQL. The additional methods FindNext, FindPrevious, and FindLast operate on the same basis.

After positioning the record pointer, an application can assign variables to result set column values using the Fields property of the Snapshot. The Fields collection is simply a representation of the columns in the result set. They can be accessed by name or by a zero-based index, starting with the left-most column. Listing 48.2 demonstrates methods for assigning application variables to result set data.

Listing 48.2. The Fields collection is used to access result set data.

Dim iRow As Long

iRow = 0
dsContacts.MoveFirst

While Not dsContacts.EOF