Trying to insert data from windows form to database via mysql, error?

Question

I get error at calling cmd.ExecuteNonQuery.

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 'angelina','biology')'' at line 1

private void button2_Click(object sender, EventArgs e)
{
    MySqlConnection con = new MySqlConnection(@"server=localhost;database=name;uid=root;pwd=xxx;");
    string query = "INSERT INTO table_student (@name, @major) VALUES ('" + textBox3.Text + "','" + textBox4.Text + "');";
    MySqlCommand cmd = new MySqlCommand(query, con);
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.Add("@name", MySqlDbType.VarChar).Value = this.textBox3.Text;
    cmd.Parameters.Add("@major", MySqlDbType.VarChar).Value = this.textBox4.Text;
    
    con.Open();
    
    int i = cmd.ExecuteNonQuery();
    
    con.Close();
    
    if (i > 0)
    {
        MessageBox.Show(i + "Data Saved");
    }
}

Have another look into how SQL Parameters work. You want to use them for the values, not the column names. Then you don't want to concat your SQL. — Fildor
– Fildor, Commented Jul 11, 2022 at 12:15
^^ Not to mention you should be using using and probably not do Database I/O on the Event Thread. But that's not the problem, here. And also for later: You may want to avoid putting username / password into your code. — Fildor
– Fildor, Commented Jul 11, 2022 at 12:19
I will look into SQL Parameters more. By concat my sql, you mean I should not use my string query? — Angelina
– Angelina, Commented Jul 11, 2022 at 12:20
You can use a string query, just do not construct it with "+" (or similar concatenation / format methods like interpolation or string.Format). That is a security issue because it makes your code vulnerable to SQL injection. Using SQL Parameters was the correct way to go, you are just doing it wrong. — Fildor
– Fildor, Commented Jul 11, 2022 at 12:22
Never do this: VALUES ('" + textBox3.Text + "','" + textBox4.Text + "') i.e., take user input and put it directly into a SQL string. See explaining xkcd for more — Heretic Monkey
– Heretic Monkey, Commented Jul 11, 2022 at 12:24

David · Accepted Answer · 2022-07-11 12:28:20Z

3

You're using parameters incorrectly. The parameters for a query are the values being used in the query, but you're directly concatenating the values (which is a SQL injection vulnerability) and trying to use the parameters as names of your columns.

The query should be more like this:

string query = "INSERT INTO table_student (name, major) VALUES (@name, @major);";

The name and major parts are column names and are statically known. The @name and @major parts are the value placeholders in the query, which are substituted by the query engine internally when the query is executed after you add the values for those parameters:

cmd.Parameters.Add("@name", MySqlDbType.VarChar).Value = this.textBox3.Text;
cmd.Parameters.Add("@major", MySqlDbType.VarChar).Value = this.textBox4.Text;

Additionally, as pointed out in a comment, get rid of this line:

cmd.CommandType = CommandType.StoredProcedure;

You're not executing a stored procedure, just a direct query.

edited Jul 11, 2022 at 12:28

answered Jul 11, 2022 at 12:23

David

221k42 gold badges245 silver badges337 bronze badges

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

5 Comments

Heretic Monkey Over a year ago

certainly there are duplicates for this?

David Over a year ago

@HereticMonkey: For using parameters in general, I'm sure. But for this specific mistake of swapping the column names and the values? I haven't found one. I was on the fence between answering or closing as a generic query parameter duplicate, and went this route.

duerzd696 Over a year ago

It also is not a stored procedure they are using. cmd.CommandType = CommandType.StoredProcedure should be CommandType.Text

Fildor Over a year ago

Nice catch, @duerzd696 - that's absolutely another point on the list.

Heretic Monkey Over a year ago

The OP is not swapping the column names and the values, or at least I see no evidence of that in the question. An equally valid assumption is that they just didn't realize they needed to insert the parameter name into the SQL instead of the value.

Collectives™ on Stack Overflow

Trying to insert data from windows form to database via mysql, error?

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related