MySQL server version for the right syntax to use near

  • (2 Pages)
  • +
  • 1
  • 2

18 Replies - 787 Views - Last Post: 22 August 2013 - 09:32 AM Rate Topic: -----

#1 d.struiwig  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 13
  • Joined: 14-August 13

MySQL server version for the right syntax to use near

Posted 22 August 2013 - 06:45 AM

Hello, i got a script.. but it isnt work very well.. it gives a error calls:

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 ''HH-HH-HH' at line 1

Here is some pictures how this is happend.

On picture 1 u can see i search for a record "HH-HH-HH" then the records shows up and i have change the name of the person on picture 2 on the label "Naam"



When i wanna press save calls "Opslaan" u can see on picture 2 that it gives a error.

My purpose in this script is update the requested records whats showing.


Picture 1 =

Attached Image
Picture 2 =

Attached Image
Picture 3 =

Attached Image


Here is my code


  Dim conn As New MySqlConnection
        Dim myCommand As New MySqlCommand
        Dim myAdapter As New MySqlDataAdapter
        Dim dt2 As New DataTable

        conn.ConnectionString = "server=83.86.60.46;port=3306; user id=mod: user id removed; password=mod: password removed; database=poetsbedrijf"
        conn.Open()
        myCommand.CommandText = "INSERT INTO Klanten(Naam, Tussenvoegsel, Achternaam, Straat, Postcode, Plaats, Telefoonnummer, Email, Merk, Model, Kenteken) VALUES ('" + Naam.Text + "','" + Tussenvoegsel.Text + "','" + Achternaam.Text + "','" + Straat.Text + "','" + Postcode.Text + "','" + Plaats.Text + "','" + Telefoonnummer.Text + "','" + Email.Text + "','" + Merk.Text + "','" + Model.Text + "','" + Kenteken.Text + ";"
        myAdapter.SelectCommand = myCommand

        'Assisgn to textbox   
        Naam.DataBindings.Clear()
        myCommand.Parameters.AddWithValue("Naam", Naam.Text)

        Tussenvoegsel.DataBindings.Clear()
        myCommand.Parameters.AddWithValue("Tussenvoegsel", Tussenvoegsel.Text)

        Achternaam.DataBindings.Clear()
        myCommand.Parameters.AddWithValue("Achternaam", Achternaam.Text)

        Straat.DataBindings.Clear()
        myCommand.Parameters.AddWithValue("Straat", Straat.Text)

        Postcode.DataBindings.Clear()
        myCommand.Parameters.AddWithValue("Postcode", Postcode.Text)

        Plaats.DataBindings.Clear()
        myCommand.Parameters.AddWithValue("Plaats", Plaats.Text)

        Telefoonnummer.DataBindings.Clear()
        myCommand.Parameters.AddWithValue("Telefoonnummer", Telefoonnummer.Text)

        Email.DataBindings.Clear()
        myCommand.Parameters.AddWithValue("Email", Email.Text)

        Merk.DataBindings.Clear()
        myCommand.Parameters.AddWithValue("Merk", Merk.Text)

        Model.DataBindings.Clear()
        myCommand.Parameters.AddWithValue("Model", Model.Text)

        Kenteken.DataBindings.Clear()
        myCommand.Parameters.AddWithValue("Kenteken", Kenteken.Text)

        KlantID.DataBindings.Clear()
        myCommand.Parameters.AddWithValue("Klant", KlantID.Text)
        myCommand.Connection = conn
        myCommand.ExecuteNonQuery()

        MsgBox("Updated")
        conn.Close()
            conn.Dispose()
    End Sub

This post has been edited by modi123_1: 22 August 2013 - 06:55 AM
Reason for edit:: let's not share the db's root name and password - removed them both


Is This A Good Question/Topic? 0
  • +

Replies To: MySQL server version for the right syntax to use near

#2 ybadragon  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 192
  • View blog
  • Posts: 1,183
  • Joined: 11-May 12

Re: MySQL server version for the right syntax to use near

Posted 22 August 2013 - 06:56 AM

This line is what is causing the error
myCommand.CommandText = "INSERT INTO Klanten(Naam, Tussenvoegsel, Achternaam, Straat, Postcode, Plaats, Telefoonnummer, Email, Merk, Model, Kenteken) VALUES ('" + Naam.Text + "','" + Tussenvoegsel.Text + "','" + Achternaam.Text + "','" + Straat.Text + "','" + Postcode.Text + "','" + Plaats.Text + "','" + Telefoonnummer.Text + "','" + Email.Text + "','" + Merk.Text + "','" + Model.Text + "','" + Kenteken.Text + ";"



below this you are adding values into your parameters using a parameterized query. This gets rid of you needing all the escape characters and .Text values in your query so you just need this

myCommand.CommandText = "INSERT INTO Klanten VALUES(Naam, Tussenvoegsel, Achternaam, Straat, Postcode, Plaats, Telefoonnummer, Email, Merk, Model, Kenteken)"


Also when you are adding the parameters below, make sure that they are added in the order the Values are in the Query. Otherwise It won't work.

Looking over it again, make sure you have all the correct column names in your database And the Data Type is correct for the type you are trying to insert (I.E. Text, Number, Date)

This post has been edited by ybadragon: 22 August 2013 - 06:59 AM

Was This Post Helpful? 1
  • +
  • -

#3 d.struiwig  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 13
  • Joined: 14-August 13

Re: MySQL server version for the right syntax to use near

Posted 22 August 2013 - 07:14 AM

Alright it seems ok now but there is a next error:

Column count doesn't match value count at row 1

This is the code:

Dim conn As New MySqlConnection
        Dim myCommand As New MySqlCommand
        Dim myAdapter As New MySqlDataAdapter
        Dim dt2 As New DataTable

        conn.ConnectionString = "server=83.86.60.88;port=3306; user id=root; password=sleutel12; database=poetsbedrijf"
        conn.Open()
        myCommand.CommandText = "INSERT INTO Klanten VALUES(Naam, Tussenvoegsel, Achternaam, Straat, Postcode, Plaats, Telefoonnummer, Email, Merk, Model, Kenteken)"
        myCommand = myCommand


        'Assisgn to textbox   
        Naam.DataBindings.Clear()
        myCommand.Parameters.AddWithValue("Naam", Naam.Text)

        Tussenvoegsel.DataBindings.Clear()
        myCommand.Parameters.AddWithValue("Tussenvoegsel", Tussenvoegsel.Text)

        Achternaam.DataBindings.Clear()
        myCommand.Parameters.AddWithValue("Achternaam", Achternaam.Text)

        Straat.DataBindings.Clear()
        myCommand.Parameters.AddWithValue("Straat", Straat.Text)

        Postcode.DataBindings.Clear()
        myCommand.Parameters.AddWithValue("Postcode", Postcode.Text)

        Plaats.DataBindings.Clear()
        myCommand.Parameters.AddWithValue("Plaats", Plaats.Text)

        Telefoonnummer.DataBindings.Clear()
        myCommand.Parameters.AddWithValue("Telefoonnummer", Telefoonnummer.Text)

        Email.DataBindings.Clear()
        myCommand.Parameters.AddWithValue("Email", Email.Text)

        Merk.DataBindings.Clear()
        myCommand.Parameters.AddWithValue("Merk", Merk.Text)

        Model.DataBindings.Clear()
        myCommand.Parameters.AddWithValue("Model", Model.Text)

        Kenteken.DataBindings.Clear()
        myCommand.Parameters.AddWithValue("Kenteken", Kenteken.Text)

        KlantID.DataBindings.Clear()
        myCommand.Parameters.AddWithValue("Klant", KlantID.Text)
        myCommand.Connection = conn
        myCommand.ExecuteNonQuery()

        MsgBox("Updated")
        conn.Close()
            conn.Dispose()

Was This Post Helpful? 0
  • +
  • -

#4 ybadragon  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 192
  • View blog
  • Posts: 1,183
  • Joined: 11-May 12

Re: MySQL server version for the right syntax to use near

Posted 22 August 2013 - 07:18 AM

You need to add "Klant" at the end of the query just like the other values.
Was This Post Helpful? 1
  • +
  • -

#5 d.struiwig  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 13
  • Joined: 14-August 13

Re: MySQL server version for the right syntax to use near

Posted 22 August 2013 - 07:22 AM

View Postd.struiwig, on 22 August 2013 - 07:14 AM, said:

Alright it seems ok now but there is a next error:

Column count doesn't match value count at row 1

This is the code:

Dim conn As New MySqlConnection
        Dim myCommand As New MySqlCommand
        Dim myAdapter As New MySqlDataAdapter
        Dim dt2 As New DataTable

        conn.ConnectionString = "server=83.86.60.88;port=3306; user id=mod:removed; password=mod:removed; database=poetsbedrijf"
        conn.Open()
        myCommand.CommandText = "INSERT INTO Klanten VALUES(Naam, Tussenvoegsel, Achternaam, Straat, Postcode, Plaats, Telefoonnummer, Email, Merk, Model, Kenteken)"
        myCommand = myCommand


        'Assisgn to textbox   
        Naam.DataBindings.Clear()
        myCommand.Parameters.AddWithValue("Naam", Naam.Text)

        Tussenvoegsel.DataBindings.Clear()
        myCommand.Parameters.AddWithValue("Tussenvoegsel", Tussenvoegsel.Text)

        Achternaam.DataBindings.Clear()
        myCommand.Parameters.AddWithValue("Achternaam", Achternaam.Text)

        Straat.DataBindings.Clear()
        myCommand.Parameters.AddWithValue("Straat", Straat.Text)

        Postcode.DataBindings.Clear()
        myCommand.Parameters.AddWithValue("Postcode", Postcode.Text)

        Plaats.DataBindings.Clear()
        myCommand.Parameters.AddWithValue("Plaats", Plaats.Text)

        Telefoonnummer.DataBindings.Clear()
        myCommand.Parameters.AddWithValue("Telefoonnummer", Telefoonnummer.Text)

        Email.DataBindings.Clear()
        myCommand.Parameters.AddWithValue("Email", Email.Text)

        Merk.DataBindings.Clear()
        myCommand.Parameters.AddWithValue("Merk", Merk.Text)

        Model.DataBindings.Clear()
        myCommand.Parameters.AddWithValue("Model", Model.Text)

        Kenteken.DataBindings.Clear()
        myCommand.Parameters.AddWithValue("Kenteken", Kenteken.Text)

        KlantID.DataBindings.Clear()
        myCommand.Parameters.AddWithValue("Klant", KlantID.Text)
        myCommand.Connection = conn
        myCommand.ExecuteNonQuery()

        MsgBox("Updated")
        conn.Close()
            conn.Dispose()



No this is done its ok but the problem is when i update my records it seems there is no records in my database.... and it make a new table with nothing in it...


Picture 1 is when i wanna change the record into something else

Picture 2 is what it must be "what is changed"

Picture 3 is how it gonna add in the database


Picture 1 =

Attached Image


Picture 2 =

Attached Image


Picture 3 =


Attached Image

This is the updated code



 Dim conn As New MySqlConnection
        Dim myCommand As New MySqlCommand
        Dim myAdapter As New MySqlDataAdapter
        Dim dt2 As New DataTable

        conn.ConnectionString = "server=83.86.60.46;port=3306; user id=mod:removed; password=mod:removed; database=poetsbedrijf"
        conn.Open()
        myCommand.CommandText = "INSERT INTO Klanten VALUES(KlantID, Naam, Tussenvoegsel, Achternaam, Straat, Postcode, Plaats, Telefoonnummer, Email, Merk, Model, Kenteken)"
        myCommand = myCommand


        'Assisgn to textbox   
        Naam.DataBindings.Clear()
        myCommand.Parameters.AddWithValue("Naam", Naam.Text)

        Tussenvoegsel.DataBindings.Clear()
        myCommand.Parameters.AddWithValue("Tussenvoegsel", Tussenvoegsel.Text)

        Achternaam.DataBindings.Clear()
        myCommand.Parameters.AddWithValue("Achternaam", Achternaam.Text)

        Straat.DataBindings.Clear()
        myCommand.Parameters.AddWithValue("Straat", Straat.Text)

        Postcode.DataBindings.Clear()
        myCommand.Parameters.AddWithValue("Postcode", Postcode.Text)

        Plaats.DataBindings.Clear()
        myCommand.Parameters.AddWithValue("Plaats", Plaats.Text)

        Telefoonnummer.DataBindings.Clear()
        myCommand.Parameters.AddWithValue("Telefoonnummer", Telefoonnummer.Text)

        Email.DataBindings.Clear()
        myCommand.Parameters.AddWithValue("Email", Email.Text)

        Merk.DataBindings.Clear()
        myCommand.Parameters.AddWithValue("Merk", Merk.Text)

        Model.DataBindings.Clear()
        myCommand.Parameters.AddWithValue("Model", Model.Text)

        Kenteken.DataBindings.Clear()
        myCommand.Parameters.AddWithValue("Kenteken", Kenteken.Text)

        KlantID.DataBindings.Clear()
        myCommand.Parameters.AddWithValue("Klant", KlantID.Text)
        myCommand.Connection = conn
        myCommand.ExecuteNonQuery()

        MsgBox("Updated")
        conn.Close()
            conn.Dispose()
    End Sub

This post has been edited by modi123_1: 22 August 2013 - 09:31 AM
Reason for edit:: removed user id and password

Was This Post Helpful? 0
  • +
  • -

#6 ybadragon  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 192
  • View blog
  • Posts: 1,183
  • Joined: 11-May 12

Re: MySQL server version for the right syntax to use near

Posted 22 August 2013 - 07:24 AM

I told you in a previous post to make sure the parameters you add under 'Assign to textbox are in the same order otherwise that will happen. Look at the order you have in your query then look at the values you are adding to the parameters. One of them is not correct and one of them is in the wrong order.
Was This Post Helpful? 1
  • +
  • -

#7 d.struiwig  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 13
  • Joined: 14-August 13

Re: MySQL server version for the right syntax to use near

Posted 22 August 2013 - 07:48 AM

Now i have added KlantID in the parameters and query and it still doing the same...



This is the code:


  Dim conn As New MySqlConnection
        Dim myCommand As New MySqlCommand
        Dim myAdapter As New MySqlDataAdapter

        conn.ConnectionString = "server=83.86.60.46;port=3306; user id=mod:removed; password=mod:removed; database=poetsbedrijf"
        conn.Open()
        myCommand.CommandText = "INSERT INTO Klanten VALUES(KlantID, Naam, Tussenvoegsel, Achternaam, Straat, Postcode, Plaats, Telefoonnummer, Email, Merk, Model, Kenteken)"
        myAdapter.SelectCommand = myCommand

        'Assisgn to textbox   

        KlantID.DataBindings.Clear()
        myCommand.Parameters.AddWithValue("KlantID", KlantID.Text)

        Naam.DataBindings.Clear()
        myCommand.Parameters.AddWithValue("Naam", Naam.Text)

        Tussenvoegsel.DataBindings.Clear()
        myCommand.Parameters.AddWithValue("Tussenvoegsel", Tussenvoegsel.Text)

        Achternaam.DataBindings.Clear()
        myCommand.Parameters.AddWithValue("Achternaam", Achternaam.Text)

        Straat.DataBindings.Clear()
        myCommand.Parameters.AddWithValue("Straat", Straat.Text)

        Postcode.DataBindings.Clear()
        myCommand.Parameters.AddWithValue("Postcode", Postcode.Text)

        Plaats.DataBindings.Clear()
        myCommand.Parameters.AddWithValue("Plaats", Plaats.Text)

        Telefoonnummer.DataBindings.Clear()
        myCommand.Parameters.AddWithValue("Telefoonnummer", Telefoonnummer.Text)

        Email.DataBindings.Clear()
        myCommand.Parameters.AddWithValue("Email", Email.Text)

        Merk.DataBindings.Clear()
        myCommand.Parameters.AddWithValue("Merk", Merk.Text)

        Model.DataBindings.Clear()
        myCommand.Parameters.AddWithValue("Model", Model.Text)

        Kenteken.DataBindings.Clear()
        myCommand.Parameters.AddWithValue("Kenteken", Kenteken.Text)

        myCommand.Connection = conn
        myCommand.ExecuteNonQuery()

        MsgBox("Updated")
        conn.Close()
            conn.Dispose()


This is the database SQL

CREATE TABLE IF NOT EXISTS `klanten` (
`KlantID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`Naam` varchar(255) NOT NULL,
`Tussenvoegsel` varchar(255) NOT NULL,
`Achternaam` varchar(255) NOT NULL,
`Straat` varchar(255) NOT NULL,
`Postcode` varchar(255) NOT NULL,
`Plaats` varchar(255) NOT NULL,
`Telefoonnummer` varchar(255) NOT NULL,
`Email` varchar(255) NOT NULL,
`Merk` varchar(255) NOT NULL,
`Model` varchar(255) NOT NULL,
`Kenteken` varchar(255) NOT NULL,
UNIQUE KEY `KlantID` (`KlantID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=24 ;

This post has been edited by modi123_1: 22 August 2013 - 09:31 AM
Reason for edit:: removed user id and password

Was This Post Helpful? 0
  • +
  • -

#8 ybadragon  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 192
  • View blog
  • Posts: 1,183
  • Joined: 11-May 12

Re: MySQL server version for the right syntax to use near

Posted 22 August 2013 - 07:54 AM

put a breakpoint in your code and step through it to look at the values of all your variables as they are being put in. Make sure they are all correct.
Was This Post Helpful? 1
  • +
  • -

#9 rgfirefly24  Icon User is offline

  • D.I.C Lover
  • member icon


Reputation: 262
  • View blog
  • Posts: 1,467
  • Joined: 07-April 08

Re: MySQL server version for the right syntax to use near

Posted 22 August 2013 - 07:54 AM

you are probably getting the error because It doesn't see the items you put in the VALUES section to be variables, Try adding the @ symbol in front of them all (Including the Add Parameters section) and run it again.

EX:

command.CommandText = "INSERT INTO <Table> VALUES (@Val1, @Val2, @Val3, @Val4)"

command.Parameters.AddWithValue("@Val1",Val1)
command.Parameters.AddWithValue("@Val2",Val2)
command.Parameters.AddWithValue("@Val3",Val3)
command.Parameters.AddWithValue("@Val4",Val4)


This post has been edited by rgfirefly24: 22 August 2013 - 07:56 AM

Was This Post Helpful? 1
  • +
  • -

#10 ybadragon  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 192
  • View blog
  • Posts: 1,183
  • Joined: 11-May 12

Re: MySQL server version for the right syntax to use near

Posted 22 August 2013 - 07:59 AM

I completely forgot that it needed the "@" symbol :stupid: good catch rgfirefly!
Was This Post Helpful? 1
  • +
  • -

#11 d.struiwig  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 13
  • Joined: 14-August 13

Re: MySQL server version for the right syntax to use near

Posted 22 August 2013 - 08:02 AM

Yes Thank u both your are genius!!!!

If you both was a woman i proberly doing a trio with u 2 LOOOOL
ok that was a joke but there is one thing to do...

Duplicate entry '14' for key 'KlantID'

How i fix this problem?
Was This Post Helpful? 0
  • +
  • -

#12 ybadragon  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 192
  • View blog
  • Posts: 1,183
  • Joined: 11-May 12

Re: MySQL server version for the right syntax to use near

Posted 22 August 2013 - 08:07 AM

lol Thanks,

Since KlantID is your primary key, you won't be allowed to have Duplicate entries of it. So you will need to either set it up as an auto-number, or you can query for that specific ID in your database when the user clicks the button and if the query returns anything, don't insert it and show a box saying the ID is a duplicate.

Another option is to put a Try Catch around this block of code and if it goes to the catch block see if the error says Duplicate ID and if it does, show a message box saying that.

This post has been edited by ybadragon: 22 August 2013 - 08:09 AM

Was This Post Helpful? 0
  • +
  • -

#13 rgfirefly24  Icon User is offline

  • D.I.C Lover
  • member icon


Reputation: 262
  • View blog
  • Posts: 1,467
  • Joined: 07-April 08

Re: MySQL server version for the right syntax to use near

Posted 22 August 2013 - 08:15 AM

A better option since you want to update/add information to an existing item is to use a SQL UPDATE statement instead of the INSERT statement. You are not trying to insert a new record anyway.

As a side note, since your KlantID is a Auto Increment Primary Key you should not be inserting into that column anyway. That is what the Auto_Increment is for. So if you want to insert data into the table remove that all together from your INSERT statement.

This post has been edited by rgfirefly24: 22 August 2013 - 08:17 AM

Was This Post Helpful? 0
  • +
  • -

#14 d.struiwig  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 13
  • Joined: 14-August 13

Re: MySQL server version for the right syntax to use near

Posted 22 August 2013 - 08:16 AM

View Postybadragon, on 22 August 2013 - 08:07 AM, said:

lol Thanks,

Since KlantID is your primary key, you won't be allowed to have Duplicate entries of it. So you will need to either set it up as an auto-number, or you can query for that specific ID in your database when the user clicks the button and if the query returns anything, don't insert it and show a box saying the ID is a duplicate.

Another option is to put a Try Catch around this block of code and if it goes to the catch block see if the error says Duplicate ID and if it does, show a message box saying that.




Alright but now i have a problem it seems it insert the records what is searched double in the database but it supposed to be updated if i wanna modify the records what i put in the textboxes...
Was This Post Helpful? 0
  • +
  • -

#15 ybadragon  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 192
  • View blog
  • Posts: 1,183
  • Joined: 11-May 12

Re: MySQL server version for the right syntax to use near

Posted 22 August 2013 - 08:20 AM

Then you need to create an UPDATE Query instead of an insert query if you are updating. The link provided explains how to create an Update query in SQL, it's also an amazing site for research and common questions.
Was This Post Helpful? 1
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2