Hello all,

The problem:

I have a simple Access 97 database that contains records of employee quality assured training gradings. I am building an E-learning app that will eventually have a Client Side SQL database behind it for storing the aforementioned records. I've built the database and have built most of the bones of the script for querying it. The actual script works and allows you to bring up records no problem. The problem is, I can't use ORDER BY through ADO from Javascript. I'm accessing ADO through its ActiveX control using the Microsoft Jet 4.0 drivers. I've reinstalled MDAC (Microsoft Data Access Components) and this hasn't helped.

Browser: MSIE 6 SP1.
OS: Win NT 4.0

Here's the script chunk I'm having trouble with:

function queryDB(qType, op, strVal, oBy, ascDesc)
{


var oBySetUp = "QAGrades" + "." + "`" + oBy + "`" //this compiles a text string containing order by sequence I'd like to use, oBy is passed in as an argument (text string) from a select option.

//I then append this to the end of either a string prepared for numeric query or a string prepared for a text query:

var qString="SELECT * FROM QAGrades WHERE " + qTypeUser + " " + op + " " + "'" + strVal + "'" + " ORDER BY " + "'" + oBySetUp + "' " + ascDesc
//This is my string prepared for a text query. QAGrades is the name of the table

var qNumeric = "SELECT * FROM QAGrades WHERE " + qTypeUser + " " + op + " " + strVal + " ORDER BY " + "'" + oBySetUp + "' " + ascDesc
//This is my string prepared for a numeric query.

Theres another chunk of script here that determines if we're querying a number or a text string:

//check dType
var validChars = "0123456789";
var isNumber=true;
var Char;
for (i = 0; i < strVal.length && isNumber == true; i++)
{
Char = strVal.charAt(i);
if (validChars.indexOf(Char) == -1)
{
setStr = qString
}
else
{
setStr = qNumeric
}
}

Once we know what the datatype is we then append it into the rSet.Open command as an argument (in red below):

//Setting up the connection string:
var cString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\\vcResults.mdb";
var adOpenKeyset = 1;
var adLockOptimistic = 3;
var adCmdText = 1;
var adUseClient = 3;
var tdStr = ""
var thStr = ""
quoStr = "\'"

//initialise database and submit the query:

var cObj = new ActiveXObject("ADODB.Connection"); //set up the activex control for the connection to the database
var rSet = new ActiveXObject("ADODB.Recordset"); //setup a Recordset activex to contain the results returned from the query

rSet.CursorLocation = adUseClient //everything will be client side
cObj.CursorLocation = adUseClient

cObj.Open (cString);
try
{
rSet.Open(setStr,cObj,adOpenKeyset,adLockOptimistic,adCmdText); // <---this is where the query is sent to the database (setStr is inherited from the datatype checking routing above (where it says check dType in bold)

rSet.MoveFirst
for (i =0; i < rSet.Fields.Count; i+=1)
{
thStr+= "<td class = tdClass>" + rSet.Fields(i).Name + "</td>"
}
for (rCount = 0; rCount < rSet.RecordCount; rCount+=1)
{
for(counter = 0; counter <rSet.Fields.Count; counter+=1)
{
tdStr += "<td> " + rSet.Fields(counter).Value + "</td>"
}
if (counter =rSet.Fields.Count)
{
tdStr+="<tr>"
}
rSet.AbsolutePosition+=1
}
}
catch(err)
{
alert("Error! No Records Found! \n\n" + err.description)
}
try
{
rSet.Close();
}

catch(err)
{
alert("I couldn't close, cos errrrrr, I wasn't open!" + '\n\n' + err.description)
}
cObj.Close();
document.getElementById("retDiv").innerHTML = "<table><tr>" + thStr + "</tr><tr>" + tdStr + "</table>"

}

The rest of the script just builds a table to keep the results in (hence all the for loops). All variables:

qType = Query Type (from select option)
op = Operator (From Select Option)
strVal = String Value (From text box)
oBy = Order By (from select box)
ascDesc = Ascending or Descending order? (From Select Option) <--Doesn't work (Value can either be ASC or DESC)

Hope this isn't too garbled, I've tried to outline the problem the best I can.

Thanks for any help you all can give.

Joe