0

I have a table which contains a single column "CODE_CAR". I want to populate this table with list of string as below :

_Connection.Open();
var lst_code_cars = new List<string> { "YH_21", "NM_00", "BAR_N178" };
string cmdText = "INSERT INTO Y157.CARS_IDENDITY(CODE_CAR) VALUES ("+lst_code_cars+")";
MySqlCommand cmd = new MySqlCommand(cmdText, _Connection);
foreach (string cars_code in lst_code_cars )
{
   cmd.Parameters.AddWithValue("@"+cars_code , cars_code );
   cmd.ExecuteNonQuery();
}
_Connection.Close();

When i try this code, i get error : MySqlException : Parameter '@NM_00' must be defined.

Improve this question
2
  • 1
    Side note, you can send these values all at one time to insert into your table (will require a few changes to the query), fewer trips to the DB is always better. Also when adding a parameter, please define the length and the actual type of the parameter, when not doing so, the DB has to infer the DB type and it may not be what it should be. Commented Mar 23, 2021 at 14:07
  • 1
    As well as the obvious SQL injection, given your connection object is called _Connection, I suspect you are caching it. Don't cache connection objects, create when you need them and dispose with using, see stackoverflow.com/questions/17552829/… Commented Mar 23, 2021 at 14:19

1 Answer 1

Reset to default
1

The error tells you exactly what is wrong. You did not define a parameter, you concatenated the value directly into the query string. So don't do that.

_Connection.Open();
var lst_code_cars = new List<string> { "YH_21", "NM_00", "BAR_N178" };
string cmdText = "INSERT INTO Y157.CARS_IDENDITY(CODE_CAR) VALUES (@CarCode)";
MySqlCommand cmd = new MySqlCommand(cmdText, _Connection);
foreach (string cars_code in lst_code_cars )
{
   cmd.Parameters.Clear();
   cmd.Parameters.AddWithValue("@CarCode" , cars_code );
   cmd.ExecuteNonQuery();
}
_Connection.Close();

You'll also need to clear the parameters if you mean to re-add them each loop. Either that or add the parameter once and then edit the value in the loop instead of adding a new parameter each time.

Ideally, though, you could write this as a batch insert.

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

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.