Can any of you SQL programmers out there convert this so it can be used with Access?
Apparently this won't display properly unless it's downloaded - so here's the file itself:
<%@ LANGUAGE = "JScript" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML>
<HEAD>
<TITLE>Test Appointment</TITLE>
</HEAD>
<BODY>
<%
// this page is used to exercise JAL Appointment dll, this page is reentrant
var objAppt= Server.CreateObject("JALAppt.Appointment");
// first we get any values passed to the page, inserting default values if they are undefined
var lYear= GetInt(Request,"year",2001);
var lMonth= GetInt(Request,"month",3);
var lDay= GetInt(Request,"day",1);
var lHour= GetInt(Request,"hour",8);
var lMinute= GetInt(Request,"minute",0);
var lDefaultClient= GetInt(Request,"default_client",2);
var strConcurrencyLevel= GetString(Request,"concurrency","ALL");
var strAction= GetString(Request,"action","appt");
var strIDV= GetString(Request,"IDV","NULL");
// Declare and initialize user variables
var strDate= lMonth+"/";
if (lDay<10) {
strDate+= "0";
}
strDate+= lDay+"/"+lYear;
var strSQL= "Select DATEPART(hour,A.date_time) AS 'Hour', DATEPART(minute, A.date_time) AS 'Minute', A.id AS 'Edit', A.id AS 'Delete', A.id AS 'Appt. #', C.last_name+', '+C.first_name+ ' '+ C.middle_initial AS 'Client', A.date_time AS 'Time', A.service_type AS 'Type', A.status AS 'Status', A.is_confirmed AS 'Confirmed', A.comment AS 'Comment', A.is_overbooked AS 'OB',A.is_charged AS '$', A.provider AS 'Provider', A.location AS 'Loc.' From Appointments AS A, Clients AS C, Providers AS P WHERE A.client = C.id AND A.provider = P.abbreviation AND DATEDIFF(day,'"+strDate+"', A.date_time) =0 ORDER BY Time";
var strSQLDelete= "Select A.id AS 'Appt. #', C.last_name+', '+C.first_name+ ' '+ C.middle_initial AS 'Client', A.date_time AS 'Time', A.service_type AS 'Type', A.status AS 'Status', A.is_confirmed AS 'Confirmed', A.comment AS 'Comment', A.is_overbooked AS 'OB',A.is_charged AS '$', A.provider AS 'Provider', A.location AS 'Loc.' From Appointments AS A, Clients AS C, Providers AS P WHERE A.client = C.id AND A.provider = P.abbreviation AND A.id="+strIDV;
var strSQLEdit= "SELECT * FROM Appointments WHERE id="+strIDV; // DO NOT! change column names
var strDateTime= "'"+lMonth+"/";
if (lDay<10) {
strDateTime+= "0";
}
strDateTime+= lDay+"/"+lYear;
strDateTime+= " "+lHour+":"
if (lMinute<10) {
strDateTime+= "0";
}
strDateTime+= lMinute+"'";
var strConn= "Driver=(SQL Server);Data Source=localserver;Database=Appointments";
var strID= "sa";
var strPW= "";
var strTableTags= "<TABLE><CAPTION ALIGN=CENTER><B>AM</BOLD></CAPTION>";
var strHeadingRowTags= "<TR ALIGN=CENTER BGCOLOR=\"#E0E0E0\">";
var strDataRowTagsEven= "<TR BGCOLOR=\"#C6EFF7\">";
var strDataRowTagsOdd= "<TR BGCOLOR=\"#FFFF9C6\">"
var strFooterRowTags= "<TR ALIGN=CENTER BGCOLOR=\"#E0E0E0\">";
var strNullChars= "*";
var strTrueChars= "Yes";
var strFalseChars= "No";
var strNewApptLink= "./TestAppt.asp?concurrency="+strConcurrencyLevel+"&Action=new&";
var strEditApptLink= "./TestAppt.asp?concurrency="+strConcurrencyLevel+"&Action=edit&year="+lYear+"&Month="+lMonth+"&day="+lDay+"&IDV=";
var strDeleteApptLink= "./TestAppt.asp?concurrency="+strConcurrencyLevel+"&Action=delete&year="+lYear+"&Month="+lMonth+"&day="+lDay+"&IDV=";
var strURLForm= "./TestAppt.asp?concurrency="+strConcurrencyLevel+"&Action=appt&year="+lYear+"&Month="+lMonth+"&day="+lDay;
var strFalseChars= "No";
var strTrueChars= "Yes";
var strPKey= "id";
var strTableName= "Appointments";
var lTextBoxSize= 50;
// Set object properties
objAppt.strConn= strConn;
objAppt.strID= strID
objAppt.strPW= strPW;
objAppt.lYear= lYear;
objAppt.lMonth= lMonth;
objAppt.lDay= lDay;
objAppt.strNullChars= strNullChars;
objAppt.strTrueChars= strTrueChars;
objAppt.strFalseChars= strFalseChars;
objAppt.strTableTags= strTableTags;
objAppt.strHeadingRowTags= strHeadingRowTags;
objAppt.strDataRowTagsOdd= strDataRowTagsOdd;
objAppt.strDataRowTagsEven= strDataRowTagsEven;
objAppt.strNewApptLink= strNewApptLink;
objAppt.strEditApptLink= strEditApptLink;
objAppt.strDeleteApptLink= strDeleteApptLink;
objAppt.strConcurrencyLevel= strConcurrencyLevel;
objAppt.lTextBoxSize= lTextBoxSize;
if (objAppt.Connect()) {
DoAction(strAction);
}
else {
Response.write(objAppt.GetLog());
}
// Debug
//Response.write("<BR>"+objAppt.GetLog());
//Response.write("<BR>"+objAppt.strSQL+"<BR>");
Response.write("<BR>"+objAppt.GetVersion());
// Clean up
objAppt.Disconnect()
objAppt= null;
%>
<BR>
All Rights Reserved 2001 J. Louie <BR>
Revised -- 10.14.2001 <BR>
</BODY>
</HTML>
<SCRIPT RUNAT=server LANGUAGE= JScript>
function DoAction(inAction) {
// switch on action
if (inAction.toLowerCase() == "new") {
New();
DisplayAppt();
}
else if (inAction.toLowerCase() == "edit") {
Edit();
}
else if (inAction.toLowerCase() == "delete") {
Delete();
//DisplayAppt();
}
else if (inAction.toLowerCase() == "delete_confirmed") {
DeleteConfirmed();
DisplayAppt();
}
else if (inAction.toLowerCase() == "appt") {
DisplayAppt();
}
else if (inAction.toLowerCase() == "save") {
Save();
DisplayAppt();
}
else if (inAction.toLowerCase() == "cancel_delete") {
CancelDelete();
DisplayAppt();
}
else {
Response.write("<BR>Logic Error");
DisplayAppt();
}
}
function CancelDelete() {
Response.write("Delete Canceled.");
}
function DisplayAppt() {
objAppt.strSQL= strSQL;
objAppt.lFirstSlotHour= 8;
objAppt.lLastSlotHour= 11;
objAppt.lSlotMinutes= 15;
Calendar();
// Call object methods
if (objAppt.Update()) {Response.write(objAppt.GetGrid());}
else {Response.write(objAppt.GetLog());}
objAppt.lFirstSlotHour= 12;
objAppt.lLastSlotHour= 16;
objAppt.strTableTags= "<TABLE><CAPTION ALIGN=CENTER><B>PM</BOLD></CAPTION>";
if (objAppt.Update()) {Response.write(objAppt.GetGrid());}
else {Response.write(objAppt.GetLog());}
}
function Delete() {
objAppt.strSQL= strSQLDelete;
objAppt.strTableTags= "<TABLE><CAPTION ALIGN=CENTER><FONT COLOR=\"RED\"><B>REALLY? DELETE?</B></FONT></CAPTION>";
if (!objAppt.Update()) {
Response.write("<BR>"+objAppt.strSQL);
Response.write("<BR>"+objAppt.GetLog());
}
else {
Response.write("<FORM METHOD=\"POST\" ACTION=\"./TestAppt.asp\">");
Response.write(objAppt.GetRecord());
Response.Write("DELETE<INPUT NAME=\"Action\" VALUE=\"delete_confirmed\" TYPE=\"radio\">");
Response.Write("CANCEL<INPUT TYPE=\"radio\" NAME=\"Action\" VALUE=\"cancel_delete\" CHECKED>");
Response.Write("<INPUT TYPE=\"submit\"</FORM>"); }
//Response.write("<FONT COLOR=RED><B>REALLY? DELETE Appointment #: "+strIDV+"?</FONT></B>");
//Response.write("<BR><A HREF=\"./TestAppt.asp?concurrency="+strConcurrencyLevel+"&year="+lYear+"&Month="+lMonth+"&day="+lDay+
//"&Action=delete_confirmed&IDV="+strIDV+"\">Delete</A>");
//Response.write(" <A HREF=\"./TestAppt.asp?concurrency="+strConcurrencyLevel+"&year="+lYear+"&Month="+lMonth+"&day="+lDay+
//"&Action=appt\">Cancel</A>")
}
function DeleteConfirmed() {
objAppt.strSQL= "DELETE FROM Appointments WHERE id="+strIDV;
if (!objAppt.UpdateNoRecords()) {
Response.write("<BR>Deletion Failed.");
Response.write("<BR>"+objAppt.strSQl);
Response.write("<BR>"+objAppt.GetLog());
}
else {
Response.write("<BR>Appointment #: "+strIDV+" Deleted.");
}
}
function New() {
objAppt.strSQL= "INSERT INTO Appointments (client,provider,location,date_time) "+
"VALUES ("+lDefaultClient+",'MD','Clinic',"+strDateTime+")";
if (!objAppt.UpdateNoRecords()) {
Response.write("<BR>Failed to create new appointment.");
Response.write("<BR>"+objAppt.strSQl);
Response.write("<BR>"+objAppt.GetLog());
}
else {
Response.write("<BR>Edit New Appointment.");
}
}
function Edit() {
objAppt.strSQL= strSQLEdit;
if (!objAppt.Update()) {
Response.write("<BR>"+objAppt.strSQL);
Response.write("<BR>"+objAppt.GetLog());
}
else {
Response.write("<FORM METHOD=\"POST\" ACTION=\"./TestAppt.asp\">");
Response.write(objAppt.GetForm());
Response.write(objAppt.GetHiddenRecord());
Response.write("</FORM>");
}
}
function Save() {
var nIndex= 1;
var nSize= Request.Form.Count;
var strOldKey="";
var strNewKey="";
var strOldValue="";
var strNewValue="";
var strTypeKey="";
var strTypeValue="";
var nLength;
var strWhere= " WHERE "+strPKey+" = ";
var strDirty="";
var strAll="";
var strUpdate= "UPDATE "+strTableName+" SET ";
var isDirtyAny= false;
var isDirty= false;
var isStringLiteral= false;
var strTemp="";
var isIgnoreADO128= true;
var isEnableAccess135= false;
var strConcurrency= "ALL";
var strExtendedSQL= "";
try {
for (i=1;i<=nSize;i++) {
strOldKey= Request.Form.Key(i); // may throw exception
nLength= strOldKey.length;
if (strOldKey.substring(0,13)== "hidden_value_") {
strNewKey= strOldKey.substring(13);
strTypeKey= "hidden_type_"+strNewKey;
strOldValue= Request.Form.Item(strOldKey)+"";
strNewValue= Request.Form.Item(strNewKey)+"";
strTypeValue= Request.Form.Item(strTypeKey)+"";
if (!(isIgnoreADO128 && strTypeValue == 128)) { // skip on timestamp
isDirty= (strNewValue != strOldValue);
isStringLiteral= IsLiteral(strTypeValue,strOldValue);
if (strNewKey == strPKey) { //PKey cannot be NULL, strConcurrency= "UNIQUE"
if (isStringLiteral) {
strWhere+= "'";
strWhere+= PrepString(strOldValue);
strWhere+= "'";
}
else {
strWhere+= strOldValue;
}
}
else {
if (isDirty) { // strConcurrency= "DIRTY"
if (isDirtyAny) {
strUpdate+= ", ";
}
isDirtyAny= true;
strUpdate+= strNewKey;
strUpdate+= "=";
strDirty+= " AND "+strNewKey;
// process new value
if (strNewValue == strNullChars || (!isStringLiteral && strNewValue.length == 0)){
isStringLiteral= false;
strTemp= "NULL";
}
else if (strTypeValue == 11) { //boolean
if (strNewValue.toLowerCase()== strTrueChars.toLowerCase() || strNewValue.toLowerCase() == "true" ) {
strTemp= "1";
}
else if (strNewValue.toLowerCase()== strFalseChars.toLowerCase() || strNewValue.toLowerCase() == "false" ){
strTemp= "0";
}
}
else {
strTemp= strNewValue;
isStringLiteral= IsLiteral(strTypeValue,strNewValue);
}
if (isStringLiteral) {
strUpdate+= "'";
strUpdate+= PrepString(strTemp);
strUpdate+= "'";
}
else {
strUpdate+= strTemp;
}
// process old value
isStringLiteral= IsLiteral(strTypeValue,strNewValue); // bug fix 8.29.01
if (strOldValue == strNullChars) {
isStringLiteral= false;
strTemp= " IS NULL ";
}
else if (strTypeValue == 11) { //boolean
if (strOldValue.toLowerCase()== strTrueChars.toLowerCase() || strOldValue.toLowerCase() == "true" ) {
strDirty+="=";
strTemp= "1";
}
else if (strOldValue.toLowerCase()== strFalseChars.toLowerCase() || strOldValue.toLowerCase() == "false" ){
strDirty+="=";
strTemp= "0";
}
}
else if (isEnableAccess135 && strTypeValue == 135) { // Access DateTime
isStringLiteral= false;
strDirty+= "=";
strTemp= "#";
strTemp+= strOldValue;
strTemp+= "#";
}
else {
strDirty+= "=";
strTemp= strOldValue;
}
if (isStringLiteral) {
strDirty+="'";
strDirty+= PrepString(strTemp);
strDirty+="'";
}
else {
strDirty+= strTemp;
}
}
else { // strConcurrency= "ALL" , process unmodified fields
strAll+= " AND "+strNewKey;
if (strOldValue == strNullChars) {
isStringLiteral= false;
strTemp= " IS NULL ";
}
else if (strTypeValue == 11) { //boolean
if (strOldValue.toLowerCase()== strTrueChars.toLowerCase() || strOldValue.toLowerCase() == "true" ) {
strAll+="=";
strTemp= "1";
}
else if (strOldValue.toLowerCase()== strFalseChars.toLowerCase() || strOldValue.toLowerCase() == "false" ){
strAll+="=";
strTemp= "0";
}
}
else if (isEnableAccess135 && strTypeValue == 135) { // Access DateTime
isStringLiteral= false;
strAll+= "=";
strTemp= "#";
strTemp+= strNewValue;
strTemp+= "#";
}
else {
strAll+= "=";
strTemp= strNewValue;
}
if (isStringLiteral) {
strAll+= "'";
strAll+= PrepString(strTemp);
strAll+= "'";
}
else {
strAll+= strTemp;
}
}
}
}
}
}
}
catch(Exception) {
Response.write("<BR>Exception Thrown in Update.");
Response.write(objAppt.strSQL);
}
if (isDirtyAny) {
if (strConcurrency.toLowerCase() =="unique") {
strExtendedSQL= strUpdate+strWhere;
}
else if (strConcurrency.toLowerCase() =="dirty") {
strExtendedSQL= strUpdate+strWhere+strDirty;
}
else { // default ALL
strExtendedSQL= strUpdate+strWhere+strDirty+strAll;
}
objAppt.strSQL= strExtendedSQL;
if (objAppt.UpdateNoRecords()) {
Response.write("<BR>Update Succeeded. ");
Response.write(objAppt.GetAffected()+" Record(s) Updated.");
}
else {
Response.write("<BR>Update Failed.");
Response.write("<BR>"+objAppt.GetConnError());
Response.write("<BR>"+objAppt.strSQL+"<BR>");
}
}
} // end_Save
// returns false on non_literal data types and escape sequences
function IsLiteral(ADO_Type,inString){
var isEnableEscape= false;
if (isEnableEscape && inString.length>0 && inString.charAt(0) == "{") {
return false;
}
//var arrayStringLiterals= new Array(8,129,133,201,203,200,202,130,134,135);
//var bValue= false;
//for (var i=0; i<arrayStringLiterals.length; i++) {
//if (ADO_Type == arrayStringLiterals[i]) {
//bValue= true;
//}
//}
ADO_Type+= "";
switch(ADO_Type) { // optimized 8.11.01
case "8": // falls through
case "129":
case "133":
case "201":
case "203":
case "200":
case "202":
case "130":
case "134":
case "135":
bValue= true;
break;
default:
bValue= false;
break;
}
return bValue;
}
// workaround for single quotes
// automatically doubles single quotes
// do NOT call on nonliteral values such as escape sequences
function PrepString(inString) {
var isDoubleSingleQuotes= true;
if (isDoubleSingleQuotes) {
var outString= "" ;
for (var i=0; i<inString.length; i++) {
var temp= inString.charAt(i);
if (temp =="'") {
outString+= "''";
}
else {
outString+= temp;
}
}
return outString;
}
else {
return inString;
}
}
function Calendar() {
Response.write("<BR><FORM METHOD=GET ACTION=\""+strURLForm+"\">");
Response.write("<INPUT TYPE=\"hidden\" NAME=\"concurrency\" VALUE=\""+strConcurrencyLevel+"\">");
Response.write("<SELECT NAME=Month SIZE=1>");
for (i=0;i<12;i++) {
var nTemp=i+1;
if (nTemp==lMonth) {
Response.write("<OPTION SELECTED VALUE="+nTemp+">");
}
else {
Response.write("<OPTION VALUE="+nTemp+">");
}
switch(nTemp){
case 1:
Response.write("January");
break;
case 2:
Response.write("February");
break;
case 3:
Response.write("March");
break;
case 4:
Response.write("April");
break;
case 5:
Response.write("May");
break;
case 6:
Response.write("June");
break;
case 7:
Response.write("July");
break;
case 8:
Response.write("August");
break;
case 9:
Response.write("September");
break;
case 10:
Response.write("October");
break;
case 11:
Response.write("November");
break;
case 12:
Response.write("December");
break;
}
}
Response.write("</SELECT>");
Response.write("<SELECT NAME=Day SIZE=1>");
for (i=1;i<32;i++) {
if (lDay == i) {
Response.write("<OPTION SELECTED VALUE="+i+">"+i);
}
else {
Response.write("<OPTION VALUE="+i+">"+i);
}
}
Response.write("</SELECT><SELECT NAME=Year SIZE=1>");
for (i=2000;i<2101;i++) {
if (lYear == i) {
Response.write("<OPTION SELECTED VALUE="+i+">"+i);
}
else {
Response.write("<OPTION VALUE="+i+">"+i);
}
}
Response.write("</SELECT><INPUT TYPE=SUBMIT NAME=SUBMIT VALUE=Go></FORM>");
}
// function that checks for null or "undefined" string, an empty string is accepted
function IsValid(inString) {
var isValid
if ((inString == null) || inString.toLowerCase() == "undefined") {
isValid= false
} else {isValid= true}
return isValid
}
// function that returns non-null or default value, empty string is accepted
function GetString(request,fieldName,defaultString) {
var outString= request(fieldName)+""
if (! IsValid(outString)) {
outString= defaultString
}
return outString
}
// function that returns valid integer value or default value
function GetInt(request,fieldName, defaultInt) {
var tempStr= GetString(request,fieldName, defaultInt)
var nValue
try {
nValue= Math.round(tempStr)
}
catch(e) {
nValue= defaultInt
}
return nValue
}
// function that checks for true or false boolean values
function GetBool(request,fieldName,defaultBool) {
var strOut=request(fieldName)+"";
var bOut= defaultBool;
if (strOut.toLowerCase() == "true") {bOut = true;}
if (strOut.toLowerCase() == "false") {bOut = false;}
return bOut;
}
</SCRIPT>
Topic Locked
Posted - 08 December 2002 : 19:12:11
