MySQL Insert into error using VB.net

Question

I am using VB.net to access a MySQL database and insert data into this table. I am getting this data from an opera database, the query I am using is:

Dim queryInsert As String = "INSERT INTO customer_company(customer_id, name, street, zip, 
city,country,comments) values(" + c.sn_account.Trim + ", " + name + ", " + road + ", " 
+ postcode + ", " + city + ", " + country + ", " + name + ")"

I am then getting an error when it comes to the record:

B010, Charles Birt & Co, Loch House, null, Tenby, Dyfed, Charles Birt & Co

I though that this may be the '&' in the data so I have tried replacing it with || chr(38) || and also escaping it using \& but these do not work. Also I tried setting the postcode to various things like 'N/A', ' ' and null because this particular record doesn't have a postcode but this still gives the error.

Don't know if its the data or the query, any suggestions would be great.

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 'Birt & Co, Loch House, null, Tenby, Dyfed, Charles Birt & Co)' at line 1 — Sam Lucas
– Sam Lucas, Commented Apr 4, 2016 at 8:54

Alex B. · Accepted Answer · 2016-04-04 09:30:51Z

3

Please use parameters when executing SQL commands. This avoids problems like you encounter in your question and also minimizes SQL injection attacks:

Dim queryInsert As String = 
"INSERT INTO customer_company(customer_id, name, street, zip,city,country,comments) values (@customer_id, @name, @street, @zip, @city, @country, @comments)"

Dim cmd as new MySqlCommand(queryInsert, <YourConnection>)
cmd.Parameters.AddWithValue("@customer_id", c.sn_account.Trim)
cmd.Parameters.AddWithValue("@name", name)
cmd.Parameters.AddWithValue("@street", road)
cmd.Parameters.AddWithValue("@zip", postcode)
cmd.Parameters.AddWithValue("@city", city)
cmd.Parameters.AddWithValue("@country", country)
cmd.Parameters.AddWithValue("@comments", name)
cmd.ExecuteNonQuery();

answered Apr 4, 2016 at 9:30

Alex B.

2,1671 gold badge17 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Sam Lucas Over a year ago

I thought parameters make a query slower?

Alex B. Over a year ago

Where did you get this? Have you measured the performance whith and without parameters?

Sam Lucas Over a year ago

A colleague told me not to use them because they increase execution time, I trusted him and didn't use them. Got it fixed now anyway thanks!

Alex B. Over a year ago

Sorry, but I think your colleague is wrong. See this article which concludes prepared statements in MySQL are about 14.5% faster than non-prepared statements.

Sam Lucas Over a year ago

I'll have to let him know, and I can actually start using them now! Thanks

Emil Heraña · Accepted Answer · 2016-04-07 10:20:18Z

0

Not too sure if this will fix your problem but, when adding/inserting a value with a String Data Type Column in SQL, you should have ' '.

in your example:

Dim queryInsert As String = "INSERT INTO customer_company(customer_id, 
name, street, zip, city,country,comments) 
values(" + c.sn_account.Trim + ", '" + name + "', '" + road + "', " 
+ postcode + ", '" + city + "', '" + country + "', '" + name + "')"

i dont add ' ' in customer_id and postcode because i think that they are not string date type.

edited Apr 7, 2016 at 10:20

answered Apr 4, 2016 at 9:10

Emil Heraña

8210 bronze badges

1 Comment

Alex B. Over a year ago

Try to insert a customer named Sean O'Brian. Or even worse: '; Delete From customer_company; --'

Collectives™ on Stack Overflow

MySQL Insert into error using VB.net

2 Answers 2

5 Comments

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

1 Comment

Related