using a database field to 'trim' itself... 1

[SirLars]

hi.. me again.. hehe..

since i've completed my first project.. and it was a success.. ty for the help...

i've moved on to a bigger and bolder project.. using a database to signup players.. instead of the admistrator signing in the players...

i've got the signup page working great..
it adds players to the database as they sign up...

but before i can apply this database to the forms i created before... i need to be able to 'confirm' each player...

i created a script that will read the database and create a form with yes/no radio buttons to confirm each player...
(sometimes players dont show up...)

here's the form script....

<form method=&quot;POST&quot; action=&quot;trimdbase.asp&quot;>

<table BORDER=&quot;1&quot; align=&quot;center&quot; width=&quot;500&quot;>
<tr>
<td align=&quot;center&quot; rowspan=&quot;2&quot; bgcolor=&quot;#0000FF&quot;><font color=&quot;#FFFFFF&quot;><b>#</b></font></td>
<td align=&quot;center&quot; rowspan=&quot;2&quot; bgcolor=&quot;#0000FF&quot;><font color=&quot;#FFFFFF&quot;><b>Player Name</b></font></td>
<td align=&quot;center&quot; rowspan=&quot;2&quot; bgcolor=&quot;#0000FF&quot;><font color=&quot;#FFFFFF&quot;><b>Email</b></font></td>
<td align=&quot;center&quot; colspan=&quot;2&quot; bgcolor=&quot;#0000FF&quot;><font color=&quot;#FFFFFF&quot;><b>Confirmed</b></font></td>
</tr>
<tr>
<td align=&quot;center&quot; bgcolor=&quot;#0000FF&quot;><font color=&quot;#FFFFFF&quot;><b>Yes</b></font></td>
<td align=&quot;center&quot; bgcolor=&quot;#0000FF&quot;><font color=&quot;#FFFFFF&quot;><b>No</b></font></td>
</tr>

<%
dim counter
counter=1
' Move to the first record
rs.movefirst

' Start a loop that will end with the last record
do while not rs.eof

%>

<tr>
<td align=&quot;center&quot;><% =counter %></td>

<td align=&quot;center&quot;><%= rs(&quot;playername&quot;) %></td>

<td align=&quot;center&quot;><%= rs(&quot;playeremail&quot;) %></td>

<td align=&quot;center&quot; bgcolor=&quot;#008000&quot;><input type=&quot;radio&quot; name=&quot;confirm<% =counter %>&quot; value=&quot;Yes&quot;></td>

<td align=&quot;center&quot; bgcolor=&quot;#FF0000&quot;><input type=&quot;radio&quot; name=&quot;confirm<% =counter %>&quot; value=&quot;No&quot; checked></td>

</tr>

<%
counter=counter+1
' Move to the next record
rs.movenext
' Loop back to the do statement
loop %>

</table>
<input type=&quot;hidden&quot; name=playercount value=&quot;<% =counter-1 %>&quot;>
<p align=&quot;center&quot;><input type=&quot;submit&quot; value=&quot;Submit&quot; name=&quot;B1&quot;><input type=&quot;reset&quot; value=&quot;Reset&quot; name=&quot;B2&quot;><br></form>

and this works nicely...

so.. now the database/tourney administrator has a form with each of the players in it and a button to confirm/delete each player...

i want to submit this data to an asp script to "trim" the database... confirming the players that showed up...

so now i have a bunch of form variables ( confirm1, confirm2..etc.) passed on to the next page

so that script will loop thru the form data.. and insert the confirm(counter) value into the "confirm" field on the database
(so when the tournament is ready to start... it can ignore the 'unconfirmed' players when creating the pairings tables)...

this is where i've come to a stumbling block...
what would be the best/easiest way to do this?

would it be better/easier to loop thru the confirm1,confirm2 values and delete the unconfirmed records?
OR
would it be better/easier to just add the values to the field...

 

[smbure]

SirLars,

I see what you are doing and I was wondering if the "confirm" field in the database is a bit field. If not this would make your life a bit easier (no pun intended). If not confirmed, just change the bit to 0, and 1 for a confirmation. Also, it may be easier to bring back a primary key value (if possible) from the table and plug it in to the script like this:

<td align=&quot;center&quot; bgcolor=&quot;#008000&quot;><input type=&quot;radio&quot; name=&quot;confirm<%=rs(&quot;PrimaryKey&quot%>value=&quot;Yes&quot;></td>

<td align=&quot;center&quot; bgcolor=&quot;#FF0000&quot;><input type=&quot;radio&quot; name=&quot;confirm<%=rs(&quot;PrimaryKey&quot%>&quot; value=&quot;No&quot; checked></td>

This would give you a real easy way to identify what record needs the update.

This is just an opinion so take this with a grain of salt!!

hope this helps!
smbure

 

[SirLars]

ty smbure... it is a bit field...and that isn't what is not working... and the counter is the same as the &quot;primary key&quot; value so changing that (while being a little easier to code) doesn't change the results of the form...

well i was &quot;shopping/surfing&quot; around for ideas.. clues or hints to this problem.. and think i found it.. but i'm not familiar with using sets, for batch deletes...

what am i doing wrong?

(playercount will be the #of players signed up.. AND will be equal to the #of records in the database)

<%
dim counter
dim playerstodelete
dim confirmed

playerstodelete=&quot;&quot;
counter=1

do while not counter = Request.form(&quot;playercount&quot;)
  confirmed= &quot;confirm&quot; & counter

if Request.form(confirmed)=&quot;No&quot; then
  playerstodelete=playerstodelete & counter &  &quot;, &quot;
end if

counter=counter+1

loop

' add a final value to the playerstodelete set
playerstodelete=playerstodelete & &quot;99&quot;

'delete the set from the database
accessdb=&quot;/fpdb/signup&quot; 
cn=&quot;DRIVER={Microsoft Access Driver (*.mdb)};&quot;
cn=cn & &quot;DBQ=&quot; & server.mappath(accessdb)
set rs = server.createobject(&quot;ADODB.Recordset&quot;)
sql = &quot;delete from Records WHERE ID IN (&quot; & playerstodelete & &quot;)&quot;
rs.open sql, cn

'Output the new database
%>

i fear i have set something wrong.... as it times out on the server.. giving me this

error 'ASP 0113'
Script timed out

/tournaments/template/single_elim_16/trimdbase.asp

The maximum amount of time for a script to execute was exceeded. You can change this limit by specifying a new value for the property Server.ScriptTimeOut or by changing the value in the IIS administration tools.

help anyone???

 

[smbure]

This looks perfect, the only thing I may say is verify that the loop has a stop point at the

do while not counter = Request.form(&quot;playercount&quot

this is the only place that would be suspect of timing out you script...

 

[Swany]

old code

counter=1

do while not counter = Request.form("playercount")
  confirmed= "confirm" & counter

if Request.form(confirmed)="No" then
  playerstodelete=playerstodelete & counter &  ", "
end if

counter=counter+1

loop

new code
PlayerCount = request.form(&quot;PlayerCount&quot

redim Players(0 to PlayerCount-1)

if lcase(request.form(confirmed)) = &quot;no&quot; then
'Not confirmed, so delete

'replace do loop with for/next
for i = 0 to PlayerCount - 1
'replace string concat(&) with array assignment
Players(i) = i
next

'Make a comma seperated list of all the players
PlayersToDelete = join(Players, &quot;,&quot
else
'Confirmed, so don't delete anything
response.end
end if

'Database code goes here
....
[/color]

 

[link9]

Try replacing it with

do while counter < cint(Request.form(&quot;playercount&quot)

Here, you guarantee that the server is going to evaluate it based on an integer value and...

I have troubles with vbscript sometimes evaluating boolean values as you would expect it to -- hence not using the 'not' operator...

Just some ideas -- and don't forget you can always change your script timeout -- although I wouldn't think that this amount of work would timeout the script -- so it's probably a logic issue.


Paul Prewett

 

[Swany]

The main reason I reordered your code was for speed

1) you reference the request object every time you loop.
that is a bad thing. If you need to repeatedly access
a request variable, store it in a local variable. It
will be MUCH faster

2) If the IF inside the loop evaluated to true once, it
would evaluate to true everytime because you were
always referencing the same request variable (see #1 as
well) so I moved the IF outside the loop where it makes
more sense.

3) For loops handle the initialization and incrementing of
counter variables for you, so if your only goal is to
increment a count, use a FOR loop. Much more elegant
and actually faster.

4) String concatenation is slow, especially in loops. Using
an array to hold the variables, then constructing the
comma separated list will be much faster.

 

[SirLars]

i'd like to thank you guys so much...

as i said before... i'm a beginner (again) at this programming thing...

it's been over 10 years since i had written any code that wasn't just basic html...

i had begun these projects with a bit of trepidation, but now i'm kinda excited to pull this stuff off.. this is helping me alot and i would just like to say thanks again...

esp. for the coding 'tips/reasoning' swany.

i'm sure i'll be posting again when i start round robin tournaments... lol

 

[Swany]

Thanks for saying thanks , and you are welcome.

 

[SirLars]

actually..

as i was working on something else last night i thought a much better way would be to pass a comma delimited list instead of a (sometimes large) group of variables to this &quot;database trimmer&quot;

and did that by using checkboxs in the form and passing off two lists confirm / delete to the asp page

so i've done that instead.. uses alot less server resources...

collect comma separated lists
dim playerstodelete, playerstoconfirm
playerstodelete=Request.form("delete")
playerstoconfirm=Request.form("confirm")

'Open a connection to the database
dim accessdb
accessdb="/fpdb/signup" 
dim cn
cn="DRIVER={Microsoft Access Driver (*.mdb)};"
cn=cn & "DBQ=" & server.mappath(accessdb)
dim rs
set rs = server.createobject("ADODB.Recordset")
'Now, use the SQL set notation to delete all of the records
'specified by playerstodelete
Dim delSQL
delSQL = "DELETE FROM Results " & "WHERE ID IN (" & playerstodelete & ")"

'Now, use the SQL set notation to confirm all of the records
'specified by playerstoconfirm

Dim itstrue
itstrue="True"
dim confirmSQL
confirmSQL = "UPDATE Results SET confirmed = " & itstrue & " in (" & playerstoconfirm & ")"
			 
rs.Execute delSQL
rs.Execute confirmSQL
	
'Clean up
rs.Close
Set rs = Nothing

but my SQL commands are getting this output and error

Confirming players :1, 2, 4, 6, 9 Deleting players :3, 8
Microsoft VBScript runtime error '800a01b6'

Object doesn't support this property or method: 'Execute'

/tournaments/template/single_elim_16/trimdbase.asp, line 41

line 41: rs.Execute delSQL


ugh what is wrong with those sql statements???
this is (nearly) taken right from 4guysfromrolla batch deletes demo..

Lars

 

[Swany]

cn.execute
not
rs.execute

 

[SirLars]

accessdb=&quot;/fpdb/signup&quot;
dim cn
cn=&quot;DRIVER={Microsoft Access Driver (*.mdb)};&quot;
cn=cn & &quot;DBQ=&quot; & server.mappath(accessdb)

dim rs
set rs = server.createobject(&quot;ADODB.Recordset&quot

Dim delSQL
delSQL = &quot;DELETE FROM Results &quot; & &quot;WHERE ID IN (&quot; & playerstodelete & &quot&quot;

Dim itstrue
itstrue=&quot;True&quot;
dim confirmSQL
confirmSQL = &quot;UPDATE Results confirmed = &quot; & itstrue & &quot; WHERE ID IN (&quot; & playerstoconfirm & &quot&quot;

cn.Execute confirmSQL
cn.Execute delSQL

u sure?
i tried that... and i get this

Confirming players :1, 2, 4, 6, 7, 9 Deleting players :3, 8
Microsoft VBScript runtime error '800a01a8'

Object required: 'DRIVER={Microsoft Ac'

/tournaments/template/single_elim_16/trimdbase.asp, line 37

line 37: cn.Execute confirmSQL

 

[Swany]

set cn = server.createobject(&quot;ADODB.connection&quot
DSN = &quot;DRIVER={Microsoft Access Driver (*.mdb)};DBQ=&quot; & server.mappath(accessdb)

cn.open DSN

...

cn.execute confirmSQL
cn.execute delSQL