using variables in recordset object

[pixelz]

Hey all,

I have a database of responses to a questionaire - each question has a number of possible responses and each response is stored as a numerical value (ie answer A = 1; answer B = 2 etc)

I'm trying to write a function (in javascript ASP) that will give me an array of objects, each with the name of the response and how many of these responses have been given

I'm wanting to pass the recordset object and the field I'm looking for to this function and do the looping there using a variable in the object call. This is throwing an ADODB.Recordset error '800a0cc1': Item cannot be found in the collection corresponding to the requested name or ordinal error on line 93 which reads:

 var tempValue = objRec(tempQuestion);

where 'objRec' is the recordset object and 'tempQuestion' is the fieldname in the database.

Is it possible to use a variable when referencing the recordset object??

Here's the full function code so you can get a better idea of what I'm doing:

function processResults(objRec, tempQuestion, nameArray) {
		var tempArray = new Array;
                var maxNum = nameArray.length; 
               //make sure the tempQuestion is a string
		[b]var tempQuestion = new String(tempQuestion);[/b]
		
                //set temporary array with names and zero values
		for (i=0; i<nameArray.length; i++) {
			tempArray.push(new qObj(nameArray[i],0));
		}
		
               //loop through recorset object and test each record's value	
		while (!objRec.EOF) {
                        
                        //assign value from the recordset to a temp value
			[b]var tempValue = objRec(tempQuestion);[/b]
                        
                        //loop through to find corresponding value and increment 
                       //object array accordingly
			for (i=1; i<maxNum; i++) {
				if (i == tempValue) {
					tempArray[i].value++;
					break;
				}
			}
			
		}
		//send my competed array back
		return tempArray;
		tempArray = null;			
	}

Here's how the function is called:

//set the array of possible answers for this question
var answerArray = new Array('Student','Post-Doc','Senior researcher','Clinical scientist','Clinician in training','Clinical consultant','Business Dev','Patient / Carer','Trial support','Charity rep','Nurse','Other');

 //set the database field name for this field for ease of reuse in later versions
 var thisQuestion = new String('positionHeld');
 //build the SQL
 SQL = 'SELECT '+thisQuestion+' FROM tbl_2007ConferenceFeedback';
 //open the recodset
 objRec.Open(SQL,objConn,2);

 //pass the recordset, the fieldname and the
 //response names to processResults() - 
 //this should return an array of objects to use for result display
  var Q1Array = processResults(objRec, thisQuestion, answerArray);

 objRec.Close;
	//loop through the responses and print out the values 			
	for (i=0; i<Q1Array.length; i++) {
	  %><%= Q1Array.name %> = <%= Q1Array.value %><%
				}

Hope somebody can help!

thanks

Pix

 

[pixelz]

BTW - I've DEFINATELY checked all spelling between the code and the database!

 

[tsuji]

[1] >var thisQuestion = new String('positionHeld');
What do you mean by this? thisQuestion is "positionHeld". What for?
[2] >var tempArray = new Array;
Better, though the above still functions.
[tt]var tempArray = new Array[red]()[/red];[/tt]
[3] >var maxNum = nameArray.length;
Defined at that position, it is zero. And later, you have.
>for (i=1; i<maxNum; i++) {
Why i starts from 1? maxNum never changed there since, it is still zero. The loop never loops.
[4] >tempArray.push(new qObj(nameArray,0));
What is qObj()?
[5] >var tempValue = objRec(tempQuestion);
You may or may not have error precisely at this line. In any case, better make sure it picks up a value rather than a field object.
[tt]var tempValue = objRec(tempQuestion)[red].value[/red][/tt]
[6] If you intend to continuously call the processResults, and that the objRec is not built anew, should you not make a movefirst() before the loop while(!objRec.EOF)? But, maybe there isn't a problem: just not clear.
[7] The script seems somewhat lack of clarity (maybe only to me). Maybe after cleaning it up a bit, the problem will show up more clearly.

 

[pixelz]

Hey Tsuji,

first of all, this is just my first pass through this script - you're right, it's not very clear and I've been working on it subsequently in a slightly different form to get results out - ie not using the function, just writing it straight, and it's getting better.

Anyway, here are the answers to your questions:

1. This is my attempt at making sure the passed variable is a string value - added this after I got the error message the first time. Prob could also have done positionheld = positionheld.toString() - that's the idea.
2. gotcha!
3. yup - this is where the script gets a bit more finesse after the first glance. Have rewritten that section quite substantially. The array numbering starts from 0, but there's never a 0 entered as a value in the dbase. Fixed that now though.
4. Sorry - should have said - qObj is the object I'm going to use to keep the processed data in. It has two properties .name and .value.

5. Ah now we get to the meat of the business. This is the exact line I'm having trouble with. Unfortunately I put the .value edit onto the line, but it still throws an error there.

Thanks for your help mate! any more thoughts on point 5? How to use a variable with a recordset object?

Cheers!

Pix

 

[tsuji]

>any more thoughts on point 5? How to use a variable with a recordset object?
On the face of it, there is absolutely no problem using variable at that position. Suppose rs("A") is a field, if you define x="A", there would be no problem referring the same field rs(x).

 

[pixelz]

yeah, that's what I thought, but still getting the error 'Item cannot be found in the collection corresponding to the requested name or ordinal' which seems to me that it's just not recognising the variable. Tried it out of the context of the function as well, but no luck there either...

 

[gmmastros]

Try adding square brackets around your column names, like this...

SQL = 'SELECT [!][[/!]'+thisQuestion+'[!]][/!] FROM tbl_2007ConferenceFeedback';

If I interpret this code correctly, your column names could have spaces in them. For Access and SQL Server (and possibly other databases), you must qualify column names with square brackets when they are reserved words and/or contain spaces.

Anyway... give it a try and see what happens.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[pixelz]

Right - fixed it!

This is very wierd - I changed the line:

var tempQuestion = new String(tempQuestion);

to actively cast the tempQuestion value into a string datatype

var tempQuestion = tempQuestion.toString();

And that works beautifully...

jeeez - i knew JScript was picky with it's datatyping, but my word!

Anyway thanks for your help! Here's the updated function if you're interested in looking over it:

function processResults(objRec, tempQuestion, nameArray) {
			var tempArray = new Array();
			var tempQuestion = tempQuestion.toString();
			var maxNum = nameArray.length;
			
			//set temporary array with names and zero values
			for (i=0; i<nameArray.length; i++) {
				tempArray.push(new qObj(nameArray[i],0));
			}
			
			while (!objRec.EOF) {
						var tempValue = objRec(tempQuestion);
						for (i=1; i<=maxNum; i++) {
							iNum = i.toString();
							if (tempValue == iNum) {
								tempArray[i-1].value++;
								break;
							}
						}
					objRec.MoveNext;	
					}
			
			return tempArray;
			tempArray = null;
		}

Cheers!!

Pix