...

View Full Version : Access database connection



mack1604
03-07-2007, 11:53 PM
A friend of mine was kind enough to write this calculation script for me. He was using a SQL Server database. I'm not sure how to create a connection to my Access database within this script. I tried a few ways without and success. Any help would be appreciated.


<%

' **** GRAB VARIABLES FROM FORM ****
userID = Request.QueryString("userID")
leagueID = Request.QueryString("leagueID")
If userID = "" Then
userID = 0
End If
If leagueID = "" Then
leagueID = 0
End If


' **** DISPLAY ALL GAMES ****
Function showAllGames
' **** CREATE CONNECTION ****
Set grabAllGames = siteConn.Execute("SELECT gameID, leagueID, userID, season, week, laneNumber, gameNumber, score FROM BSA_GAMES WHERE userID = " & userID & " AND leagueID = " & leagueID)

showAllGames = showAllGames & Space(0) & "Displaying individual game results for user: <b>" & userID & "</b><p>" & vbCrLf

showAllGames = showAllGames & Space(0) & "<table width=""600"" border=""1"" cellpadding=""2"" cellspacing=""0"">" & vbCrLf
showAllGames = showAllGames & Space(2) & "<tr>" & vbCrLf
showAllGames = showAllGames & Space(4) & "<td><b>Game ID</b></td>" & vbCrLf
showAllGames = showAllGames & Space(4) & "<td><b>League ID</b></td>" & vbCrLf
showAllGames = showAllGames & Space(4) & "<td><b>Season</b></td>" & vbCrLf
showAllGames = showAllGames & Space(4) & "<td><b>Week</b></td>" & vbCrLf
showAllGames = showAllGames & Space(4) & "<td><b>Lane Number</b></td>" & vbCrLf
showAllGames = showAllGames & Space(4) & "<td><b>Game Number</b></td>" & vbCrLf
showAllGames = showAllGames & Space(4) & "<td><b>Score</b></td>" & vbCrLf
showAllGames = showAllGames & Space(2) & "</tr>" & vbCrLf

If not grabAllGames.EOF Then
Do While Not grabAllGames.EOF
showAllGames = showAllGames & Space(2) & "<tr>" & vbCrLf
showAllGames = showAllGames & Space(4) & "<td>" & grabAllGames("gameID") & "</td>" & vbCrLf
showAllGames = showAllGames & Space(4) & "<td>" & grabAllGames("leagueID") & "</td>" & vbCrLf
showAllGames = showAllGames & Space(4) & "<td>" & grabAllGames("season") & "</td>" & vbCrLf
showAllGames = showAllGames & Space(4) & "<td>" & grabAllGames("week") & "</td>" & vbCrLf
showAllGames = showAllGames & Space(4) & "<td>" & grabAllGames("laneNumber") & "</td>" & vbCrLf
showAllGames = showAllGames & Space(4) & "<td>" & grabAllGames("gameNumber") & "</td>" & vbCrLf
showAllGames = showAllGames & Space(4) & "<td>" & grabAllGames("score") & "</td>" & vbCrLf
showAllGames = showAllGames & Space(2) & "</tr>" & vbCrLf
grabAllGames.MoveNext
Loop
Else
showAllGames = showAllGames & Space(2) & "<tr>" & vbCrLf
showAllGames = showAllGames & Space(4) & "<td colspan=""7"" align=""center""><b>Sorry, no data exists for this user.</b></td>" & vbCrLf
showAllGames = showAllGames & Space(2) & "</tr>" & vbCrLf
End If


showAllGames = showAllGames & Space(0) & "</table>" & vbCrLf


' **** CLOSE CONNECTION ****
grabAllGames.Close
Set grabAllGames = Nothing
End Function


' **** SHOW SEASON STATS ****
Function showSeason
' **** GRAB LEAGUE NAME ****
Set grabLeague = siteConn.Execute("SELECT leagueName FROM BSA_LEAGUES WHERE leagueID = " & leagueID)


' **** ASSIGN LEAGUE NAME ****
If Not grabLeague.EOF Then
leagueName = grabLeague("leagueName")
End If


' **** CLOSE CONNECTION ****
grabLeague.Close
Set grabLeague = Nothing


' **** CREATE TABLE ****
showSeason = showSeason & Space(0) & "<table id=""scores"" width=""405"" border=""0"" cellpadding=""0"" cellspacing=""0"" bgcolor=""#7f9db9"">" & vbCrLf
showSeason = showSeason & Space(2) & "<tr>" & vbCrLf
showSeason = showSeason & Space(4) & "<td colspan=""3"" class=""leagueHeading""><a href=""bsa_default.asp""></a>&nbsp;&nbsp;League Name:&nbsp;</td>" & vbCrLf
showSeason = showSeason & Space(4) & "<td class=""leagueName"" colspan=""6"" align=""left"">" & leagueName & "</td>" & vbCrLf
showSeason = showSeason & Space(2) & "</tr>" & vbCrLf
showSeason = showSeason & Space(2) & "<tr>" & vbCrLf
showSeason = showSeason & Space(4) & "<td align=""center"" class=""topRow"" style=""border-left: 1px solid #dc143c""><b>Week</b></td>" & vbCrLf
showSeason = showSeason & Space(4) & "<td align=""center"" class=""topRow""><b>Lane</b></td>" & vbCrLf
showSeason = showSeason & Space(4) & "<td align=""center"" class=""topRow""><b>Gm 1</b></td>" & vbCrLf
showSeason = showSeason & Space(4) & "<td align=""center"" class=""topRow""><b>Gm 2</b></td>" & vbCrLf
showSeason = showSeason & Space(4) & "<td align=""center"" class=""topRow""><b>Gm 3</b></td>" & vbCrLf
showSeason = showSeason & Space(4) & "<td align=""center"" class=""topRow""><b>Series</b></td>" & vbCrLf
showSeason = showSeason & Space(4) & "<td align=""center"" class=""topRow""><b>T-Pins</b></td>" & vbCrLf
showSeason = showSeason & Space(4) & "<td align=""center"" class=""topRow""><b>Gms</b></td>" & vbCrLf
showSeason = showSeason & Space(4) & "<td align=""center"" class=""topRow""><b>Average</b></td>" & vbCrLf
showSeason = showSeason & Space(2) & "</tr>" & vbCrLf

' **** LOOP THROUGH WEEKS OF SEASON ****
seasonWeeks = 36

For weekNum = 1 To seasonWeeks

' **** CREATE CONNECTION ****
Set grabAllInfo = siteConn.Execute("SELECT laneNumber, gameNumber, score FROM BSA_GAMES WHERE userID = " & userID & " AND leagueID = " & leagueID & " AND week = " & weekNum)


If Not grabAllInfo.EOF Then
' **** CREATE ARRAY OF SCORES ****
Do While Not grabAllInfo.EOF
myWeekScores = myWeekScores & grabAllInfo("score") & ","
myScores = myScores & grabAllInfo("score") & ","
grabAllInfo.MoveNext
Loop


' **** MOVE TO TOP OF RECORDSET ****
grabAllInfo.MoveFirst


' **** SPLIT SCORES TO DISPLAY ****
myWeekScoresArr = Split(Left(myWeekScores, (Len(myWeekScores) - 1)), ",")


' **** CALCULATE SERIES, TOTAL PINS, NUM GAMES, AVERAGE ****
For gameNum = 0 TO UBound(myWeekScoresArr)
TotalPins = CInt(TotalPins) + CInt(myWeekScoresArr(gameNum))
TotalGames = TotalGames + 1
Next
Average = Round((TotalPins / TotalGames), 2)
SeasonPins = SeasonPins + TotalPins
SeasonGames = SeasonGames + TotalGames


' **** START ROW ****
showSeason = showSeason & Space(2) & "<tr>" & vbCrLf


' **** SHOW WEEK NUM AND LANE INFO ****
showSeason = showSeason & Space(4) & "<td align=""center"" class=""week"">" & weekNum & "</td>" & vbCrLf
showSeason = showSeason & Space(4) & "<td align=""center"" class=""lane"">" & grabAllInfo("laneNumber") & "</td>" & vbCrLf


' **** SHOW INDIVIDUAL GAME SCORES ****
For gameNum = 0 TO UBound(myWeekScoresArr)
showSeason = showSeason & Space(4) & "<td align=""right"" class=""gm" & (gameNum + 1) & """>" & myWeekScoresArr(gameNum) & "</td>" & vbCrLf
Next


' **** SHOW TOTAL PINS, GAMES, AND AVERAGE ****
showSeason = showSeason & Space(4) & "<td align=""right"" class=""series"">" & TotalPins & "</td>" & vbCrLf
showSeason = showSeason & Space(4) & "<td align=""right"" class=""Tpins"">" & SeasonPins & "</td>" & vbCrLf
showSeason = showSeason & Space(4) & "<td align=""right"" class=""games"">" & SeasonGames & "</td>" & vbCrLf
showSeason = showSeason & Space(4) & "<td align=""right"" class=""average"">" & Average & "</td>" & vbCrLf


' **** CLOSE ROW ****
showSeason = showSeason & Space(2) & "</tr>" & vbCrLf


' **** CALCULATE HIGH/LOW SERIES ****
If TotalPins > highSeries OR highSeries = "" Then
highSeries = TotalPins
End If
If TotalPins < lowSeries OR lowSeries = "" Then
lowSeries = TotalPins
End If


' **** CLEAR VALUES ****
myWeekScores = ""
TotalPins = 0
TotalGames = 0
Average = 0

Else

showSeason = showSeason & Space(2) & "<tr>" & vbCrLf
showSeason = showSeason & Space(4) & "<td align=""center"" class=""week"">" & weekNum & "</td>" & vbCrLf
showSeason = showSeason & Space(4) & "<td class=""lane"">&nbsp;</td>" & vbCrLf
showSeason = showSeason & Space(4) & "<td class=""gm1"">&nbsp;</td>" & vbCrLf
showSeason = showSeason & Space(4) & "<td class=""gm2"">&nbsp;</td>" & vbCrLf
showSeason = showSeason & Space(4) & "<td class=""gm3"">&nbsp;</td>" & vbCrLf
showSeason = showSeason & Space(4) & "<td class=""series"">&nbsp;</td>" & vbCrLf
showSeason = showSeason & Space(4) & "<td class=""Tpins"">&nbsp;</td>" & vbCrLf
showSeason = showSeason & Space(4) & "<td class=""games"">&nbsp;</td>" & vbCrLf
showSeason = showSeason & Space(4) & "<td class=""average"">&nbsp;</td>" & vbCrLf
showSeason = showSeason & Space(2) & "</tr>" & vbCrLf

End If

' **** CLOSE CONNECTION ****
grabAllInfo.Close
Set grabAllInfo = Nothing
Next

' **** REMOVE LAST COMMA FROM MYSCORES ****
If myScores <> "" Then
myScores = Left(myScores, Len(myScores) - 1)
End If


' **** SHOW GAME AVERAGES AND CURRENT AVERAGE ****
showSeason = showSeason & Space(2) & "<tr>" & vbCrLf
showSeason = showSeason & Space(4) & "<td colspan=""2"" class=""topRow"" align=""center""><b>Game Avg</b></td>" & vbCrLf
showSeason = showSeason & Space(4) & "<td class=""gmAvg1"" align=""right"">" & calcGameAvg(1) & "</td>" & vbCrLf
showSeason = showSeason & Space(4) & "<td class=""gmAvg2"" align=""right"">" & calcGameAvg(2) & "</td>" & vbCrLf
showSeason = showSeason & Space(4) & "<td class=""gmAvg3"" align=""right"">" & calcGameAvg(3) & "</td>" & vbCrLf
showSeason = showSeason & Space(4) & "<td bgcolor=""#000000"">&nbsp;</td>" & vbCrLf
showSeason = showSeason & Space(4) & "<td colspan=""2"" class=""topRow"" align=""center""><b>Current Avg</b></td>" & vbCrLf
showSeason = showSeason & Space(4) & "<td class=""week"" align=""right"">" & calcGameAvg("ALL") & "</td>" & vbCrLf
showSeason = showSeason & Space(2) & "</tr>" & vbCrLf


' **** DISPLAY HIGH GAME AND SERIES ****
showSeason = showSeason & Space(2) & "<tr>" & vbCrLf
showSeason = showSeason & Space(4) & "<td colspan=""2"" class=""topRow"" align=""center""><b>High Game</b></td>" & vbCrLf
showSeason = showSeason & Space(4) & "<td class=""week"" align=""right"">" & calcHighLowGame(myScores, "HIGH") & "</td>" & vbCrLf
showSeason = showSeason & Space(4) & "<td colspan=""3"" bgcolor=""#000000"">&nbsp;</td>" & vbCrLf
showSeason = showSeason & Space(4) & "<td colspan=""2"" class=""topRow"" align=""center""><b>High Series</b></td>" & vbCrLf
showSeason = showSeason & Space(4) & "<td class=""week"" align=""right"">" & highSeries & "</td>" & vbCrLf
showSeason = showSeason & Space(2) & "</tr>" & vbCrLf


' **** DISPLAY LOW GAME AND SERIES ****
showSeason = showSeason & Space(2) & "<tr>" & vbCrLf
showSeason = showSeason & Space(4) & "<td colspan=""2"" class=""topRow"" align=""center""><b>Low Game</b></td>" & vbCrLf
showSeason = showSeason & Space(4) & "<td class=""week"" align=""right"">" & calcHighLowGame(myScores, "LOW") & "</td>" & vbCrLf
showSeason = showSeason & Space(4) & "<td colspan=""3"" align=""center"" bgcolor=""#ffce9d"" style=""border-top: 1px solid #ff0000; border-bottom: 1px solid #ff0000;""><a href=""lane_average.asp""><b>View Lane Average</b></a></td>" & vbCrLf
showSeason = showSeason & Space(4) & "<td colspan=""2"" class=""topRow"" align=""center""><b>Low Series</b></td>" & vbCrLf
showSeason = showSeason & Space(4) & "<td class=""week"" align=""right"">" & lowSeries & "</td>" & vbCrLf
showSeason = showSeason & Space(2) & "</tr>" & vbCrLf


' **** CLOSE TABLE ****
showSeason = showSeason & Space(0) & "</table>"

End Function


' **** FUNCTION TO CALCULATE WEEK AVERAGE ****
Function calcGameAvg(gameNum)
If gameNum <> "ALL" Then
sqlGameNum = " AND gameNumber = " & gameNum
End If

' **** CREATE CONNECTION ****
Set grabGames = siteConn.Execute("SELECT score FROM BSA_GAMES WHERE userID = " & userID & " AND leagueID = " & leagueID & sqlGameNum)

If not grabGames.EOF Then
Do While Not grabGames.EOF
TotalScore = TotalScore + grabGames("score")
NumGames = NumGames + 1
grabGames.MoveNext
Loop
End If

If NumGames > 0 Then
calcGameAvg = Round((TotalScore / NumGames), 2)
End If

' **** CLOSE CONNECTION ****
grabGames.Close
Set grabGames = Nothing
End Function



' **** FUNCTION TO CALCULATE HIGH/LOW GAME ****
Function calcHighLowGame(myGames, highLow)
myGamesArr = Split(myGames, ",")
For gameNum = 0 To UBound(myGamesArr)
If highLow = "LOW" Then
If lowGame = "" OR CInt(myGamesArr(gameNum)) < CInt(lowGame) Then
lowGame = myGamesArr(gameNum)
calcHighLowGame = lowGame
End If
Else
If highGame = "" OR CInt(myGamesArr(gameNum)) > CInt(highGame) Then
highGame = myGamesArr(gameNum)
calcHighLowGame = highGame
End If
End If
Next
End Function


Function showGameStats
' **** CREATE CONNECTION TO GRAB SCORES ****
Set grabScores = siteConn.Execute("SELECT score FROM BSA_GAMES WHERE userID = " & userID & " AND leagueID = " & leagueID)
Do While Not grabScores.EOF
myScores = myScores & grabScores("score") & ","
grabScores.MoveNext
Loop


' **** CLOSE CONNECTION ****
grabScores.Close
Set grabScores = Nothing


' **** REMOVE LAST COMMAN FROM MYSCORES ****
If myScores <> "" Then
myScores = Left(myScores, Len(myScores) - 1)
End If


' **** CREATE TABLE ****
showGameStats = showGameStats & Space(0) & "<table border=""0"" cellpadding=""0"" cellspacing=""0"" style=""border-width: 1px solid #ff0000;"" bgcolor=""#000000"">" & vbCrLf
showGameStats = showGameStats & Space(2) & "<tr>" & vbCrLf
showGameStats = showGameStats & Space(4) & "<td colspan=""8"" align=""center""class=""leagueName""><b>Statistics And Analysis</b></td>" & vbCrLf
showGameStats = showGameStats & Space(2) & "</tr>" & vbCrLf
showGameStats = showGameStats & Space(2) & "<tr>" & vbCrLf
showGameStats = showGameStats & Space(4) & "<td colspan=""8"" align=""center"" class=""leagueHeading""><b>Game Scores By Number And Percent</b></td>" & vbCrLf
showGameStats = showGameStats & Space(2) & "</tr>" & vbCrLf
showGameStats = showGameStats & Space(2) & "<tr>" & vbCrLf
showGameStats = showGameStats & Space(4) & "<td width=""65"" align=""center"" class=""topRow""><b>< 120</b></td>" & vbCrLf
showGameStats = showGameStats & Space(4) & "<td width=""66"" align=""center"" class=""topRow""><b>120-129</b></td>" & vbCrLf
showGameStats = showGameStats & Space(4) & "<td width=""66"" align=""center"" class=""topRow""><b>130-149</b></td>" & vbCrLf
showGameStats = showGameStats & Space(4) & "<td width=""66"" align=""center"" class=""topRow""><b>150-174</b></td>" & vbCrLf
showGameStats = showGameStats & Space(4) & "<td width=""66"" align=""center"" class=""topRow""><b>175-199</b></td>" & vbCrLf
showGameStats = showGameStats & Space(4) & "<td width=""66"" align=""center"" class=""topRow""><b>200-224</b></td>" & vbCrLf
showGameStats = showGameStats & Space(4) & "<td width=""66"" align=""center"" class=""topRow""><b>225-249</b></td>" & vbCrLf
showGameStats = showGameStats & Space(4) & "<td width=""65"" align=""center"" class=""topRow""><b>250 ></b></td>" & vbCrLf
showGameStats = showGameStats & Space(2) & "</tr>" & vbCrLf

' **** SHOW NUMBER OF GAMES IN RANGE ****
showGameStats = showGameStats & Space(2) & "<tr>" & vbCrLf
showGameStats = showGameStats & Space(4) & "<td align=""center"" class=""tpins"">" & calcGameRange_Num(myScores, 0, 119) & "</td>" & vbCrLf
showGameStats = showGameStats & Space(4) & "<td align=""center"" class=""tpins"">" & calcGameRange_Num(myScores, 120, 129) & "</td>" & vbCrLf
showGameStats = showGameStats & Space(4) & "<td align=""center"" class=""tpins"">" & calcGameRange_Num(myScores, 130, 149) & "</td>" & vbCrLf
showGameStats = showGameStats & Space(4) & "<td align=""center"" class=""tpins"">" & calcGameRange_Num(myScores, 150, 174) & "</td>" & vbCrLf
showGameStats = showGameStats & Space(4) & "<td align=""center"" class=""tpins"">" & calcGameRange_Num(myScores, 175, 199) & "</td>" & vbCrLf
showGameStats = showGameStats & Space(4) & "<td align=""center"" class=""tpins"">" & calcGameRange_Num(myScores, 200, 224) & "</td>" & vbCrLf
showGameStats = showGameStats & Space(4) & "<td align=""center"" class=""tpins"">" & calcGameRange_Num(myScores, 225, 249) & "</td>" & vbCrLf
showGameStats = showGameStats & Space(4) & "<td align=""center"" class=""tpins"">" & calcGameRange_Num(myScores, 250, 300) & "</td>" & vbCrLf
showGameStats = showGameStats & Space(2) & "</tr>" & vbCrLf


' **** SHOW PERCENTAGE OF GAMES IN RANGE ****
showGameStats = showGameStats & Space(2) & "<tr>" & vbCrLf
showGameStats = showGameStats & Space(4) & "<td align=""center"" class=""gm2"">" & calcGameRange_Pct(myScores, 0, 119) & "</td>" & vbCrLf
showGameStats = showGameStats & Space(4) & "<td align=""center"" class=""gm3"">" & calcGameRange_Pct(myScores, 120, 129) & "</td>" & vbCrLf
showGameStats = showGameStats & Space(4) & "<td align=""center"" class=""gm2"">" & calcGameRange_Pct(myScores, 130, 149) & "</td>" & vbCrLf
showGameStats = showGameStats & Space(4) & "<td align=""center"" class=""gm3"">" & calcGameRange_Pct(myScores, 150, 174) & "</td>" & vbCrLf
showGameStats = showGameStats & Space(4) & "<td align=""center"" class=""gm2"">" & calcGameRange_Pct(myScores, 175, 199) & "</td>" & vbCrLf
showGameStats = showGameStats & Space(4) & "<td align=""center"" class=""gm3"">" & calcGameRange_Pct(myScores, 200, 224) & "</td>" & vbCrLf
showGameStats = showGameStats & Space(4) & "<td align=""center"" class=""gm2"">" & calcGameRange_Pct(myScores, 225, 249) & "</td>" & vbCrLf
showGameStats = showGameStats & Space(4) & "<td align=""center"" class=""gm3"">" & calcGameRange_Pct(myScores, 250, 300) & "</td>" & vbCrLf
showGameStats = showGameStats & Space(2) & "</tr>" & vbCrLf


' **** CLOSE TABLE ****
showGameStats = showGameStats & Space(0) & "</table>" & vbCrLf
End Function



%>

degsy
03-08-2007, 10:38 AM
Change the SQL connection to an Access OLEDB connection
http://www.connectionstrings.com/?carrier=access

SSJ
03-08-2007, 12:11 PM
Post your connectionstring...



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum