locked
MySQL Error RRS feed

  • Question

  • Im using wamp and im getting this error when I run my program. I'm following a tut and it looks another person is getting the asame 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 ''users' WHERE 'name' = 'Form2'' at line 1

    Private Sub btnSubmit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSubmit.Click
            Using con As New MySqlConnection(frmMain.SQLConnect)
                con.Open()
                Using cmd As MySqlCommand = con.CreateCommand()
                    Dim Accountname As String = Me.Text
                    Dim Accountid As Integer
                    cmd.CommandText = "SELECT 'id','name' FROM 'users' WHERE 'name' = '" & Accountname & "'"
                    Using reader As MySqlDataReader = cmd.ExecuteReader()
                        If Not reader.Read() Then
                            Return
                        End If
                        Accountname = reader.GetInt32(0)
                        reader.Close()
                        Using Update As MySqlCommand = con.CreateCommand
                            Update.CommandText = "UPDATE 'users' SET 'score' = '" & score & "' WHERE 'id' = " & Accountid
                            Update.ExecuteNonQuery()
                        End Using
                    End Using
                End Using
            End Using
        End Sub

    It says this line is the cause:

    Using reader As MySqlDataReader = cmd.ExecuteReader()

    Ideas?
    Monday, August 13, 2012 8:47 PM

Answers

  • Doesn't seem to be the apostrophe ' but the ` character for quoting reserved words. See "Schema Object Names" saying "The identifier quote character is the backtick".

    I still suggest using parameters and don't do it as in the video.


    Armin

    • Proposed as answer by Heslacher Tuesday, August 14, 2012 2:41 PM
    • Marked as answer by Youen Zen Friday, August 24, 2012 7:57 AM
    Monday, August 13, 2012 9:50 PM

All replies

  • Why use apostrophees with table and field names? Is this valid MySql syntax? If not, it should be

       SELECT id, name FROM users ...

    I'm not quite sure what's your intention because you use reader.GetInt32(0) which returns the ID, but you assign it to variable Accountname instead of AccountID. This is not compilable here. It's strongly recommended to switch Option Strict On to avoid these flaws. So I assume you wanted to write

       AccountID = reader.GetInt32(0)

    You should make use of the command's parameters property, so I think the solution is...:

                cmd.CommandText = "SELECT id FROM users WHERE name = @accountname"
                cmd.Parameters.AddWithValue("@accountname", Accountname)
    
    
    
    
    
                      Update.CommandText = "UPDATE users SET score = @score WHERE id = @id"
                      Update.Parameters.AddWithValue("@score", score)
                      Update.Parameters.AddWithValue("@id", Accountid)
    


    Armin

    Monday, August 13, 2012 9:02 PM
  • This is not c# lol we dont use @ in VB. Now back to the ' I have no idea i was just following the tut and it worked in the first forum. I assume it acts a seperator in php or something. Idk i honestly know nothing about MySQl so sorry i dont know that answer. Also how do you know option strict is not on and it dont matter so please lets solve the issue at hand please using syntax for the correct language we are working in.
    • Edited by old_School Monday, August 13, 2012 9:16 PM
    Monday, August 13, 2012 9:11 PM
  • It's used for parameters names no matter in which language.

    Armin

    Monday, August 13, 2012 9:13 PM
  • Ahh I see sorry for that one. Here is a link to the tut. Abot 3:05 into it is where you will see his code:

    Video:

    http://www.youtube.com/watch?v=i8zZd4LV0LI&feature=channel&list=UL

    Normaly I like videos that explain more as they go along but not a large choice on Youtube for multi-player VB games. I want to use the info to make some simple online board games but i have to get this fixed so I can play with it and understand it more first. So for now Id preffer to just get this working first.  IDK maybe you guys can figure out the video or something.


    • Edited by old_School Monday, August 13, 2012 9:25 PM
    Monday, August 13, 2012 9:25 PM
  • Doesn't seem to be the apostrophe ' but the ` character for quoting reserved words. See "Schema Object Names" saying "The identifier quote character is the backtick".

    I still suggest using parameters and don't do it as in the video.


    Armin

    • Proposed as answer by Heslacher Tuesday, August 14, 2012 2:41 PM
    • Marked as answer by Youen Zen Friday, August 24, 2012 7:57 AM
    Monday, August 13, 2012 9:50 PM
  • Will it work? If so can you retype then as you suggest cause im still lost
    • Edited by old_School Monday, August 13, 2012 10:00 PM
    Monday, August 13, 2012 9:59 PM
  • Sorry, I don't understand. Have you replaced the characters?

    Armin

    Monday, August 13, 2012 10:04 PM
  • Not yet sorry not sure what im supose to replace in it. Thats why i asked if you could rewrite that section using what i provided.
    Monday, August 13, 2012 10:57 PM
  • You are using the apostrophe: '

    You should use the backtick: `

    Enclosing table names and field names is only required if the name is a reserved word, so first try without enclosing the names. I've already posted code as an example.


    Armin


    Tuesday, August 14, 2012 12:08 AM
  • this ` ?

    Tuesday, August 14, 2012 12:28 AM
  • Why not try? Seem to be the same three pixels.... Best place is looking in the documentation that I linked above.

    Armin

    Tuesday, August 14, 2012 1:02 AM
  • Not sure which version of MySQL you are working with but I would check out the Prepare section in the documentation for how to use Command Parameters:

    http://dev.mysql.com/doc/refman/5.0/es/connector-net-examples-mysqlcommand.html

    Of all the good tutorials I've seen for MySQL I'm still rather surprised that most of them do not implement Command Parameters.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Tuesday, August 14, 2012 2:31 PM