Not looping

[Jeff]

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("select team_name from teams where idivision =
'ACA'")
do while not stats1.eof
varT = stats1.fields.item("team_name").value

set check1 = conn.execute("select count(teama) as cnt1 from matches
where teama = '" & varT & "'")
bam1 = check1.fields.item("cnt1").value

if bam1 = 0 then
varW1 = 0
elseif bam1 = 1 then
set stats1 = conn.execute("select teama_points from matches where
teama = '" & varT & "'")
varW1 = stats1.fields.item("teama_points").value
elseif bam1 > 1 then
set stats1 = conn.execute("select sum(teama_points) as pts1 from
matches where teama = '" & varT & "'")
varW1 = stats1.fields.item("pts1").value
end if


set check2 = conn.execute("select count(teamb) as cnt11 from matches
where teamb = '" & varT & "'")
bam2 = check2.fields.item("cnt11").value

if bam2 = 0 then
varL12 = 0
elseif bam2 = 1 then
set stats21 = conn.execute("select teamb_points from matches where
teamb = '" & varT & "'")
varL12 = stats21.fields.item("teamb_points").value
elseif bam2 > 1 then
set stats21 = conn.execute("select sum(teamb_points) as pts2 from
matches where teamb = '" & varT & "'")
varL12 = stats21.fields.item("pts2").value
end if


set stats2 = conn.execute("select count(iwinner) as wins from matches
where iwinner = '" & varT & "'")
varW2 = stats2.fields.item("wins").value

set stats3 = conn.execute("select count(iloser) as losses from matches
where iloser = '" & varT & "'")
varL1 = stats3.fields.item("losses").value

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


%>

 

[Anthony Jones]

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("select team_name from teams where idivision =
'ACA'")
do while not stats1.eof
varT = stats1.fields.item("team_name").value

set check1 = conn.execute("select count(teama) as cnt1 from matches
where teama = '" & varT & "'")
bam1 = check1.fields.item("cnt1").value

if bam1 = 0 then
varW1 = 0
elseif bam1 = 1 then
set stats1 = conn.execute("select teama_points from matches where
teama = '" & varT & "'")
varW1 = stats1.fields.item("teama_points").value
elseif bam1 > 1 then
set stats1 = conn.execute("select sum(teama_points) as pts1 from
matches where teama = '" & varT & "'")
varW1 = stats1.fields.item("pts1").value
end if


set check2 = conn.execute("select count(teamb) as cnt11 from matches
where teamb = '" & varT & "'")
bam2 = check2.fields.item("cnt11").value

if bam2 = 0 then
varL12 = 0
elseif bam2 = 1 then
set stats21 = conn.execute("select teamb_points from matches where
teamb = '" & varT & "'")
varL12 = stats21.fields.item("teamb_points").value
elseif bam2 > 1 then
set stats21 = conn.execute("select sum(teamb_points) as pts2 from
matches where teamb = '" & varT & "'")
varL12 = stats21.fields.item("pts2").value
end if


set stats2 = conn.execute("select count(iwinner) as wins from matches
where iwinner = '" & varT & "'")
varW2 = stats2.fields.item("wins").value

set stats3 = conn.execute("select count(iloser) as losses from matches
where iloser = '" & varT & "'")
varL1 = stats3.fields.item("losses").value

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?

 

[Jeff]

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

 

[Bob Lehmann]

Probably because you are assigning different result sets to stats1 within
the loop.

set stats1 = conn.execute("select team_name from teams
do while not stats1.eof
set stats1 = conn.execute("select teama_points from matches
set stats1 = conn.execute("select sum(teama_points) 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

 

[Anthony Jones]

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.

 

[Jeff]

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