...

View Full Version : Calculation problem



mack1604
03-06-2007, 01:39 AM
Here's the deal. I have a web form with 6 fields. I need to calculate a series of totals and averages based upon the users input of 3 number fields. The fields are:
1. LeagueID
2. Week (Week that the series was bowled)
3. LaneNumber (The odd numbered lane of the pair bowled upon)
4. Game1 (Score for game 1)
5. Game2 (Score for game 2)
6. Game3 (Score for game 3)

Based upon the score entries of the 3 games and the input from the other 3 fields, a series of calculations need to be written to the stats table in an access database. There are quite a few so I've inserted a link at the bottom so the database can be opened or downloaded. Then you can see in the "design view."

I've never done math calculation's in asp or vbscript before, and I'm not quite sure how to integrate the calculations into the processing page of this form or do I need to create a "functions" file to perform these tasks. Any help or suggestions would be greatly appreciated. The code for the processing page is display below and is functioning properly (as far as it goes). It is writting the UserID, LeagueID, LaneNumber and Week to the stats table. My dilema is how to generate the rest of the stats.



<%
'declare your variables
dim Your_UserName
Your_UserName = ("UserName")
dim Your_LeagueName
Your_LeagueName = ("LeagueName")
dim Your_Week
Your_Week = ("Week")
dim Your_LaneNumber
Your_LaneNumber = ("LaneNumber")
dim Your_Game1
Your_Game1 = ("Game1")
dim Your_Game2
Your_Game2 = ("Game2")
dim Your_Game3
Your_Game3 = ("Game3")
Dim sConnString, connection, sSQL
'Receiving values from Form, assign the values entered to variables
Your_LeagueID = Request.Form("LeagueID")
Your_Week = Request.Form("Week")
Your_LaneNumber = Request.Form("LaneNumber")
Your_Game1 = Request.Form("Game1")
Your_Game2 =Request.Form("Game2")
Your_Game3 =Request.Form("Game3")

sConnString="PROVIDER=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Server.MapPath("../db/BBAMstats.mdb")
'create an ADO connection object
Set connection = Server.CreateObject("ADODB.Connection")

'Open the connection to the database
connection.Open(sConnString)

'declare SQL statement that will query the database
Dim rs,sqlInsert,your_userID,your_leagueID
sqlInsert = "SELECT BSA_Leagues.LeagueID, Users.UserID FROM BSA_Leagues, Users WHERE Cust_UserName='"&Request.Cookies("UserName")&"'"

Set rs = server.CreateObject("Adodb.Recordset")
rs.open sqlInsert,connection,3
your_userID=rs("UserID")

rs.close
set rs=Nothing

sSQL = "INSERT INTO BSA_STATS (UserID, LeagueID, Week, LaneNumber, Game1, Game2, Game3) values ("& your_userID &"," & _
Your_LeagueID & "," & Your_Week & "," & Your_LaneNumber & "," & Your_Game1 & "," & Your_Game2 & "," & Your_Game3 & ")"
'Response.Write sSQL
'Response.End
'define the connection string, specify database
'driver and the location of database
'execute the SQL
connection.execute(sSQL)

response.write "Your scores have been successfully recorded.<br /><a href=""bsa.asp"">View League Stats.</a>"
'Done. Close the connection object
connection.Close
Set connection = Nothing
%>

Download: BBAM Database (http://www.bowlingballsandmore.com/downloads/BBAM.zip)

This is all part of a Bowling Score And Stats Tracking application that I will be offering to anyone for FREE if there are any avid bowlers here. :thumbsup:

miranda
03-06-2007, 05:56 PM
What do you need to calculate? what you have shown is just entry of stats. Do you want to figure out things like average etc?

mack1604
03-06-2007, 08:27 PM
Yes, that's exactly right. I just need to know where and how to get started.

A friend of mine suggested that the actual calculations should be done on the form itself, which would populate hidden fields. Another suggested that I could do the actual math in Access, automatically updating the stats table which has a one to many relationship. My thinking was that it would be done within the code of the processing page, which is the script above. If I could see one calculation made and where and how it fit in, I'm sure I could figure out the rest.

miranda
03-06-2007, 09:56 PM
The easiest way would be to do this in VBScript

Something like this

<%
'declare your variables
dim Your_UserName
dim Your_LeagueName
dim Your_Week
dim Your_LaneNumber
dim Your_Game1
dim Your_Game2
dim Your_Game3
Dim sConnString
Dim sSQL
Dim oRs
Dim your_userID
Dim your_leagueID
Dim TotalGame1
Dim TotalGame2
Dim TotalGame3
Dim TotalGames
Dim NumRows


'Receiving values from Form, assign the values entered to variables
Your_LeagueID = Request.Form("LeagueID")
Your_Week = Request.Form("Week")
Your_LaneNumber = Request.Form("LaneNumber")
Your_Game1 = CInt(Request.Form("Game1"))
Your_Game2 = CInt(Request.Form("Game2"))
Your_Game3 = CInt(Request.Form("Game3"))

sConnString="PROVIDER=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Server.MapPath("../db/BBAMstats.mdb")
'create an ADO connection object
Set oConn = Server.CreateObject("ADODB.Connection")

'Open the connection to the database
oConn.Open(sConnString)


sSQL = "SELECT BSA_Leagues.LeagueID, Users.UserID FROM BSA_Leagues, Users WHERE Cust_UserName='" & Request.Cookies("UserName") & "'"
Set oRs = Server.CreateObject("ADODB.Recordset")
oRs.open sSQL,oConn,3
your_userID= oRs("UserID")
oRs.Close


sSQL = "INSERT INTO BSA_STATS (UserID, LeagueID, Week, LaneNumber, Game1, Game2, Game3) VALUES (" & your_userID & "," & _
Your_LeagueID & "," & Your_Week & "," & Your_LaneNumber & "," & Your_Game1 & "," & Your_Game2 & "," & Your_Game3 & ")"
oConn.Execute(sSQL)

sSQL = "SELECT Game1, Game2, Game3 FROM BSA_STATS WHERE UserID = " & yourUserID
oRs.Open sSQL, oConn, 3,3
NumRows = oRs.RecordCount
Do While Not oRs.EOF
TotalGame1 = TotalGame1 + CInt(oRs("Game1"))
TotalGame2 = TotalGame2 + CInt(oRs("Game2"))
TotalGame3 = TotalGame3 + CInt(oRs("Game3"))
oRs.MoveNext
Loop
oRs.Close
Set oRs = nothing
TotalGames = NumRows * 3


response.write "Your scores have been successfully recorded.<br /><a href=""bsa.asp"">View League Stats.</a>"
Response.Write "<br />Tonight's Average " & ((Your_Game1 + Your_Game2 + Your_Game3) / 3)
Response.Write "<br />Season's Average " & ((TotalGame1 + TotalGame2 + TotalGame3) / TotalGames)
'Done. Close the connection object
oConn.Close
Set oConn = Nothing
%>

SSJ
03-07-2007, 05:29 AM
miranda has done the right thing here



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum