[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]