Mysqlcommand Command.Parameters.Add Is Obsolete

MySqlCommand Command.Parameters.Add is obsolete

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

Query working in mysql but not from c# code

You need to change how you are adding parameters slightly:

In your SQL, no quotes and no % symbols.

using (MySqlCommand cmd = new MySqlCommand(@"SELECT PORUDZBINAID, USERID, BRDOKKOM, DATUM,
                STATUS, MAGACINID, PPID, INTERNIKOMENTAR, REFERENT_OBRADE, NACIN_PLACANJA, TAG FROM 
                PORUDZBINA WHERE TAG LIKE @MOB", con))
    {

Then the parameter like this, without quotes.

cmd.Parameters.AddWithValue("@MOB", "%" + Mobilni + "%");

BTW: Ideally you should not use AddWithValue, but rather Add(). See this blog:
https://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/

And this SO post:
MySqlCommand Command.Parameters.Add is obsolete

Instead, it should be like this:

cmd.Parameters.Add("@MOB", SqlDbType.Varchar).Value = "%" + Mobilni + "%";
//you must update to use the correct DBType for your data

SqlCommand Parameters Add vs. AddWithValue

Use Add if you want to make all explicit with a little bit more work. Use AddWithValue if you are lazy. AddWithValue will derive the type of the parameter of its value, so ensure that it's the correct type. You should, for example, parse a string to int if that is the correct type.

There is one reason to avoid Add: if your parameter type is int you must be careful with the overload that takes the parameter-name and an object since then another overload is chosen with the SqlDbType-enum.

From remarks (method overload is even obsolete now):

Use caution when you are using this overload of the
SqlParameterCollection.Add method to specify integer parameter values.
Because this overload takes a value of type Object, you must convert
the integral value to an Object type when the value is zero
... If you do not perform this conversion, the
compiler assumes that you are trying to call the
SqlParameterCollection.Add(string, SqlDbType) overload.

How to add parameters to a query when query is unknown length

Use SqlCommand.Parameters.Add.

Instead of using foreach use for, and add parameter names with appended number "@tok" + i

https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlparametercollection.addwithvalue?view=netframework-4.8

Just example draft code, I didn't try it:

var tokens = new List<dynamic>() { new { tok = 1, logedinId = 2 }, new { tok = 1, logedinId = 2 } };
var query = "INSERT INTO tokenlist(token, user_id) VALUES";

SqlCommand cmd = new SqlCommand(query, new SqlConnection());

for (int i = 0; i < tokens.Count; i++)
{
    var tokName = $"@tok{i}";
    var logedinIdName = $"@logedinId{i}";
    var token = tokens[i];
    query += $" ({tokName}, {logedinIdName}),";
    cmd.Parameters.Add(new SqlParameter(tokName, SqlDbType.Int)).Value = token.tok;
    cmd.Parameters.Add(new SqlParameter(logedinIdName, SqlDbType.Int)).Value = token.logedinId;
}

if (tokens.Any())
{
    query = query.Remove(query.Length - 1);
    query += ";";

    cmd.ExecuteNonQuery();
}

MySql command parameters with quotes

I really appreciate your help ladies and gentlemen - and special thanks to Progman, your comment solved my problem.

I'll now answer my own question to sum it up. There are 3 possible solutions:

The whole issue is related to the SQL mode NO_BACKSLASH_ESCAPES. One solution could be turning off that particular SQL mode.

fubo mentioned in his comment:

The escaping result of MySql.Data.MySqlClient.MySqlHelper.EscapeString("12'34") is 12\'34

It is weird but the SQL mode NO_BACKSLASH_ESCAPES struggles with the escape strings of parameter values (imo this is a bug in Connector/Net since Connector/ODBC works fine). Another solution could be using Connector/ODBC.

The final trick for me was Progmans comment:

According to dev.mysql.com/doc/connector-net/en/… you have to call Prepare() before adding the values.

This is more like a workaround, but if you use SQL mode NO_BACKSLASH_ESCAPES and command parameters together, you have to call Prepare() before calling ExecuteNonQuery(). Please note that - unlike the example in the developer guide - i couldn't call it before adding the parameters (you'll get an exception), and i added IgnorePrepare=false to the connection string. I assume the reason why this works is because it changes the data transfer between client and server:

Another advantage of prepared statements is that, with server-side prepared statements enabled, it uses a binary protocol that makes data transfer between client and server more efficient.

Here is the code snippet that works for me:

//add IgnorePrepare=false to the connection string
MySqlConnection Con = new MySqlConnection("Server = localhost; Port = 3306; Database = param_test; Uid = ***; Pwd = ***; SslMode=none; IgnorePrepare=false");
Con.Open();
MySqlCommand Cmd = new MySqlCommand("INSERT INTO testtab(TestCol1,TestCol2) VALUES(@test1,@test2)", Con);

Cmd.Parameters.AddWithValue("@test1", "12'34");
Cmd.Parameters.AddWithValue("@test2", "56");

Cmd.Prepare();  //calling it after Adding the parameters works fine
Cmd.ExecuteNonQuery();

C# MySQL Parameters : ? or @

From the documentation:

Prior versions of the provider used the '@' symbol to mark parameters in SQL. This is incompatible with MySQL user variables, so the provider now uses the '?' symbol to locate parameters in SQL. To support older code, you can set 'old syntax=yes' on your connection string. If you do this, please be aware that an exception will not be throw if you fail to define a parameter that you intended to use in your SQL.

MySQL Parameters.AddWithValue fails/not working in c#

You are trying to use parameters without setting the proper placeholder in the query text.
So change the condition to....

if (Masterpage.Container.theObjectPropertyAut == 1)
{
    sql += " AND doTableEmail = @email ");
}

....

Now add the parameter using the same name used for the placeholder

cmd.Parameters.AddWithValue("@email", strEmail);

Then you have a useless loop that clear the parameter collection at each loop.

You don't need it because you have already added the values directly through the string format above.

// foreach (var param in paramValuenamens)
// {
//     cmd.Parameters.Clear();
//     cmd.Parameters.AddWithValue("param2", param.ToString());
// }

Now this should work, but of course, this is not a complete parameterized query because the IN clause for the doTableNameNs still uses a string concatenation (Format is still a concatenation and doesn't keep you safe from sql injection). This is acceptable only if you are absolutely sure that the value used don't come from user input or other media that you cannot control. Otherwise you need to create a collection of parameters and strings placeholders to insert in the IN statement for doTableNameNs

---

Related Topics