MySqlCommand Command.Parameters.Add is obsolete

Question

I'm making an C# windows Form Application in visual studio 2010.

That application is connecting to an mysql database, and I want to insert data in it.

Now do I have this part of code:

MySqlConnection connection;
string cs = @"server=server ip;userid=username;password=userpass;database=databse";
connection = new MySqlConnection(cs);
connection.Open();

MySqlCommand command = new MySqlCommand();
string SQL = "INSERT INTO `twMCUserDB` (`mc_userName`, `mc_userPass`, `tw_userName`, `tw_userPass`) VALUES ('@mcUserName', '@mcUserPass', '@twUserName', '@twUserPass')";
command.CommandText = SQL;
command.Parameters.Add("@mcUserName", mcUserNameNew);
command.Parameters.Add("@mcUserPass", mcUserPassNew);
command.Parameters.Add("@twUserName", twUserNameNew);
command.Parameters.Add("@twUserPass", twUserPassNew);
command.Connection = connection;
command.ExecuteNonQuery();
connection.Close();

The connection is fine. That works.

I readed here that the way that I have now, is an save way to do query's. Is that still right?

And now to the real question. With that code above, I get the following warning in visual studio:

'MySql.Data.MySqlClient.MySqlParameterCollection.Add(string, object)' is obsolete: '"Add(String parameterName, Object value) has been deprecated.  Use AddWithValue(String parameterName, Object value)"'

That warning is for every parameters.add

And it isn't even working, because the values that are inserted are @mcUserName, @mcUserPass and so on, instead of the values that the variables mcUserNameNew and so on are holding...

So my question is, am I doing something wrong, and what is the new way to sql injection save do an query?

@Habib, done, warnings are gone, but still not the right value in the db...] — Mathlight
– Mathlight, Commented Nov 27, 2012 at 9:27

silkfire · Accepted Answer · 2016-06-07 09:33:28Z

32

try AddWithValue

command.Parameters.AddWithValue("@mcUserName", mcUserNameNew);
command.Parameters.AddWithValue("@mcUserPass", mcUserPassNew);
command.Parameters.AddWithValue("@twUserName", twUserNameNew);
command.Parameters.AddWithValue("@twUserPass", twUserPassNew);

and don't wrap the placeholders with single quotes.

string SQL = "INSERT INTO `twMCUserDB` (`mc_userName`, `mc_userPass`, `tw_userName`, `tw_userPass`) VALUES (@mcUserName, @mcUserPass, @twUserName, @twUserPass)";

edited Jun 7, 2016 at 9:33

silkfire

26.3k17 gold badges92 silver badges116 bronze badges

answered Nov 27, 2012 at 9:27

John Woo

265k70 gold badges509 silver badges500 bronze badges

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

2 Comments

John Woo Over a year ago

to parameterized the query? yes.

booyaa Over a year ago

@MarcMeesters how about accepting the answer if it worked for you?

Adam Calvet Bohl · Accepted Answer · 2020-04-16 18:39:47Z

2

Edit: As Bradley Grainger pointed out, in MySQL is safe to use AddWithValue. I'm keeping my answer if you get here by chance and use Microsoft SQL.

Please read this article, advising you against using AddWithValue:

https://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/

It says basically that AddWithValue could sometimes incorrectly infer the correct type. Use Add instead.

edited Apr 16, 2020 at 18:39

answered Aug 7, 2018 at 6:31

Adam Calvet Bohl

1,07914 silver badges30 bronze badges

1 Comment

Bradley Grainger Over a year ago

That article is for SQL Server. There's no reason to avoid AddWithValue for MySQL: mysqlconnector.net/overview/using-addwithvalue

Ramgy Borja · Accepted Answer · 2017-08-01 05:48:39Z

1

Just edit/remove some code in this part

('@mcUserName', '@mcUserPass', '@twUserName', '@twUserPass')

to

(@mcUserName, @mcUserPass, @twUserName, @twUserPass)

and Add( to AddWithValue(

answered Aug 1, 2017 at 5:48

Ramgy Borja

2,4682 gold badges24 silver badges41 bronze badges

Comments

Chris · Accepted Answer · 2013-05-17 21:44:32Z

0

@mcUserName has to match the mc_userName in the query ..

so your parm should be @mc_userName

answered May 17, 2013 at 21:44

Chris

91 bronze badge

Comments

guhou · Accepted Answer · 2014-11-27 08:00:01Z

0

This is VB code...

cmd.Parameters.AddWithValue("@number", 1) 'set @number as numeric
cmd.Parameters.AddWithValue("@text", "this will be a text variable") 

cmd.Parameters("@number").Value = 321  'now @number has a value
cmd.Parameters("@text").Value = "A string value" 'now @text has a value

cmd.ExecuteNonQuery()

edited Nov 27, 2014 at 8:00

guhou

1,73213 silver badges32 bronze badges

answered Jun 5, 2013 at 20:55

David

191 bronze badge

1 Comment

Paul S Chapman Over a year ago

i think you will find that you should be using square brackets around the parameter name so 'cmd.Parameters["@number"].Value = 321'

Peter Csala · Accepted Answer · 2022-01-19 10:16:10Z

0

should be used as such to prevent any errors set the dbtype correctly then assign

cmd.Parameters.Add("@ID", MySqlDbType.Int32);

correct way to set a MySqlDBType

cmd.Parameters["@ID"].Value = 1;

now set the value

edited Jan 19, 2022 at 10:16

Peter Csala

23.7k16 gold badges51 silver badges96 bronze badges

answered Jan 19, 2022 at 9:55

David W

1

Collectives™ on Stack Overflow

MySqlCommand Command.Parameters.Add is obsolete

6 Answers 6

2 Comments

1 Comment

Comments

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

2 Comments

1 Comment

Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related