Private Sub Calc_PAT() ' Calculate and Update Total/Match Points and Running Totals after each Match ' ' Note that table Scoring has been replaced with a simple String array (SL_P2W), and ' a new table called Scoring which contains what is needed for calculating Match Points. ' ' The following SQL command is an example of a query that would return the number of ' Match Points given to the loser with a Skill Level of 3, and their Total Points was 20: ' SELECT DISTINCT SL3 FROM Scoring GROUP BY LS, SL3 HAVING LS = 20; Dim command_builder As New OleDbCommandBuilder(OleDbDataAdapterMatches) Dim LsrMP As Int32 = 0, WnrMP As Int32 = 0, LsrNo As Int32 = IIf(TN = 1, 2, 1) Dim LoserSL As Int32 = CInt(PT(SK, LsrNo).Text) Dim SL_Clmn As String = "SL" & CStr(LoserSL) Dim LoserTP As Int32 = CInt(PT(MP, LsrNo).Text) Using con As New OleDbConnection(OleDbConnection.ConnectionString) con.Open() Using cmd As New OleDbCommand cmd.Connection = con cmd.CommandText = "SELECT DISTINCT ? FROM Scoring GROUP BY ?, " _ & SL_Clmn & " HAVING LS = " & PT(MP, LsrNo).Text & ";" cmd.Parameters.Add(New OleDbParameter("@Param1", SL_Clmn)) cmd.Parameters.Add(New OleDbParameter("@Param2", LoserTP)) Try LsrMP = cmd.ExecuteScalar() Catch ex As OleDbException MessageBox.Show(ex.Message) End Try End Using End Using WnrMP = 20 - LsrMP PT(MP, TN).Text = CStr(WnrMP) PT(MP, LsrNo).Text = CStr(LsrMP) ' Adjust Running Totals - Make sure we are updating the correct team If PT(NO, TN).Text = PT(TN, P1).Text Then PT(RT, P1).Text = CInt(PT(RT, P1).Text) + WnrMP PT(RT, P2).Text = CInt(PT(RT, P2).Text) + LsrMP Else PT(RT, P1).Text = CInt(PT(RT, P1).Text) + LsrMP PT(RT, P2).Text = CInt(PT(RT, P2).Text) + WnrMP End If End Sub
You tried to execute a query that does not include the specified expression 'LS=0' as part of an aggregate function.
From novice to tech pro — start learning today.