[script      language = "VBscript" 
                runat = "Server"]
  function dbug(ss)
    Response.Clear()
    Response.Write(ss)
    Response.End()
  end function '--dbug
[/script]

[script   language = jscript      runat = server]

/*---------------------------------------------------------------------
 * FUNCTION: fnDataSELECT(...)
 * DESCRIPTION:
    This function displays an html <select> element (that is 
    a 'drop-down box' or 'look-up box') whose <option> elements
    are filled directly 
    (with one column filling the 'value' attribute and another 
    column filling the 'display' attribute) or by specifying 
    a query string (in SQL) which will then supply the values
    for the set of <option> elements. The 'sLookupString'
    parameter can either be a valid SQL query, the name of 
    a table within the current database (the database refered to 
    by the adoConnection parameter) or the name of a stored-proceedure.
 * DOCUMENTATION: 
    http://www.geocities.com/matth3wbishop/eg/asp/     
 * LOCATION: 
     http://www.geocities.com/matth3wbishop/eg/asp/fnDataSELECT
 * LANGUAGE: MicroSoft JScript
 * NOTES: This function fulfils the common task (is ASP 
     programming) of filling a <select> element with values
     from a database. Usually this is done by programmers by
     cut-and-paste techniques. I have capitalized the 
     'SELECT' in the function's name in order to emhasize that
     it is an html select element being refered to.
     The fnDataRADIO function is very similar except that an
     html radio button set is produced instead of a <select> element.
 * SEE ALSO: fnDataRADIO(), fnDisplayRecorset(), fnShowRecord()
----------------------------------------------------------------------*/

function fnDataSELECT(
  adoConnection, sLookupString, sValueField, 
  sDisplayField, sSelectedValue, sName, 
  aaConnect)
{  
  var bCloseConnection = new Boolean(false);
  var sConnString = new String();
  var sQuery = new String();
  var adoRs;
  
 //' on error resume next
  if (sSelectedValue == null)
    {sSelectedValue = '0';}
  if (sLookupString == null)
  {    
    fnErrorMessage(
      "fnDataSELECT", "LookupString",
      "This required parameter was omitted from the function call.");
  } /* if parameters missing */

  
  if ((adoConnection == null) && (aaConnect == null))
  {
    fnErrorMessage(
      "fnDataSELECT", "adoConnection, aaConnect",
      "One of the two parameters above must be supplied<br>" +
      "Both were omitted.");
  } /* if no db opening info */                            
  
  if ((adoConnection == null) && (aaConnect != null))
  {
		var sConn;                     //temp string variable
		var aaCon = new Array();	

    aaConn = fnConvertArray(aaConnect);
    //***Provide some default values
    if (aaConn[0] == null)
      {aaConn[0] = "sql server";}
    if (aaConn[1] == null)
      {aaConn[1] = "(local)";}
    if (aaConn[2] == null)
      {aaConn[2] = "sa";}
    if (aaConn[3] == null)
      {aaConn[3] = "max";}
        
    sConn = "DRIVER={" + aaConn[0] + "};";
    sConn += "SERVER=" + aaConn[1];
    adoConnection = Server.CreateObject("ADODB.connection");
    //dbug(sConn);
    
    adoConnection.Open(sConn, aaConn[2], aaConn[3]);
    if (aaConn[4] != null)
      {adoConnection.DefaultDatabase = aaConn[4];}
      
    bCloseConnection = true;
  } /* if no connection */
  
  adoRs = Server.CreateObject("ADODB.recordset");
  adoRs.ActiveConnection = adoConnection;
  
  //***different types of open strings
  var regQ = /([^ ]+).*/gi;
  var regP = /\bselect\b/gi;
  var sQ;                //***Holds the query string 
  var sTemp;             //***Temporary variable
  
  if (regP.test(sLookupString) == false)
  {
    //***handle raw table names
    sTemp = sLookupString.replace(regQ, "$1");
    //-- dbug(sTemp);
    
    sQ = 
      "sp_tables '" + sTemp + "'";
      
      adoRs.Open(sQ);
    
    if (!adoRs.EOF)
    {
      sLookupString = "SELECT * FROM " + sTemp;      
    } /* if */  
    else
    {
      //***handle stored procedures
      adoRs.Close();
      var sQ = 
	  "sp_stored_procedures '" + sTemp + "'";
	    adoRs.Open(sQ);
	if (!adoRs.EOF)
	{
	  //do nothing: the string is okay
	} /* if */  
	else
	{
	  /*------------
	  REM This is causing a problem with stored procs like
	  sp_column, which is why I have disabled it.
	  
	  fnErrorMessage(
	   "fnDataSELECT", "sLookupString ('" + sLookupString + "')",
	   "The parameter is neither a SQL SELECT statement,<br>" +
	   "nor the name of a Table, nor the name of a stored procedure<br>" +
	   "in the database. The parameter must be one of these");
	   
	  --------------*/ 
	} /* if, else */
    } /* if, else */
    adoRs.Close();    
  } /* if no 'select' in the string */
     
  adoRs.MaxRecords = 1000;     
  adoRs.Open(sLookupString);

  if (adoRs.EOF)
    {sReturn = "ERROR:"}
      
  if (!adoRs.EOF)
  {    
    //***Defaults and error checking
    if (sValueField == null) 
    {    
	sValueField = adoRs.Fields.Item(0).Name;
    } /* if */
  
    if (sDisplayField == null)
    {    
	sDisplayField = adoRs.Fields.Item(1).Name;
    } /* if */
    
    if (fnCheckForField(adoRs, sValueField) == false)
    {
      fnErrorMessage(
        "fnDataSELECT", "sValueField ('" + sValueField + "')",
        "The parameter does not correspond to any field<br>" +
        "in the data set");
    } /* if */  

    if (fnCheckForField(adoRs, sDisplayField) == false)
    {
      fnErrorMessage(
        "fnDataSELECT", "sDisplayField ('" + sDisplayField + "')",
        "The parameter does not correspond to any field<br>" +
        "in the data set");
    } /* if */  
  } /* if there is data */
  
  strReturn = "<select ";
  if (sName != null)
    {strReturn += "name = " + sName;}
  strReturn +=
    " size = 1>\n" + 
    "   <option value = null>\n";
  
  
  //*** create the option elements
  while (!adoRs.EOF)
  {
    strReturn += 
      "   <option value = \"" + Server.HTMLEncode(adoRs(sValueField)) + "\" ";
    if (adoRs(sValueField) == sSelectedValue)
      {strReturn +=  " SELECTED";}    
    strReturn +=  ">" + Server.HTMLEncode(adoRs(sDisplayField)) + 
      "   </option>\n";
    adoRs.MoveNext();
  } /* while */       
  
  strReturn += "</select>\n";
  strReturn += 
    "<input    type = 'hidden'  name = 'hidValueField' \n" + 
    "         value = \"" + sValueField + "\"> \n";
  strReturn += 
    "<input    type = 'hidden'  name = 'hidDisplayField' \n" + 
    "         value = \"" + sDisplayField + "\"> \n";
  strReturn += 
    "<input    type = 'hidden'  name = 'hidOriginalValue' \n" + 
    "         value = \"" + sSelectedValue + "\"> \n";
    
  adoRs.Close();
  adoRs = null;
  
  if (bCloseConnection == true)
  {
    adoConnection.Close();
    adoConnection = null;
  } /* if */   

  return strReturn;
} /* fnDataSELECT */

[/script]