asked on

Trouble With Three Dependent Drop Down Boxes - Using ASP, Javascript, AJAX - Follow On

Hi,
This is a follow up on question: https://www.experts-exchange.com/questions/23980060/Trouble-With-Three-Dependent-Drop-Down-Boxes-Using-ASP-Javascript-AJAX.html

I have kindly been helped (by b0lsc0tt ) in putting together some ajax drop down select box code, but Im having a few issues that Im completly stuck on.

Currently what is happening here is the first drop down box populates it's self, but when making a selection it rewrites the new value back into it's self, and then has the second selection populated. Then when the second box has a selection made to it it writes out the answer underneath.

1. What I'd like is to have thee select boxes from the start.
2. The first select (Venue) populates as normal, but when a selection is made it populates the second box (Rank) with the available choices, and keeps the users value.
3. The second select (Rank) then populates the third box (Players Name), again keeping the users value.
4. The third select (Players Name) then displays the name, instead of being written out.

I've added below the four files required
dbfunredux_ajax.htm
dbfunredux_ajax_lookup.asp
json2.js
sample-rank.mdb

If any further info is required please ask

Thanks in advance

***********  start of dbfunredux_ajax.htm *************
 
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
	"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
 
<html>
<head>
<link href="style.css" rel="stylesheet" type="text/css" />
<title>DBFun w/ Ajax</title>
<script type="text/javascript" src="json2.js"></script>
<script type="text/javascript">
<!--
var xmlHttp;
var rootpath;
 
function getContents(sel) {
	if (sel.name == "venue") {
		str = "venue=" + sel.value;
	} else if (sel.name == "rank") {
		str = "venue=" + document.form1.venue.value + "&rank=" + sel.value;
	} else {
		str = "";
	}
	var url = "dbfunredux_ajax_lookup.asp?" + str;
	xmlHttp = GetXmlHttpObject(stateChanged);
	xmlHttp.open("GET", url , true);
	xmlHttp.send(null);
} 
function stateChanged() {
	if (xmlHttp.readyState==4 || xmlHttp.readyState=="complete") {
//		document.getElementById("result").innerHTML = xmlHttp.responseText;
		var oResponse = JSON.parse(xmlHttp.responseText);
		if (oResponse.venue) {
			for (var i=document.form1.venue.length - 1; i>0; i--) {
				document.form1.venue.remove(i);
			}
			for (var i=0; i<oResponse.venue.length; i++) {
				var optNew = document.createElement("option");
				optNew.text = oResponse.venue[i].name;
				optNew.value = oResponse.venue[i].value;
				try {
					document.form1.venue.add(optNew, null);
				} catch(e) {
					document.form1.venue.add(optNew);
				}
				if (oResponse.venue.length == 1) document.form1.venue.selectedIndex = 1;
			}
		}
		if (oResponse.rank) {
			for (var i=document.form1.rank.length - 1; i>0; i--) {
				document.form1.rank.remove(i);
			}
			for (var i=0; i<oResponse.rank.length; i++) {
				var optNew = document.createElement("option");
				optNew.text = oResponse.rank[i].name;
				optNew.value = oResponse.rank[i].value;
				try {
					document.form1.rank.add(optNew, null);
				} catch(e) {
					document.form1.rank.add(optNew);
				}
			}
			if (oResponse.rank.length == 1) document.form1.rank.selectedIndex = 1;
			document.form1.rank.disabled = (!document.form1.rank.length > 1);
		}
		if (oResponse.player) {
			document.getElementById("result").innerHTML = oResponse.player;
		}
	}
}
function GetXmlHttpObject(handler) {
	var objXmlHttp=null
	if (navigator.userAgent.indexOf("Opera")>=0) {
		alert("Opera not supported...");
		return;
	}
	if (navigator.userAgent.indexOf("MSIE")>=0) {
		var strName="Msxml2.XMLHTTP"
		if (navigator.appVersion.indexOf("MSIE 5.5")>=0) {
			strName="Microsoft.XMLHTTP"
		}
		try {
			objXmlHttp=new ActiveXObject(strName)
			objXmlHttp.onreadystatechange=handler
			return objXmlHttp
		}
		catch(e) {
			alert("Error. Scripting for ActiveX might be disabled")
			return
		}
	}
	if (navigator.userAgent.indexOf("Mozilla")>=0) {
		objXmlHttp=new XMLHttpRequest()
		objXmlHttp.onload=handler
		objXmlHttp.onerror=handler
		return objXmlHttp
	}
}
//-->
</script>
</head>
 
<body onload="getContents(document.form1.venue);">
<h3>Let's have some fun w/ AJAX and the Tournaments</h3>
 
<br /><br />
<form method="post" action="" name="form1">
Venue: <select name="venue" onchange="getContents(this);">
<option value="">Please select one</option>
</select>
<br /><br />
Rank: <select name="rank" onchange="getContents(this);" disabled="disabled">
<option value="">Please select one</option>
</select>
<br /><br />
<input type="reset" name="reset" value="Push Me (reset)" onclick="document.location = 'dbfunredux_ajax.htm'" />
</form>
<br /><br />
<div id="result"></div>
 
</body>
</html>
 
***********  end of dbfunredux_ajax.htm *************
 
*********** start of dbfunredux_ajax_lookup.asp ****************
 
<% Option Explicit %>
<%
' **********************************
' AJAX Player - LOOK UP part
'    This page will fill in order of venue, rank, and then playerName
' **********************************
' DB named sample_rank.mdb in this folder
' File to try to list those that need to be removed from a tournament if too many enrolled
'  Tables are : rank & tournament
'            Fields in Rank are :  ID, PlayerID, PlayerName, PlayerRank
'      Fields in Tournament are : ID, TournamentID, TournamentName, Year, PlayerID
'          ' ID field in each table is just primary key, PlayerID is relation
 
Dim nVenue, nRank, sPlayer, sSQL, nSQL
If Request("venue") <> "" then
	nVenue = Request("venue")
	If Request("rank") <> "" then
		nRank = Request("rank")
		nSQL = 3
	Else
		nSQL = 2
	End if
Else
	nSQL = 1
End if
 
Select Case nSQL
	Case 1
		' show venues
		sSQL = "SELECT DISTINCT TournamentName, TournamentID FROM Tournament WHERE Year = 2006"
	Case 2
		' sql for venue only find rank of players in that venue
		sSQL = "SELECT Rank.PlayerRank FROM Rank INNER JOIN Tournament ON Rank.PlayerID = Tournament.PlayerID WHERE Tournament.Year = 2006 AND Tournament.TournamentID = " & nVenue & " ORDER BY Rank.PlayerRank"
	Case 3
		' sql for player info with rank and venue selected
		sSQL = "SELECT Rank.PlayerName FROM Rank INNER JOIN Tournament ON Rank.PlayerID = Tournament.PlayerID WHERE Tournament.Year = 2006 AND Tournament.TournamentID = " & nVenue & " AND Rank.PlayerRank = " & nRank
End select
 
If sSQL <> "" then
	Dim objConn, objRS
	Dim strPath, aFields, item
	strPath = Server.MapPath("sample_rank.mdb")
	Set objConn = Server.CreateObject("ADODB.Connection")
	Set objRS = Server.CreateObject("ADODB.Recordset")
	objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strPath
	objRS.Open sSQL, objConn, 3
	aFields = objRS.GetRows
	Dim sResponse, i
	Select Case nSQL
		Case 1
			sResponse = "{""venue"" : ["
			For i=0 to ubound(aFields,2)
				sResponse = sResponse & "{ ""name"" : """ & aFields(0,i) & """,""value"" : """ & aFields(1,i) & """ },"
			Next
			sResponse = Left(sResponse, Len(sResponse) - 1)
			sResponse = sResponse & "] }"
			Response.Write sResponse
		Case 2
			sResponse = "{""venue"" : ["
			sResponse = sResponse & "{ ""name"" : """ & nVenue & """,""value"" : """ & nVenue & """ }"
			sResponse = sResponse & "],"
			sResponse = sResponse & " ""rank"" : ["
			For each item in aFields
				sResponse = sResponse & "{ ""name"" : """ & item & """,""value"" : """ & item & """ },"
			Next
			sResponse = Left(sResponse, Len(sResponse) - 1)
			sResponse = sResponse & "] }"
			Response.Write sResponse
		Case 3
			sResponse = "{""venue"" : ["
			sResponse = sResponse & "{ ""name"" : """ & nVenue & """,""value"" : """ & nVenue & """ }"
			sResponse = sResponse & "],"
			sResponse = sResponse & " ""rank"" : ["
			sResponse = sResponse & "{ ""name"" : """ & nRank & """,""value"" : """ & nRank & """ }"
			sResponse = sResponse & "],"
			sResponse = sResponse & " ""player"" : """
			For each item in aFields
				sResponse = sResponse & "The player's name is " & item
			Next
			sResponse = sResponse & """ }"
			Response.Write sResponse
	End select
	objRS.Close
	Set objRS = Nothing
	objConn.Close
	Set objConn = Nothing
End if
%>
 
*********** end of dbfunredux_ajax_lookup.asp ****************
 
***********  start of json2.js  *************
 
 
/*
    http://www.JSON.org/json2.js
    2008-11-19
 
    Public Domain.
 
    NO WARRANTY EXPRESSED OR IMPLIED. USE AT YOUR OWN RISK.
 
    See http://www.JSON.org/js.html
 
    This file creates a global JSON object containing two methods: stringify
    and parse.
 
        JSON.stringify(value, replacer, space)
            value       any JavaScript value, usually an object or array.
 
            replacer    an optional parameter that determines how object
                        values are stringified for objects. It can be a
                        function or an array of strings.
 
            space       an optional parameter that specifies the indentation
                        of nested structures. If it is omitted, the text will
                        be packed without extra whitespace. If it is a number,
                        it will specify the number of spaces to indent at each
                        level. If it is a string (such as '\t' or '&nbsp;'),
                        it contains the characters used to indent at each level.
 
            This method produces a JSON text from a JavaScript value.
 
            When an object value is found, if the object contains a toJSON
            method, its toJSON method will be called and the result will be
            stringified. A toJSON method does not serialize: it returns the
            value represented by the name/value pair that should be serialized,
            or undefined if nothing should be serialized. The toJSON method
            will be passed the key associated with the value, and this will be
            bound to the object holding the key.
 
            For example, this would serialize Dates as ISO strings.
 
                Date.prototype.toJSON = function (key) {
                    function f(n) {
                        // Format integers to have at least two digits.
                        return n < 10 ? '0' + n : n;
                    }
 
                    return this.getUTCFullYear()   + '-' +
                         f(this.getUTCMonth() + 1) + '-' +
                         f(this.getUTCDate())      + 'T' +
                         f(this.getUTCHours())     + ':' +
                         f(this.getUTCMinutes())   + ':' +
                         f(this.getUTCSeconds())   + 'Z';
                };
 
            You can provide an optional replacer method. It will be passed the
            key and value of each member, with this bound to the containing
            object. The value that is returned from your method will be
            serialized. If your method returns undefined, then the member will
            be excluded from the serialization.
 
            If the replacer parameter is an array of strings, then it will be
            used to select the members to be serialized. It filters the results
            such that only members with keys listed in the replacer array are
            stringified.
 
            Values that do not have JSON representations, such as undefined or
            functions, will not be serialized. Such values in objects will be
            dropped; in arrays they will be replaced with null. You can use
            a replacer function to replace those with JSON values.
            JSON.stringify(undefined) returns undefined.
 
            The optional space parameter produces a stringification of the
            value that is filled with line breaks and indentation to make it
            easier to read.
 
            If the space parameter is a non-empty string, then that string will
            be used for indentation. If the space parameter is a number, then
            the indentation will be that many spaces.
 
            Example:
 
            text = JSON.stringify(['e', {pluribus: 'unum'}]);
            // text is '["e",{"pluribus":"unum"}]'
 
 
            text = JSON.stringify(['e', {pluribus: 'unum'}], null, '\t');
            // text is '[\n\t"e",\n\t{\n\t\t"pluribus": "unum"\n\t}\n]'
 
            text = JSON.stringify([new Date()], function (key, value) {
                return this[key] instanceof Date ?
                    'Date(' + this[key] + ')' : value;
            });
            // text is '["Date(---current time---)"]'
 
 
        JSON.parse(text, reviver)
            This method parses a JSON text to produce an object or array.
            It can throw a SyntaxError exception.
 
            The optional reviver parameter is a function that can filter and
            transform the results. It receives each of the keys and values,
            and its return value is used instead of the original value.
            If it returns what it received, then the structure is not modified.
            If it returns undefined then the member is deleted.
 
            Example:
 
            // Parse the text. Values that look like ISO date strings will
            // be converted to Date objects.
 
            myData = JSON.parse(text, function (key, value) {
                var a;
                if (typeof value === 'string') {
                    a =
/^(\d{4})-(\d{2})-(\d{2})T(\d{2}):(\d{2}):(\d{2}(?:\.\d*)?)Z$/.exec(value);
                    if (a) {
                        return new Date(Date.UTC(+a[1], +a[2] - 1, +a[3], +a[4],
                            +a[5], +a[6]));
                    }
                }
                return value;
            });
 
            myData = JSON.parse('["Date(09/09/2001)"]', function (key, value) {
                var d;
                if (typeof value === 'string' &&
                        value.slice(0, 5) === 'Date(' &&
                        value.slice(-1) === ')') {
                    d = new Date(value.slice(5, -1));
                    if (d) {
                        return d;
                    }
                }
                return value;
            });
 
 
    This is a reference implementation. You are free to copy, modify, or
    redistribute.
 
    This code should be minified before deployment.
    See http://javascript.crockford.com/jsmin.html
 
    USE YOUR OWN COPY. IT IS EXTREMELY UNWISE TO LOAD CODE FROM SERVERS YOU DO
    NOT CONTROL.
*/
 
/*jslint evil: true */
 
/*global JSON */
 
/*members "", "\b", "\t", "\n", "\f", "\r", "\"", JSON, "\\", apply,
    call, charCodeAt, getUTCDate, getUTCFullYear, getUTCHours,
    getUTCMinutes, getUTCMonth, getUTCSeconds, hasOwnProperty, join,
    lastIndex, length, parse, prototype, push, replace, slice, stringify,
    test, toJSON, toString, valueOf
*/
 
// Create a JSON object only if one does not already exist. We create the
// methods in a closure to avoid creating global variables.
 
if (!this.JSON) {
    JSON = {};
}
(function () {
 
    function f(n) {
        // Format integers to have at least two digits.
        return n < 10 ? '0' + n : n;
    }
 
    if (typeof Date.prototype.toJSON !== 'function') {
 
        Date.prototype.toJSON = function (key) {
 
            return this.getUTCFullYear()   + '-' +
                 f(this.getUTCMonth() + 1) + '-' +
                 f(this.getUTCDate())      + 'T' +
                 f(this.getUTCHours())     + ':' +
                 f(this.getUTCMinutes())   + ':' +
                 f(this.getUTCSeconds())   + 'Z';
        };
 
        String.prototype.toJSON =
        Number.prototype.toJSON =
        Boolean.prototype.toJSON = function (key) {
            return this.valueOf();
        };
    }
 
    var cx = /[\u0000\u00ad\u0600-\u0604\u070f\u17b4\u17b5\u200c-\u200f\u2028-\u202f\u2060-\u206f\ufeff\ufff0-\uffff]/g,
        escapable = /[\\\"\x00-\x1f\x7f-\x9f\u00ad\u0600-\u0604\u070f\u17b4\u17b5\u200c-\u200f\u2028-\u202f\u2060-\u206f\ufeff\ufff0-\uffff]/g,
        gap,
        indent,
        meta = {    // table of character substitutions
            '\b': '\\b',
            '\t': '\\t',
            '\n': '\\n',
            '\f': '\\f',
            '\r': '\\r',
            '"' : '\\"',
            '\\': '\\\\'
        },
        rep;
 
 
    function quote(string) {
 
// If the string contains no control characters, no quote characters, and no
// backslash characters, then we can safely slap some quotes around it.
// Otherwise we must also replace the offending characters with safe escape
// sequences.
 
        escapable.lastIndex = 0;
        return escapable.test(string) ?
            '"' + string.replace(escapable, function (a) {
                var c = meta[a];
                return typeof c === 'string' ? c :
                    '\\u' + ('0000' + a.charCodeAt(0).toString(16)).slice(-4);
            }) + '"' :
            '"' + string + '"';
    }
 
 
    function str(key, holder) {
 
// Produce a string from holder[key].
 
        var i,          // The loop counter.
            k,          // The member key.
            v,          // The member value.
            length,
            mind = gap,
            partial,
            value = holder[key];
 
// If the value has a toJSON method, call it to obtain a replacement value.
 
        if (value && typeof value === 'object' &&
                typeof value.toJSON === 'function') {
            value = value.toJSON(key);
        }
 
// If we were called with a replacer function, then call the replacer to
// obtain a replacement value.
 
        if (typeof rep === 'function') {
            value = rep.call(holder, key, value);
        }
 
// What happens next depends on the value's type.
 
        switch (typeof value) {
        case 'string':
            return quote(value);
 
        case 'number':
 
// JSON numbers must be finite. Encode non-finite numbers as null.
 
            return isFinite(value) ? String(value) : 'null';
 
        case 'boolean':
        case 'null':
 
// If the value is a boolean or null, convert it to a string. Note:
// typeof null does not produce 'null'. The case is included here in
// the remote chance that this gets fixed someday.
 
            return String(value);
 
// If the type is 'object', we might be dealing with an object or an array or
// null.
 
        case 'object':
 
// Due to a specification blunder in ECMAScript, typeof null is 'object',
// so watch out for that case.
 
            if (!value) {
                return 'null';
            }
 
// Make an array to hold the partial results of stringifying this object value.
 
            gap += indent;
            partial = [];
 
// Is the value an array?
 
            if (Object.prototype.toString.apply(value) === '[object Array]') {
 
// The value is an array. Stringify every element. Use null as a placeholder
// for non-JSON values.
 
                length = value.length;
                for (i = 0; i < length; i += 1) {
                    partial[i] = str(i, value) || 'null';
                }
 
// Join all of the elements together, separated with commas, and wrap them in
// brackets.
 
                v = partial.length === 0 ? '[]' :
                    gap ? '[\n' + gap +
                            partial.join(',\n' + gap) + '\n' +
                                mind + ']' :
                          '[' + partial.join(',') + ']';
                gap = mind;
                return v;
            }
 
// If the replacer is an array, use it to select the members to be stringified.
 
            if (rep && typeof rep === 'object') {
                length = rep.length;
                for (i = 0; i < length; i += 1) {
                    k = rep[i];
                    if (typeof k === 'string') {
                        v = str(k, value);
                        if (v) {
                            partial.push(quote(k) + (gap ? ': ' : ':') + v);
                        }
                    }
                }
            } else {
 
// Otherwise, iterate through all of the keys in the object.
 
                for (k in value) {
                    if (Object.hasOwnProperty.call(value, k)) {
                        v = str(k, value);
                        if (v) {
                            partial.push(quote(k) + (gap ? ': ' : ':') + v);
                        }
                    }
                }
            }
 
// Join all of the member texts together, separated with commas,
// and wrap them in braces.
 
            v = partial.length === 0 ? '{}' :
                gap ? '{\n' + gap + partial.join(',\n' + gap) + '\n' +
                        mind + '}' : '{' + partial.join(',') + '}';
            gap = mind;
            return v;
        }
    }
 
// If the JSON object does not yet have a stringify method, give it one.
 
    if (typeof JSON.stringify !== 'function') {
        JSON.stringify = function (value, replacer, space) {
 
// The stringify method takes a value and an optional replacer, and an optional
// space parameter, and returns a JSON text. The replacer can be a function
// that can replace values, or an array of strings that will select the keys.
// A default replacer method can be provided. Use of the space parameter can
// produce text that is more easily readable.
 
            var i;
            gap = '';
            indent = '';
 
// If the space parameter is a number, make an indent string containing that
// many spaces.
 
            if (typeof space === 'number') {
                for (i = 0; i < space; i += 1) {
                    indent += ' ';
                }
 
// If the space parameter is a string, it will be used as the indent string.
 
            } else if (typeof space === 'string') {
                indent = space;
            }
 
// If there is a replacer, it must be a function or an array.
// Otherwise, throw an error.
 
            rep = replacer;
            if (replacer && typeof replacer !== 'function' &&
                    (typeof replacer !== 'object' ||
                     typeof replacer.length !== 'number')) {
                throw new Error('JSON.stringify');
            }
 
// Make a fake root object containing our value under the key of ''.
// Return the result of stringifying the value.
 
            return str('', {'': value});
        };
    }
 
 
// If the JSON object does not yet have a parse method, give it one.
 
    if (typeof JSON.parse !== 'function') {
        JSON.parse = function (text, reviver) {
 
// The parse method takes a text and an optional reviver function, and returns
// a JavaScript value if the text is a valid JSON text.
 
            var j;
 
            function walk(holder, key) {
 
// The walk method is used to recursively walk the resulting structure so
// that modifications can be made.
 
                var k, v, value = holder[key];
                if (value && typeof value === 'object') {
                    for (k in value) {
                        if (Object.hasOwnProperty.call(value, k)) {
                            v = walk(value, k);
                            if (v !== undefined) {
                                value[k] = v;
                            } else {
                                delete value[k];
                            }
                        }
                    }
                }
                return reviver.call(holder, key, value);
            }
 
 
// Parsing happens in four stages. In the first stage, we replace certain
// Unicode characters with escape sequences. JavaScript handles many characters
// incorrectly, either silently deleting them, or treating them as line endings.
 
            cx.lastIndex = 0;
            if (cx.test(text)) {
                text = text.replace(cx, function (a) {
                    return '\\u' +
                        ('0000' + a.charCodeAt(0).toString(16)).slice(-4);
                });
            }
 
// In the second stage, we run the text against regular expressions that look
// for non-JSON patterns. We are especially concerned with '()' and 'new'
// because they can cause invocation, and '=' because it can cause mutation.
// But just to be safe, we want to reject all unexpected forms.
 
// We split the second stage into 4 regexp operations in order to work around
// crippling inefficiencies in IE's and Safari's regexp engines. First we
// replace the JSON backslash pairs with '@' (a non-JSON character). Second, we
// replace all simple value tokens with ']' characters. Third, we delete all
// open brackets that follow a colon or comma or that begin the text. Finally,
// we look to see that the remaining characters are only whitespace or ']' or
// ',' or ':' or '{' or '}'. If that is so, then the text is safe for eval.
 
            if (/^[\],:{}\s]*$/.
test(text.replace(/\\(?:["\\\/bfnrt]|u[0-9a-fA-F]{4})/g, '@').
replace(/"[^"\\\n\r]*"|true|false|null|-?\d+(?:\.\d*)?(?:[eE][+\-]?\d+)?/g, ']').
replace(/(?:^|:|,)(?:\s*\[)+/g, ''))) {
 
// In the third stage we use the eval function to compile the text into a
// JavaScript structure. The '{' operator is subject to a syntactic ambiguity
// in JavaScript: it can begin a block or an object literal. We wrap the text
// in parens to eliminate the ambiguity.
 
                j = eval('(' + text + ')');
 
// In the optional fourth stage, we recursively walk the new structure, passing
// each name/value pair to a reviver function for possible transformation.
 
                return typeof reviver === 'function' ?
                    walk({'': j}, '') : j;
            }
 
// If the text is not JSON parseable, then a SyntaxError is thrown.
 
            throw new SyntaxError('JSON.parse');
        };
    }
})();
 
***********  end of json2.js  *************

Open in new window

sample-rank.mdb

jkdt0077

Is this page live online anywhere to look at?

b0lsc0tt

The problem is with one line of your code. In the ASP file the MDB file name is mistyped.
strPath = Server.MapPath("sample-rank.mdb")
Let me know how that change works for you or if you have a question.
bol

ASKER CERTIFIED SOLUTION

b0lsc0tt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

b0lsc0tt

The code for the ASP file.
bol

<% Option Explicit %>
<%
' **********************************
' AJAX Player - LOOK UP part
'    This page will fill in order of venue, rank, and then playerName
' **********************************
' DB named sample_rank.mdb in this folder
' File to try to list those that need to be removed from a tournament if too many enrolled
'  Tables are : rank & tournament
'            Fields in Rank are :  ID, PlayerID, PlayerName, PlayerRank
'      Fields in Tournament are : ID, TournamentID, TournamentName, Year, PlayerID
'          ' ID field in each table is just primary key, PlayerID is relation
 
Dim nVenue, nRank, sPlayer, sSQL, nSQL
If Request.Form("venue") <> "" then
	nVenue = Replace(Request.Form("venue"), "_", " ")
	If Request.Form("rank") <> "" then
		nRank = Request.Form("rank")
		nSQL = 3
	Else
		nSQL = 2
	End if
Else
	nSQL = 1
End if
 
Select Case nSQL
	Case 1
		' show venues
		sSQL = "SELECT DISTINCT TournamentName, TournamentID FROM Tournament WHERE Year = 2006"
	Case 2
		' sql for venue only find rank of players in that venue
		sSQL = "SELECT Rank.PlayerRank FROM Rank INNER JOIN Tournament ON Rank.PlayerID = Tournament.PlayerID WHERE Tournament.Year = 2006 AND Tournament.TournamentName = '" & nVenue & "' ORDER BY Rank.PlayerRank"
	Case 3
		' sql for player info with rank and venue selected
		sSQL = "SELECT Rank.PlayerName FROM Rank INNER JOIN Tournament ON Rank.PlayerID = Tournament.PlayerID WHERE Tournament.Year = 2006 AND Tournament.TournamentName = '" & nVenue & "' AND Rank.PlayerRank = " & nRank
End select
 
If sSQL <> "" then
	Dim objConn, objRS
	Dim strPath, aFields, item
	strPath = Server.MapPath("sample-rank.mdb")
	Set objConn = Server.CreateObject("ADODB.Connection")
	Set objRS = Server.CreateObject("ADODB.Recordset")
	objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strPath
	objRS.Open sSQL, objConn, 3
	aFields = objRS.GetRows
	Dim sResponse, i
	Select Case nSQL
		Case 1
			sResponse = "{""venue"" : ["
			For i=0 to ubound(aFields,2)
				sResponse = sResponse & "{ ""name"" : """ & aFields(0,i) & """,""value"" : """ & aFields(1,i) & """ },"
			Next
			sResponse = Left(sResponse, Len(sResponse) - 1)
			sResponse = sResponse & "] }"
			Response.Write sResponse
		Case 2
			sResponse = "{""venue"" : ["
			sResponse = sResponse & "{ ""name"" : """ & nVenue & """,""value"" : """ & nVenue & """ }"
			sResponse = sResponse & "],"
			sResponse = sResponse & " ""rank"" : ["
			For each item in aFields
				sResponse = sResponse & "{ ""name"" : """ & item & """,""value"" : """ & item & """ },"
			Next
			sResponse = Left(sResponse, Len(sResponse) - 1)
			sResponse = sResponse & "] }"
			Response.Write sResponse
		Case 3
			sResponse = "{""venue"" : ["
			sResponse = sResponse & "{ ""name"" : """ & nVenue & """,""value"" : """ & nVenue & """ }"
			sResponse = sResponse & "],"
			sResponse = sResponse & " ""rank"" : ["
			sResponse = sResponse & "{ ""name"" : """ & nRank & """,""value"" : """ & nRank & """ }"
			sResponse = sResponse & "],"
			sResponse = sResponse & " ""player"" : """
			For each item in aFields
				sResponse = sResponse & item
			Next
			sResponse = sResponse & """ }"
			Response.Write sResponse
	End select
	objRS.Close
	Set objRS = Nothing
	objConn.Close
	Set objConn = Nothing
End if
%>

Open in new window

DanielR6

ASKER

bol,

Thank you for your assitance, your obviously very busy. The changes you made are what I'm looking for. I'll put it along side the previous code and try and learn the changes. If I have a query i'll drop you a comment.

Cheers
Dan

b0lsc0tt

Dan,
Thanks for the fun question, the grade and the points. I am glad I could help. Let me know if some question does come up as you review the changes. There weren't too many. :) I am glad to hear the changes gave you just what you wanted.
bol