?
Solved

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

Posted on 2014-02-19
4
Medium Priority
?
605 Views
Last Modified: 2014-02-19
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
Comment
Question by:runnerjp2005
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 44

Expert Comment

by:AndyAinscow
ID: 39869877
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
 

Author Comment

by:runnerjp2005
ID: 39869882
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
 
LVL 44

Accepted Solution

by:
AndyAinscow earned 2000 total points
ID: 39869899
>> 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
 

Author Closing Comment

by:runnerjp2005
ID: 39869904
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

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

User art_snob (http://www.experts-exchange.com/M_6114203.html) encountered strange behavior of Android Web browser on his Mobile Web site. It took a while to find the true cause. It happens so, that the Android Web browser (at least up to OS ver. 2.…
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
Suggested Courses

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question