SiteExperts.com Logo Home | Community | Developer's Paradise | Jobs
User Groups | Site Tools | Site Information | Search

Inside Technique : Sokoban Level Designer : Client Data Access with HTML Applications : Accessing Text Data with ADO

If you have scripted Active Server Pages before, you will almost certainly have come across ADO. ADO stands for ActiveX Data Objects, and is a core Microsoft Data Access technology. ADO is a wrapper for OLE DB (a low-level API for data access), and provides the developer with various objects that allow high-level access to this API. Active Server Pages normally use the ADO Connection and Recordset objects to access databases on the server. In this section we will see how we can use this technology to manipulate text data files on the client. Specifically, we will be looking at:

  • The ADO Connection object
  • The Microsoft Text Driver
  • Connecting to a Text Driver database
  • Understanding the connection string
  • Executing SQL statements

The ADO Connection object

The ADO Connection object is used to establish a connection to a database. To do this, you will first have to create an instance of the object like this: 

ADOConn=new ActiveXObject("ADODB.Connection");

The next step is to open the connection. You do this by sending a connection string to the object. This string is usually of the form 

"DSN=DataSourceName;UID=UserID;PWD=Password"

Here DSN represents an ODBC database which has been defined using the ODBC Data Source Administrator in the Control Panel. UID and PWD hold security information that the ODBC driver requires in order to establish the connection.

The Microsoft Text Driver

So how do the Connection object and ODBC come into the picture when we are discussing text files? Well, among the various ODBC drivers which come packaged with Windows, is the Microsoft Text Driver. Using this driver, a developer can manipulate text files as database tables, using standard SQL statements. These files need to be in standard CSV format, which is the same format that the TDC uses for it's data files. This is not a coincidence, the TDC uses an ADO recordset internally. Although the Text Driver has many restrictions on the type of SQL statements it can execute, it is still powerful enough for the type of application we have in mind.

FYI: The Text Driver also supports the fixed-width format for data files, but we will not be covering this in the article.

Connecting to a Text Driver database

The connection string to access a Text Driver database looks like this: 

ConnStr="DRIVER={Microsoft Text Driver (*.txt; *.csv)}; DBQ="+
	DataFilePath.substring(0,DataFilePath.lastIndexOf('\\'));

Phew! Looks like quite a handful, doesn't it? Also, it doesn't look anything like the standard connection string. Don't worry, we'll go through it step by step.

The first thing you need to understand is that the Text Driver treats files as database tables, and the directories that these files reside in as databases. To use a database in ODBC, you would normally first have to create an ODBC DSN that points to the location of the database. What this translates into while using the Text Driver is this - every time the user wants to manipulate files in a particular directory, he will first have to register that directory as an ODBC DSN in order to be able to access the files within it.

It is obvious that this is not an acceptable state of affairs. Fortunately, ODBC provides a mechanism whereby the developer can dynamically create a DSN, without having to force the user to mess with the Control Panel. A special type of connection string, known as DSN-less, is used to implement this mechanism. The string looks like this: 

ConnStr="DRIVER={ODBCDriverName};DBQ=PathToDatabase"

Understanding the connection string

So now, hopefully, you should understand the Text Driver connection string. The ODBCDriverName in this case is: 


Microsoft Text Driver (*.txt; *.csv)

You must type in this string EXACTLY as it is displayed here. One of the most common mistakes developers make is to omit the space just before the *.csv, causing the connection to bomb. The PathToDatabase is the directory in which the data file resides. We have used the JScript substring operator to extract the directory path from the DataFilePath variable, which holds the full path of the text data file we want to manipulate. You will have obtained this path from the Common Dialog control, which we discussed in part 1 of this series.

Executing SQL statements

Now that we've established a connection to the database (read directory), the next step is manipulate the data file. You do this by sending an SQL string to the connection object like this: 

ADOConn.Execute('SQL String');

For a list of the limitations imposed on SQL statements by the Text Driver, check the ODBCJET.HLP file in your Windows System directory. For a general refresher course on SQL, check the JETSQLnn.HLP in the same directory.

In the next four sections, we will look at a library of functions used to implement common tasks like Open File, Create File, Save File, and Save File As. These functions require that you have 3 ActiveX controls on your page, the Common Dialog control (to help the user select a data file), the Tabular Data Control (which your application will use to manipulate the data), and the ADO Connection object(which the library will use to write data to the client disk ). We will assume that they are named CommonDialog1, TDC1, and ADOConn respectively. We have already covered the creation of these objects. Now we will look at them in action.

© 1997-2000 InsideDHTML.com, LLC. All rights reserved.