Chapter 2: Your First Grid


2.1 Sample 1: \Samples\02_first\Simple.asp

Click on the URL http://localhost/aspgrid/02_first/simple.asp. You will see a screen similar to this:

This grid is based on the table Departments from our sample MS Access database. If you click on a button, the corresponding row will appear in the edit mode:

You can now edit the record, then click the icon to save changes or to cancel. In either case, the grid will switch back to the regular display mode.

The button lets you delete a record, and button enables you to add a new one.

2.2 Code Analysis

Here is the underlying ASP code:

' Build connection string to aspgrid.mdb
strConnect = "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" &_

' Create an instance of AspGrid
Set Grid = Server.CreateObject("Persits.Grid")

' Connect to the database
Grid.Connect strConnect, "", ""

' Specify SQL statement
Grid.SQL = "select id, name, phone from departments"

' Hide identity column
Grid.Cols("id").Hidden = True

' Specify location of button images
Grid.ImagePath = "../images/"

' Display grid

The first line builds a connection string to our sample MS Access database (AspGrid.mdb) located in the folder \Samples\db. To obtain a physical path to this folder, we use ASP's built-in method Server.MapPath which converts a virtual path (in our case "..\db\aspgrid.mdb") to its physical equivalent. We then append this path to a standard OLE DB connection string for MS Access.

If we used a SQL Server database, our connection string would look similar to this:


The line

Set Grid = Server.CreateObject("Persits.Grid")

creates an instance of the AspGrid component. The line

Grid.Connect strConnect, "", ""

instructs the grid object to connect to the database specified by our connection string. The first argument can either be a system DSN or a DSN-less connection string. The second and third arguments of the Connect method enable you to specify a username and password in case your database requires authentication. Our sample Access database does not, so the arguments are left blank. Note that you can also pass user credentials as part of the connection string via the attributes "UID=username;PWD=password".

The line

Grid.SQL = "select id, name, phone from departments"

specifies an SQL SELECT statement that the grid will be based on. For a grid to be editable, it must be based on a table that contains an identity column, or Autonumber, and this column (in our case "id") must appear first in your SELECT statement.

Not every major database management system provides an equivalent of SQL Server's IDENTITY columns or MS Access's Autonumbers. In Oracle, the same effect can be achieved by using SEQUENCE's and TRIGGER's in tandem. The file oracle.sql located under the folder \Samples\db demonstrates this technique.

IMPORTANT: As far as AspGrid is concerned, an identity column cannot be substituted by an integer primary key, i.e. even if your table already contains an integer primary key you must still add an identity column to it, or editable grids could not be created. You can always add an identity column to your existing table without changing existing primary keys or entity relationships.

The line

Grid.Cols("id").Hidden = True

makes the first column ("id") hidden. The method Grid.Cols returns an instance of the Column object that corresponds to the specified database field (in our case, "id"). By setting the Column.Hidden property to True, we force AspGrid to make the respective column invisible. The Grid.Cols method accepts both field names and 1-based integer indices. The integer index of a field corresponds to this field's position in the SQL statement. In our case, the "id" field is first, so the above line would be equivalent to

Grid.Cols(1).Hidden = True

IMPORTANT: You should always hide identity columns as shown above, or at least make them read-only, or record-adding functionality of AspGrid may be impaired.

The next line

Grid.ImagePath = "../images/"

specifies the relative location of control button images such as or . If you choose to place the images files in the same directory as your ASP files you don't need to use the ImagePath property.

The line


calls the Display method, the workhorse of the AspGrid component. The Display method provides most of AspGrid's functionality. It opens and scrolls through a recordset based on the specified SQL statement, handles user commands such as "edit" or "save", sends data to the browser, etc.

The Display method accepts an optional Boolean argument which is True by default. Grid.Display(False) works the same way except that it does not send anything to the client. Instead, it constructs a memory array of the individual HTML elements of a grid accessible via the Grid.Output property. You can use this feature to replace default grid elements with your own or otherwise customize the appearance of your grid. This is covered in detail in later chapters.

The last two lines,



Set Grid = Nothing

disconnect the grid from the database and destroy the AspGrid object, respectively.

2.3 Alternative Connection Options

When the Grid.Connect method is called, AspGrid internally creates an ADO Connection object, then uses it to create an ADO Recordset object.

You may instead choose to supply an external ADO Connection or Recordset object to AspGrid. The code sample

is almost identical to simple.asp except that instead of calling Grid.Connect, it creates an ADO Connection object, opens it with our connection string, and passes the opened Connection object to AspGrid via the Grid.Connection property as follows:

Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open strConnect
Grid.Connection = Conn

IMPORTANT: You MUST NOT pass an external ADO Connection object to Grid.Connection if AspGrid is running under MTS (Component Services) as the Connection object cannot be marshaled across apartments. Use Grid.Connect instead.

Yet another way to connect to the database is to pass an opened Recordset object via the Grid.Recordset property. The code sample

demonstrates this technique.