PDA

View Full Version : Building a calculator

ido3dfx
09-04-2007, 06:21 PM
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:)

miranda
09-04-2007, 09:15 PM
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

<%
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)

ido3dfx
09-05-2007, 05:33 PM
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. :)

miranda
09-05-2007, 11:18 PM
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.

ido3dfx
09-06-2007, 02:19 AM
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)

miranda
09-06-2007, 06:30 PM
Which line is line 33? It really helps to write that line out to the forum ;)

ido3dfx
09-06-2007, 06:45 PM
Doh!!

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

miranda
09-06-2007, 07:16 PM
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

ido3dfx
09-07-2007, 08:30 PM
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>"