Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    New Coder
    Join Date
    Mar 2007
    Location
    Florida
    Posts
    50
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Calculation problem

    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.

    Code:
    <%   
    '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

    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.
    Last edited by mack1604; 03-06-2007 at 02:44 AM.

  • #2
    Senior Coder
    Join Date
    Dec 2002
    Location
    Arlington, Texas USA
    Posts
    1,072
    Thanks
    4
    Thanked 8 Times in 8 Posts
    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?

  • #3
    New Coder
    Join Date
    Mar 2007
    Location
    Florida
    Posts
    50
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

  • #4
    Senior Coder
    Join Date
    Dec 2002
    Location
    Arlington, Texas USA
    Posts
    1,072
    Thanks
    4
    Thanked 8 Times in 8 Posts
    The easiest way would be to do this in VBScript

    Something like this
    Code:
    <%   
    '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
    %>

  • #5
    SSJ
    SSJ is offline
    Regular Coder
    Join Date
    Mar 2007
    Posts
    230
    Thanks
    0
    Thanked 4 Times in 4 Posts
    miranda has done the right thing here


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •