[RESOLVED] Syntax error in update statement?

[faxmunky]

Firstly, I am very new to programming and even more so to Visual Basic. So my code here is probably pretty bad. Anyway, I am getting a very strange error.

This code does not work. The error is shown on line 111:

For i = 0 To 65000
            If dsgames.Tables("games").Rows(i).Item(0) = draw Then
                For m = 0 To 4
                    usernums(m) = dsgames.Tables("games").Rows(i).Item(m + 2)
                Next
                count = 0
 
                ' Cycles through each user number, then cycles through each winning number (supp is position 5).
                ' Checks to see if the user number is equal to the winning number.
                ' Checks to see if the winning user numbers are supplementary or not.
                ' Supplementary numbers cause the supplement variable to be set to true.
                ' Other winning numbers increment a counter.
                For m = 0 To 4
                    For p = 0 To 5
                        If usernums(m) = winning(p) Then
                            If p = 5 Then
                                supplement = True
                            Else
                                count += 1
                            End If
                        End If
                    Next
                Next
 
                ' This case statement assigns the current row a division from 0 to 4 based on the number in the counter.
                ' It also counts the number of winners from each division.
                Select Case count
                    Case 5
                        dsgames.Tables("games").Rows(i).Item("Div") = 1
                        first += 1
                    Case 4
                        dsgames.Tables("games").Rows(i).Item("Div") = 2
                        second += 1
                    Case 3
                        If supplement = True Then
                            dsgames.Tables("games").Rows(i).Item("Div") = 3
                            third += 1
 
                        Else
                            dsgames.Tables("games").Rows(i).Item("Div") = 4
                            fourth += 1
                        End If
                    Case Else
                        dsgames.Tables("games").Rows(i).Item("Div") = 0
                End Select
                supplement = False
            End If
        Next
 
 
 
 
 
        ' The following if statements are used to output the amount of money that each winner from each division receives.
        If first > 0 Then
            firstp = Int(div1 / first)
            lstWinners.Items.Add("Division 1: " & first & " ($" & firstp & " each)")
        Else
            firstp = 0
            lstWinners.Items.Add("Division 1: None")
        End If
 
        If (second > 0) Then
            secondp = Int(div2 / second)
            lstWinners.Items.Add("Division 2: " & second & " ($" & secondp & " each)")
        Else
            secondp = 0
            lstWinners.Items.Add("Division 2: None")
        End If
 
        If (third > 0) Then
            thirdp = Int(div3 / third)
            lstWinners.Items.Add("Division 3: " & third & " ($" & thirdp & " each)")
        Else
            thirdp = 0
            lstWinners.Items.Add("Division 3: None")
        End If
 
        If (fourth > 0) Then
            fourthp = Int(div4 / fourth)
            lstWinners.Items.Add("Division 4: " & fourth & " ($" & fourthp & " each)")
        Else
            fourthp = 0
            lstWinners.Items.Add("Division 4: None")
        End If
 
 
 
        For i = 0 To 65000
            Dim division As Integer = Int(dsgames.Tables("games").Rows(i).Item("Div"))
 
            Select Case division
                Case 4
                    dsgames.Tables("games").Rows(i).Item("Money") = fourthp
                Case 3
                    dsgames.Tables("games").Rows(i).Item("Money") = thirdp
                Case 2
                    dsgames.Tables("games").Rows(i).Item("Money") = secondp
                Case 1
                    dsgames.Tables("games").Rows(i).Item("Money") = firstp
                Case Else
                    dsgames.Tables("games").Rows(i).Item("Money") = 0
            End Select
        Next
 
 
        lstWinners.BackColor = Color.White
 
        ' Updates the games table with the new 'Div' values.
        Dim cb As New OleDb.OleDbCommandBuilder(dagames)
        dagames.Update(dsgames, "games") ' THE ERROR IS HERE "Syntax error in UPDATE statement."
 
 
        ' Updates(the) 'Pool' table with the new pool value (after subtracting the winnings).
        Dim cb2 As New OleDb.OleDbCommandBuilder(dapool)
        dspool.Tables("Pool").Rows(0).Item(1) += 1
        dspool.Tables("Pool").Rows(0).Item(0) = dspool.Tables("Pool").Rows(0).Item(0) - firstp - secondp - thirdp - fourthp
        dapool.Update(dspool, "pool")

This code DOES work. The only difference is that I moved the code from lines 109-111 to 51-53:

For i = 0 To 65000
            If dsgames.Tables("games").Rows(i).Item(0) = draw Then
                For m = 0 To 4
                    usernums(m) = dsgames.Tables("games").Rows(i).Item(m + 2)
                Next
                count = 0
 
                ' Cycles through each user number, then cycles through each winning number (supp is position 5).
                ' Checks to see if the user number is equal to the winning number.
                ' Checks to see if the winning user numbers are supplementary or not.
                ' Supplementary numbers cause the supplement variable to be set to true.
                ' Other winning numbers increment a counter.
                For m = 0 To 4
                    For p = 0 To 5
                        If usernums(m) = winning(p) Then
                            If p = 5 Then
                                supplement = True
                            Else
                                count += 1
                            End If
                        End If
                    Next
                Next
 
                ' This case statement assigns the current row a division from 0 to 4 based on the number in the counter.
                ' It also counts the number of winners from each division.
                Select Case count
                    Case 5
                        dsgames.Tables("games").Rows(i).Item("Div") = 1
                        first += 1
                    Case 4
                        dsgames.Tables("games").Rows(i).Item("Div") = 2
                        second += 1
                    Case 3
                        If supplement = True Then
                            dsgames.Tables("games").Rows(i).Item("Div") = 3
                            third += 1
 
                        Else
                            dsgames.Tables("games").Rows(i).Item("Div") = 4
                            fourth += 1
                        End If
                    Case Else
                        dsgames.Tables("games").Rows(i).Item("Div") = 0
                End Select
                supplement = False
            End If
        Next
 
 
        ' Updates the games table with the new 'Div' values. ' I MOVED IT HERE
        Dim cb As New OleDb.OleDbCommandBuilder(dagames) ' HERE
        dagames.Update(dsgames, "games") ' HERE
 
 
        ' The following if statements are used to output the amount of money that each winner from each division receives.
        If first > 0 Then
            firstp = Int(div1 / first)
            lstWinners.Items.Add("Division 1: " & first & " ($" & firstp & " each)")
        Else
            firstp = 0
            lstWinners.Items.Add("Division 1: None")
        End If
 
        If (second > 0) Then
            secondp = Int(div2 / second)
            lstWinners.Items.Add("Division 2: " & second & " ($" & secondp & " each)")
        Else
            secondp = 0
            lstWinners.Items.Add("Division 2: None")
        End If
 
        If (third > 0) Then
            thirdp = Int(div3 / third)
            lstWinners.Items.Add("Division 3: " & third & " ($" & thirdp & " each)")
        Else
            thirdp = 0
            lstWinners.Items.Add("Division 3: None")
        End If
 
        If (fourth > 0) Then
            fourthp = Int(div4 / fourth)
            lstWinners.Items.Add("Division 4: " & fourth & " ($" & fourthp & " each)")
        Else
            fourthp = 0
            lstWinners.Items.Add("Division 4: None")
        End If
 
 
 
        For i = 0 To 65000
            Dim division As Integer = Int(dsgames.Tables("games").Rows(i).Item("Div"))
 
            Select Case division
                Case 4
                    dsgames.Tables("games").Rows(i).Item("Money") = fourthp
                Case 3
                    dsgames.Tables("games").Rows(i).Item("Money") = thirdp
                Case 2
                    dsgames.Tables("games").Rows(i).Item("Money") = secondp
                Case 1
                    dsgames.Tables("games").Rows(i).Item("Money") = firstp
                Case Else
                    dsgames.Tables("games").Rows(i).Item("Money") = 0
            End Select
        Next
 
 
        lstWinners.BackColor = Color.White
 
 
 
 
        ' Updates(the) 'Pool' table with the new pool value (after subtracting the winnings).
        Dim cb2 As New OleDb.OleDbCommandBuilder(dapool)
        dspool.Tables("Pool").Rows(0).Item(1) += 1
        dspool.Tables("Pool").Rows(0).Item(0) = dspool.Tables("Pool").Rows(0).Item(0) - firstp - secondp - thirdp - fourthp
        dapool.Update(dspool, "pool")

Sorry for the huge walls of code, but they are necessary for me to explain what's happening here. Does anyone know why this is happening and how I can fix it? The code needs to be at the bottom like the first one.

[kareninstructor]

What is the error message reported back?

Use a Try/Catch to get the error message

Try
    ' statement which is failing
Catch ex As Exception
    ' this is displayed in the IDE output window
    Console.WriteLine(ex.Message)
End Try

[faxmunky]

What is the error message reported back?

Use a Try/Catch to get the error message

Try
    ' statement which is failing
Catch ex As Exception
    ' this is displayed in the IDE output window
    Console.WriteLine(ex.Message)
End Try

A first chance exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll

[jmcilhinney]

A first chance exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll

That is not the error message. That is telling you that an exception of that type was thrown, not what the Message of the caught exception was. You've already provided the error message in the thread title and, though it's a little harder to find, in your code snippet.

If you get a syntax error when using a command builder, the reason is usually that one or more of your table and/or column names is a reserved word or contains special characters. To fix that, set the QuotePrefix and QuoteSuffix properties of your command builder so that the identifiers will be delimited appropriately. For Access, any many other databases, the values should be "[" and "]" respectively.

[faxmunky]

That is not the error message. That is telling you that an exception of that type was thrown, not what the Message of the caught exception was. You've already provided the error message in the thread title and, though it's a little harder to find, in your code snippet.

If you get a syntax error when using a command builder, the reason is usually that one or more of your table and/or column names is a reserved word or contains special characters. To fix that, set the QuotePrefix and QuoteSuffix properties of your command builder so that the identifiers will be delimited appropriately. For Access, any many other databases, the values should be "[" and "]" respectively.

It turns out 'Money' is a reserved word.
Thanks for the help!