C# with MySQL INSERT parameters

Question

Good day to all, I'm using Visual C# 2010 and MySQL Version 5.1.48-community. I hope you can help me with this code. I don't find it working on me. What am I missing?

string connString = ConfigurationManager.ConnectionStrings["default"].ConnectionString; MySqlConnection conn = new MySqlConnection(connString); conn.Open(); MySqlCommand comm = conn.CreateCommand(); comm.CommandText = "INSERT INTO room(person,address) VALUES(@person, @address)"; comm.Parameters.Add("@person", "Myname"); comm.Parameters.Add("@address", "Myaddress"); comm.ExecuteNonQuery(); conn.Close(); 

And when I try to compile it. It says:

Person column cannot be null

EDITED:

But when I try this code.

comm.CommandText = "INSERT INTO room(person,address) VALUES('Myname', 'Myaddress')"; 

But this code is prone to sql injection attack but it works, doesn't gives me an error.

EDITED:

I tried to use this. I found it here so I thought It would work but gives me this error

Index (zero based) must be greater than or equal to zero and less than the size of the argument list.

Any idea?

    string a = "myname";     MySqlCommand cmd = new MySqlCommand();     cmd.Connection = conn;     cmd.CommandText = "INSERT INTO room(person,address) VALUES(?,?)";     //cmd.Prepare();      cmd.Parameters.Add("person", MySqlDbType.VarChar).Value = a;     cmd.Parameters.Add("address", MySqlDbType.VarChar).Value = "myaddress";     cmd.ExecuteNonQuery(); // HERE I GOT AN EXCEPTION IN THIS LINE 

Any help would be much appreciated.

EDITED: SOLVED I used this code:

cmd.CommandText = "INSERT INTO room(person,address) VALUES(?person,?address)"; cmd.Parameters.Add("?person", MySqlDbType.VarChar).Value = "myname"; cmd.Parameters.Add("?address", MySqlDbType.VarChar).Value = "myaddress"; cmd.ExecuteNonQuery(); 

Thanks SO!

Answer 1

You may use AddWithValue method like:

string connString = ConfigurationManager.ConnectionStrings["default"].ConnectionString; MySqlConnection conn = new MySqlConnection(connString); conn.Open(); MySqlCommand comm = conn.CreateCommand(); comm.CommandText = "INSERT INTO room(person,address) VALUES(@person, @address)"; comm.Parameters.AddWithValue("@person", "Myname"); comm.Parameters.AddWithValue("@address", "Myaddress"); comm.ExecuteNonQuery(); conn.Close(); 

OR

Try with ? instead of @, like:

string connString = ConfigurationManager.ConnectionStrings["default"].ConnectionString; MySqlConnection conn = new MySqlConnection(connString); conn.Open(); MySqlCommand comm = conn.CreateCommand(); comm.CommandText = "INSERT INTO room(person,address) VALUES(?person, ?address)"; comm.Parameters.Add("?person", "Myname"); comm.Parameters.Add("?address", "Myaddress"); comm.ExecuteNonQuery(); conn.Close(); 

Hope it helps...

Answer 2

Use the AddWithValue method:

comm.Parameters.AddWithValue("@person", "Myname"); comm.Parameters.AddWithValue("@address", "Myaddress");