CoverYourASP --> "/MemberLogin.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
46 active users
908 visitors today
1042 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...

MemberLogin.asp

<!--#include file = "/include/Startup.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.
// ============================================

// increment the parent articles counter
sIncArticlePage = '/MemberCode.html';

// output relevant meta tags
Init( "CoverYourASP member sign in" );

// output common top of page
Header( '<a href="/MemberServices.html">Member Services</a> --> Signing in' );

// output page content
Content ( );

// output common bottom of page
Footer( );

// ============================================
// the content of this page - every page has a function 'Content' that
// is called above.
// ============================================
function Content ( )
{
   Out ( '<td valign="top" class="content">' );

      // as always, the form is submitted to the same page so that
      // all the logic for the form is in the same place. you'll see
      // later where this is done.

      // the first thing to do is validate the data if the form has been
      // submitted - start by getting the data from the form
      var bSubmitted = ( Request.Form.Count>0 );
      var sEmail = '';
      var sAccess = '/MemberServices.html';
      var bCookieLogin = true;

      // has the form been submitted?
      if ( bSubmitted )
      {
         // get the data from the form...
         sEmail = '' + Request.Form ( 'email' );
         sPassword = '' + Request.Form ( 'password' );
         sAccess = '' + Request.Form ( 'access' );

         if ( 'cookielogin' != '' + Request.Form ( 'cookielogin' ) )
            bCookieLogin = false;

         // validate the logon and moan if it fails
         // see utils/Loginl.asp for details about the ValidateLogin function
         if ( !ValidateLogin ( sEmail, sPassword, bCookieLogin ) )
         {
            Out ( '<center><font color="red">' );
               Out ( '<font size=+1>Unknown email address or incorrect password.</font>' );
               Out ( '<p>(it is also possible that you haven\'t yet <a href="/MemberHelp.html">confirmed your email address</a>)' );
            Out ( '</font></center><p>' );
            // pretend the form hasn't been sent yet
            bSubmitted = false;
         }
      }
      else
      {
         sEmail = '' + Request.Cookies ( sCookieEmail );
      
         if ( Request.QueryString ( 'access' ).Count )
         {
            Out ( '<hr color="#ff9900"><font color="blue">Sorry, but the page you tried to access is restricted to signed-in members only. Please sign in below.</font><hr color="#ff9900">' );
            sAccess = '' + Request.QueryString ( 'access' );
         }
      }

      // show the form if not submitted yet
      if ( !bSubmitted )
      {
         Out ( '<h3>Registered already?</h3>' );

         Out ( 'If you are already a registered member, please sign in by entering your email address and password in the form below. For more information about this process see the <a href="/MemberHelp.html">Membership Help</a>. <i>Forgotten your password? <a href="/SendPassword.html">I can send it via email</a></i>' );

         Out ( '<center>' );

         // here's the form tag. the action attribute is the name of
         // the file that will be called with the answer - in this case
         // it's the same page. the method can be "post" to send the
         // form data 'behind the scenes' or "get" to appending the
         // data to the URL in the style page.asp?data1=a&data2=b
         //
         // use post most of the time - it's neater and "get" is limited
         // in the amount of data that can be sent.
         Out ( '<form action="/MemberLogin.html" method="post">' );

            Out ( '<input type="hidden" name="access" value="' + sAccess + '">' );

            // another table to line up the titles and inputs
            Out ( '<p><table bgcolor="#dddddd">' );

            Out ( '<tr><td>' );
               Out ( '&nbsp;' );
            Out ( '</td><td align="left">' );
               Out ( '<font size=+1>Please sign in</font>' );
            Out ( '</td></tr>' );

            Out ( '<tr><td align="right">' );
               Out ( 'Email:' );
            Out ( '</td><td align="left">' );
               // a simple text box. I'll reference it with the name "email"
               // and show 20 characters on the form. use the maxlength
               // attribute to set the maximum characters they can enter.
               // use value="some text" to pre-fill the input with data.
               //
               // IMPORTANT! using names that are commonly used by
               // other web sites has a big advantage to the user - IE
               // will drop down a list of previous answers, which they
               // can usually pick from rather than type in.
               Out ( '<input type="text" name="email" value="' + sEmail + '" size="20">' );
            Out ( '</td></tr>' );

            Out ( '<tr><td align="right">' );
               Out ( 'Password:' );
            Out ( '</td><td align="left">' );
               Out ( '<input type="password" name="password" size="20">' );
            Out ( '</td></tr>' );

            Out ( '<tr><td colspan=2 align="center">' );
               Out ( '<input type="checkbox" ' + ( bCookieLogin ? 'checked' : '' ) + ' name="cookielogin" value="cookielogin"> Sign me in automatically on this computer.' );
            Out ( '</td></tr>' );

            Out ( '<tr><td align="right">' );
               Out ( '&nbsp;' );
            Out ( '</td><td align="left">' );
               // type='submit" provides a submit button to perform the
               // form action. the button says "Submit" unless you override
               // with the value attribute.
               Out ( '<input type="Submit" value="    Sign in    ">' );
            Out ( '</td></tr>' );
            Out ( '</table>' );

         Out ( '</form>' );

         Out ( '</center>' );

         Out ( '<h3>Not registered yet?</h3>' );

         Out ( 'Registering to become a lifetime member is quick, easy and free, and <a href="/Privacy.html">your privacy</a> is guaranteed. <a href="/MemberEdit.html">Register now!</a>' );

         Out ( '<p>Want to see how this page signs you in? Click below to get all the source code!' );
         Out ( '<p><center><a href="/ShowSource_page_MemberLogin.html"><img src="images/source.gif" border=0></a></center>' );
      }
      else
      {
         // successfully logged in, so redirect to original page
         Redirect ( sAccess );
      }

      ShowBottomBanner()

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

      // show rotating banners
      ShowBanners ( 2 );

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

utils/Login.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 bLoggedIn;         // leave undefined
var sMemberName = '';
var sMemberEmail = '';
var nMemberID = 0;
var nMemberLevel = 0;
var bMemberCookie = false;

// front page personalization
var bIntro = true;
var bSuggestions = true;
var bCategories = false;
var bNews = false;
var bDiary = false;
var nNew = 5;
var nPopular = 0;
var nNewSnippets = 5;
var nPopularSnippets = 10;

// level names
var sLevels = new Array (
   'Bronze',
   'Silver',
   'Gold',
   'Advertising'
   );

// ============================================
// is member logged in?
// ============================================
function IsLoggedIn ( )
{
   if ( bLoggedIn == undefined )
   {
      // am I logged in?
      if ( ! ( bLoggedIn = Session ( 'Authenticated' ) ) )
      {
         // no, so try to get data from cookie
         if ( "" != Request.Cookies ( sCookieEmail ) )
         {
            // yes I have a cookie...
            var sEmail = "" + Request.Cookies ( sCookieEmail );
            var sPassword = "" + Request.Cookies ( sCookiePassword );

            //..so act as though they have just entered it
            bLoggedIn = ValidateLogin ( sEmail, sPassword, true );
         }
      }

      if ( bLoggedIn )
      {
         // store data in global variables
         sMemberName = Session ( 'MemberName' );
         sMemberEmail = Session ( 'MemberEmail' );
         nMemberID = Session ( 'MemberID' );
         nMemberLevel = Session ( 'MemberLevel' );
         bMemberCookie = Session ( 'MemberCookie' );

         bIntro = Session ( 'MemberIntro' );
         bCategories = Session ( 'MemberCategories' );
         bDiary = Session ( 'MemberDiary' );
         bSuggestions = Session ( 'MemberSuggestions' );
         bNews = Session ( 'MemberNews' );
         nNew = Session ( 'MemberNewArticles' );
         nPopular = Session ( 'MemberPopular' );
         nNewSnippets = Session ( 'MemberNewSnippets' );
         nPopularSnippets = Session ( 'MemberPopularSnippets' );
      }
   }

   return bLoggedIn;
}

// ============================================
// show if user is logged in or not, with link to login/logout
// ============================================
function ShowLoginStatus ( sStart, sEnd )
{
   Out ( sStart );

   // hide the login and competition when used on template for 3rd party sites
   if ( bNoDynamic )
   {
      Out ( '<p>Please note: You are browsing an area of CoverYourASP whose content is being served by one of my partners.' );
      
      Out ( '<p>Your membership login is not used here, so you are NOT accruing points in the <a href="/Contest.html">CoverYourASP contest</a> while in this area of the site.' );
      
      Out ( sEnd );
      return;
   }

   // connect to database
   DBInitConnection ( );

   // get prize image name
   var sImage = '' + Application ( 'ContestImage' );

   if ( sImage == 'undefined' )
   {
      DBGetRecords ( 'SELECT TOP 1 ContestImage FROM Contest WHERE Awarded=False ORDER BY ContestID ASC' );

      if (oRecordSet.EOF)
         sImage = '';
      else
         sImage = '' + oRecordSet ( 0 );
   }

   // release database
   DBReleaseConnection ( );

   // store image name for next page
   Application.Lock ();
   Application ( 'ContestImage' ) = sImage;
   Application.Unlock ();

   if ( IsLoggedIn ( ) )
   {
      // connect to database
      DBInitConnection ( );

      if (sImage != '')
      {
         // stop the user from just refreshing their way to the maximum points!
         // I keep the last 2 pages in Session variables and don't increment if
         // either are re-visited. A long way from foolproof, but much better!
         var sThisPage = "" + Request.ServerVariables ( "SCRIPT_NAME" );

         if ( Session ( 'LastPage' ) != sThisPage &&
               Session ( 'SecondLastPage' ) != sThisPage )
         {
            Session ( 'SecondLastPage' ) = Session ( 'LastPage' );
            Session ( 'LastPage' ) = sThisPage;

            // increment member page count up to a maximum of 500 pages per contest
            // (dont store in Session variable so I can update from other scripts like StartContest.asp)
            oConnection.Execute( 'UPDATE Members SET nPoints=IIF(nPoints>=500,500,nPoints+1) WHERE MemberID=' + nMemberID );
         }

         // get current score
         DBGetRecords( 'SELECT nPoints FROM Members WHERE MemberID=' + nMemberID );

         var nMemberPoints = oRecordSet ( 0 ) - 0;

         // release database
         DBReleaseConnection ( );

         // show number of page views
         Out ( '<a href="/Contest.html">You have <font size=+1>' + nMemberPoints + '</font> chances of winning these prizes!</a>' );

         // show prize gif
         Out ( '<p><a href="ContestPrizes.asp"><img src="images/prizes/' + sImage + '.jpg" width="135" height="100" border="0" alt="Win these prizes!"></a>' );
      }

      Out ( '<p><a href="/MemberLogout.html">Sign out</a> ' + sMemberName );
   }
   else
   {
      if (sImage != '')
      {
         Out ( '<a href="/Contest.html">You are missing out on some <font size=+1>cool prizes!</font>' );
         // show prize gif
         Out ( '<p><a href="ContestPrizes.asp"><img src="images/prizes/' + sImage + '.jpg" width="135" height="100" border="0" alt="Win these prizes!"></a>' );
      }

      Out ( '<p>Join in the fun! <a href="/MemberLogin.html">Sign in</a>' );
   }

   Out ( sEnd );
}

// ============================================
// show if user can be upgraded or not
// ============================================
function ShowMemberStatus ( sStart, sEnd )
{
   Out ( sStart );

   if ( !IsLoggedIn ( ) )
   {
      Out ( '<a href="/MemberLogin.html">Get free lifetime membership, and personalize the site!</a>' );
   }
   else
   {
      switch ( nMemberLevel )
      {
      case 1:
//         Out ( '<a href="/MemberUpgrade.html">Now available:<br>exclusive tutorials for Silver members!</a>' );
         break;

      case 2:
         Out ( '' );
         break;
      }
   }

   Out ( sEnd );
}

// ============================================
// validate email/password
// ============================================
function ValidateLogin ( sEmail, sPassword, bCookieLogin )
{
   // connect to database
   DBInitConnection ( );

   // search for matching email/password
   DBGetRecords ( 'SELECT MemberID,Name,MemberLevel,bIntro,bNews,bCategories,bDiary,nNew,nPopular,bSuggestions,nNewSnippets,nPopularSnippets FROM Members WHERE Confirmed=True AND Email=\'' + DBEncode ( sEmail ) + '\' AND MemberPassword=\'' + DBEncode ( sPassword ) + '\'' );

   if ( !oRecordSet.EOF )
   {
      Session ( 'MemberEmail' ) = sEmail;
      Session ( 'MemberID' ) = oRecordSet ( 0 ) - 0;
      Session ( 'MemberName' ) = '' + oRecordSet ( 1 );
      Session ( 'MemberLevel' ) = oRecordSet ( 2 ) - 0;
      Session ( 'MemberCookie' ) = bCookieLogin;

      Session ( 'MemberIntro' ) = oRecordSet ( 3 ) - 0;
      Session ( 'MemberNews' ) = oRecordSet ( 4 ) - 0;
      Session ( 'MemberCategories' ) = oRecordSet ( 5 ) - 0;
      Session ( 'MemberDiary' ) = oRecordSet ( 6 ) - 0;
      Session ( 'MemberNewArticles' ) = oRecordSet ( 7 ) - 0;
      Session ( 'MemberPopular' ) = oRecordSet ( 8 ) - 0;
      Session ( 'MemberSuggestions' ) = oRecordSet ( 9 ) - 0;
      Session ( 'MemberNewSnippets' ) = oRecordSet ( 10 ) - 0;
      Session ( 'MemberPopularSnippets' ) = oRecordSet ( 11 ) - 0;

      Session ( 'Authenticated' ) = 1;

      // add 5 points for starting a new session
      // up to a maximum of 500 pages per contest
      oConnection.Execute( 'UPDATE Members SET nPoints=IIF(nPoints>=500,500,nPoints+5) WHERE MemberID=' + Session ( 'MemberID' ) );

      if ( bCookieLogin )
      {
         Response.Cookies ( sCookieEmail ) = sEmail;
         Response.Cookies ( sCookiePassword ) = sPassword;

         // get a date 1 year in the future
         var d = new Date;
         var m = d.getMonth() + 1;
         if (m == 2)   // if february, make it march (to stop failing on 2/29!
            m = 3;
         var sDate = d.getDate ( ) + '/' + m + '/' + (d.getFullYear ( ) + 1);

         Response.Cookies ( sCookieEmail ).Expires = sDate;
         Response.Cookies ( sCookiePassword ).Expires = sDate;
      }
      else
      {
         KillLoginCookies ( );
      }

      // release database
      DBReleaseConnection ( );

      return true;
   }

   // release database
   DBReleaseConnection ( );

   KillLoginCookies ( );

   return false;
}

// ============================================
// remove login cookies
// ============================================
function KillLoginCookies ( )
{
   Response.Cookies ( sCookieEmail ) = '';
   Response.Cookies ( sCookieEmail ).Expires = '01/01/1980';
   Response.Cookies ( sCookiePassword ) = '';
   Response.Cookies ( sCookiePassword ).Expires = '01/01/1980';
}

// ============================================
// sign out the user, remove cookie
// ============================================
function Logout ( )
{
   // clear the authenticated status
   Session ( 'Authenticated' ) = 0;

   // remove login cookies
   KillLoginCookies ( );

   // redirect to front page
   Redirect ( '/default.html' );
}

// ============================================
// make sure the user is signed in, and has sufficient access rights
// if not then redirect to passed in page
// ============================================
function NeedAccessLevel ( nLevel, sRedirect )
{
   if ( !IsLoggedIn ( ) )
      Redirect ( '/MemberLogin_access_.html' + Request.ServerVariables ( "SCRIPT_NAME" ) );

   if ( nMemberLevel < nLevel )
      Redirect ( sRedirect );
}
%>

MemberLogout.asp

<!--#include file = "/include/Startup.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.
// ============================================

// increment the parent articles counter
sIncArticlePage = '/MemberCode.html';

// sign out the user
Logout ( );
%>

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', '[email protected]' + 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


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!


Qualify for Free Trade Magazines

Free subscriptions to industry leading publications for those who qualify!


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

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


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...

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?