Page #70 (Chapter 10 - Retrieving and Storing Data in Databases)

	Chapter 10 - Retrieving and Storing Data in Databases
	Visual Basic Developers Guide to ASP and IIS
	A. Russell Jones

Copyright 1999 SYBEX Inc.

---

	Introducing the ADO Object Model

	You need to worry about only four ADO objects for retrieving and storing data (see, it's already easier than DAO). These are:

	Connection object  Connections are pipelines to data. You can't get data until you tell ADO where the data is. You also need to tell ADO which driver (called provider in ADO) you want to use to retrieve the data. The Connection object provides the properties and methods to open and close database connections.

	Recordset object  The ADO Recordset object is a two-dimensional array that holds data that you retrieve from a data store. The Recordset object provides the methods and properties to access any single data element and to navigate between data rows.

	Field object  This object contains data for a single column in one row of a recordset.

	Command object  You use ADO Command objects to get data from queries or stored procedures. Queries and stored procedures are pre-compiled SQL stored in the database. You can use Connection objects and recordsets with queries and stored procedures that don't require parameters. You need to use Command objects only when you're performing the same query or stored procedure multiple times, or when you need to supply or retrieve parameter values to or from a query or stored procedure.

	Note		To use ADO from Visual Basic, you must set a reference to the Microsoft ActiveX Data Objects library.

	Opening and Closing Connections

	To open a database connection, you create a Connection object, then use its Open method to create an open connection through which you can retrieve and update data.

	Dim conn as new ADODB.Connection

conn.Open  "Provider=SQLOLEDB;" & _ "SERVER=myServer;DATABASE=pubs","sa",""

	Note		The Microsoft pubs database comes with SQL Server. If you don't have SQL Server, you can download a Microsoft Access database containing the tables of the pubs database from the Sybex Web site.

	Note		To download code, navigate to http://www.sybex.com. Click Catalog and search for this book's title. Click the Downloads button and accept the licensing agreement. Accepting the agreement grants you access to the downloads page for the book.

	The Open method takes up to four arguments. ADO passes any additional arguments through to the provider. The four arguments are:

	ConnectionString  The ConnectionString can be a DSN (for example, pubs), or it can be a semicolon-separated string containing the provider, database, server, remote provider, and remote server. The remote provider and remote server parameters are required only for connections opened remotely (such as the client browser). You can't specify the provider with a DSN. If you don't specify a provider, ADO uses the default ODBC driver (MSDASQL). You should avoid DSNs unless you're happy with the default ODBC driver.

	UserID  The ID of the person you want to connect to the database. Usually, you can use the same UserID for every connection unless you need multiple levels of permissions.

	Password  The password that matches the specified UserID.

	Optional  A ConnectOptionEnum value. The only current valid values are none (leave the parameter blank) and adConnectAsync. The Connection object will notify you via a ConnectComplete event when the connection is available if you use the adConnectAsync value.

	Get used to writing the connection string. If you aren't sure how to write one, create a DSN, open a connection, then print or use the debugger to view the ConnectionString property. For example, after opening a connection using the pubs DSN, my connection string looks like this:

	Provider=MSDASQL.1;User ID=sa;Data Source=pubs;Connect

Timeout=15;Extended Properties="DSN=pubs;Description=Pubs

database;SERVER=myServer;UID=sa;APP=Visual

Basic;WSID=myServer;DATABASE=pubs;Network=DBMSSOCN;

Address=myServer,1433;Regional=Yes;UseProcForPrepare=0";

Locale Identifier=1033

	After you see the connection string, you can copy the appropriate parts and change the provider:

	Provider=SQLOLEDB;SERVER=myServer;DATABASE=pubs

	By default, ADO opens connections in an unknown state, which is effectively read-only. If you need to update or insert data via a connection, you need to set the Connection.Mode property before you open the connection.

	Dim conn as new ADODB.Connection

Conn.Mode = adOpenReadOnly

conn.Open  "Provider=SQLOLEDB;" & _ "SERVER=myServer;DATABASE=pubs","sa",""

	After you have an open connection, you are ready to access the data. Databases are capable of only two types of operations: They can retrieve data, and they can alter the data and objects in the database. You'll see how to do both in the rest of this chapter.