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

    Access database connection

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

  • #2
    Senior Coder
    Join Date
    Nov 2002
    Location
    North-East, UK
    Posts
    1,265
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Change the SQL connection to an Access OLEDB connection
    http://www.connectionstrings.com/?carrier=access

  • #3
    SSJ
    SSJ is offline
    Regular Coder
    Join Date
    Mar 2007
    Posts
    230
    Thanks
    0
    Thanked 4 Times in 4 Posts
    Post your connectionstring...


  •  

    Posting Permissions

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