CODEWITHSUNDEEP
c#mysqlsqlsql-insert
user14470922
user14470922

Reputation: 5

How to Insert C# values to MySql?

I want to insert C# winform values to Mysql

there are 3 columns

name,id are TextBox text and gender is ComboBox value

but there is error and error messsage said: MySql.Data.MySqlClient.MySqlException: '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 '',')' at line 1'

what code should i fix?

using (MySqlConnection conn2 = new MySqlConnection(strconn))
        { 
            conn2.Open();
            string query = "INSERT INTO student1(name,id,gender) values ('" + name3.Text + "'," + id3.Text + "'," + gender3.SelectedValue+"');";
            MySqlCommand cmd = new MySqlCommand(query, conn2);
            cmd.ExecuteNonQuery();
        }

Upvotes: 0

Views: 630

Answers (2)

Joel Coehoorn
Joel Coehoorn

Reputation: 416131

You're missing a single quote in the connecting string literal between name3.Text and id3.Text and again between id3.Text and gender3.SelectedValue

But it shouldn't matter. If you're concatenating user-supplied strings like this it's only a matter of time until your system is breached. There's a better way that avoids this risk, makes it easier to get your SQL statements correct, and runs faster.

//This could be marked const!
string query = "INSERT INTO student1(name,id,gender) values (@name, @id, @gender);";

using (var conn2 = new MySqlConnection(strconn))
using (var cmd = new MySqlCommand(query, conn2))
{ 
    //I have to guess, but you can find exact column types/lengths from your db
    //Do not use AddWithValue()!
    // MySql is less at risk for the AddWithValue() performance issues
    // than Sql Server but the risk isn't completely eliminated.
    cmd.Parameters.Add("@name", MySqlDbType.VarChar, 30).Value = name3.Text;
    cmd.Parameters.Add("@id", MySqlDbType.VarChar, 50).Value = id3.Text; 
    cmd.Parameters.Add("@gender", MySqlDbType.VarChar, 5).Value = gender3.SelectedValue;

    conn2.Open();
    cmd.ExecuteNonQuery();
}

Upvotes: 5

Chameera
Chameera

Reputation: 217

 using (MySqlConnection conn2 = new MySqlConnection(strconn))
            {
                String query = "INSERT INTO student1(name,id,gender) values (@name,@id,@gender)";
                MySqlCommand = new MySqlCommand(query, conn2);
                command.Parameters.AddWithValue("@name", name3.Text);
                command.Parameters.AddWithValue("@id", id3.Text);
                command.Parameters.AddWithValue("@gender", gender3.SelectedValue.ToString());
                command.ExecuteNonQuery();
            }

use need use parameters

Upvotes: 0

Related Questions