473,715 Members | 4,902 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Not looping

Hey gang.

I have a script that gets stats from a mssql db, and then inserts those
stats into a temp table. where i can work with them as i wish.

the problem is it isn't looping through all the records, and i am not sure
why.

i know the script isn't perfect, as it is open to sql injection and stuff
like that, but once the site is complete, i will be putting in checks and
things like that to stop it.

any ideas why it isn't looping??

<% set stats1 = conn.execute("s elect team_name from teams where idivision =
'ACA'")
do while not stats1.eof
varT = stats1.fields.i tem("team_name" ).value

set check1 = conn.execute("s elect count(teama) as cnt1 from matches
where teama = '" & varT & "'")
bam1 = check1.fields.i tem("cnt1").val ue

if bam1 = 0 then
varW1 = 0
elseif bam1 = 1 then
set stats1 = conn.execute("s elect teama_points from matches where
teama = '" & varT & "'")
varW1 = stats1.fields.i tem("teama_poin ts").value
elseif bam1 > 1 then
set stats1 = conn.execute("s elect sum(teama_point s) as pts1 from
matches where teama = '" & varT & "'")
varW1 = stats1.fields.i tem("pts1").val ue
end if
set check2 = conn.execute("s elect count(teamb) as cnt11 from matches
where teamb = '" & varT & "'")
bam2 = check2.fields.i tem("cnt11").va lue

if bam2 = 0 then
varL12 = 0
elseif bam2 = 1 then
set stats21 = conn.execute("s elect teamb_points from matches where
teamb = '" & varT & "'")
varL12 = stats21.fields. item("teamb_poi nts").value
elseif bam2 > 1 then
set stats21 = conn.execute("s elect sum(teamb_point s) as pts2 from
matches where teamb = '" & varT & "'")
varL12 = stats21.fields. item("pts2").va lue
end if
set stats2 = conn.execute("s elect count(iwinner) as wins from matches
where iwinner = '" & varT & "'")
varW2 = stats2.fields.i tem("wins").val ue

set stats3 = conn.execute("s elect count(iloser) as losses from matches
where iloser = '" & varT & "'")
varL1 = stats3.fields.i tem("losses").v alue

total_points = varW1 + varL12
var_total = bam1 + bam2
if bam1 = 0 then
win_pct = 0
else
win_pct = formatnumber((( bam1 / var_total)*100) ,0)
end if
strSQL = "insert into temp (team, points, wins, losses, win_pct)
values ('" & varT & "', " & total_points & ", " & varW2 & ", " & varL1 & ",
" & win_pct & ")"
conn.execute (strSQL)

stats1.movenext
loop
%>
May 12 '06 #1
5 1491

"Jeff" <gi*****@adelph ia.net> wrote in message
news:1I******** ************@ad elphia.com...
Hey gang.

I have a script that gets stats from a mssql db, and then inserts those
stats into a temp table. where i can work with them as i wish.

the problem is it isn't looping through all the records, and i am not sure
why.

i know the script isn't perfect, as it is open to sql injection and stuff
like that, but once the site is complete, i will be putting in checks and
things like that to stop it.

any ideas why it isn't looping??

<% set stats1 = conn.execute("s elect team_name from teams where idivision = 'ACA'")
do while not stats1.eof
varT = stats1.fields.i tem("team_name" ).value

set check1 = conn.execute("s elect count(teama) as cnt1 from matches
where teama = '" & varT & "'")
bam1 = check1.fields.i tem("cnt1").val ue

if bam1 = 0 then
varW1 = 0
elseif bam1 = 1 then
set stats1 = conn.execute("s elect teama_points from matches where
teama = '" & varT & "'")
varW1 = stats1.fields.i tem("teama_poin ts").value
elseif bam1 > 1 then
set stats1 = conn.execute("s elect sum(teama_point s) as pts1 from
matches where teama = '" & varT & "'")
varW1 = stats1.fields.i tem("pts1").val ue
end if
set check2 = conn.execute("s elect count(teamb) as cnt11 from matches
where teamb = '" & varT & "'")
bam2 = check2.fields.i tem("cnt11").va lue

if bam2 = 0 then
varL12 = 0
elseif bam2 = 1 then
set stats21 = conn.execute("s elect teamb_points from matches where
teamb = '" & varT & "'")
varL12 = stats21.fields. item("teamb_poi nts").value
elseif bam2 > 1 then
set stats21 = conn.execute("s elect sum(teamb_point s) as pts2 from
matches where teamb = '" & varT & "'")
varL12 = stats21.fields. item("pts2").va lue
end if
set stats2 = conn.execute("s elect count(iwinner) as wins from matches where iwinner = '" & varT & "'")
varW2 = stats2.fields.i tem("wins").val ue

set stats3 = conn.execute("s elect count(iloser) as losses from matches where iloser = '" & varT & "'")
varL1 = stats3.fields.i tem("losses").v alue

total_points = varW1 + varL12
var_total = bam1 + bam2
if bam1 = 0 then
win_pct = 0
else
win_pct = formatnumber((( bam1 / var_total)*100) ,0)
end if
strSQL = "insert into temp (team, points, wins, losses, win_pct)
values ('" & varT & "', " & total_points & ", " & varW2 & ", " & varL1 & ", " & win_pct & ")"
conn.execute (strSQL)

stats1.movenext
loop
%>

Is there reason why this lot can't be done in a stored procedure making
proper use of Table joins etc?

May 12 '06 #2
not really. what i am doing here, is getting the data, uploading it to a
temp folder, then retrieving that data and sorting it by points. then i
delete everything from the table.

the main reason i haven't done anything like that, is because i am not
familiar enough with joins to do it. i was working with what i knew so to
speak..lol
"Anthony Jones" <An*@yadayadaya da.com> wrote in message
news:%2******** ********@TK2MSF TNGP04.phx.gbl. ..

"Jeff" <gi*****@adelph ia.net> wrote in message
news:1I******** ************@ad elphia.com...
Hey gang.

I have a script that gets stats from a mssql db, and then inserts those
stats into a temp table. where i can work with them as i wish.

the problem is it isn't looping through all the records, and i am not
sure
why.

i know the script isn't perfect, as it is open to sql injection and stuff
like that, but once the site is complete, i will be putting in checks and
things like that to stop it.

any ideas why it isn't looping??

<% set stats1 = conn.execute("s elect team_name from teams where idivision

=
'ACA'")
do while not stats1.eof
varT = stats1.fields.i tem("team_name" ).value

set check1 = conn.execute("s elect count(teama) as cnt1 from matches
where teama = '" & varT & "'")
bam1 = check1.fields.i tem("cnt1").val ue

if bam1 = 0 then
varW1 = 0
elseif bam1 = 1 then
set stats1 = conn.execute("s elect teama_points from matches where
teama = '" & varT & "'")
varW1 = stats1.fields.i tem("teama_poin ts").value
elseif bam1 > 1 then
set stats1 = conn.execute("s elect sum(teama_point s) as pts1 from
matches where teama = '" & varT & "'")
varW1 = stats1.fields.i tem("pts1").val ue
end if
set check2 = conn.execute("s elect count(teamb) as cnt11 from
matches
where teamb = '" & varT & "'")
bam2 = check2.fields.i tem("cnt11").va lue

if bam2 = 0 then
varL12 = 0
elseif bam2 = 1 then
set stats21 = conn.execute("s elect teamb_points from matches where
teamb = '" & varT & "'")
varL12 = stats21.fields. item("teamb_poi nts").value
elseif bam2 > 1 then
set stats21 = conn.execute("s elect sum(teamb_point s) as pts2 from
matches where teamb = '" & varT & "'")
varL12 = stats21.fields. item("pts2").va lue
end if
set stats2 = conn.execute("s elect count(iwinner) as wins from

matches
where iwinner = '" & varT & "'")
varW2 = stats2.fields.i tem("wins").val ue

set stats3 = conn.execute("s elect count(iloser) as losses from

matches
where iloser = '" & varT & "'")
varL1 = stats3.fields.i tem("losses").v alue

total_points = varW1 + varL12
var_total = bam1 + bam2
if bam1 = 0 then
win_pct = 0
else
win_pct = formatnumber((( bam1 / var_total)*100) ,0)
end if
strSQL = "insert into temp (team, points, wins, losses, win_pct)
values ('" & varT & "', " & total_points & ", " & varW2 & ", " & varL1 &

",
" & win_pct & ")"
conn.execute (strSQL)

stats1.movenext
loop
%>

Is there reason why this lot can't be done in a stored procedure making
proper use of Table joins etc?

May 12 '06 #3
Probably because you are assigning different result sets to stats1 within
the loop.

set stats1 = conn.execute("s elect team_name from teams
do while not stats1.eof
set stats1 = conn.execute("s elect teama_points from matches
set stats1 = conn.execute("s elect sum(teama_point s) as pts1
stats1.movenext
loop

In addition, you should take Anthony's advice and rewrite. I know that I
wouldn't want to maintain all that goop.

Bob Lehmnan

"Jeff" <gi*****@adelph ia.net> wrote in message
news:1I******** ************@ad elphia.com...
Hey gang.

I have a script that gets stats from a mssql db, and then inserts those
stats into a temp table. where i can work with them as i wish.

the problem is it isn't looping through all the records, and i am not sure
why.

i know the script isn't perfect, as it is open to sql injection and stuff
like that, but once the site is complete, i will be putting in checks and
things like that to stop it.

any ideas why it isn't looping??

<% set stats1 = conn.execute("s elect team_name from teams where idivision = 'ACA'")
do while not stats1.eof
varT = stats1.fields.i tem("team_name" ).value

set check1 = conn.execute("s elect count(teama) as cnt1 from matches
where teama = '" & varT & "'")
bam1 = check1.fields.i tem("cnt1").val ue

if bam1 = 0 then
varW1 = 0
elseif bam1 = 1 then
set stats1 = conn.execute("s elect teama_points from matches where
teama = '" & varT & "'")
varW1 = stats1.fields.i tem("teama_poin ts").value
elseif bam1 > 1 then
set stats1 = conn.execute("s elect sum(teama_point s) as pts1 from
matches where teama = '" & varT & "'")
varW1 = stats1.fields.i tem("pts1").val ue
end if
set check2 = conn.execute("s elect count(teamb) as cnt11 from matches
where teamb = '" & varT & "'")
bam2 = check2.fields.i tem("cnt11").va lue

if bam2 = 0 then
varL12 = 0
elseif bam2 = 1 then
set stats21 = conn.execute("s elect teamb_points from matches where
teamb = '" & varT & "'")
varL12 = stats21.fields. item("teamb_poi nts").value
elseif bam2 > 1 then
set stats21 = conn.execute("s elect sum(teamb_point s) as pts2 from
matches where teamb = '" & varT & "'")
varL12 = stats21.fields. item("pts2").va lue
end if
set stats2 = conn.execute("s elect count(iwinner) as wins from matches where iwinner = '" & varT & "'")
varW2 = stats2.fields.i tem("wins").val ue

set stats3 = conn.execute("s elect count(iloser) as losses from matches where iloser = '" & varT & "'")
varL1 = stats3.fields.i tem("losses").v alue

total_points = varW1 + varL12
var_total = bam1 + bam2
if bam1 = 0 then
win_pct = 0
else
win_pct = formatnumber((( bam1 / var_total)*100) ,0)
end if
strSQL = "insert into temp (team, points, wins, losses, win_pct)
values ('" & varT & "', " & total_points & ", " & varW2 & ", " & varL1 & ", " & win_pct & ")"
conn.execute (strSQL)

stats1.movenext
loop
%>

May 12 '06 #4

"Jeff" <gi*****@adelph ia.net> wrote in message
news:Bb******** ************@ad elphia.com...
not really. what i am doing here, is getting the data, uploading it to a
temp folder, then retrieving that data and sorting it by points. then i
delete everything from the table.

the main reason i haven't done anything like that, is because i am not
familiar enough with joins to do it. i was working with what i knew so to
speak..lol


Time to start learning I think :)

Start here http://www.w3schools.com/sql/default.asp

Not the most accurate resource in the world but I've found it useful for
developers who need somewhere to start.

MS SQL Books online is also good if really want to get stuck in.

Anthony.
May 12 '06 #5
you know what, i found that mistake a bit ago, and was coming to let you all
know i solved it.
thanks so much for the help, and i will widen my knowledge of joins and
stuff.

thanks again
"Bob Lehmann" <no****@dontbot herme.zzz> wrote in message
news:uy******** ******@TK2MSFTN GP05.phx.gbl...
Probably because you are assigning different result sets to stats1 within
the loop.

set stats1 = conn.execute("s elect team_name from teams
do while not stats1.eof
set stats1 = conn.execute("s elect teama_points from matches
set stats1 = conn.execute("s elect sum(teama_point s) as pts1
stats1.movenext
loop

In addition, you should take Anthony's advice and rewrite. I know that I
wouldn't want to maintain all that goop.

Bob Lehmnan

"Jeff" <gi*****@adelph ia.net> wrote in message
news:1I******** ************@ad elphia.com...
Hey gang.

I have a script that gets stats from a mssql db, and then inserts those
stats into a temp table. where i can work with them as i wish.

the problem is it isn't looping through all the records, and i am not
sure
why.

i know the script isn't perfect, as it is open to sql injection and stuff
like that, but once the site is complete, i will be putting in checks and
things like that to stop it.

any ideas why it isn't looping??

<% set stats1 = conn.execute("s elect team_name from teams where idivision

=
'ACA'")
do while not stats1.eof
varT = stats1.fields.i tem("team_name" ).value

set check1 = conn.execute("s elect count(teama) as cnt1 from matches
where teama = '" & varT & "'")
bam1 = check1.fields.i tem("cnt1").val ue

if bam1 = 0 then
varW1 = 0
elseif bam1 = 1 then
set stats1 = conn.execute("s elect teama_points from matches where
teama = '" & varT & "'")
varW1 = stats1.fields.i tem("teama_poin ts").value
elseif bam1 > 1 then
set stats1 = conn.execute("s elect sum(teama_point s) as pts1 from
matches where teama = '" & varT & "'")
varW1 = stats1.fields.i tem("pts1").val ue
end if
set check2 = conn.execute("s elect count(teamb) as cnt11 from
matches
where teamb = '" & varT & "'")
bam2 = check2.fields.i tem("cnt11").va lue

if bam2 = 0 then
varL12 = 0
elseif bam2 = 1 then
set stats21 = conn.execute("s elect teamb_points from matches where
teamb = '" & varT & "'")
varL12 = stats21.fields. item("teamb_poi nts").value
elseif bam2 > 1 then
set stats21 = conn.execute("s elect sum(teamb_point s) as pts2 from
matches where teamb = '" & varT & "'")
varL12 = stats21.fields. item("pts2").va lue
end if
set stats2 = conn.execute("s elect count(iwinner) as wins from

matches
where iwinner = '" & varT & "'")
varW2 = stats2.fields.i tem("wins").val ue

set stats3 = conn.execute("s elect count(iloser) as losses from

matches
where iloser = '" & varT & "'")
varL1 = stats3.fields.i tem("losses").v alue

total_points = varW1 + varL12
var_total = bam1 + bam2
if bam1 = 0 then
win_pct = 0
else
win_pct = formatnumber((( bam1 / var_total)*100) ,0)
end if
strSQL = "insert into temp (team, points, wins, losses, win_pct)
values ('" & varT & "', " & total_points & ", " & varW2 & ", " & varL1 &

",
" & win_pct & ")"
conn.execute (strSQL)

stats1.movenext
loop
%>


May 12 '06 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

8
4059
by: kaptain kernel | last post by:
i've got a while loop thats iterating through a text file and pumping the contents into a database. the file is quite large (over 150mb). the looping causes my CPU load to race up to 100 per cent. Even if i remove the mysql insert query and just loop through the file , it still hits 100 per cent CPU. This has the knock on effect of slowing my script down so that mysql inserts are occuring every 1/2 second or so.
2
3999
by: ensnare | last post by:
Hi all, I'm using a database session handler and am looking to loop through data residing in the sessions table to make a 'Users online' array. I've found that using urldecode on the data column gives me a serialized string of varname|datatype:data; for each variable. I'm having a bit of trouble with deserializing the data. I could always use regexps to grab the specific data I want, but I'm
2
2574
by: Ivo | last post by:
Hi, I have an audio file (.mid or .wav or .mp3) in an object element: <object id="snd" classid="CLSID:22D6F312-B0F6-11D0-94AB-0080C74C7E95" codebase="http://activex.microsoft.com/activex/controls/mplayer/en/nsmp2inf. cab#Version=5,1,52,701" type="application/x-oleobject" width="282" height="53"> <param name="FileName" value="Tjaikovsky_-_The_sorcerers_apprentice.mid" /> <param name="loop" value="true" /> <... more presentational params...
45
7460
by: Trevor Best | last post by:
I did a test once using a looping variable, first dimmed as Integer, then as Long. I found the Integer was quicker at looping. I knew this to be true back in the 16 bit days where the CPU's (80286) word size was 16 bits same as an integer. Now with a 32 bit CPU I would have expected the long to be faster as it's the same size as the CPU's word size so wouldn't need sawing in half like a magician's assistant to calculate on like an...
5
2414
by: masood.iqbal | last post by:
My simplistic mind tells me that having local variables within looping constructs is a bad idea. The reason is that these variables are created during the beginning of an iteration and deleted at the end of the iteration. Kindly note that I am not talking about dynamically allocating memory within a loop, which is a perfectly valid operation (albeit with a chance of resulting in a memory leak, unless we are careful). The most common...
1
5390
by: Diva | last post by:
Hi, I have a data grid in my application. It has 20 rows and I have set the page size as 5. I have a Submit button on my form and when I click on Submit, I need to loop through the rows in the datagrid. Using the items collection just gives me the 5 rows that are displayed on the screen. Is there any way of looping through all the rows in the grid?
5
4000
by: johnb41 | last post by:
I need to loop through a bunch of textbox controls on my form. The order of the loop is very important. For example, the top one must be read first, then the one below it, etc. My first attempt was to put the controls in a Panel, and then loop through the controls collection of the panel. What happened is it read the controls from the bottom of my form to the top! So I tried a more manual process. I named each textbox like this:
0
1962
by: anthon | last post by:
Hi all - first post! anywho; I need to create a function for speeding up and down a looping clip. imagine a rotating object, triggered by an action, and slowly decreasing in speed, till it reaches a point 0 (compare a hand spinning a fortune wheel). now, this is quite an easy this to achieve, since you just have to set an interval to increase rotation, with a value that decreases over time (on every call), till it finally reaches a...
20
2845
by: Ifoel | last post by:
Hi all, Sorry im beginer in vb. I want making programm looping character or number. Just say i have numbers from 100 to 10000. just sample: Private Sub Timer1_Timer() if check1.value= 1 then
2
2080
by: Davaa | last post by:
Dear all, I am a student making a MS Form application in C++. I would ask a question about "Timer". Sample code which I am developing is below. private: System::Void buttonStart_Click(System::Object^ sender, System::EventArgs^ e){ for (int i=1; i<=10; i++){ String^ strValue; FunctionName(&strValue); int interval=1000; // time...
0
8821
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9340
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9196
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
9047
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
6646
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5967
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4477
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4738
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3175
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.