Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 619
  • Last Modified:

vb.net error -> You have an error in your SQL syntax

Hi ALL,

Currently I am storing my data into a datatable:
Dim dtRecords As DataTable = GetDataTable("SELECT * FROM soccerstatsgoals")
        Dim dtRecords2 As New DataView(dtRecords)
        Dim DvTheData2 As New DataView(cominguptable)
        If DvTheData2.Count > 0 Then
            DvTheData2.Sort = "Kickofftime DESC"
            'loop through them all and show them

            'Dim scoretest As String = getthestats()
            For Each dv2 As DataRowView In DvTheData2
                Dim scoretest As List(Of Integer) = Getthestats(dtRecords2, dv2.Row.Item("hometeam").Trim(), "")
                Dim scoretest2 As List(Of Integer) = Getthestats(dtRecords2, dv2.Row.Item("awayteam").Trim(), "")
                Dim scoretest4 As List(Of Integer) = Getthehtstats(dtRecords2, dv2.Row.Item("hometeam").Trim(), "")
                Dim scoretest5 As List(Of Integer) = Getthehtstats(dtRecords2, dv2.Row.Item("awayteam").Trim(), "")
                Dim scoretest3 As List(Of Integer) = Gettheversesstats(dtRecords2, dv2.Row.Item("awayteam").Trim(), dv2.Row.Item("hometeam").Trim())
                Dim meanscore As Integer = 0
                Dim meanscore2 As Integer = 0
                Dim meanscore3 As Integer = 0
                Dim meanscore4 As Integer = 0
                Dim meanscore5 As Integer = 0
                Dim testit As String = ""
                For Each element As String In scoretest
                    meanscore = meanscore + element
                Next
                For Each element2 As String In scoretest2
                    meanscore2 = meanscore2 + element2
                Next
                For Each element3 As String In scoretest3
                    meanscore3 = meanscore3 + element3
                Next
                For Each element4 As String In scoretest4
                    meanscore4 = meanscore4 + element4
                Next
                For Each element5 As String In scoretest5
                    meanscore5 = meanscore5 + element5
                Next

                Dim rounduphomescore As String = Math.Round(meanscore / scoretest.Count).ToString
                Dim roundupawayscore As String = Math.Round(meanscore2 / scoretest2.Count).ToString
                Dim rounduphthomescore As String = Math.Round(meanscore4 / scoretest4.Count).ToString
                Dim rounduphtawayscore As String = Math.Round(meanscore5 / scoretest5.Count).ToString
                Dim roundupversesscore As String = Math.Round(meanscore3 / scoretest3.Count).ToString
                If roundupawayscore = "NaN" And rounduphomescore = "NaN" Then

                    'remove the data from the database
                    'remove the data from the database

                Else

                    test1.Text += "<tr><td>" & dv2.Row.Item("Kickofftime") & " " & dv2.Row.Item("hometeam") & " V " & dv2.Row.Item("awayteam") & "</td>" & colourcodeit(rounduphomescore.ToString().Replace("NaN", "N/A"), rounduphthomescore) & colourcodeit(roundupawayscore.ToString().Replace("NaN", "N/A"), rounduphtawayscore) & colourcodeit(roundupversesscore.ToString().Replace("NaN", "N/A"), "") & "</tr>"

                End If



            Next
        Else
            '  test.Text = "No Matches!"
        End If

Open in new window



i was thinking off adding this into the loop so when the text is displayed
 test1.Text += "<tr><td>" & dv2.Row.Item("Kickofftime") & " " & dv2.Row.Item("hometeam") & " V " & dv2.Row.Item("awayteam") & "</td>" & colourcodeit(rounduphomescore.ToString().Replace("NaN", "N/A"), rounduphthomescore) & colourcodeit(roundupawayscore.ToString().Replace("NaN", "N/A"), rounduphtawayscore) & colourcodeit(roundupversesscore.ToString().Replace("NaN", "N/A"), "") & "</tr>"

Open in new window


it is actually replaced with this class


    Private Shared Function inserttodaysmatchesintodb(ByVal thematch As String, ByVal HAG As String, ByVal AAG As String, ByVal VAG As String) As String

        Dim con As New MySqlConnection(GetConnection())
        Dim cmd As New MySqlCommand
        Try
            con.Open()
            cmd.Connection = con
            cmd.CommandText = "INSERT INTO todaysstats([Thematch],[HAG],[AAG],[VAG]) VALUES([" & thematch.Trim() & "],[" & HAG & "],[" & AAG & "],[" & VAG & "])"
            cmd.ExecuteNonQuery()
            '  Catch ex As Exception
            ' textbox5.text = "Error while inserting record on table..." & ex.Message & "Insert Records"
        Finally
            con.Close()
        End Try

    End Function
                  

Open in new window


my questions is im getting the error:

1) With the code above im getting the error You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '[Thematch],[HAG],[AAG],[VAG]) VALUES([Nottingham],[2],[2],[2])' at line 1  - how can i fix this?
0
runnerjp2005
Asked:
runnerjp2005
  • 2
  • 2
1 Solution
 
AndyAinscowCommented:
Just an aside:
               Dim scoretest As List(Of Integer) = Getthestats(dtRecords2, dv2.Row.Item("hometeam").Trim(), "")
                Dim scoretest2 As List(Of Integer) = Getthestats(dtRecords2, dv2.Row.Item("awayteam").Trim(), "")
                Dim scoretest4 As List(Of Integer) = Getthehtstats(dtRecords2, dv2.Row.Item("hometeam").Trim(), "")
                Dim scoretest5 As List(Of Integer) = Getthehtstats(dtRecords2, dv2.Row.Item("awayteam").Trim(), "")
                Dim scoretest3 As List(Of Integer) = Gettheversesstats(dtRecords2, dv2.Row.Item("awayteam").Trim(), dv2.Row.Item("hometeam").Trim())
                Dim meanscore As Integer = 0
                Dim meanscore2 As Integer = 0
                Dim meanscore3 As Integer = 0
                Dim meanscore4 As Integer = 0
                Dim meanscore5 As Integer = 0

Why don't you have a class which contains the individual pieces of information and one list of those classes.
It means that you do not have to worry about synchronising multiple lists, prbably improves readability and maintainability and very likely reduces the code required (duplicate code)
0
 
runnerjp2005Author Commented:
yes i like to write it all out in full while i get it working  then cut it down into class's - it just helps me with the debugging :)
0
 
AndyAinscowCommented:
>> it just helps me with the debugging :)

Actually it increases the debugging effort required.  You need to debug this code then you need to debug the code again when you move into classes.
Anyway, good to hear you realise it can be rather better organised.  I'll bow out re the original question at this point.
0
 
runnerjp2005Author Commented:
I figured out the original question being i missed '' .... but im awarding you the points as you have took your time to view the code and help me other then the question asked
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now