MySqlException You have an error in your SQL syntax;

Question

I keep having this 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 'Name like 'f'' at line 1 i want to filter data

This is my code can someone help me

MySqlConnection connection = new MySqlConnection(MyConnection);
connection.Open();

DataTable dt = new DataTable();
MySqlDataAdapter da = new MySqlDataAdapter("Select * from new_order where Full Name like '" + textBox1.Text + "'", connection);
da.Fill(dt);
dataGridView1.DataSource = dt;

connection.Close();

First thing to know: you shouldn't be putting values into your SQL like that. Use parameterized SQL, *right now*. — Jon Skeet, Oct 05 '15 at 18:17
If *Full Name* really is the name of your column, you probably should enclosed it with backticks, see http://stackoverflow.com/questions/261455/using-backticks-around-field-names. — Fasermaler, Oct 05 '15 at 19:11

score 0 · Answer 1 · answered Oct 05 '15 at 18:15

0

To use a space in a column name you have to put it in square brackets:

"select * from new_order where [Full Name] like ..."

And never concatenate your queries like this, it makes you vulnerable to SQL injection attacks. Use parameters.

answered Oct 05 '15 at 18:15

Jakub Lortz

14,616
3
25
39

score 0 · Accepted Answer · answered Oct 05 '15 at 18:15

0

If the column Full Name has a space in it it must be wrapped in quotes to be read by the SQL.

i.e. "Select * from new_order where 'Full Name' like '" + textBox1.Text + "'"

I haven't tested this but it should work.

answered Oct 05 '15 at 18:15

calderonmluis

547
4
11

it works but the problem now is when i put something on textbox1 the data in datagridview disappears. – ang Oct 05 '15 at 18:29
@ang are you using a data binding on the element or are you manually populating the datagridview? – calderonmluis Oct 05 '15 at 18:31
Either way you should probably trigger an event on exit of the TextBox to run the query and populate the datagridview – calderonmluis Oct 05 '15 at 18:32
And maybe give my answer an upvote for having solved the original question ;) – calderonmluis Oct 05 '15 at 18:32

MySqlException You have an error in your SQL syntax;

2 Answers2