CoverYourASP --> "/default.html" --> Source

Free membership

Join in the fun! Sign in
Member Services

Site navigation
Download the entire site!
Search my articles
Free Magazines
Browse the directory

Send me feedback
Buy my boxer shorts

Recommend this page
Printer-friendly page

Resources I recommend
Link to my site
Advertising slashed!
About your privacy
Legal stuff
Site statistics
98 active users
9 visitors today
16 pages today
how is this done?
Tools I use

CoverYourASP
Copyright © 1999-2016 James Shaw.
All rights reserved.

ASP.NET Blog
RSS submissions
E-commerce

Now open source with SourceForge!

This page shows the actual source code used on this site. You can read the article that discusses this code here.

If this is the first CYA source code you've seen you should read this overview first.

Did you know you can download all the source code (and the database) of this site? Then get my newsletter to be emailed when I update the source code!

Please spread the word by recommending my site to your friends and colleagues!

This is JScript (server-side JavaScript), not the more common VBScript. More...

default.asp

<!--#include file = "/include/Startup.html"-->
<!--#include file = "/utils/Personalize.html"-->
<!--#include file = "/utils/News.html"-->

<%
// ============================================
// NOTE: all source code downloaded from CoverYourASP was written by
// James Shaw (unless stated otherwise), and is copyright (c) 2000-2002
// by James Shaw. You can use the code for any purpose, but do not
// publish or distribute the content in any way.
//
// See http://CoverYourASP.com/Legal.asp for up-to-date details.
// ============================================

// output relevant meta tags
Init( "CoverYourASP - by James Shaw" );

// output common top of page
Header( "" );

// output page content
Content ( );

// output common bottom of page
Footer( );

// ============================================
// the content of this page
// ============================================
function Content ( )
{
   Out ( '<td valign="top" class="content">' );

      if ( bIntro )
         ShowIntro ( );

      // open database connection
      DBInitConnection ( );

      if ( bDiary )
         ShowDiary ( );

      if ( bSuggestions )
         ShowSuggestions ( );

      // show product spotlights
//      ContentTableTop ( 'Product spotlight' );
//         DebugOut ( '<font color="blue">showing four ASPAlliance ads</font>' );
//         ShowAllianceAd ( 0, 1 );Out('<br>' );
//         ShowAllianceAd ( 0, 2 );Out('<br>' );
//         ShowAllianceAd ( 0, 3 );Out('<br>' );
//         ShowAllianceAd ( 0, 4 );Out('<br>' );
//      ContentTableBottom ( );

      // show links to content if any is not shown
      // because of personalization settings
      ShowHidden ( );

      if ( nNewSnippets )
         ShowNewSnippets ( nNewSnippets );

      if ( nPopularSnippets )
         ShowPopularSnippets ( nPopularSnippets );

      if ( nNew )
         ShowNew ( nNew );

      if ( nPopular )
         ShowPopular ( nPopular );

      if ( bCategories )
         ShowCategories ( );

      // release the database ASAP
      DBReleaseConnection ( );

      if ( bNews )
         ShowASPExternal ( );

      Out ( '<p><center><a href="/ShowSource_page_default.html"><img src="images/source.gif" border=0></a></center>' );

      ShowBottomBanner();

   Out ( '</td>' );
   Out ( '<td background="images/gx/navgap.gif" valign="top">' );

      // Calculate how many banners to display
      var nBanners = 1;

      if ( bIntro )
         nBanners++;

      if ( bSuggestions )
         nBanners++;

      if ( bDiary )
         nBanners++;

      if ( nNewSnippets || nPopularSnippets )
         nBanners +=  Math.floor ( (nNewSnippets + nPopularSnippets) / 9 );

      if ( nNew || nPopular )
         nBanners +=  Math.floor ( (nNew + nPopular) / 2.5 );

      if ( bCategories )
         nBanners += 5;

      if ( bNews )
         nBanners += 2;

      // show rotating banners
      ShowBanners ( nBanners );

   Out ( '</td>' );
}
%>

utils/Personalize.asp

<%
// ============================================
// NOTE: all source code downloaded from CoverYourASP was written by
// James Shaw (unless stated otherwise), and is copyright (c) 2000-2002
// by James Shaw. You can use the code for any purpose, but do not
// publish or distribute the content in any way.
//
// See http://CoverYourASP.com/Legal.asp for up-to-date details.
// ============================================

// declare enums for ShowArticles ( )
var nByDate = 217;
var nByHits = 233;
var nByCategory = 723;

// ============================================
// introduction
// ============================================
function ShowIntro ( )
{
   ContentTableTop ( 'Introduction' );

      Out ( '<a href="/AboutASP.html">Active Server Pages</a> is fun! If you want to know how to use ASP to create a dynamic web site, you\'ve come to the right place.' );
   
      Out ( '<p>You can <a href="/CYA.html">download</a> <i>all</i> the source code for this site, FREE. The site is completely self-documenting - every article describes how the site was written and introduces you to <a href="/DBAdmin.html">databases</a>, <a href="/Forms.html">forms</a>, <a href="/ContactDescr.html">email components</a> and all the other things you probably want to do. <a href="/AboutCYA.html">More...</a>' );
   
   ContentTableBottom ( );
}
      
// ============================================
// suggested links
// ============================================
function ShowSuggestions ( )
{
   ContentTableTop ( 'My personal favorites - a good place to start...' );
   
      Out ( '<table><tr>' );

      Out ( '<td width="50%" align="center" valign="top">' );
      
         Out ( '<h3>New to ASP?</h3>' );

         Out ( '<table>' );
            Out ( '<tr><td align="left">&#0149;&nbsp;<a href="/AboutASP.html">Getting started - what is ASP?</a></td></tr>' );
            Out ( '<tr><td align="left">&#0149;&nbsp;<a href="/PWS.html">Running ASP on your home PC</a></td></tr>' );
            Out ( '<tr><td align="left">&#0149;&nbsp;<a href="/MetaTags.html">A simple example: Hiding your META tags</a></td></tr>' );
            Out ( '<tr><td align="left">&#0149;&nbsp;<a href="/ContactDescr.html">Getting feedback emails from a &lt;form&gt;</a></td></tr>' );
            Out ( '<tr><td align="left">&#0149;&nbsp;<a href="/Diary.html">Display data from a database - my diary</a></td></tr>' );
            Out ( '<tr><td align="left">&#0149;&nbsp;<a href="/WalkFolders.html">Walk through your files and folders</a></td></tr>' );
            Out ( '<tr><td align="left">&#0149;&nbsp;<a href="/Columns.html">Implement Yahoo-style categories</a></td></tr>' );
            Out ( '<tr><td align="left">&#0149;&nbsp;<a href="/CreditCards.html">Accept credit cards with ASP</a></td></tr>' );
         Out ( '</table>' );
         
      Out ( '</td>' );
      Out ( '<td width="1" bgcolor="ff9900">&nbsp;</td>' );
      Out ( '<td width="50%" align="center" valign="top">' );
      
         Out ( '<h3>Want the good stuff?</h3>' );

         Out ( '<table>' );
            Out ( '<tr><td align="left">&#0149;&nbsp;<a href="/DownloadCYA.html">Download the code for the entire site</a></td></tr>' );
            Out ( '<tr><td align="left">&#0149;&nbsp;<a href="/DBAdmin.html">Administer your database online</a></td></tr>' );
            Out ( '<tr><td align="left">&#0149;&nbsp;<a href="/MemberCode.html">The CoverYourASP membership system</a></td></tr>' );
            Out ( '<tr><td align="left">&#0149;&nbsp;<a href="/BannerApp.html">A complete banner advertising system</a></td></tr>' );
            Out ( '<tr><td align="left">&#0149;&nbsp;<a href="/DontRender.html">Using &lt;% %&gt; correctly in ASP</a></td></tr>' );
            Out ( '<tr><td align="left">&#0149;&nbsp;<a href="/ValidateEmail.html">Validate your email addresses</a></td></tr>' );
            Out ( '<tr><td align="left">&#0149;&nbsp;<a href="/DBException.html">Exception handling</a></td></tr>' );
            Out ( '<tr><td align="left">&#0149;&nbsp;<a href="/SurveyDescr.html">Database-driven surveys with one function</a></td></tr>' );
         Out ( '</table>' );
         
      Out ( '</td>' );

      Out ( '</tr></table>' );

   ContentTableBottom ( );
}
      
// ============================================
// links to hidden content
// ============================================
function ShowHidden ( )
{
   ContentTableTop ( 'Personalize this page' );

   // show links to content if any is not shown
   // because of personalization settings
   // (ignore the intro though)
   if ( !bDiary || !bSuggestions || !nNew || !nPopular || !bCategories || !bNews || !nNewSnippets || !nPopularSnippets )
   {
      if ( !IsLoggedIn ( ) )
      {
         Out ( '<b>Did you know?</b> You can choose the content on this page when you <a href="/MemberLogin.html">sign in as a member</a> - it\'s free to join, no personal info, no spam!' );
         
         Out ( '<p><b>More great stuff:</b>' );
      }
      else
      {
         Out ( '<p>Content you\'ve hidden:<br>' );
      }

      if ( !bDiary )
         Out ( '&nbsp;&nbsp;<a href="/AllContent_Diary_.html">The&nbsp;Diary</a>' );

      if ( !bSuggestions )
         Out ( '&nbsp;&nbsp;&nbsp;<a href="AllContent.asp?Suggestions">Suggested&nbsp;articles</a>' );

      if ( !nNew )
         Out ( '&nbsp;&nbsp;&nbsp;<a href="AllContent.asp?New">What\'s&nbsp;New?</a>' );

      if ( !nPopular )
         Out ( '&nbsp;&nbsp;&nbsp;<a href="/AllContent_Popular_.html">What\'s&nbsp;Popular</a>' );

      if ( !bCategories )
         Out ( '&nbsp;&nbsp;&nbsp;<a href="/AllContent_Categories_.html">Article&nbsp;by&nbsp;Category</a>' );

      if ( !nNewSnippets )
         Out ( '&nbsp;&nbsp;&nbsp;<a href="AllContent.asp?NewSnippets">New&nbsp;snippets</a>' );

      if ( !nPopularSnippets )
         Out ( '&nbsp;&nbsp;&nbsp;<a href="AllContent.asp?PopularSnippets">Popular&nbsp;snippets</a>' );

      if ( !bNews )
         Out ( '&nbsp;&nbsp;&nbsp;<a href="/AllContent_News_.html">ASPWire&nbsp;News</a>' );
         

   }
   else
   {
      // has anything been personalized?
      if ( IsLoggedIn ( ) && nNew == 3 && nPopular == 3 && nNewSnippets == 5 && nPopularSnippets == 0 )
         Out ( '<p><center><a href="Personalize.asp">Make your life easier at CoverYourASP - personalize the site!</a></center>' );
   }

   ContentTableBottom ( );
}

// ============================================
// diary - assume DBInitConnection called already
// ============================================
function ShowDiary ( )
{
   ContentTableTop ( 'The Diary - bug fixes, thoughts, hints and tips...&nbsp;&nbsp;&nbsp;&nbsp;<a href="/Diary.html"><font color="black">(how is this done?)</font></a>' );

   // the diary section - thoughts and fixes
   if ( -1 != sAgent.indexOf ( 'msie 6' ) && '' != Request.QueryString ( 'ShowIE6' ) )
   {
      // buggy IE6: Mozilla/4.0 (compatible; MSIE 6.0b; Windows NT 5.0; .NET CLR 1.0.2914)
      // released IE6: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0; .NET CLR 1.0.2914)
      InlineDiary ( );
   }
   else
   {
      Out ( '<iframe src="ShowDiary.asp" width="100%" height="150" marginheight="0" marginwidth="0">' );
         Out ( '[Your user agent does not support frames or is currently configured  not to display frames. However, you may visit <A href="ShowDiary.asp">the related document.</A>]' );
      Out ( '</iframe>' );
   }

   ContentTableBottom ( );
}

// ============================================
// show diary entries inline if iframe not supported
// ============================================
function InlineDiary ( )
{
   DBGetRecords ( 'SELECT TOP 2 DiaryDate,Entry FROM Diary ORDER BY DiaryDate DESC' );

   // find anything?
   if ( !oRecordSet.EOF )
   {
      var sLastDate;

      while ( !oRecordSet.EOF )
      {
         var sDate = FormatDateDM ( oRecordSet ( 0 ) );

         // show date if it's changed
         if ( sDate != sLastDate )
         {
            if ( sLastDate != undefined )
               Out ( '</td></tr></table><hr color="#ff9900">' );

            Out ( '<table border=0 cellpadding=3>' );
            Out ( '<tr><td><b>' + sDate + '</b></td></tr>' );
            Out ( '<tr><td>' );

            sLastDate = sDate;
         }
         else
         {
            Out ( '<p>&#0149;&nbsp;' );
         }

         Out ( oRecordSet ( 1 ) + '<br>' );

         oRecordSet.MoveNext();
      }

      if ( sLastDate != undefined )
      {
         Out ( '<p><a href="ShowDiary.asp">View the diary for the last month..</a>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<a href="ShowDiary.asp?all=1">View entire diary..</a>' );
         Out ( '</td></tr></table>' );
      }
   }
}

// ============================================
// articles by date
// ============================================
function ShowNew ( nShowNew )
{
   // the what's new section - the last articles to be published
   ShowArticles ( 'What\'s new? The articles I\'ve been working on recently...', nByDate, nShowNew );
}

// ============================================
// articles by hits
// ============================================
function ShowPopular ( nShowPopular )
{
   // the what's popular section - the most popular articles
   ShowArticles ( 'What\'s popular? Read what your peers think is most interesting!', nByHits, nShowPopular );
}

// ============================================
// articles by category
// ============================================
function ShowCategories ( )
{
   ShowArticles ( 'Articles by category', nByCategory );
}

// ============================================
// snippets by date
// ============================================
function ShowNewSnippets ( nShowNew )
{
   // the what's new section - the last articles to be published
   ShowSnippets ( 'New snippets...', nByDate, nShowNew );
}

// ============================================
// snippets by hits
// ============================================
function ShowPopularSnippets ( nShowPopular )
{
   // the what's popular section - the most popular articles
   ShowSnippets ( 'Popular snippets...', nByHits, nShowPopular );
}

// ============================================
// Aspin categories + ASPWire news feed
// ============================================
function ShowASPExternal ( )
{
   Out ( '<a name="News"></a>' );

   ContentTableTop ( 'The most up to date ASP news from all over the web...' );

      Out ( '<table width="100%" cellpadding="3" border="0" align="center">' );
      Out ( '<tr><td width="50%" nowrap valign="top">' );

         Out ( '<a href="eu.asp?url=http://coveryourasp.aspin.com/home/webapps"><b>Applications</b></a><br>' );
         Out ( '<i><a href="eu.asp?url=http://coveryourasp.aspin.com/home/webapps/admanage">Ad Manager</a>, <a href="eu.asp?url=http://coveryourasp.aspin.com/home/webapps/bbs">  BBS</a>, ...</i><br><br>' );

         Out ( '<a href="eu.asp?url=http://coveryourasp.411asp.net"><b>ASP.NET Directory</b></a><br><i><a href="eu.asp?url=http://coveryourasp.411asp.net/home/webapps">Apps</a>, <a href="eu.asp?url=http://coveryourasp.411asp.net/home/components">  Assemblies</a>, ...</i><br><br>' );
         
         Out ( '<a href="eu.asp?url=http://coveryourasp.aspin.com/home/books"><b>Books &amp; Media</b></a><br>' );
         Out ( '<i><a href="eu.asp?url=http://coveryourasp.aspin.com/home/books/asp">ASP</a>, <a href="eu.asp?url=http://coveryourasp.aspin.com/home/books/componen">Components</a>, <a href="eu.asp?url=http://coveryourasp.aspin.com/home/books/database">Db</a>, ...</i><br><br>' );
         
         Out ( '<a href="eu.asp?url=http://coveryourasp.aspin.com/home/community"><b>Community</b></a><br>' );
         Out ( '<i><a href="eu.asp?url=http://coveryourasp.aspin.com/home/community/aspclass">Classes</a>,&nbsp; <a href="eu.asp?url=http://coveryourasp.aspin.com/home/community/conferen">Conferences</a>, ...</i><br><br>' );
         
         Out ( '<a href="eu.asp?url=http://coveryourasp.aspin.com/home/components"><b>Components</b></a><br>' );
         Out ( '<i><a href="eu.asp?url=http://coveryourasp.aspin.com/home/components/browser">Browser Utilities</a>, <a href="eu.asp?url=http://coveryourasp.aspin.com/home/components/bbs">  BBS</a>, ...</i>' );

      Out ( '</td><td width="50%" nowrap valign="top">' );

         Out ( '<a href="eu.asp?url=http://coveryourasp.aspin.com/home/sites"><b>Other ASP Sites</b></a>&nbsp;<br>' );
         Out ( '<i><a href="eu.asp?url=http://coveryourasp.aspin.com/home/sites/aspdev">Developers</a>, <a href="eu.asp?url=http://coveryourasp.aspin.com/home/sites/asphosts"> Hosts</a>, ...</i><br><br>' );
         
         Out ( '<a href="eu.asp?url=http://coveryourasp.aspin.com/home/references"><b>References</b></a><br>' );
         Out ( '<i><a href="eu.asp?url=http://coveryourasp.aspin.com/home/references/casestud">Case Studies</a>, <a href="eu.asp?url=http://coveryourasp.aspin.com/home/references/database">Db</a>, ...</i><br><br>' );
         
         Out ( '<a href="eu.asp?url=http://coveryourasp.aspin.com/home/software"><b>Software &amp; Server</b></a><br>' );
         Out ( '<i><a href="eu.asp?url=http://coveryourasp.aspin.com/home/software/applicat">Application Servers</a>,&nbsp;...</i><br><br>' );
         
         Out ( '<a href="eu.asp?url=http://coveryourasp.aspin.com/home/tutorial"><b> Tutorials &amp; Code</b></a><br>' );
         Out ( '<i><a href="eu.asp?url=http://coveryourasp.aspin.com/home/tutorial/applicat">Application Making</a>, ...</i><br><br>' );
         
         Out ( '<b>Powered&nbsp;By:</b>&nbsp;<br><a href="eu.asp?url=http://www.aspin.com"><img align="textTop" border="0" height="30" src="http://coveryourasp.aspin.com/images/aspin/cobrand/aspin_cob.gif" width="130"></a>' );
            
      Out ( '</td></tr>' );
      Out ( '</table><p><hr color="#ff9900">' );

         ShowNews();

   ContentTableBottom ( );
}

// ============================================
// show the articles table ordered by various methods
// ============================================
function ShowArticles ( sTitle, nSortBy, nNumber )
{
   var sSortBy;
   var bShowFullDescr;
   var bMouseOver = false;    // set to true to add descriptions to category links

   switch ( nSortBy )
   {
   case nByDate:
      sSortBy = 'UpdateDate DESC';
      bShowFullDescr = true;
      break;

   case nByHits:
      sSortBy = 'Hits DESC';
      bShowFullDescr = true;
      break;

   case nByCategory:
      sSortBy = 'Category,ShortDescr';
      bShowFullDescr = false;

      if ( bMouseOver )
         sTitle += ' - hover mouse to get description!';
      break;

   default:
      return;
   }

   DBGetRecords ( 'SELECT Category,PublishDate,UpdateDate,ShortDescr,LongDescr,URL,Hits FROM Articles ORDER BY ' + sSortBy );

   // find anything?
   if ( !oRecordSet.EOF )
   {
      ContentTableTop ( sTitle );

      Out ( '<table>' );

      if ( bShowFullDescr )
      {
         var i = 0;

         while ( !oRecordSet.EOF && i++ < nNumber )
         {
            // the category (strip off first two chars used for sorting)
            var sCategory = "" + oRecordSet ( 0 );

            sCategory = sCategory.slice ( 2 );

            // the description (strip off first two chars used for sorting)
            var sDescription = "" + oRecordSet ( 3 );

            sDescription = sDescription.slice ( 2 );

            Out ( '<tr><td>' );

               // the short description of the article, as a hyperlink
               Out ( '<b><a href="' + oRecordSet ( 5 ) + '">' + sDescription + '</a></b>' );

            Out ( '</td></tr>' );

            Out ( '<tr><td>' );

               Out ( '<table cellpadding=3>' );
               Out ( '<tr><td>' );
                  
                  // the long description of the article
                  Out ( '' + oRecordSet ( 4 ) );
                  
               Out ( '</td></tr>' );

               // show footer (category, date, hits)
               Out ( '<tr><td>' );
               
                  // the category
                  Out ( '<i>From <b>' + sCategory + '</b>, ' );

                  // the date last updated, or the date first published
                  if ( oRecordSet ( 2 ) > oRecordSet ( 1 ) )
                     Out ( 'updated ' + FormatDateDM ( oRecordSet ( 2 ) ) );
                  else
                     Out ( 'published ' + FormatDateDM ( oRecordSet ( 1 ) ) );

                  // number of times viewed
                  Out ( ', ' + oRecordSet ( 6 ) + ' views</i>' );

               Out ( '</td></tr>' );

               Out ( '</table><hr color="#ff9900">' );

            Out ( '</td></tr>' );

            oRecordSet.MoveNext();
         }

         if ( oRecordSet.EOF )
            bShowFullDescr = false;
      }
      else
      {
         var sLastCategory;

         while ( !oRecordSet.EOF )
         {
            // the category (strip off first two chars used for sorting)
            var sCategory = "" + oRecordSet ( 0 );

            sCategory = sCategory.slice ( 2 );

            // show category if it's changed
            if ( sCategory != sLastCategory )
            {
               Out ( '</td></tr>' );

               Out ( '<tr bgcolor="#ff9900"><td>' + sCategory + '<br></td></tr>' );

               Out ( '<tr><td>' );

               sLastCategory = sCategory;
            }

            // the description (strip off first two chars used for sorting)
            var sDescription = "" + oRecordSet ( 3 );

            sDescription = sDescription.slice ( 2 );

            // mouseover text - uses title attribute to
            // display full description on hyperlink
            var sMouseOver = '';

            if ( bMouseOver )
               sMouseOver = ' title="' + oRecordSet ( 4 ) + ' - ' + oRecordSet ( 6 ) + ' views"';

            // the short description of the article, as a hyperlink
            Out ( '&#0149;&nbsp;<a href="' + oRecordSet ( 5 ) + '"' + sMouseOver + '>' + sDescription + '</a><br>' );

            oRecordSet.MoveNext();
         }

         Out ( '</td></tr>' );
      }

      Out ( '</table>' );

      if ( bShowFullDescr )
         Out ( '<a href="/AllArticles.html">See a complete list of all my articles...</a>' );

      ContentTableBottom ( );
   }
}


// ============================================
// show snippets ordered by various methods
// ============================================
function ShowSnippets ( sTitle, nSortBy, nNumber )
{
   var sSortBy;
   var bShowFullDescr;

   switch ( nSortBy )
   {
   case nByDate:
      sSortBy = 'PublishDate DESC';
      bShowFullDescr = true;
      break;

   case nByHits:
      sSortBy = 'Hits DESC';
      bShowFullDescr = true;
      break;

   default:
      return;
   }

   DBGetRecords ( 'SELECT SnippetID,PublishDate,Title,Hits FROM Snippets ORDER BY ' + sSortBy );

   // find anything?
   if ( !oRecordSet.EOF )
   {
      ContentTableTop ( sTitle );

      Out ( '<table>' );

      var i = 0;

      while ( !oRecordSet.EOF && i++ < nNumber )
      {
         Out ( '<tr><td>' );

            if ( nSortBy == nByDate )
               Out ( FormatDateDM ( oRecordSet ( 1 ) ) + ':' );
            else
               Out ( oRecordSet ( 3 ) + ' views:' );

         Out ( '</td><td>' );

            // the title of the snippet, as a hyperlink
            Out ( '<b><a href="Snippet.asp?snip=' + oRecordSet ( 0 ) + '">' + oRecordSet ( 2 ) + '</a></b>' );

         Out ( '</td></tr>' );

         oRecordSet.MoveNext();
      }

      if ( oRecordSet.EOF )
         bShowFullDescr = false;

      Out ( '</table>' );

      ContentTableBottom ( );
   }

   if ( bShowFullDescr )
      Out ( '<a href="/AllSnippets.html">See a complete list of all my snippets...</a>' );
}
%>

utils/Database.asp

<%
// ============================================
// NOTE: all source code downloaded from CoverYourASP was written by
// James Shaw (unless stated otherwise), and is copyright (c) 2000-2002
// by James Shaw. You can use the code for any purpose, but do not
// publish or distribute the content in any way.
//
// See http://CoverYourASP.com/Legal.asp for up-to-date details.
// ============================================

// globals
var oConnection;
var oRecordSet;

// enums

// Connection.State and Recordset.State property
var adStateClosed = 0;         // the object is closed.
var adStateOpen = 1;             // the object is open.
var adStateConnecting = 2;   // the object is connecting.
var adStateExecuting = 4;      // the object is executing a command.
var adStateFetching = 8;         // the rows of the object are being fetched.

// Recordset.Cursor property
var adOpenUnspecified = -1;   // does not specify the type of cursor.
var adOpenForwardOnly = 0;   // (default) a forward-only cursor, i.e. you get only one pass thru the data!
var adOpenKeyset = 1;         // can go in any direction, and as a bonus you'll see changes other users make.  EXPENSIVE!
var adOpenDynamic = 2;      // as Keyset, but also you can see additions/deletions other users make.  EXPENSIVE!
var adOpenStatic = 3;         // can go in any direction, but read-only.

// Recordset.LockType property
var adLockUnspecified = -1;   // does not specify a type of lock.
var adLockReadOnly = 1;      // (default) guess!
var adLockPessimistic = 2;      // guaranteed to work
var adLockOptimistic = 3;      // records locked only when you call Update. fingers crossed
var adLockBatchOptimistic = 4;// required for batch update mode

var adCmdUnspecified = -1;   // Does not specify the command type argument.
var adCmdUnknown = 8;      // Default. Indicates that the type of command in the CommandText property is not known.
var adCmdText = 1;            // a textual definition of a command or stored procedure call.
var adCmdTable = 2;            // a table name whose columns are all returned by an internally generated SQL query.
var adCmdStoredProc = 4;      // a stored procedure name.
var adCmdFile = 256;            // a persisted Recordset.
var adCmdTableDirect = 512;   // a table name whose columns are all returned.

// SchemaEnum - specifies the type of schema Recordset to be retrieved by the OpenSchema method
var adSchemaTables = 20;      // returns the tables
var adSchemaForeignKeys = 27   // returns the foreign keys (relationships)
// ============================================
// example usage:
//      DBInitConnection ( );
//
//      DBGetRecords ( "SELECT * FROM Somewhere" );
//
//      ...use oRecordSet
//
//      DBReleaseRecords ( );      // optional step
//
//      DBGetRecords ( "SELECT * FROM SomewhereElse" );
//
//      ...use oRecordSet
//
//      DBReleaseRecords ( );      // optional step
//
//      DBReleaseConnection ( );
// ============================================

// ============================================
// initializes database variables for first use on page - leave it to the
// last possible second before calling this function
// ============================================
function DBInitConnection ( )
{
   // don't open it again if already opened!
   if ( oConnection != undefined )
      return;

   // don't bother trying to open if path is below SSI folders
   if ( -1 != sDBPath.indexOf ( '\\utils\\' ) || -1 != sDBPath.indexOf ( '\\include\\' ) )
      return;

   // you can open Recordset objects without a Connection object, but
   // it's far less efficient if you are opening multiple Recordsets.
   //
   // if you don't create a Connection object ADO creates a new one for
   // each new Recordset.Open, even if you use the same connection string.
   oConnection = Server.CreateObject( 'ADODB.Connection' );

   try
   {
      // open the database, catching any errors that occur
      oConnection.Open( sConnectionString );
   }
   catch ( e )
   {
      // display error message, and send email
      DatabaseException ( e );

      // quit running the script completely
      Response.End ( );
   }

   // create a Recordset
   oRecordSet = Server.CreateObject( 'ADODB.Recordset' );
}

// ============================================
// tidies up after DBInitConnection
// ============================================
function DBReleaseConnection ( )
{
   // don't release the connection if not connected!
   if ( oConnection == undefined )
      return;

   // close and delete the Recordset object
   DBReleaseRecords ( );

   oRecordSet = undefined;

   // Don't call Close if the Recordset failed to Open properly, i.e. its
   // State is still adStateClosed (0)
   if ( oConnection.State != adStateClosed )
      oConnection.Close();

   oConnection = undefined;
}

// ============================================
// executes the passed in SQL statement and returns a read-only
// forward-only oRecordSet object
// ============================================
function DBGetRecords ( sSQL )
{
   // if the Recordset is already open, close it
   DBReleaseRecords ( );

   // I could use oRecordSet = oConnection.Execute( sSQL ) here
   // but then I will always get back a read-only, forward-only cursor.
   // (admittedly this is the most used type, but still)

   // use oRecordSet.Open and I have far more control. For details
   // read the definitions of the enums at the top of this file.

   //Out ( sSQL );Response.Flush();

   try
   {
      // remember that this can fail if passed garbage, and hence the
      // Recordset will remain closed, State == adStateClosed
      if ( oConnection )
         oRecordSet.Open ( sSQL, oConnection, adOpenForwardOnly, adLockReadOnly );
   }
   catch ( e )
   {
      // display error message, and send email
      DatabaseException ( e );

      // quit running the script completely
      Response.End ( );
   }
}

// ============================================
// tidies up after DBGetRecords
// ============================================
function DBReleaseRecords ( )
{
   // when you have finished with an open Recordset object, call the
   // Close method to release its resources. You can call Open again.

   // Don't call Close if the Recordset failed to Open properly, i.e. its
   // State is still adStateClosed
   if ( oRecordSet != undefined && oRecordSet.State != adStateClosed )
      oRecordSet.Close();
}

// ============================================
// display exception message, but strip out database path if necessary
// ============================================
function DatabaseException ( e )
{
   Out ( '<table bgcolor="#ff0000" cellpadding="20"><tr><td>' );

      Out ( '<h4><font color="white">An error has occured while connecting to the database:</font></h4>' );

      var sMessage = e.description;

      // strip out the database path if present
      var nStart = sMessage.indexOf ( sDBPath )

      if ( -1 != nStart )
         sMessage = sMessage.slice ( 0, nStart ) + '[database path]' + sMessage.slice ( nStart + sDBPath.length );

      Out ( '<h4>&nbsp;&nbsp;&nbsp;"' + sMessage + '"</h4>' );

      Out ( '<h4><font color="white">Don\'t despair - this problem is probably well-documented in my <a href="http://CoverYourASP.com/Trouble.asp"><font color="white">trouble-shooting</font></a> section.</font></h4>' );

   Out ( '</td></tr></table>' );

   // make up the message body
   var sBody = 'The file "' + Request.ServerVariables ( "URL" ) + '?' + Request.QueryString ( ) + '" generated a database error\n\n';

   sBody += 'Referrer: "' + Request.ServerVariables ( "HTTP_REFERER" ) + '".\n';
   sBody += 'Browser: "' + Request.ServerVariables ( "HTTP_USER_AGENT" ) + '".\n';
   sBody += 'IP address: "' + Request.ServerVariables ( "REMOTE_ADDR" ) + '".\n';

   var dateToday = new Date();

   sBody += 'Time: "' + dateToday.getHours() + ':' + dateToday.getMinutes() + '".\n';

   sBody += sMessage;

   // send the email
   SendEmail ( 'Database.Exception', 'BadDB@' + sHostDomain, '', 'Reporting exception', sBody );
}

// ============================================
// are we using Jet engine db, or SQL server?
// ============================================
var bUsingJet;

function DBIsJet ( )
{
   // for efficiency, only work out if which I'm using
   // the first time I'm used on a page.
   if ( bUsingJet == undefined )
      bUsingJet = ( -1 != sDBDriver.indexOf ( '.Jet.' ) );

   return bUsingJet;
}

// ============================================
// wrap date in relevant delimeters depending on db engine
// ============================================
function DBWrapDate ( sDate )
{
   return ( DBIsJet ( ) ? '#' + sDate + '#' : '\'' + sDate + '\'' );
}

// ============================================
//
// ============================================
function DBIsNull ( )
{
   return ( DBIsJet ( ) ? 'Is Null' : '= null' );
}

// ============================================
// stores dropdown lists in Application variables for use with foreign keys
// ============================================
function DBGatherForeignKeys ( )
{
   if ( !Application ( 'GatheredForeignKeys' ) )
   {
      DBInitConnection ( );

      bDebug = true;

      oRecordSet = oConnection.OpenSchema ( adSchemaForeignKeys );

      var nFields = oRecordSet.Fields.Count;
      var bHeaders = false;

      var sRefTables = new Array;
      var sRefColumns = new Array;
      var sForeignTables = new Array;
      var sForeignColumns = new Array;
      var nForeign = 0;

      while ( !oRecordSet.EOF )
      {
         if ( IsDebug ( ) )
         {
            if ( !bHeaders )
            {
               Out ( '<table border="1"><tr>' );

               for ( i=0; i<nFields; i++ )
                  Out ( '<td>' + oRecordSet.Fields ( i ).Name + '</td>' );

               Out ( '</tr>' );

               bHeaders= true;
            }

            Out ( '<tr>' );

            for ( i=0; i<nFields; i++ )
               Out ( '<td>' + oRecordSet ( i ) + '</td>' );

            Out ( '</tr>' );
         }
      
         sRefTables [ nForeign ] = '' + oRecordSet ( 'FK_TABLE_NAME' );
         sRefColumns [ nForeign ] = '' + oRecordSet ( 'FK_COLUMN_NAME' );
         sForeignTables [ nForeign ] = '' + oRecordSet ( 'PK_TABLE_NAME' );
         sForeignColumns [ nForeign++ ] = '' + oRecordSet ( 'PK_COLUMN_NAME' );

         oRecordSet.MoveNext  ( );
      }

      if ( bHeaders )
         DebugOut ( '</table>' );

      for ( i=0; i<nForeign; i++ )
      {
         DBGetRecords ( 'SELECT * FROM ' + sForeignTables [ i ] );

         try
         {
            var sList = '<select name="' + sRefColumns [ i ] + '">';
            var sForeignColumn = sForeignColumns [ i ];

            while ( !oRecordSet.EOF )
            {
               // I assume that the second field is
               // the one to show in dropdown list
               sList += '<option value="' + oRecordSet ( sForeignColumn ) + '">' + oRecordSet ( 1 ) + '</option>';

               oRecordSet.MoveNext  ( );
            }

            sList += '</select>';

            Application ( sRefTables [ i ] + ':' + sRefColumns [ i ] ) = sList;

            DebugOut ( '<p>Created ' + sRefTables [ i ] + ':' + sRefColumns [ i ] );
            DebugOut ( '<p>' + sRefColumns [ i ] + '=' + sForeignTables [ i ] + ':' + sForeignColumn + ' output:'+ Server.HTMLEncode ( sList ) + sList );
         }
         catch ( e )
         {
            DebugOut ( '<p>Failed to create dropdown list for ' + sRefTables [ i ] + ':' + sRefColumns [ i ] );
         }
      }

      DBReleaseConnection ( );

      Application ( 'GatheredForeignKeys' ) = true;
   }
}

// ============================================
// display (not editable) recordset column value
// ============================================
function DBDisplayValue ( oRecordSet, sTableName, nColumn )
{
   var sColumnName = oRecordSet.Fields ( nColumn ).Name;
   var oValue = oRecordSet ( nColumn );

   // get dropdown list if a foreign key
   var sHTML = Application ( sTableName + ':' + sColumnName );

//   DebugOut ( '<p>Application (  ' + sTableName + ':' + sColumnName + '=' + sHTML );

   if ( sHTML )
   {
      // disable control
      var nIndex = sHTML.indexOf ( ' name' );

      if ( nIndex != -1 )
         sHTML = sHTML.slice ( 0, nIndex ) + ' disabled' + sHTML.slice ( nIndex );

      // place 'selected' in the correct spot
      var nIndex = sHTML.indexOf ( ' value="' + oValue );

      if ( nIndex != -1 )
         sHTML = sHTML.slice ( 0, nIndex ) + ' selected' + sHTML.slice ( nIndex );
   }
   else
   {
      // show prettier dates
      if ( oValue.Type == 7/*date*/ )
         sHTML = FormatDateDMY ( oValue );
      else
         sHTML = "" + Server.HTMLEncode ( '' + oValue );

      // for brevity show the first x characters only
      if ( sHTML.length > 35 )
         sHTML = sHTML.slice ( 0, 35 ) + '...';
   }

   return sHTML;
}

// ============================================
// display editable recordset column value
// ============================================
function DBEditValue ( oRecordSet, sTableName, nColumn )
{
   var sColumnName = oRecordSet.Fields ( nColumn ).Name;
   var oValue = oRecordSet ( nColumn );

   // get dropdown list if a foreign key
   var sHTML = Application ( sTableName + ':' + sColumnName );

//   DebugOut ( '<p>Application (  ' + sTableName + ':' + sColumnName + '=' + sHTML );

   if ( sHTML )
   {
      // place 'selected' in the correct spot
      var nIndex = sHTML.indexOf ( ' value="' + oValue );

      if ( nIndex != -1 )
         sHTML = sHTML.slice ( 0, nIndex ) + ' selected' + sHTML.slice ( nIndex );
   }
   else
   {
      // show prettier dates
      if ( oValue.Type == 7/*date*/ )
         sHTML = FormatDateDMY ( oValue );
      else
         sHTML = "" + Server.HTMLEncode ( '' + oValue );

      sHTML = '<input type="text" name="' + sColumnName + '" size="45" value="' + sHTML + '">';
   }

   return sHTML;
}

// ============================================
// return value with ' replaced by SQL-safe ''
// ============================================
function DBEncode ( sValue )
{
   return sValue.replace ( /\'/g, '\'\'' );
}
%>

Hopefully much of this is self-explanatory. If not, or if you see ways that I can improve the code, please drop me a line.

To see the source code for this page, click on the icon below.

Featured sponsor
My favorite resources


Qualify for Free Trade Magazines

Free subscriptions to industry leading publications for those who qualify!


I share my content

Supporting ASPRSS

Do you need a quick and easy way to link to my articles? All the information you need is published with ASPRSS...


New Proposal Kit Professional 5.1
Brand yourself as a top professional: create quotes and amazing proposals and get many legal documents free!

The latter saved me 3 times the purchase price on the first day I owned it!

CoverYourASP Mugs, T-shirts, caps - even Boxer shorts...
I don't make a penny from these, but they're a lot of fun! Don't you need a new mouse mat?


See my source code
wherever you see this icon...

You can also download the entire site source code for FREE!