Chapter 2: Your First Grid

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.

Here is the underlying ASP code:

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

creates an instance of the AspGrid component. The line

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

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.

The line

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

The next line

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,

and

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

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:

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.