Need help with 2006 brainjar football pool

[stpmtp]

Hello,

I have been nominated by my office to try to make the

http://www.brainjar.com/asp/pool/

football pool into a confidence pool. The Main task is to change the tie into points so I have done this

I added a field in the db called points
then change the code

original

 <td align="center"><input class="radio" name="pick-<% = games(i).id %>" type="radio" value="<% = TIE_STR %>" <% = IsChecked(pick, TIE_STR) %> />

Change to

      Points <select name="points"><option>
	<option value="16"> 16 <option value="15"> 15 <option value="14"> 14 <option value="13"> 13 <option value="12"> 12 <option value="11"> 11 <option value="10"> 10 <option value="9"> 9 <option value="8"> 8 <option value="7"> 7 <option value="6"> 6 <option value="5"> 5 <option value="4"> 4 <option value="3"> 3 <option value="2"> 2 <option value="1"> 1
	</select></td>

and here is where I ran into problems. I cannot get the selected records into the db I think I am missing the connection between the "points" and the db

 

[snowboardr]

your going to have post some more code than that, and be more specific on your problem that you are having, because I am seriously lost.

Jason

[red]Army[/red] : [white]Combat Engineer[/white] : [blue]21B[/blue]

 

[stpmtp]

Here is the whole code
for the entry.asp I have added the drop down in line 500
I need it to upload the results to the points field on the Picks table of the NFL.mdb

Does this make sence?

<%@LANGUAGE="VBSCRIPT"%>


<% '***************************************************************************
   '* ASP Football Pool                                                       *
   '*                                                                         *
   '* Do not remove this notice.                                              *
   '*                                                                         *
   '* Copyright 1999-2004 by Mike Hall                                        *
   '* Please see [URL unfurl="true"]http://www.brainjar.com[/URL] for documentation and terms of use.  *
   '*                                                                         *
   '* Allows players to enter or update an entry for a given week's pool.     *
   '***************************************************************************

   subTitle = "Entry Form" %>

<!-- #include file="protect.asp" -->
<!-- #include file="common.asp" -->
<!-- #include file="header.asp" -->

<% 'Open the database.

   call OpenDB()

   'Default to current week if no valid number was given.

   week = Request("week")
   if not IsNumeric(week) then
     week = CurrentWeek()
   else
     week = Round(week)
     if week < 1 or week > NumberOfWeeks() then
       week = CurrentWeek()
     end if
   end if

   'Initialize global lock out variable.

   dateNow = CurrentDateTime()
   allLocked = false

   'Create an array of game objects

   dim games
   n = NumberOfGames(week)
   redim games(n - 1)

   sql = "select Schedule.*," _
      & " Teams.City as VCity, Teams.Name as VName, Teams.DisplayName as VDisplayName," _
      & " Teams2.City as HCity, Teams2.Name as HName, Teams2.DisplayName as HDisplayName" _
      & " from (Schedule inner join Teams on Schedule.VisitorID = Teams.TeamID)" _
      & " inner join Teams as Teams2 on Schedule.HomeID = Teams2.TeamID" _
      & " where Schedule.Week = " & week _
      & " order by Schedule.Date, Schedule.Time, Teams.City, Teams.Name"
   set rs = DbConn.Execute(sql)
   if not (rs.BOF and rs.EOF) then
     i = 0
     do while not rs.EOF
       set games(i) = new GameObj
       games(i).setData(rs)
       i = i + 1
       rs.MoveNext
     loop
   end if

   'If there's form data, check it.

   errorFlag = false
   if Request.ServerVariables("Content_Length") > 0 then
     tb = Request.Form("tb")

     'Administrator may not make entries.

     if Session("FootballPoolUsername") = ADMIN_USERNAME then
       call ErrorMessage("User '" & ADMIN_USERNAME & "' may not enter picks.")
       errorFlag = true

     'If all games locked, no changes allowed.

     elseif allLocked then
       call ErrorMessage("All games for this week have been locked out, changes not accepted.")
       errorFlag = true

     'Check tie breaker value.

     else
       if tb = "" then
         call ErrorMessage("You must enter a point total for the tie breaker, changes not accepted.")
         errorFlag = true
       elseif not IsNumeric(tb) then
         call ErrorMessage("Tie breaker point total must be numeric, changes not accepted.")
         errorFlag = true
       elseif tb < 0 or CDbl(tb) <> Round(CDbl(tb)) then
         call ErrorMessage("Invalid tie breaker point value, changes not accepted.")
         errorFlag = true
       end if

     end if

     'Check for picks in locked out games

     if not allLocked then
       anyLocked = false
       for i = 0 to UBound(games)
         if Request.Form("pick-" & games(i).id) <> "" and games(i).isLocked then
           call ErrorMessage(games(i).visitorName & " at " & games(i).homeName _
             & " has been locked, this change not accepted.")
           errorFlag = true
           anyLocked = true
         end if
       next
       if anyLocked then
         call ErrorMessage("Please resubmit to update remaining picks.")
       end if
     end if

     'If there were no errors, process the data and redirect the user to the
     'results page.

     if not errorFlag then
       for i = 0 to UBound(games)
         pick = Request.Form("pick-" & games(i).id)
         if pick = "" then
           pick = GetPick(games(i).id)
         end if
         sql = "delete from Picks" _
            & " where Username = '" & Session("FootballPoolUsername") & "'" _
            & " and GameID = " & games(i).id
         set rs = DbConn.Execute(sql)
         sql = "insert into Picks" _
            & " (Username, GameID, Pick)" _
            & " values('" & Session("FootballPoolUsername") & "'," _
            & " '" & games(i).id & "', " _
            & " '" & pick & "')"
         set rs = DbConn.Execute(sql)
       next
       if tb <> "" then
         sql = "delete from TieBreaker" _
            & " where Week = " & week _
            & " and Username = '" & Session("FootballPoolUsername") & "'"
         set rs = DbConn.Execute(sql)   
         sql = "insert into TieBreaker" _
            & " (Week, Username, TBPoints)" _
            & " values(" & week & ", '" & Session("FootballPoolUsername") & "'," _
            & " " & tb & ")"
         set rs = DbConn.Execute(sql)
       end if
       Response.Redirect("results.asp?week=" & week)
     end if

   end if

   'Build the form. %>
<http><head>



<script language="javascript" type="text/javascript">
function checkform() 
{	
	var ptid = 2;
	var myform = window.document.forms[0];
	var message = '';
	for (loop=1; loop <17; loop++)

			{
				var pickid = 'pickid' + loop;
				var roadcheck = eval('myform.'+pickid+'[0].checked'); 
				var homecheck = eval('myform.'+pickid+'[1].checked'); 
				if (roadcheck || homecheck) {
					// all good
				} else {
					
					var message = 'Please pick all the games.  You missed at least one.';
				}
				
			}
	if (message == '') {	
		checkRanks();
	} else { 
		alert(message);
	} 
	return true;
}


function sethome(num) 
{	
	var myform = window.document.forms[0];
	for (loop=1; loop <17; loop++)

			{
				var pickid = 'pickid' + loop;
				// alert ('pickid = '+pickid);
				eval('myform.'+pickid+'[num].checked = true'); 
			}

	return true;
}


function setfavorites(dog) 
{	
	var myform = window.document.forms[0];
	
	
	var favorite1 = 1;
	
	var favorite2 = 1;
	
	var favorite3 = 1;
	
	var favorite4 = 1;
	
	var favorite5 = 1;
	
	var favorite6 = 1;
	
	var favorite7 = 1;
	
	var favorite8 = 1;
	
	var favorite9 = 1;
	
	var favorite10 = 1;
	
	var favorite11 = 1;
	
	var favorite12 = 1;
	
	var favorite13 = 1;
	
	var favorite14 = 1;
	
	var favorite15 = 1;
	
	var favorite16 = 1;
	
	for (loop=1; loop <17; loop++)

			{
				var pickid = 'pickid' + loop;
				var num = eval('favorite'+loop);
				if (dog == 1) { num = 1-num; }
				// alert ('pickid = '+pickid);
				eval('myform.'+pickid+'[num].checked = true'); 
			}

	return true;
}



function fillranks() 
{
	var myform = window.document.forms[0];
	
	var games = 17;
	for (loop=1; loop < games; loop++)
		{
			var pickid = 'rank' + loop;
			eval('myform.'+pickid+'.selectedIndex = '+eval(games-loop)); 
		}
		
	return true;
}


function randomRanks()
	{
		// if you copy this give me some credit by including my comments
		// randomRanks by John Cranston
		// help from Chris Grim and Webmonkey.com
		
		var myform = window.document.forms[0];
		var RankArray = new Array();
		var games = 16;
		for (i=0; i < games; i++)
			{
				RankArray[i]=i+1;	
			}
		for (i=1; i < games+1; i++)
		{
			var testrand= Math.random()*(RankArray.length) // 0 to 4.99
			var randnum=Math.floor(testrand); // 0 to 4
			var index = RankArray[randnum];
			
			// say your RankArray is (1,2,3,4,5)
			// say your random number is 3
			// then your index is RankArray[3] = 4
			// index = 4 documnet.rank1[4].selected=true;
			// then your first rankdrop should be the 4th option or 12
			// set the temp array = rankarray
			// redefine the RankArray
			//  loop through the tempArray i = 0 to i=TempArray.length
			//  if TempArray[i] != RankArray[randnum]
			//  RankArray[n] = TempArray[i]
			// alert ('index= '+index+' RankArray.length ='+RankArray.length);
			eval('myform.rank'+i+'['+index+'].selected = true;');
			var tempArray = RankArray;
			var RankArray = new Array();
				var n = 0;
				for (j=0; j < tempArray.length; j++)
				{	
					if ( tempArray[j] != index) // if we aren't on the selected index
						{
						RankArray[n]=tempArray[j];	
						n++;
						}			
				}  // end rebuild RankArray
				
		}  // end loop through games
		
}  // end function




function checkRanks() {
	var myform = window.document.forms[0];
	var games = 16 + 1;
	var RankArray = new Array();
	for (loop=1; loop < games; loop++)
	{
		//eval(document.picks.rank+'loop'+.selectedIndex);
		var pickid = 'rank' + loop;
		var test = 	eval('myform.'+pickid+'.selectedIndex');
		RankArray[loop] = test;
	}
	var cont = 1;
	for (j=1; j < games; j++)  // check for ranks used more than once, loop 1
	{
		var pickid = 'rank' + j;
		var thisrank = 	eval('myform.'+pickid+'.selectedIndex');
		if (thisrank == '') {
			alert('Please make sure you enter a rank for every game.  You did not have a rank for game number '+ j +' on the card');
			return;
			}
		for (k=1; k < games; k++)  // loop 2
		{
			var thatrank = RankArray[k];
			var thisrankvalue = eval('myform.'+pickid+'['+thisrank+'].value')
			if (j != k && thisrank == RankArray[k] && cont == 1) {
				alert('Please use ranks only once.  You used the following rank twice: '+thisrankvalue); 
				cont = 0;
				return;  
				 }
		}// end for loop 2
	}  // end for loop 1
	
	// make sure every rank is used
	for (loop=1; loop < games; loop++)
	{
		var pickid = 'rank' + loop;
		var test = 	eval('myform.'+pickid+'.selectedIndex');
		if (test == 0) {  
		alert('Please select ranks for every game before submitting your picks.');
		cont = 0;
		return;
		}
	}
	
	if (cont == 1) {checkMNF();	} else { return; }
	// checkMNF();
}  // end function




	
function quickPick(ranks)
{
	var myform = window.document.forms[0];
	var games = 17;
	for (loop=1; loop < games; loop++)
	{
	
		var side = Math.round(Math.random()*1)   // number 0 to 0.99 rounded to 0 or 1
		eval('myform.pickid'+loop+'['+side+'].checked = true;')
	}
	
	if (ranks == 'Y')
		{
			randomRanks();
		}
		
	var tiebreaker = Math.round(20+Math.random()*40);   // number 0 to 39.99 + 20 rounded to int
	myform.MNF.value= tiebreaker;
	myform.sendmail.checked=true;
}


function checkMNF()
{
	var myform = window.document.forms[0];
	var myvalue = myform.MNF.value;
	var Chars = "0123456789.";
	var isnumeric = 1;
	for (var i = 0; i < myvalue.length; i++) {
       if (Chars.indexOf(myvalue.charAt(i)) == -1)
         { isnumeric = 0;}
    }
	
	
	if (myform.MNF.value <= 0 || myform.MNF.value > 999.9 || isnumeric == 0)
		{
		alert('Please enter a value between 1 and 999.9 for your MNF tiebreaker');
		myform.MNF.select();
		}
	else
		{
		myform.submit();
		}
}
</script>






</head><body>
<form action="<% = Request.ServerVariables("SCRIPT_NAME") %>" method="post">
<div><input name="week" type="hidden" value="<% = week %>" /></div>
<table width="717" cellpadding="0" cellspacing="0" class="main" id="points">
  <tr>
    <th align="left" colspan="9">Week
        <% = week %></th>
  </tr>
  <% for i = 0 to UBound(games)
     if Round(i / 2) * 2 = i then %>
  <tr align="right">
    <%   else %>
  </tr>
  <tr align="right" class="alt">
    <%   end if %>
    <td><% = WeekdayName(Weekday(games(i).datetime), true) %></td>
    <td><% = FormatDate(games(i).datetime) %></td>
    <td><% = FormatTime(games(i).datetime) %></td>
    <%   visitor = games(i).visitorName
     home = games(i).homeName
     tie = "&nbsp;"

     'If no pick was given for this game or the game is locked, check the
     'database for an existing value.

     pick = Request.Form("pick-" & games(i).id)
     if pick = "" or games(i).isLocked then
       pick = GetPick(games(i).id)
     end if

     'Get pick indicator for locked games.

     if games(i).isLocked then
       vpick = "&nbsp;"
       hpick = "&nbsp;"
       tpick = "&nbsp;"
       if pick = games(i).visitorID then
         vpick = "X"
       elseif pick = games(i).homeID then
         hpick ="X"
       elseif pick = TIE_STR then
         tie = TIE_STR
         tpick = "X"
       end if

       'Highlight result, if available.

       if games(i).result = games(i).visitorID then
         visitor = "<strong>" & visitor & "</strong>"
         if pick = games(i).result then
           vpick = "<strong>" & vpick & "</strong>"
         end if
       elseif games(i).result = games(i).homeID then
         home = "<strong>" & home & "</strong>"
         if pick = games(i).result then
           hpick = "<strong>" & hpick & "</strong>"
         end if
       elseif games(i).result = TIE_STR then
         tie = "<strong>" & TIE_STR & "</strong>"
         if pick = games(i).result then
           tpick = "<strong>" & tpick & "</strong>"
         end if
       end if %>
    <td><% = visitor %></td>
    <td align="center"><% = vpick %></td>
    <td>at
        <% = home %></td>
    <td align="center"><% = hpick %></td>
    <td><% = tie %></td>
    <td align="center"><% = tpick %></td>
    <%   else %>
    <td><% = visitor %></td>
    <td align="center"><input class="radio" name="pick-<% = games(i).id %>" type="radio" value="<% = games(i).visitorID %>" <% = IsChecked(pick, games(i).visitorID) %> /></td>
    <td>at
        <% = home %></td>
    <td align="center"><input class="radio" name="pick-<% = games(i).id %>" type="radio" value="<% = games(i).homeID %>" <% = IsChecked(pick, games(i).homeID) %> /></td>
    <td>Tie</td>
    <td align="center"><input class="radio" name="pick-<% = games(i).id %>" type="radio" value="<% = TIE_STR %>" <% = IsChecked(pick, TIE_STR) %> />
      Points <select name="points"><option>
	<option value="16"> 16 <option value="15"> 15 <option value="14"> 14 <option value="13"> 13 <option value="12"> 12 <option value="11"> 11 <option value="10"> 10 <option value="9"> 9 <option value="8"> 8 <option value="7"> 7 <option value="6"> 6 <option value="5"> 5 <option value="4"> 4 <option value="3"> 3 <option value="2"> 2 <option value="1"> 1
	</select></td>
    <td align="center"><option value="16"><span class="s2"> </span> </option>
        <option value="1"></option></td>
    <%   end if %>
  </tr>
  <% next %>
  <tr>
    <th align="left" colspan="9">Tie Breaker</th>
  </tr>
  <tr>
    <td align="right" colspan="9">
      <% 'Get tie breaker game (last game of the week, usually on Monday Night but
   'may occasionally be on Sunday Night).

    dayName = WeekdayName(Weekday(games(UBound(games)).datetime)) %>
      Total points in the
      <% = dayName %>
      Night Football game:
      <% 'If no tie breaker points were given or all games have been locked, check
   'the database for an existing value.

   if tb = "" or allLocked then
     tb = UserTBGuess(Session("FootballPoolUsername"), week)
   end if
   if allLocked then

     'Check for a tie breaker point total.

     tbActual = TBPointTotal(week)
     if IsNumeric(tb) and IsNumeric(tbActual) then %>
      <% = tb & "&nbsp;<strong>(" & Abs(tbActual - tb) & ")</strong>" %>
      <%   else %>
      <% = tb %>
      <%   end if %>
      <% else %>
      <input name="tb" type="text" value="<% = tb %>" size="3" />
      <% end if %>
    </td>
  </tr>
</table>
<p><input class="button" type="submit" value="Submit" /></p>

</form>

<% str = OpenDates(week)
   if str <> "" then %>
<p><strong>Open Dates:</strong> <% = str %></p>
<% end if %>

<% 'List remaining weeks. %>
<% n = NumberOfWeeks()
   week = CurrentWeek()
   if week <> n then %>
<p><strong>Go to week:</strong>&nbsp;
<%   for i = week to NumberOfWeeks() %>
<a href="<% = Request.ServerVariables("SCRIPT_NAME") %>?week=<% = i %>">
<% = i %></a>&nbsp;
<%   next %>
</p>
<% end if %>

<!-- #include file="footer.asp" -->

<% '***************************************************************************
   '* Local functions and subroutines.                                        *
   '***************************************************************************

   '---------------------------------------------------------------------------
   ' GetPick(id): Find this user's current pick for the given game. Returns a
   ' null string if no pick is found in the database.
   '---------------------------------------------------------------------------

   function GetPick(id)

     dim sql, rs

     GetPick = ""
     sql = "select Pick from Picks" _
        & " where Username = '" & Session("FootballPoolUsername") & "'" _
        & " and GameID = " & id
     set rs = DbConn.Execute(sql)
     if not (rs.BOF and rs.EOF) then
       GetPick = rs.Fields("Pick").Value
     end if

   end function

   '---------------------------------------------------------------------------
   ' IsChecked(v1, v2): Used to set a radio button checked parameter if the
   ' values given are equal.
   '---------------------------------------------------------------------------

   function IsChecked(v1, v2)

     IsChecked = ""
     if v1 = v2 then
       IsChecked = " checked=""checked"""
     end if

   end function

   '***************************************************************************
   '* Local class definitions.                                                *
   '***************************************************************************

   '---------------------------------------------------------------------------
   ' GameObj: Used to hold data for a single game.
   '---------------------------------------------------------------------------

   class GameObj

     public id
     public datetime
     public visitorID
     public visitorName
     public homeID
     public homeName
     public result
     public isLocked

     private sub Class_Initialize()
     end sub

     private sub Class_Terminate()
     end sub

     public sub setData(rs)

       'Set game properties using the supplied database record.

       id          = rs.Fields("GameID").Value
       datetime    = CDate(rs.Fields("Date").Value & " " & rs.Fields("Time").Value)
       visitorID   = rs.Fields("VisitorID").Value
       visitorName = rs.Fields("VCity").Value
       homeID      = rs.Fields("HomeID").Value
       homeName    = rs.Fields("HCity").Value
       result      = rs.Fields("Result").Value
       isLocked    = false

       'Use display name for team if available.

       if rs.Fields("VDisplayName").Value <> "" then
         visitorName = rs.Fields("VDisplayName").Value
       end if
       if rs.Fields("HDisplayName").Value <> "" then
         homeName = rs.Fields("HDisplayName").Value
       end if

       'Determine game lock out status. If this is a Sunday game and it's
       'past the game's start time, set the global lock all flag.

       if Weekday(datetime) = vbSunday and datetime <= dateNow then
         allLocked = true
       end if
       if allLocked or datetime <= dateNow then
         isLocked = true
       end if

     end sub

   end class %>

 

[stpmtp]

I think I have isolated the place where the input has to be, but I am sitll having a difficult time
the original code is

 if not errorFlag then
       for i = 0 to UBound(games)
         pick = Request.Form("pick-" & games(i).id)
         if pick = "" then
           pick = GetPick(games(i).id)
         end if
         sql = "delete from Picks" _
            & " where Username = '" & Session("FootballPoolUsername") & "'" _
            & " and GameID = " & games(i).id
         set rs = DbConn.Execute(sql)
         sql = "insert into Picks" _
            & " (Username, GameID, Pick)" _
            & " values('" & Session("FootballPoolUsername") & "'," _
            & " '" & games(i).id & "', " _
            & " '" & pick & "')"
         set rs = DbConn.Execute(sql)
       next
       if tb <> "" then
         sql = "delete from TieBreaker" _
            & " where Week = " & week _
            & " and Username = '" & Session("FootballPoolUsername") & "'"
         set rs = DbConn.Execute(sql)   
         sql = "insert into TieBreaker" _
            & " (Week, Username, TBPoints)" _
            & " values(" & week & ", '" & Session("FootballPoolUsername") & "'," _
            & " " & tb & ")"
         set rs = DbConn.Execute(sql)
       end if
       Response.Redirect("results.asp?week=" & week)
     end if

   end if

   'Build the form. %>

I have trid to add points to

sql = "delete from Picks" _
            & " where Username = '" & Session("FootballPoolUsername") & "'" _
            & " and GameID = " & games(i).id
         set rs = DbConn.Execute(sql)
         sql = "insert into Picks" _
            & " (Username, GameID, Pick, points)" _
            & " values('" & Session("FootballPoolUsername") & "'," _
            & " '" & games(i).id & "', " _
            & " '" & pick & "')" _
            & " '" & points & "')"

But this does not seem to work

could any body give me an idea on how to go about this

I would really appreciate it.


thanks in advance

 

[ermora]

When using the INSERT sql command, the fields and their values need to be in proper order; that being of the actual database table.

Another thing to note is that the values must be the correct datatype.

Hope this helps.

 

[stpmtp]

the way the fields are
UserName, GameID, Picks, points

I thought I had them in order. The data type is text

 

[stpmtp]

Is there anybody that could please give me a hand with this?