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 9 of 9
  1. #1
    New Coder
    Join Date
    Sep 2007
    Posts
    10
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Exclamation Building a calculator

    I am writing a Golf Handicap calculator in asp and need help. I am not good at array's and I am pretty sure that is what is needed here. I think this because the same math wil be performed repeatedly on similar fields.

    In the formula (per the USGA) it is a 3 part calculation. The numbers entered by the user determine the values used in the calculations.

    I have named these in both the form and db-yes I am story this in a db)

    CR_1 - course rating
    SL_1 - slope
    S_1 - score

    The first part of the calculation is :
    (Score - Course Rating) x 113 / Slope Rating


    This answer for each round is known as the handicap differential

    The second part of the calculation is:

    used to figure out how many differentials will be used in part three. This is based on the following table:

    ( a minimum of 5 rounds and a max of 20 rounds.)

    Rounds Entered / Differentials Used
    5-6 / 1 lowest
    7-8 / 2 lowest
    9-10 /3 lowest
    11-12 / 4 lowest
    13-14 / 5 lowest
    15-16 6 lowest
    17 /7 lowest
    18 /8 lowest
    19 /9 lowest
    20 /10 lowest

    the third part of the calculation:

    averaging the differentials multiplied by .96 and round to tenths.

    avg(diff) X .96 rounded to tenths.

    If any of this is confusing please contact me. I am at a loss and any help is greatly appreciated.

    thank you in advance

    best regards,

    MM

  • #2
    Senior Coder
    Join Date
    Dec 2002
    Location
    Arlington, Texas USA
    Posts
    1,070
    Thanks
    4
    Thanked 8 Times in 8 Posts
    You don't need to use an array

    Since the user enters the data in the first three fields I am assuming you have figured out how to do that part of the calculation.

    To do the second part you will need to query your database to get the number of rounds that are saved in the db for that user. then you need to use that value to determine how many differentials to get. then add the differentials together and divide that total by the number of differentials


    The code could look something like this
    Code:
    <%
    Function CalulateAverageScore(ByVal UserID)
    	Dim oRs
    	Dim sConnectionString
    	Dim sSQL
    	Dim iNumRounds
    	Dim iNumDifferntials
    	Dim iScore
    	
    	sConnectionString = "Your_Dsn_OR_DsnLess_Connection_String"
    	iScore = 0
    	Set oRs = Server.CreateObject("ADODB.Recordset")
    	'If the UserID is NOT numeric then enclose it in single quotes
    	sSQL = "SELECT COUNT(*) AS NumRounds FROM Your_Table WHERE ID=" & UserID 
    	oRs.Open sSQL, sConnectionString
    	If oRs.EOF Then
    		iNumRounds = 0
    	Else
    	    iNumRounds = oRs("NumRounds")
    	End If
    	oRs.Close
    		If iNumRounds >= 5 AND iNumRounds <= 20 Then
    		Select Case Cint(iNumRounds)
    			Case 5,6
    				iNumDifferntials = 1
    			Case 7,8
    				iNumDifferntials = 2
    			Case 9,10
    				iNumDifferntials = 3
    			Case11,12
    				iNumDifferntials = 4
    			Case 13,14
    				iNumDifferntials = 5
    			Case 15,16
    				iNumDifferntials = 6
    			Case 17
    				iNumDifferntials = 7
    			Case 18
    				iNumDifferntials = 8
    			Case 19
    				iNumDifferntials = 9
    			Case Else
    				iNumDifferntials = 10
    		End Select
    	Else
    		'too few/many rounds 
    		If IsObject(oRs) Then Set oRs = Nothing
    		Exit Function
    	End If
    	sSQL = "SELECT TOP " & iNumDifferntials & " Scores FROM Your_Table WHERE ID=" & UserID & "  ORDER BY Scores DESC"
    	oRs.Open sSQL, sConnectionString
    	Do until oRs.EOF
    		iScore = iScore + Cint(oRs("Score"))
    		oRs.MoveNext
    	Loop
    	If IsObject(oRs) Then
    		If oRs.State = 1 Then oRs.Close
    		Set oRs = Nothing
    	End If
    	CalulateAverageScore = FormatNumber(((iScore / iNumDifferntials) * .96),2) 	
    End Function
    %>

    To get the users average score you would just call the function like so
    CalulateAverageScore(The_UserID_of_the_person)

  • #3
    New Coder
    Join Date
    Sep 2007
    Posts
    10
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Question

    this looks real good. I like the case too. Thank you so much. I am having problems with the connections string I believe though.
    I am able to update the scores, slope etc. But your code I think is looking for a different connection string then what I use.

    How can I adjust your code to work with my connection string ?


    The error I get is:
    ADODB.Recordset error '800a0bb9'

    Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.






    Here is the page I am passing the data in the form to. I am only updating one round right now for simplicity sake.






    <!--#include file="../../common/ado/connection_open.asp"-->

    <%


    UserID = session("user_id")

    sql = "UPDATE HandiCap set [rating_1]="&request("rating_1")&", [slope_1]="& request("slope_1") &", [score_1]="&request("score_1")&", [PlayDate_1]='"&request("PlayDate_1")&"'"
    rs = cn.execute(sql)

    Response.Write ("<br>We just Updated: ="&sql&"")
    %>

    <a href="scores.asp">back</a>
    <%
    Function CalulateAverageScore(ByVal UserID)
    Dim oRs
    Dim sConnectionString
    Dim sSQL
    Dim iNumRounds
    Dim iNumDifferntials
    Dim iScore

    'sConnectionStr = "Your_Dsn_OR_DsnLess_Connection_String"
    iScore = 0
    Set oRs = Server.CreateObject("ADODB.Recordset")
    'If the UserID is NOT numeric then enclose it in single quotes
    sSQL = "SELECT COUNT(*) AS NumRounds FROM HandiCap WHERE uID="&UserID&";"
    oRs.Open sSQL, sConnectionString
    If oRs.EOF Then
    iNumRounds = 0
    Else
    iNumRounds = oRs("NumRounds")
    End If
    oRs.Close
    If iNumRounds >= 5 AND iNumRounds <= 20 Then
    Select Case Cint(iNumRounds)
    Case 5,6
    iNumDifferntials = 1
    Case 7,8
    iNumDifferntials = 2
    Case 9,10
    iNumDifferntials = 3
    Case11,12
    iNumDifferntials = 4
    Case 13,14
    iNumDifferntials = 5
    Case 15,16
    iNumDifferntials = 6
    Case 17
    iNumDifferntials = 7
    Case 18
    iNumDifferntials = 8
    Case 19
    iNumDifferntials = 9
    Case Else
    iNumDifferntials = 10
    End Select
    Else
    'too few/many rounds
    If IsObject(oRs) Then Set oRs = Nothing
    Exit Function
    End If
    sSQL = "SELECT TOP " & iNumDifferntials & " Scores FROM HandiCap WHERE uID=" & UserID & " ORDER BY Scores DESC"
    oRs.Open sSQL, sConnectionString
    Do until oRs.EOF
    iScore = iScore + Cint(oRs("Score"))
    oRs.MoveNext
    Loop
    If IsObject(oRs) Then
    If oRs.State = 1 Then oRs.Close
    Set oRs = Nothing
    End If
    CalulateAverageScore = FormatNumber(((iScore / iNumDifferntials) * .96),2)
    End Function

    User_HC = CalulateAverageScore("UserID")
    'UPDATE RECORD to rflect the handicap

    Response.write"Your handicap:"&User_HC&""

    ' sql = "UPDATE HandiCap set [handicap]="&User_HC&", [LastUpdate]="& request("slope_1") &", [score_1]="&request("score_1")&", [PlayDate_1]='"&request("PlayDate_1")&"'"
    rs = cn.execute(sql)

    ' Response.Write ("<br>We just Updated your handicap to: ="&sql&"")


    %>
    <!--#include file="../../common/ado/connection_close.asp"-->


    Again Thank you so very much for your help.
    Last edited by ido3dfx; 09-05-2007 at 09:25 PM. Reason: update code

  • #4
    Senior Coder
    Join Date
    Dec 2002
    Location
    Arlington, Texas USA
    Posts
    1,070
    Thanks
    4
    Thanked 8 Times in 8 Posts
    Not knowing what your connection was, that is why the variable sConnectionStr had the following line
    sConnectionStr = "Your_Dsn_OR_DsnLess_Connection_String"

    Simply remove that and each time you open a recordset reference cn instead (which according to your code is your connection object).

    To connect to the database you can create a connection object and get recordsets from that or skip the connection object and just use the connection string or DSN to do this. Just one less object to mess with meaning less overhead on the server.

  • #5
    New Coder
    Join Date
    Sep 2007
    Posts
    10
    Thanks
    2
    Thanked 0 Times in 0 Posts
    I think I understand. I took your advice and stayed with your code.
    I still get an Microsoft JET Database Engine error '80040e10'

    No value given for one or more required parameters.

    /content/scores/Cal_scores.asp, line 33

    I can see the UserID is there and the names in the tables look right..?
    I have this :


    UserID = session("user_id")
    Response.write""&UserID&" <br>"



    Function CalulateAverageScore(ByVal UserID)
    Dim oRs
    Dim sConnectionString
    Dim sSQL
    Dim iNumRounds
    Dim iNumDifferntials
    Dim iScore

    sConnectionString ="Data Source=" & Server.Mappath("../../data/aspportal.mdb") & ";Provider=Microsoft.Jet.OLEDB.4.0;"


    iScore = 0
    Set oRs = Server.CreateObject("ADODB.Recordset")
    'If the UserID is NOT numeric then enclose it in single quotes
    sSQL = "SELECT COUNT(*) AS NumRounds FROM HandiCap WHERE Uid=" & UserID &" ;"
    oRs.Open sSQL, sConnectionString
    If oRs.EOF Then
    iNumRounds = 0
    Else
    iNumRounds = oRs("NumRounds")
    End If
    oRs.Close

    Again thank you. ..and I like your recipes ;o)

  • #6
    Senior Coder
    Join Date
    Dec 2002
    Location
    Arlington, Texas USA
    Posts
    1,070
    Thanks
    4
    Thanked 8 Times in 8 Posts
    Which line is line 33? It really helps to write that line out to the forum

  • #7
    New Coder
    Join Date
    Sep 2007
    Posts
    10
    Thanks
    2
    Thanked 0 Times in 0 Posts

    line 33 error

    Doh!!

    I attached so it might be easier for you to see.
    Attached Files Attached Files

  • #8
    Senior Coder
    Join Date
    Dec 2002
    Location
    Arlington, Texas USA
    Posts
    1,070
    Thanks
    4
    Thanked 8 Times in 8 Posts
    Do two response.Writes before you open the recordset

    like so
    Response.Write sSQL & "<br><br>"
    Response.Write Server.Mappath("../../data/aspportal.mdb") & "<br>"

    So your code will look like

    sSQL = "SELECT COUNT(*) AS NumRounds FROM HandiCap WHERE Uid=" & UserID &" ;"
    Response.Write sSQL & "<br><br>"
    Response.Write Server.Mappath("../../data/aspportal.mdb") & "<br>"
    Response.End
    oRs.Open sSQL, sConnectionString

    Look at what is output to the screen. If the connection string is correct and the SQL Query looks correct then check it by opening Access and select Queries then Create query in Design view then when the wizard opens select the table, close the table selections and then right mouse click and click SQL view, now paste your query that is displayed on the page into this and run it

  • #9
    New Coder
    Join Date
    Sep 2007
    Posts
    10
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Well I got past that and I have 6 rounds of data in the db.

    If I add the response.write after the first select I see results for the query

    however it doesn't seem to count the rounds only the rows. (see my data schema below) I placed a response.write after the 2nd select statement and after the if statement.

    I think it has to do with the table design. I have one row with a ton of columns.

    id
    Uid
    rating_1
    slope_1
    score_1
    PlayDate_1
    rating_2
    slope_2
    score_2
    PlayDate_2
    rating_3
    slope_3
    score_3
    PlayDate_3

    etc, etc to cover 20 rounds and then 2 more fields

    HandiCap
    LastUpdated


    OUTPUT=
    SELECT COUNT(*) AS NumRounds FROM HandiCap WHERE Uid='40' ;

    you have played 1 roundsYou must have 5 rounds played to calculate a handicap.

    CODE=
    Function CalulateAverageScore(ByVal UserID)
    Dim oRs
    Dim sConnectionString
    Dim sSQL
    Dim iNumRounds
    Dim iNumDifferntials
    Dim iScore

    sConnectionString ="Data Source="& Server.Mappath("../../data/4ms.mdb")&"; Provider=Microsoft.Jet.OLEDB.4.0;"


    iScore = 0
    Set oRs = Server.CreateObject("ADODB.Recordset")
    'If the UserID is NOT numeric then enclose it in single quotes
    sSQL = "SELECT COUNT(*) AS NumRounds FROM HandiCap WHERE Uid='" & nID &"' ;"
    Response.Write sSQL & "<br><br>"
    ' Response.End
    oRs.Open sSQL, sConnectionString
    If oRs.EOF Then
    iNumRounds = 0
    Else
    iNumRounds = oRs("NumRounds")
    End If
    Response.Write "you have played "&iNumRounds&" rounds"
    oRs.Close
    If iNumRounds >= 5 AND iNumRounds <= 20 Then
    Select Case Cint(iNumRounds)
    Case 5,6
    iNumDifferntials = 1
    Case 7,8
    iNumDifferntials = 2
    Case 9,10
    iNumDifferntials = 3
    Case11,12
    iNumDifferntials = 4
    Case 13,14
    iNumDifferntials = 5
    Case 15,16
    iNumDifferntials = 6
    Case 17
    iNumDifferntials = 7
    Case 18
    iNumDifferntials = 8
    Case 19
    iNumDifferntials = 9
    Case Else
    iNumDifferntials = 10
    End Select
    Else
    'too few/many rounds
    If IsObject(oRs) Then Set oRs = Nothing
    Exit Function
    End If
    sSQL = "SELECT TOP " & iNumDifferntials & " Scores FROM HandiCap WHERE uID='" & nID & "' ORDER BY Scores DESC"
    Response.Write sSQL & "<br><br>"
    oRs.Open sSQL, ConnectionString
    Do until oRs.EOF
    iScore = iScore + Cint(oRs("Score"))
    oRs.MoveNext
    Loop
    If IsObject(oRs) Then
    If oRs.State = 1 Then oRs.Close
    Set oRs = Nothing
    End If
    CalulateAverageScore = FormatNumber(((iScore / iNumDifferntials) * .96),2)
    End Function

    User_HC = CalulateAverageScore("&nID&")
    'UPDATE RECORD to rflect the handicap


    If iNumRounds < 5 then
    Response.write"You must have 5 rounds played to calculate a handicap."

    Else
    Response.write"Your handicap:"&User_HC&""

    ' sql = "UPDATE HandiCap set [handicap]="&User_HC&", [LastUpdate]="& request("slope_1") &", [score_1]="&request("score_1")&", [PlayDate_1]='"&request("PlayDate_1")&"'"
    'rs = cn.execute(sql)

    ' Response.Write ("<br>We just Updated: ="&sql&"")
    End If
    sSQL = "SELECT COUNT(*) AS NumRounds FROM HandiCap WHERE Uid='" & nID &"' ;"
    Response.Write sSQL & "<br><br>"
    Last edited by ido3dfx; 09-07-2007 at 08:32 PM. Reason: error in description - added data schema


  •  

    Posting Permissions

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