Solved

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

Posted on 2014-02-19
4
601 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 500 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
VB.Net creating Contact in Outlook 1 60
ASP.NET - Why is accordion not working? 3 50
TFS 2015 Access denied. 1 30
Connect Gridview column to Textbox in C# 2 42
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.…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

697 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