Click here to Skip to main content
Click here to Skip to main content

A db grid control using ASP

, 27 Aug 2001
Rate this:
Please Sign up or sign in to vote.
A grid control for ASP written in JavaScript to display recordsets.
<!-- Download Links --> <!-- Article image -->

Sample Image - bgrid1.gif

<!-- Add the rest of your HTML here -->

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.

<!--<span class="code-comment"> METADATA NAME="Microsoft ActiveX Data Objects 2.5 Library"
              TYPE="TypeLib" UUID="{00000205-0000-0010-8000-00AA006D2EA4}" --></span>

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:

Editing

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.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Author

Bjornar Henden
Web Developer
Norway Norway
No Biography provided

Comments and Discussions

 
Question'adUseClient' is undefined Pinmembertoota16-Jul-08 17:47 
AnswerRe: 'adUseClient' is undefined Pinmembercocchio18-Sep-08 0:58 
General.net is better Pinmembershakirhussain18-Nov-07 21:30 
QuestionError PinmemberRITIK DODHIWALA29-Apr-07 21:32 
Questionvisual web developper [modified] PinmemberMoukala15-Jun-06 0:59 
GeneralInclude the datetime var into DispalyName Pinmemberellaz2-May-06 7:16 
GeneralRe: Include the datetime var into DispalyName Pinmemberellaz2-May-06 11:30 
I found it myself.
CAST() function doesn't work when you work with Access. it is just for SQL server.
I used CSTR() and now it is working
 

 
Best,
Ella
 
-- modified at 16:31 Tuesday 2nd May, 2006
QuestionWorks great, but cannot save changes Pinmemberazharmahmood22-Oct-05 19:13 
AnswerRe: Works great, but cannot save changes Pinmemberturist695-Feb-07 11:17 
QuestionPlease give me a way for display pictures which store in DB Access 2003 by &quot;OLE object&quot;? Pinmembertuyenhnp25-Sep-05 17:44 
GeneraldbGrid PinmemberKuy Tan1-Sep-05 6:41 
GeneralCan't edit using SQL SERVER PinmemberTracer_II9-Aug-05 3:10 
QuestionRe: Can't edit using SQL SERVER Pinmemberazharmahmood23-Oct-05 3:16 
AnswerRe: Can't edit using SQL SERVER PinsussAnonymous24-Oct-05 23:29 
QuestionRe: Can't edit using SQL SERVER PinmemberGiliDazzle14-Mar-07 21:32 
QuestionRe: Can't edit using SQL SERVER Pinmemberozbarney18-Nov-05 14:45 
QuestionRe: Can't edit using SQL SERVER Pinmemberemmpergul14-Nov-06 15:49 
AnswerRe: Can't edit using SQL SERVER PinmemberWill6612-Jun-07 2:02 
GeneralRe: Can't edit using SQL SERVER PinmemberMember 82798784-Oct-11 12:19 
GeneralGood job..does this work with non-IE browsers Pinmemberrra3037-Aug-05 4:30 
GeneralASP Chart PinmemberMajd Ghiba21-Mar-05 22:05 
GeneralRe: ASP Chart Pinmembermintyboy694-May-05 2:24 
GeneralRe: ASP Chart PinmemberGreen Eyes4-May-05 2:28 
GeneralNice Job Pinmemberjhidey23-Dec-04 8:30 
General[Message Deleted] Pinmembernanpal7618-Nov-04 3:05 
GeneralNot Saving ..please help PinmemberGiliDazzle14-Mar-07 22:16 
GeneraladUseClient is undefined PinmemberHubertDude12-Nov-04 8:09 
GeneralRe: adUseClient is undefined PinmemberSeaWeedSolutions23-Nov-04 6:45 
GeneralRe: adUseClient is undefined PinmemberGiliDazzle21-Mar-07 0:41 
Generala big problem Pinmemberjoffre6324-Oct-04 15:40 
GeneralRe: a big problem PinsussAnonymous27-Oct-04 6:51 
GeneralRe: a big problem Pinmemberjoffre6327-Oct-04 12:10 
GeneralRe: a big problem PinmemberSeaWeedSolutions23-Nov-04 6:56 
GeneralCreating Custom Select Statements PinmemberComrademoe8-Oct-04 10:41 
GeneralCool :) :) :) :) PinmemberVasudevan Deepak Kumar8-Oct-04 6:15 
QuestionHow can we display the folder files in two columns? Pinmemberfarooqh4225-Sep-04 1:30 
GeneralHere's the code to edit a SQL statement Pinmembermickers22-Sep-04 13:55 
Generalcool~~ Pinmemberhanzen7-Jul-04 1:35 
Generalso many mistakes ! Pinmemberwyw_email12-Nov-03 23:02 
GeneralRe: so many mistakes ! PinsussAnonymous20-Nov-03 19:35 
GeneralThe best ever Pinmemberedusa2000@aol.com11-Sep-03 16:24 
GeneralEditable List Box in ASP PinmemberDayalan Prakash7-Aug-03 14:57 
GeneralRe: Editable List Box in ASP Pinmemberrihdus5-Aug-04 20:33 
Generaldoubt jscript to vbscript , pls help me Pinmemberwysste5-Aug-03 18:43 
GeneralIf i use vbscript grid colour all loss Pinmemberwysste5-Aug-03 17:57 
Generalproblem using the new version with sql Pinmemberannoymus8-Nov-02 18:43 
Generalneed help with using netdb Pinmemberannoymus8-Nov-02 18:42 
GeneralRe: need help with using netdb PinmemberBjornar Henden8-Nov-02 23:52 
QuestionHow to update many records on many pages? PinsussAnonymous15-Oct-02 22:18 
AnswerRe: How to update many records on many pages? PinmemberBjornar Henden16-Oct-02 9:26 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.141223.1 | Last Updated 28 Aug 2001
Article Copyright 2001 by Bjornar Henden
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid