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
%>
5  1409 
"Jeff" <gi*****@adelphia.net> wrote in message
news:1I********************@adelphia.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("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?
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*@yadayadayada.com> wrote in message
news:%2****************@TK2MSFTNGP04.phx.gbl...
"Jeff" <gi*****@adelphia.net> wrote in message
news:1I********************@adelphia.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("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?
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
"Jeff" <gi*****@adelphia.net> wrote in message
news:1I********************@adelphia.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("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
%>
"Jeff" <gi*****@adelphia.net> wrote in message
news:Bb********************@adelphia.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.
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****@dontbotherme.zzz> wrote in message
news:uy**************@TK2MSFTNGP05.phx.gbl... 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
"Jeff" <gi*****@adelphia.net> wrote in message
news:1I********************@adelphia.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("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
%>
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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...
|
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.
...
|
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"...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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:
...
|
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.
...
|
by: concettolabs |
last post by:
In today's business world, businesses are increasingly turning to PowerApps to develop custom business applications. PowerApps is a powerful tool...
|
by: better678 |
last post by:
Question:
Discuss your understanding of the Java platform. Is the statement "Java is interpreted" correct?
Answer:
Java is an object-oriented...
|
by: CD Tom |
last post by:
This only shows up in access runtime. When a user select a report from my report menu when they close the report they get a menu I've called Add-ins...
|
by: Naresh1 |
last post by:
What is WebLogic Admin Training?
WebLogic Admin Training is a specialized program designed to equip individuals with the skills and knowledge...
|
by: antdb |
last post by:
Ⅰ. Advantage of AntDB: hyper-convergence + streaming processing engine
In the overall architecture, a new "hyper-convergence" concept was...
|
by: AndyPSV |
last post by:
HOW CAN I CREATE AN AI with an .executable file that would suck all files in the folder and on my computerHOW CAN I CREATE AN AI with an .executable...
|
by: Arjunsri |
last post by:
I have a Redshift database that I need to use as an import data source. I have configured the DSN connection using the server, port, database, and...
|
by: WisdomUfot |
last post by:
It's an interesting question you've got about how Gmail hides the HTTP referrer when a link in an email is clicked. While I don't have the specific...
|
by: Matthew3360 |
last post by:
Hi,
I have been trying to connect to a local host using php curl. But I am finding it hard to do this. I am doing the curl get request from my web...
| |
