[script      language = "VBscript"           runat = "Server"]
  function dbug(ss) 
    Response.Clear() 
    Response.Write(ss) 
    Response.End() 
  end function '--dbug 
   
  function fnLOWER(ss) 
    fnLOWER = LCASE(ss) 
  end function 
[/script] 
 
[script language = jscript runat = server]
/*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--* 
* FUNCTION: fnSimpleUpdateRecord(...) 
* DESCRIPTION: 
    This function attempts to provide a simple interface for 
    the 'updating' (changing the values) of a record in a 
    database. Unfortunately I was never able to eliminate the 
    many bugs. It is designed for use with the Microsoft Sql Server
    database. In addition, it should be split into two functions,
    namely; 
      fnDataRecordInterface()
        which would provide the html form interface with the 
        appropriate html <input> elements set to the current
        values of the data record and
      fnUpdateDataRecord()
        which would take the valued submitted by the form
        (generated by fnDataRecordInterface()) and use those
        values to update the record in the database.
* STATUS: buggy
* DEPENDENCIES: 
    fnConvertArray()
    fnErrorMessage()
* DOCUMENTED AT: 
    http://www.geocities.com/matth3wbishop/eg/asp/
* CODE LOCATION: 
    http://www.geocities.com/matth3wbishop/eg/asp/
* TO DO: a) make casts, converts 
         b) Include non SqlSvr6.5/7 parameter 
         c) combine the adoConnection and the aaConnect parameters 
         d) If the function is in update mode then ignore the 
            parameters 
         e) Include the datalenths of datatypes so that data is 
            not trucated    
         f) Make the hidden element generate the update string on the 
            client side. 
         g) Use the datatypes to perform form validation on the client side 
         h) Deal with embedding problems of dynamic parameter generation 
            ie when the parameters for this function are generated dynamically 
            by another page or function on the page etc. 
         i) give a parameter to allow for custom client form validation 
            ie the name of a Javascript function which the user can fill in  
            for form validation          
*  NOTES: I would like to support a text file back-end as well as 
     rdms's such as sql server, mySql, postgreSql, mSql etc.
     This function also tries to be too flexible in the way that it
     specifies what data-record is to be updated. The strOpen
     parameter for example can be a stored-proc, a query, a table name
     etc. I would also like to provide an alternative to the 
     array parameter variables namely 'string lists'; that is, strings
     containing values separated by commas (or something else)
*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*/ 
 
  function fnSimpleUpdateRecord( 
     adoConnection,   //***An open data connection (or connection string) 
     strOpen,         //***The string used to return data 
     aaFieldInfo,     //***updateable, event handler info etc 
     sTName,          //***The name of the table to be updated 
     aaLabels,        //***The labels for each field of the record 
     aaColourInfo,    //***The colours to be used in the display 
     aaConnect,       //***TO BE REMOVED 
     aaOnChangeJavaScript,  //***Functions for the onchange parameter 
     fGuessLookups,   //***Whether Lookups for Foreign Keys will be guessed 
     fIsSqlServer     //***Whether the database is SQL server or not 
     ) 
  { 
    var sErrorInformation = new String("");  
    var bCloseConnection = new Boolean(false);     
    var ooRecord;                      //The Ado recordset variable 
    var sConn;                         //Temp connection string 
    var aaFieldLabels = new Array();   //Text labels for the field values 
    var aaColours = new Array();       //Colours for cells and text 
    var aaConn = new Array();          //Hold the data connection info 
    var sMessage = new String("");     //The update status message. 
    var sErrorInfo = new String("");   //Write-back error details 
    var sTableName = new String("");   //***The name of the database table, used for the 
                        //***write-back, it is parsed from either the strOpen 
                        //***strOpen or sTableFields parameters 
     
    //dbug(aafieldlabels.getitem(0)); 
    /*--*--*--*--*--*--*--*--*--*--*--*--*--*--*-- 
    [[start error handling]] 
     --*--*--*--*--*--*--*--*--*--*--*--*--*--*--*/     
      
    if ((strOpen == null) || (typeof(strOpen) != "string")) 
    { 
      fnErrormessage( 
        "fnSimpleUpdateRecord", "strOpen", 
        "The parameter was omitted or is not a string."); 
    } /* if */ 
     
    if ((adoConnection == null) && (aaConnect == null)) 
    { 
      fnErrorMessage( 
        "fnSimpleUpdateRecord", "adoConnection, aaConnect", 
        "Either the 'adoConnection' or the 'aaConnect' parameter<br>" + 
        "is required."); 
    } /* if */ 
     
    if ((adoConnection == null) && (aaConnect != null)) 
    {       
      var aaTemp = new VBArray(aaConnect); 
      aaConn = aaTemp.toArray(); 
      var sConn;                     //temp string variable 
       
      //***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 */ 
     
    /*--*--*--*--*--*--*--*--*--*--*--*--*--*--*-- 
    UPDATE THE RECORD (IF THE FORM IS SUBMITTED) 
     --*--*--*--*--*--*--*--*--*--*--*--*--*--*--*/     
    if ("IS" + Request.Form("hidTableName") != "ISundefined") 
    { 
      var sTblName;           //***Database Table Name    
      var sUpdateFields;      //***The Fields to update  
      var sPrimaryKeys;       //***The tables primary key fields 
      var sPrimaryKeyValues;  //***The values for each of the PK fields 
      var regFields;          //***To parse the field string 
      var regKeys; 
      var aaFields = new Array(); 
      var aaKeys = new Array(); 
      var aaKeyValues = new Array(); 
      var sUpdate; 
      var adoRsDT             //***find the data types 
      var sQQ;                //***temp var 
      var sFieldDataType      //***The data type of each Field 
       
      adoRsDT = Server.CreateObject("ADODB.recordset"); 
      adoRsDT.ActiveConnection = adoConnection; 
        
      sTblName = Request.Form("hidTableName"); 
      sUpdateFields = new String(Request.Form("hidUpdateFields")); 
      sPrimaryKeys = new String(Request.Form("hidPrimaryKeys")); 
      sPrimaryKeyValues = new String(Request.Form("hidPrimaryKeyValues")); 
       
      aaFields = sUpdateFields.split(";"); 
      aaKeys = sPrimaryKeys.split(";"); 
      aaKeyValues = sPrimaryKeyValues.split(";"); 
      sUpdate =  
        "UPDATE " + sTblName + " \n" + 
        "SET "; 
         
      for (var ii = 0; ii < aaFields.length; ii++) 
      {         
       //THIS IS SQL SERVER SPECIFIC, should include 
       //a bypass for non SQL SVR databases  
        sQQ =  
          "sp_columns @table_name = '" + sTblName + "', " +  
          "          @column_name = '" + aaFields[ii] + "'"; 
        //dbug(sQQ);   
        adoRsDT.Open(sQQ);
        //--fnDbugRecordset(adoRsPK)   
        //--fnDbugRecordset(adoRsDT); 
        if (aaFields[ii] != null) 
        { 
          //--REM These attempted conversions are not working
          //--REM revise them
          sFieldValue = Request.Form("Input" + aaFields[ii]); 
          if ((adoRsDT("Type_name") == "varchar") || 
              (adoRsDT("Type_name") == "char") || 
              (adoRsDT("Type_name") == "text") || 
              (adoRsDT("Type_name") == "nchar")) 
          { 
            sUpdate += aaFields[ii] +  
              "= '" + sFieldValue + "', "; 
            //sUpdate += aaFields[ii] +  
            //  "= CONVERT(" + adoRsDT("Type_name") +  
            //     "(" + adoRsDT("PRECISION") + ")" + ", '" +  
            //           sFieldValue + "'), "; 
          } 
          else if (adoRsDT("Type_name") != "id") 
          { 
            sUpdate += aaFields[ii] +  
              "= " + sFieldValue + ", "; 
            //sUpdate += aaFields[ii] +  
            //  "= CONVERT(" + adoRsDT("Type_name") + ", '" +  
            //                  sFieldValue + "'), "; 
          } /* if else */                   
          else  
          { 
            sUpdate += aaFields[ii] +  
              "= " + sFieldValue + ", "; 
            //sUpdate += aaFields[ii] +  
            //  "= " + sFieldValue + ", "; 
          } /* if else */                   
        } /* if */ 
        adoRsDT.Close(); 
      } /* for */     
        
      adoRsDT = null; 
      sUpdate = sUpdate.substr(0, sUpdate.length - 2); 
      sUpdate += " WHERE "; 
      for (var ii = 0; ii < aaKeys.length; ii++) 
      {         
        if (aaKeys[ii] != null) 
        { 
          sUpdate += aaKeys[ii] + " = '" + aaKeyValues[ii] + "', "; 
        } 
      } /* for */      
      sUpdate = sUpdate.substr(0, sUpdate.length - 2); 
         
      //--dbug(sUpdate); 
       
      adoConnection.Execute(sUpdate); 
      sMessage = 
        "The changes to the record were successful"; 
    } /* if the form was submitted */     
    /*--*--*--*--*--*--*--*--*--*--*--*--*--*--*-- 
    END OF UPDATING 
     --*--*--*--*--*--*--*--*--*--*--*--*--*--*--*/     
     
     
    ooRecord = Server.CreateObject("ADODB.recordset"); 
    ooRecord.ActiveConnection = adoConnection; 
 
    //***different types of open strings 
    var regQ = /([^ ,@;\/]+).*/gi; 
    var regP = /\bselect\b/gi; 
    var regR = /([^ ,@;\/]+)(.)(\d{1,2})/gi; 
     
    var sQ;                //***Holds the query string  
    var sTemp;             //***Temporary variable 
     
    if (regP.test(strOpen) == false) 
    { 
      //***handle raw table names 
      sTemp = strOpen.replace(regQ, "$1"); 
      sQ =  
        "sp_tables '" + sTemp + "'"; 
      //dbug(sQ);         
      ooRecord.Open(sQ); 
        
      if (!ooRecord.EOF) 
      { 
        sTableName = sTemp; 
        strOpen = "SELECT * FROM " + sTemp;       
      } /* if */   
      else 
      { 
        //***handle stored procedures 
        ooRecord.Close(); 
         
        //SQL SVR SPECIFIC 
        var sQ =  
          "sp_stored_procedures '" + sTemp + "'"; 
        ooRecord.Open(sQ); 
        if (!ooRecord.EOF) 
        { 
          //do nothing: the string is okay 
        } /* if is a stored proc */   
        else 
        { 
          fnErrorMessage( 
            "fnSimpleUpdateRecord", "strOpen ('" + strOpen + "')", 
            "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 */ 
      ooRecord.Close();     
    } /* if no 'select' in the string */ 
    else 
    { 
      //--REM parse the table name from the  
      //--REM select statement. This will NOT work for some queries
      //--REM I have agonised over this terribly. It has cost
      //--REM me blood and sweat and bitter tears. I have laboured
      //--REM and toiled.
      
      var rxTableFinder = new RegExp("[ \n]from[ \n]+([^ \n,]+)(.|\n)*", "gi");
      sTableName = strOpen.replace(rxTableFinder, "$1");
      
      sTableName = sTableName.replace(RegExp.leftContext, "");
      //--dbug(sTableName + " ");
    } /* else is a SELECT statement*/ 
           
    ooRecord.Open(strOpen); 
           
    if (!ooRecord.EOF) 
    {   
      //dbug(ooRecord("description")); 
      /*--*--*--*--*--*--*--*--*--*--*--*--*--*--*-- 
      PROVIDE DEFAULT VALUES 
       --*--*--*--*--*--*--*--*--*--*--*--*--*--*--*/     
      //***Default all fields to text boxes  
      //***   
      if (aaFieldInfo == null)       
      { 
        aaFieldInfo = new Array(); 
        for (var ii = 0; ii < ooRecord.Fields.Count; ii++) 
        { 
          aaFieldInfo[ii] = 2; 
        } /* for */ 
      } /* if */ 
      else 
      { 
        aaFieldInfo = fnConvertArray(aaFieldInfo); 
         //dbug(aaFieldInfo[0] + " "); 
        for (var ii = 0; ii < ooRecord.Fields.Count; ii++) 
        { 
          if (aaFieldInfo[ii] == null) 
            {aaFieldInfo[ii] = 1;} 
        } /* for */ 
      } /* if aaFieldInfo is not null */ 
      if (aaLabels == null) 
      { 
        aaFieldLabels[0] = "Record"; 
        for (var ii = 0; ii < ooRecord.Fields.Count; ii++) 
        { 
          aaFieldLabels[ii+1] = ooRecord.Fields.Item(ii).Name; 
        } /* for */     
      } 
      else 
      { 
         
          var aaTemp = new VBArray(aaLabels); 
          aaFieldLabels = aaTemp.toArray(); 
         /* try, catch */ 
         
        if (aaFieldLabels[0] == null) 
          {aaFieldLabels[0] = "Record";} 
           
        for (var i = 0; i < ooRecord.Fields.Count; i++) 
        { 
          if (aaFieldLabels[i+1] == null) 
          { 
            aaFieldLabels[i+1] = ooRecord.Fields.Item(i).Name; 
          } 
        } /* for */     
      } /* if */ 
       
      if (aaColourInfo == null) 
      { 
        aaColours[0] = "white"; 
        aaColours[1] = "white"; 
        aaColours[2] = "white"; 
        aaColours[3] = "black"; 
        aaColours[4] = "black"; 
      } 
      else 
      { 
         
          var aaTemp = new VBArray(aaColourInfo); 
          aaColours = aaTemp.toArray(); 
         /* try, catch */ 
         
        if (aaColours[0] == null)  //table colour 
          {aaColours[0] = "white";} 
        if (aaColours[1] == null)  //label cell colour 
          {aaColours[1] = "white";} 
        if (aaColours[2] == null)  //value cell colour 
          {aaColours[2] = "white";} 
        if (aaColours[3] == null)  //label font colour 
          {aaColours[3] = "black";} 
        if (aaColours[4] == null)  //value font colour 
          {aaColours[4] = "black";} 
      } /* if */ 
     
      sReturn =  
        "<table    name = tblDisplay\n" +  
        "       bgcolor = " + aaColours[0] + "\n" + 
        "        border = 1>\n"; 
                
      //***Display a confirmation msg          
      if (sMessage != "") 
      {          
        sReturn +=  
          "<tr>\n" +  
          "   <td colspan = 2 align = center>\n" +  
          "   <b><font size = +0><i>" +  
              sMessage + "</i></font></b></td>\n" +  
          "</tr>\n"; 
      } /* if update message */   
       
      if (sReturn != "ERROR:") 
      {   
          
        var sFieldName;    //***Supplies value for Name attrib 
        var sUpdateInfo;   //***Supplies value for the Hidden    
          //***try to parse the Table name from the 
          //***sTableFields parameter. 
        var regA = /.*\[([^\]]+)\].*/gi; 
        if (sTName != null) 
        { 
          sTableName = sTName; 
          //dbug(sTableName); 
        } 
        else 
        { 
          if (sTableName == "") 
          { 
            fnErrorMessage( 
              "fnSimpleUpdateRecord",  
              "strOpen ('" + strOpen + "')", 
              "The name of the Table could not be extracted from the <br>" + 
              "strOpen parameter or the sTName parameter<br>" + 
              "The Table name must be contained in one of these");           
          } /* if */   
        } /* if else */ 
         
        //dbug(aaFieldLabels[0]); 
        if (aaFieldLabels[0] == "Record") 
        { 
          aaFieldLabels[0] = "Record of '" + sTableName + "': "; 
        } 
         
        sReturn +=  
          "<tr>\n" +  
          "   <td colspan = 2 align = center>" +  
          "   <b><font size = +1>" +  
              aaFieldLabels[0] + "</font></b></td>\n" +  
          "</tr>\n"; 
        sUpdateInfo = ""; 
         
        var sPKconcat = new String("");     //***primary keys concatenated 
        var sPKValuesConcat = new String(""); //*** PK values concatenated 
        var sQQ = new String("");           //***Primary key query string  
        var adoRsPK = Server.CreateObject("ADODB.recordset"); 
         
        adoRsPK.ActiveConnection = adoConnection; 
        sQQ = "sp_pkeys @table_name = '" + sTableName + "' "; 
        adoRsPK.Open(sQQ); 
        //--dbug(sTableName + "is");
        //--fnDbugRecordset(adoRsPK); 
        if (adoRsPK.EOF) 
        { 
          fnErrorMessage( 
            "fnSimpleUpdateRecord", "n/a", 
            "The table contains no primary keys and therefore<br>" + 
            "cannot be reliably updated."); 
        } /* if no primary keys */ 
         
        //***check that all primary keys are in 
        //***the recordset and concatenated strings 
        //***for the hidden elements. 
        while (!adoRsPK.EOF) 
        { 
          bFoundPK = new Boolean(false); 
          sPKconcat += adoRsPK("column_name") + ";"; 
           
          for (var ii = 0; ii < ooRecord.Fields.Count; ii++) 
          { 
            if (fnLOWER(ooRecord.Fields.Item(ii).Name) ==  
                   fnLOWER(adoRsPK("column_name"))) 
            { 
              bFoundPK = true; 
              sPKValuesConcat += ooRecord.Fields.Item(ii).value + ";"; 
            } /* if */ 
          } /* for */ 
          if (bFoundPK == false) 
          { 
            fnErrorMessage( 
              "fnSimpleUpdateRecord", "strOpen", 
              "All the primary keys for the table must be<br>" + 
              "included in the SQL string"); 
          } /* if */ 
           
          adoRsPK.MoveNext(); 
        } /* while not eof */ 
         
        sPKconcat = sPKconcat.substring(0, sPKconcat.length -1); 
        sPKValuesConcat = sPKValuesConcat.substring(0, sPKValuesConcat.length -1); 
         
        adoRsPK = null; 
        for (var i = 0; i < ooRecord.Fields.Count; i++) 
        { 
          sFieldName = ooRecord.Fields.Item(i).Name; 
          sReturn += 
           "<tr>\n" + 
           "  <td bgcolor = " + aaColours[1] + ">\n" +  
           "  <font color = " + aaColours[3] + ">" 
                    + aaFieldLabels[i+1] + "</font>" +  
           "   </td>\n" +  
           "   <td bgcolor = " + aaColours[2] + ">\n"; 
 
          //--REM Handle the different types of input 
          //--REM box (invisible, readonly, text, password,  
          //--REM Date-picker            
          if (ooRecord.Fields.Item(i).Type == "201") 
          { 
            //--REM This function cannot handle blob fields
            //--REM at the moment.
            
            var iii = new Number(10);
            var ss = ooRecord.Fields(i).Name;
            //--var ss = ooRecord.Fields(i).GetChunk(10);
            //--dbug(" ll");
            sReturn +=  
             "   <input   type = 'text'\n" + 
             "            name = 'Input" + sFieldName + "'\n" + 
             "            size = '50' \n" +   
             "           value = '?????" + 
             //--Server.HTMLEncode(ooRecord.Fields(i).GetChunk(100) + " ") 
             + "'>\n"; 
            sUpdateInfo += sFieldName + ";";  

          } /* if field is a blob  */ 
          else if (ooRecord.Fields.Item(i).Attributes == "16") 
          { 
            sReturn +=  
             "   <font color = " + aaColours[4] + ">"  
             + Server.HTMLEncode(ooRecord.Fields(i).Value + " ") + 
             "</font>\n";           
          } /* if field is an identity  */ 
          else if (aaFieldInfo[i] == 1) 
          { 
            sReturn +=  
             "   <font color = " + aaColours[4] + ">"  
             + Server.HTMLEncode(ooRecord.Fields(i).Value + " ") + 
             "</font>\n";           
          } /* if, else field is readonly */ 
          else if (aaFieldInfo[i] == 2) 
          {  
            sReturn +=  
             "   <input   type = 'text'\n" + 
             "            name = 'Input" + sFieldName + "'\n" + 
             "            size = '50' \n" +   
             "           value = '" + 
             Server.HTMLEncode(ooRecord.Fields(i).Value + " ") 
             + "'>\n"; 
            sUpdateInfo += sFieldName + ";";  
            //--dbug(ooRecord.Fields(i).Value);
          }  
          else if (aaFieldInfo[i] == 3) 
          {  
            sReturn +=  
             "   <input   type = 'Password'\n" + 
             "            name = 'Input" + sFieldName + "'\n" +  
             "           value = '" + 
             Server.HTMLEncode(ooRecord.Fields(i).Value + " ") + "'>\n"; 
            sUpdateInfo += sFieldName + ";";  
          } /* if is password */ 
          else if (aaFieldInfo[i] == 4) 
          {  
            sReturn +=  
              fnDatePicker( 
                "Input" + sFieldName, 
                  ooRecord.Fields(i).Value, null, false); 
            sUpdateInfo += sFieldName + ";";  
          } /* if is DatePicker */ 
           
          sReturn +=  
           "   </td>\n" +  
           "</tr>\n"; 
          //--Response.Write(sFieldName); 
        } /* for each field */ 
        //dbug(" " + sTableName); 
         
           
        sReturn += 
          "<tr>\n" + 
          "   <td   colspan = '2' \n" +  
          "           align = 'center'> \n" + 
          "   <input   type = 'submit' \n" +  
          "           value = 'Save Changes'> \n" + 
          "</tr>\n";  
        sReturn += 
          "<tr>\n" + 
          "   <td   colspan = '2' \n" +  
          "           align = 'center'><i> \n" + 
          "    If you violate a database rule \n" +  
          "    an error will occur</i> \n" + 
          "   </td> \n" 
          "</tr> \n";  
        sReturn += "</table>"; 
 
        //***Display update error information          
        if (sErrorInformation != "") 
        {          
          sReturn +=  
            "   <font size = +0><i>" +  
                sErrorInformation + "</i></font>\n"; 
        } /* if error info */ 
         
        sUpdateInfo = sUpdateInfo.substring(0, sUpdateInfo.length - 1); 
        //dbug(sUpdateInfo); 
         
        sReturn += 
          "<input    type = 'hidden'\n" +  
          "          name = 'hidTableName'\n" + 
          "         value = '" + sTableName + "'>\n"; 
        sReturn += 
          "<input    type = 'hidden'\n" +  
          "          name = 'hidUpdateFields'\n" + 
          "         value = '" + sUpdateInfo + "'>\n"; 
        sReturn += 
          "<input    type = 'hidden'\n" +  
          "          name = 'hidPrimaryKeys'\n" + 
          "         value = '" + sPKconcat + "'>\n"; 
        sReturn += 
          "<input    type = 'hidden'\n" +  
          "          name = 'hidPrimaryKeyValues'\n" + 
          "         value = '" + sPKValuesConcat + "'>\n"; 
      } /* if no errors */ 
      //--dbug(Server.HTMLEncode(sReturn)); 
      return sReturn; 
    }  
    else 
    { 
      return "The open string of the fnSimpleUpdateRecord function\n" + 
             "did not return any records."; 
    }  /* if */ 
     
    ooRecord.Close(); 
    ooRecord = null;                       
    if (bCloseConnection == true) 
    { 
      adoConnection.Close(); 
      adoConnection = null; 
    } /* if */ 
    aaLabels = null; 
     
  } /* fnSimpleUpdateRecord */ 
   
                             
[/script]