Variable doesn't hold it's value

[booboo0912]

I'm trying to pull data from a recordset, but when I assign values from the recordset to variables, then go to print out the variable, I get nothing! It seems once I exit my while statement, the variables return a blank. Please see code snipet below:

sSQL = "Select art_quality, COUNT(art_quality) as 'Total AQ' FROM table GROUP BY art_quality"

rs.Open(sSQL,conn);

if(!rs.EOF && !rs.BOF) {
rs.MoveFirst();
while(!rs.EOF) {
if(rs("art_quality") == 0) {
tot_AQ_NA = rs("Total AQ");
Response.write(tot_AQ_NA);
} else if(rs("art_quality") == 1) {
tot_AQ_VS = rs("Total AQ");
Response.write(tot_AQ_VS);
}
rs.MoveNext();
}
}

In my table, I try to print out the value of tot_AQ_NA and tot_AQ_VS, but it's blank. I put the Response.write in the while statement to see if my variables were being set, and they are, but when I put a Response.write outside of the while statement, the variable is blank. Here's what the recordset holds:

art_quality Total AQ
0 3
1 1

Any idea why my varibles aren't being returned outside of the while loop? Thanks in advance!

 

[Tarwn]

Are you sure you don't have a third record with blank values in the recordset?

Right now you are writing over the variables values with each loop, so if the last record has empty values, those empties will overwrite the values from the previous record, and you magically get useless variables

Lookihng at it a little more deeply it looks like you would actually have to have blank values in last two records (well, in last of each category) to get that, but I suppose it is still worth checking as a possibliity, I will give it some mor thought while standing in line at the DMV and see if I come up with anything

Heck, what else am I going to do while standing in line for a couple hours for tag renewal and personal plates

-T

[sub]01000111 01101111 01110100 00100000 01000011 01101111 01100110 01100110 01100101 01100101 00111111[/sub]
Help, the rampaging, spear-waving, rabid network gnomes are after me!

 

[booboo0912]

Hi Tarwn! Thanks for your reply! I hope you're not waiting too long at the DMV...if you're in VA, good luck!!!

For some reason, and we still haven't figured out why, when I put String() around the rs("Total AQ") in the if statements, the variables hold their values. Is this a lazy work around or do I still have a problem? I kind of wondered about that last record (3rd), and if that was causing me problems...I was getting a BOF/EOF error when I tried to print out the values in my table.

Should I just not worry about it since it's working? I would still like to understand what the problem was and why String() works.

Thank you!

 

[Tarwn]

Ok, I chickened out going to the DMV on a Friday, Monday is soon enough

That does seem odd. I'm not as good with javascript as I am with VBScript/ASP but even so that doesn't seem logical. My first thought was that it could be some kind of scope issue, but even so, using the String function shouldn't have changed that.

In any event., you will probably want your Response.Write's inside the while statement simply because that will allow you to write all the values rather then just the last record.

if your interested in looking deeper to see what the source of this problem was, maybe try putting a counter in the loop to see how many records it is looping through?

You could go real nuts with your output statements also, that might give us a clue, maybe something like:

sSQL = "Select art_quality, COUNT(art_quality) as 'Total AQ' FROM table GROUP BY art_quality"

rs.Open(sSQL,conn);

if(!rs.EOF && !rs.BOF) {
	rs.MoveFirst();
	while(!rs.EOF) {
		Response.Write("Art Quality: [" + rs("art_quality") + "]<br>\n";
		Response.Write("Art Quality == 0: [" + (rs("art_quality") == 0) + "]<br>\n";
		Response.Write("Art Quality == 1: [" + (rs("art_quality") == 1) + "]<br>\n";
		if(rs("art_quality") == 0) {
			Response.Write("Assigning Total AQ: [" + rs("Total AQ") + "]";
			tot_AQ_NA = rs("Total AQ");
			if(tot_AQ_NA.length > 0){
				Response.Write("...OK!<br>\n");
			}
			else{
				Response.Write("...<span style='color:Red'>FAIL!</span><br>\n");
			}
		} 
		else if(rs("art_quality") == 1) {
			Response.Write("Assigning Total AQ: [" + rs("Total AQ") + "]";
			tot_AQ_VS = rs("Total AQ");
			if(tot_AQ_VS.length > 0){
				Response.Write("...OK!<br>\n");
			}
			else{
				Response.Write("...<span style='color:Red'>FAIL!</span><br>\n");
			}
		}
		else{
			Response.Write("<span style='color:Red'>Assignment Failed!</span> - Art Quality matches neither 0 or 1<br>\n";
		}
		rs.MoveNext();
	}
}

Response.Write "<br>\nStored Values:<br>\n";
Response.Write "0: " + tot_AQ_NA + "<br>\n";
Response.Write "1: " + tot_AQ_VS + "<br>\n";

Well, that may help..I just realized after writing all of that, you may get an error on the .length function. Since these are coming from the database as numbers somehow your string conversion is casting them to strings. If we add in a string cast so we can check their values we will lose th capability of detecting the problem...maybe instead of the .length checks change it to check the value > -1 and initialize those two variables as -1. Ok, revised copy of the above:

sSQL = "Select art_quality, COUNT(art_quality) as 'Total AQ' FROM table GROUP BY art_quality"

rs.Open(sSQL,conn);
tot_AQ_NA = -1;
tot_AQ_VS = -1;
if(!rs.EOF && !rs.BOF) {
	rs.MoveFirst();
	while(!rs.EOF) {
		Response.Write("Art Quality: [" + rs("art_quality") + "]<br>\n";
		Response.Write("Art Quality == 0: [" + (rs("art_quality") == 0) + "]<br>\n";
		Response.Write("Art Quality == 1: [" + (rs("art_quality") == 1) + "]<br>\n";
		if(rs("art_quality") == 0) {
			Response.Write("Assigning Total AQ: [" + rs("Total AQ") + "]";
			tot_AQ_NA = rs("Total AQ");
			if(tot_AQ_NA > -1){
				Response.Write("...OK!<br>\n");
			}
			else{
				Response.Write("...<span style='color:Red'>FAIL!</span><br>\n");
			}
		} 
		else if(rs("art_quality") == 1) {
			Response.Write("Assigning Total AQ: [" + rs("Total AQ") + "]";
			tot_AQ_VS = rs("Total AQ");
			if(tot_AQ_VS > -1){
				Response.Write("...OK!<br>\n");
			}
			else{
				Response.Write("...<span style='color:Red'>FAIL!</span><br>\n");
			}
		}
		else{
			Response.Write("<span style='color:Red'>Assignment Failed!</span> - Art Quality matches neither 0 or 1<br>\n";
		}
		rs.MoveNext();
		Response.Write("--- Status:\t");
		Response.Write((tot_AQ_NA > -1)?"NA: yes<br>":"NA: no<br>");
		Response.Write((tot_AQ_VS > -1)?"VS: yes<br>":"VS: no<br>");
	}
}

Response.Write "<br>\nStored Values:<br>\n";
Response.Write "0: " + tot_AQ_NA + "<br>\n";
Response.Write "1: " + tot_AQ_VS + "<br>\n";

Anyways, that might be worth a shot. Remember my javascript skills are way rusty though, there may be some syntax errors in there,

-T

[sub]01000111 01101111 01110100 00100000 01000011 01101111 01100110 01100110 01100101 01100101 00111111[/sub]
Help, the rampaging, spear-waving, rabid network gnomes are after me!

 

[ignoreme]

I assume you are using JScript within ASP (based on the curly braces and semicolons.

If you put the response.write outside the while loop and you use the same variables, don't you clobber the previous value in each variable after each iteration.

Thus, when the while loop finishes, the variables would only hold the last value from the last iteration.

 

[Tarwn]

ignoreme: yep, I mentioned that above. But looking at his select statement he should only ever get one value for each variable since he is grouping by the Art Quality column and that column also decides which variable to write into...

[sub]01000111 01101111 01110100 00100000 01000011 01101111 01100110 01100110 01100101 01100101 00111111[/sub]
Help, the rampaging, spear-waving, rabid network gnomes are after me!

 

[booboo0912]

Wow! Thanks for your help, Tarwn! I still haven't figured out the "why", but it will have to wait until Monday...just like the DMV! I hope you have a good weekend!
Thanks again!