A db grid control using ASP






4.74/5 (32 votes)
May 13, 2001
2 min read

537512

10998
A grid control for ASP written in JavaScript to display recordsets.
Introduction
A grid control for editing databases on the web. It is written for ASP with JavaScript, but should be fully usable from VBScript as well. With just a few lines of code, you will, with this control, be able to edit your data.
Features
- Write protect fields you don't want the user to edit.
- ID fields can look up values in other recordsets.
- Sort by any column that supports sorting.
- Paging with custom page size.
- Automatically detects primary key, but can also be manually overridden.
- Get default values for new records with custom select statement.
Usage
To use the grid you create it by calling new BGrid(rs)
from
JavaScript, or you can alternately create it by calling CreateGrid(rs)
which also works from VBScript. The parameter in both methods is the recordset
you want the grid to display. This recordset should allow moving both back and
forward, so I usually set CursorLocation
to adUseClient
on my connection.
I'm too lazy to manually declare all those ad-constants, so at the top of config.asa I add the following. This means that all ADO constants are available, and is required for the demo to work without a lot of work.
<!-- METADATA NAME="Microsoft ActiveX Data Objects 2.5 Library"
TYPE="TypeLib" UUID="{00000205-0000-0010-8000-00AA006D2EA4}" -->
A simple example
Using the grid requires only a few lines of code:
<%@Language="JavaScript%">
<!--#include file=misc.asp-->
<!--#include file=b.dropdown.asp-->
<!--#include file=b.grid.asp-->
<form method=post>
<%
var Conn = CreateConnection(); // CreateConnection is defined in misc.asp
var rs = Server.CreateObject("ADODB.Recordset");
rs.Open("EmployeeTerritories",Conn,adOpenStatic,adLockOptimistic,adCmdTable);
var Grid = new BGrid(rs);
Grid.Process(); // Process must be called to handle saving and such stuff
Grid.Display(); // Display does just that
Grid = null;
rs.Close();
rs = null;
Conn.Close();
Conn = null;
%>
</form>
Since the EmployeeTerritories table has a field called TerritoryID which is a foreign key to the Territories table, we can tell the grid to look for a value to display from this table instead of displaying an ID that means nothing:
var rsTerritories = Conn.Execute("Territories");
Grid.SetLookup("TerritoryID",rsTerritories,"TerritoryID","TerritoryDescription");
When a record is being edited it will look similar to this picture:
Functions
CreateGrid(recordset)
Grid.Process()
Grid.Display()
Grid.SetDefault(field,sql)
Grid.ProtectFields(fieldlist)
Grid.SetLookup(fkfield,recordset,pkfield,displayfield)
Grid.SetOption(option,value)
Some of these are explained above, but there are a few I haven't mentioned yet:
Grid.SetOption(option,value)
option can be one of the following: "debug", "pagesize", "pk", "truncate". value varies depending on the option. "debug" requires a boolean, "pagesize" requires a number, "pk" is a comma separated list of fields which is the manually overridden primary key, and "truncate" needs a number to know how many characters that should be displayed of every field in the grid. Some examples:
Grid.SetOption("debug",true); // default is false Grid.SetOption("pagesize",20); // default is 10 Grid.SetOption("pk","field1,field2"); // if the primary key can't be found Grid.SetOption("truncate",30); // Don't display more than 30 characters
Grid.SetDefault(field,sql)
field is the name of the field in the recordset, and sql is the sql-statement used to find the default value for new records in this grid.
Grid.SetDefault("UserID","select max(UserID)+1 from TheTable"); // NOT the way to do it, // but it shows the usage. Grid.SetDefault("HireDate","select getdate()"); // This was better perhaps. Grid.SetDefault("MinAge","select 18"); // A constant.
Grid.ProtectFields(fieldlist)
Protects the fields in the fieldlist from editing. Example:
Grid.ProtectFields("UserID,HireDate"); // UserID and HireDate can't be edited.