CODEWITHSUNDEEP
mysqlvb.net
Sam Lucas
Sam Lucas

Reputation: 243

MySQL Insert into error using VB.net

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.

Upvotes: 1

Views: 354

Answers (2)

Emil Heraña
Emil Heraña

Reputation: 82

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.

Upvotes: 0

Alex B.
Alex B.

Reputation: 2167

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();

Upvotes: 3

Related Questions