| Author |
 Topic  |
|
Alfred
Senior Member
USA
1527 Posts |
 Posted - 11 September 2003 : 12:41:28
|
How can I do this?
I want to run a loop and get data from each record in the loop:
quote:
strSql = "SELECT * FROM FORUM_MEMBERS where LADDER_SPOT<>0 ORDER BY LADDER_SPOT ASC"
set mySpots = my_Conn.Execute(strSql)
mySpots.movefirst
DO WHILE NOT mySpots.EOF
'now I want to collect the data from the current record, and call it lastSpot:
strSql = "SELECT ladder_spot ???????"
set lastSpot = my_Conn.Execute(strSql)
'now I want to move to the next record and update it:
mySpots.movenext
strSQL = "UPDATE FORUM_MEMBERS SET LADDER_SPOT = lastSpot + 1"
my_Conn.execute(strSql)
loop
|
Alfred
The Battle Group
CREDO
|
Edited by - Alfred on 11 September 2003 12:54:30 |
|
|
Nikkol
Forum Moderator
USA
6907 Posts |
Posted - 11 September 2003 : 14:32:09
|
| huh? your first sql statement is grabbing everything from forum_members you don't need to make any addition calls. maybe if you explain what you are trying to do, someone can help better. |
Nikkol ~ Help Us Help You | ReadMe | 3.4.03 fixes | security fixes ~ |
 |
|
|
Alfred
Senior Member
USA
1527 Posts |
Posted - 11 September 2003 : 14:38:24
|
Yes, it is grabbing everything "as it is" then.
I had put what I want to do between the code lines (commented out).
To put it in other words, I need to update all "ladder_spot" fields to be in chronological sequence, i.e. 1,2,3, etc., whenever they get gaps between the numbers, i.e. 1,2,4,7, etc. |
Alfred
The Battle Group
CREDO
|
|
|
|
Nikkol
Forum Moderator
USA
6907 Posts |
Posted - 11 September 2003 : 14:43:45
|
okay, well your code doesn't do that.
you should just simply loop through and assign the ladder spot, no need to figure out waht the current spot is if your first sql statement orders them anyway. |
Nikkol ~ Help Us Help You | ReadMe | 3.4.03 fixes | security fixes ~ |
|
|
|
Alfred
Senior Member
USA
1527 Posts |
Posted - 11 September 2003 : 14:56:16
|
It orders them, but I need to have the numbers without gaps for other files, which display the ladder.
Otherwise, it would give an error each time it looks for a number which is missing. |
Alfred
The Battle Group
CREDO
|
|
|
|
Nikkol
Forum Moderator
USA
6907 Posts |
Posted - 11 September 2003 : 15:16:22
|
right, you've order them so just loop through and reset the ladder spot according to the the order in the loop, i.e.
iCount = 0
do while not rs.eof
iCount = iCount + 1
strsql = "UPDATE FORUM_MEMBERS SET ladder_spot = " & iCount & " WHERE MEMBER_ID = " & rs("MEMBER_ID")
my_Conn.execute(strsql)
rs.movenext
loop |
Nikkol ~ Help Us Help You | ReadMe | 3.4.03 fixes | security fixes ~ |
|
|
|
Alfred
Senior Member
USA
1527 Posts |
Posted - 11 September 2003 : 15:30:14
|
Does the "icount" count all records, or only those with actual numbers?
There are many which have no number and many which have a zero. |
Alfred
The Battle Group
CREDO
|
|
|
|
Nikkol
Forum Moderator
USA
6907 Posts |
Posted - 11 September 2003 : 15:32:56
|
| you aren't including zero in your sql statement. if there is no number, then you should modify your sql statement so that it says ladder_spot > 0 . |
Nikkol ~ Help Us Help You | ReadMe | 3.4.03 fixes | security fixes ~ |
|
|
|
Alfred
Senior Member
USA
1527 Posts |
Posted - 11 September 2003 : 15:37:37
|
This is how I had the statement: quote:
'## GET POSITIONS OF PLAYERS #########################################
strSql = "SELECT * FROM FORUM_MEMBERS where LADDER_SPOT<>0 ORDER BY LADDER_SPOT ASC"
set mySpots = my_Conn.Execute(strSql)
|
Alfred
The Battle Group
CREDO
|
|
|
|
Alfred
Senior Member
USA
1527 Posts |
|
|
Nikkol
Forum Moderator
USA
6907 Posts |
Posted - 11 September 2003 : 16:01:46
|
| you just cut and paste my example. you have to actually apply it to your code. when i put "rs" ... that's just generic for recordset, you need to modify your code to make it look like my example. |
Nikkol ~ Help Us Help You | ReadMe | 3.4.03 fixes | security fixes ~ |
|
|
|
Alfred
Senior Member
USA
1527 Posts |
Posted - 11 September 2003 : 16:12:41
|
Well, I did suspect the "rs", but always try it as it is first.
Will work on that part now.
Thanks, Nikkol. |
Alfred
The Battle Group
CREDO
|
|
|
|
Alfred
Senior Member
USA
1527 Posts |
|
|
Nikkol
Forum Moderator
USA
6907 Posts |
Posted - 11 September 2003 : 23:22:54
|
Read my earlier post about this:
quote:
Originally posted by Nikkol
you aren't including zero in your sql statement. if there is no number, then you should modify your sql statement so that it says ladder_spot > 0 .
This of course assumes that no value at all for ladder spot is the same as zero. Why don't you just make the default value for that field zero and fill in all existing empty values with zero? |
Nikkol ~ Help Us Help You | ReadMe | 3.4.03 fixes | security fixes ~ |
Edited by - Nikkol on 11 September 2003 23:24:38 |
|
|
|
Alfred
Senior Member
USA
1527 Posts |
Posted - 11 September 2003 : 23:34:23
|
I specified "<>0", and I think it does ignore all zeroes as it should.
But it does not close gaps in the numbers.
For instance, if there is no 16, there won't be any 16 after the file is run either. |
Alfred
The Battle Group
CREDO
|
|
|
|
Nikkol
Forum Moderator
USA
6907 Posts |
Posted - 12 September 2003 : 06:18:04
|
| alfred, it depends on what you want to do. if you are wanting just to reorder the existing numbers and leave alone zeroes and blank entries, then use >0 not <>0 |
Nikkol ~ Help Us Help You | ReadMe | 3.4.03 fixes | security fixes ~ |
|
|
|
Topic |
|
Topic Locked
