<%@ LANGUAGE=VBSCRIPT %> 
<% RSDispatch %>
<!--#INCLUDE FILE="cgi-bin/rs.asp" -->
<SCRIPT RUNAT=SERVER LANGUAGE="JavaScript1.2">
// public_description contains a list of all server-side functions that
// are accessable to the client.  The ServerMethods constructor
// exposes these methods.  You must do this for every server-side method
// accessed directly by the client.
var public_description = new ServerMethods();
function ServerMethods() { 
    this.GetData = GetData;
    this.UpdateInfo = UpdateInfo;
}

// Build a SQL SELECT statement to retrieve the data requested by the client.
function GetData(intEmployeeID) {
    var sql = "SELECT EmployeeID, FirstName, LastName, Address, City, Region, PostalCode, Notes FROM Employees 
         WHERE EmployeeID = " + intEmployeeID;
    return DataAccess(sql);
}

// Build a SQL UPDATE statement to record the changes made by the client.
function UpdateInfo(intEmployeeID, strFirstName, strLastName, strAddress, strCity, strState, intZip, strNotes) {
    var sql = "UPDATE Employees SET FirstName='" + strFirstName + "', LastName='" + strLastName + "', Address='" + strAddress + "', City='" + strCity + "', Region='" + strState + "', PostalCode='" + intZip + "', Notes='" + strNotes + "' WHERE EmployeeID=" + intEmployeeID;
    return DataAccess(sql);
}

// This function takes a SQL statement as its input and returns an two-dimensional array of all data
// returned from the database, or a text message in the event of a non-SELECT SQL statement.
function DataAccess(sql) {
    // These two lines extract the first six characters of the inputted SQL statement, then 
    // convert it to upper case.
    // This is used later to branch the code in a 'switch' statement.
    var sqltype = sql.slice(0,6);
    sqltype.toUpperCase();
    // Standard ASP database connection code.
    var conn = Server.CreateObject("ADODB.Connection");
    var DBlocation = Server.MapPath("../../data/northwind.mdb");
    var strconn = "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=" + DBlocation + ";USER ID=;PASSWORD=;";
    conn.open(strconn);
    var oRS = Server.CreateObject("ADODB.Recordset");
    // Determine what type of SQL statement was inputted.
    switch(sqltype) {
        case 'SELECT':
            oRS.ActiveConnection = conn;
            oRS.Source = sql;
            oRS.Open;
            // This array will contain all returned data.
            var arrData = new Array();
            var row = 0;
            var dataval;
            // Get the number of column returned by this query.
            var col = oRS.Fields.Count;
            // For each row returned by the query, do this.
            while (oRS.EOF != true) {
                // For each row, create an array to contain the columns.
                arrData[row] = new Array(col);
                for (var fcount = 0; fcount < col; fcount++) {
                    // If the database field contains a binary file, replace it
                    // with the string "unknown".
                    if (typeof(oRS.Fields.Item(fcount).value) == 'unknown') {
                        dataval = 'unknown';
                    }
                    else {
                        // Convert all fields to strings.  This is necessary 
                        // for date fields.
                        dataval = String(oRS.Fields.Item(fcount).value);
                        // Get rid of all linefeeds, carriage returns and double quotes.
                        // Remote Scripting doesn't like them.
                        dataval = dataval.replace(/[\n]|[\r]|["]/g, "");
                    }
                    arrData[row][fcount] = dataval;
                }
                row++;
                oRS.MoveNext;
            }
            // Close the database connection and destroy all data objects.
            oRS.Close;
            oRS = null;
            conn = null;
            return arrData;
        break;
        default: // If the inputted SQL was not a "SELECT" statement.
            conn.Execute = sql;
            conn = null;
            return sqltype + ' Successful';
        break;
    }
}
</script>