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

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");
    }
}

CodePudding user response：

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.

CodePudding user response：

Thank you so so much! I will look into more tutorials about sql. I know I got into deep water since I am a noob ^^ But it's so much fun. I will read all of your comments and get better.