Let's analyze the code above line by live.
The line
Set Grid2 = Server.CreateObject("Persits.Grid")
creates an instance of AspGrid. The next line
Grid2.NumberOnPage = 2
assigns this object instance a unique numeric ID of 2.
Setting this property is necessary if several instances of AspGrid are hosted
on the same ASP page. The NumberOnPage
property is 1 by default.
The next line passes an external connection object to Grid2. The same
connection object was also passed to Grid1, so both instances of
AspGrid share the same database connection:
Grid2.Connection = Conn
The following lines specifies a SQL statement this grid will be based on.
Note that the identity column ("id") is specified first in the SELECT
statement, which is a requirement for editable grids.
Grid2.SQL = "select id, dept_id, name, salary, _
hiredate, maritalstatus, fullyvested from employees"
The following line enables the control buttons (Edit/Delete in the regular mode
and Save/Cancel in the edit mode) on the left side of the grid, while
preserving them on the right-side. To show the control
buttons on the left side only, pass False as an argument to the ShowLeftSideButtons method.
Grid2.ShowLeftSideButtons
The next three lines specify the text and font for the <CAPTION> tag
of our grid. The CAPTION tag immediately follows the TABLE tag and
is displayed by a browser as a grid title.
Grid2.Table.Caption = "Employees"
Grid2.Table.Caption.Font.Face = "Arial"
Grid2.Table.Caption.Font.Bold = True
The following line makes the identity column invisible.
Failure to hide this column or make it read-only may result
in an error when trying to add a new record.
Grid2("id").Hidden = True
The following line specifies the location of control button images.
You don't need to specify this property if all images are located
in the same directory as the current page.
Grid2.ImagePath = "../images/"
The following two lines specify the CELLSPACING and CELLPADDING attributes of
the <TABLE> tag:
Grid2.Table.CellSpacing = 0
Grid2.Table.CellPadding = 1
The following line specifies a caption (title) for the "dept_id" column.
If the caption for a column is not specified explicitly, the table
field name will be used by default.
Grid2("dept_id").Caption = "Department"
The next line turns the dept_id field into a drop-down list box
populated from the specified SELECT statement:
Grid2("dept_id").AttachForeignTable _
"select id, name from departments", 1, 2
The dept_id field of the Employees table is a foreign key
referencing the id field of the Departments table (its primary key).
This represents a one-to-many relationship between the two entities:
each department has many employees, but each employee is assigned to
exactly one department. The AttachForeignTable method
accepts three parameters: a SQL SELECT statement that generates
a "foreign" recordset, the index of the field in that recordset
that serves as the foreign key (in our case, it is "id" or field 1),
and the index of the field that contains a readable value
that will be presented to the user (in our
case it is "name", or field 2).
The next two lines set captions for the "name" and "salary" columns, and we
do not show them here. The following line sets numeric formatting for the Salary column:
Grid2("salary").FormatNumeric 2, True, True, True, "$"
The first argument of the FormatNumeric method
specifies how many decimal digits to display. The other arguments
are optional. The second Boolean argument specifies whether to display comma
separators. The third Boolean argument
decides whether to use a negative sign or parentheses (...) for negative numbers.
The fourth Boolean argument controls whether to use the US format
(floating points and comma separators) European format (floating commas and dot separators). And the last string argument
specifies an optional prefix (in our case, a $ sign).
The following line aligns the contents of the Salary column to the right by
adding the ALIGN="RIGHT" attribute to every <TD> tag in that column:
Grid2("salary").Cell.Align = "RIGHT"
The next line specifies a format for the HireDate column:
Grid2("hiredate").FormatDate "%b %d, %Y"
The FormatDate method accepts a formatting string which consists of one or more
codes. The codes shown above convert a date into the format "Mon DD, YYYY".
Click here for
a complete list of codes. The FormatDate also accepts a
second optional argument which formats a date in the edit mode (as
opposed to the regular display mode). If the
second argument is omitted, AspGrid will use a default date
format when in the regular display mode.
The following line specifies a default value for the hiredate column:
Grid2("hiredate").DefaultValue = Date()
We chose to set the default value for this column to today's date,
but we could use any other date expression. A default value
can also be set for hidden columns. This type of usage will be
demonstrated in a later chapter.
The "maritalstatus" field of the Employees table stores
an integer code for an employee's marital status as follows:
1 for divorced, 2 for married and 3 for single (codes are assigned in alphabetical order).
To present this column to the user as a drop-down list of options, the following
code is used:
Grid2("maritalstatus").Array = Array("Single", "Married", "Divorced")
Grid2("maritalstatus").VArray = Array(3, 2, 1)
Here we supply an array of options (via the Array property)
and their respective values (via the VArray property).
Note that this code has much the same effect as the AttachForeignTable method.
The difference is that the values are coming
from pre-set arrays rather than recordsets.
The following line turns the Boolean field "fullyvested"
into a checkbox when in the edit mode. A non-zero value
will be displayed as a checked box, a zero value
as a clear box.
The two arguments to the AttachCheckbox
method specify what must be shown in the regular display mode
if the field value is non-zero and zero, respectively. In our case,
the arguments are simply the strings "Yes" and "No", but they can
also be HTML tags such as "<IMG SRC=yes.gif>"
and "<IMG SRC=no.gif>".
Grid2("fullyvested").AttachCheckbox "Yes", "No"
The next line enables sorting for all visible columns.
Recall that column 1 ("id") was made hidden earlier in the code.
Grid2.ColRange(2, 7).CanSort = True
The rest of the code is simply color, font and size formatting and
we will not show it here.
