Solved

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

Posted on 2014-02-19
4
592 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
  • 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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Introduction This article shows how to use the open source plupload control to upload multiple images. The images are resized on the client side before uploading and the upload is done in chunks. Background I had to provide a way for user…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

762 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now