Issue with Prepared Statement in C# with MySQL

I have created a function that takes a List of codes and some other information and attempts to insert the data into a MySQL table. I'm trying to use a prepared statement, but for some reason the records do not actually record the code value. Can you tell me what I am doing wrong with my approach, or is there a better way of going about this? Code is as follows:

public static bool WriteToDatabase(IList Codes, string PONumber, string ValidFrom, string ValidTo)
{
  if (Codes.Count < 1 || String.IsNullOrEmpty(PONumber))
  {
    return false;
  }
  string sql = "INSERT INTO gift_codes (code, customerPO, validFrom, validTo) values (@code, @po, @from, @to)";
  string code = "";
  MySqlCommand cmd = new MySqlCommand(sql, Conn);
  cmd.Parameters.AddWithValue("@po", PONumber);
  cmd.Parameters.AddWithValue("@from", ValidFrom);
  cmd.Parameters.AddWithValue("@to", ValidTo);
  cmd.Parameters.AddWithValue("@code", code);
  cmd.Prepare();
  for (int i = 0; i < Codes.Count; i++)
  {
    code = Codes[i].ToString();
    cmd.ExecuteNonQuery();
  }
  return true;
}

Open in new window

C#MySQL Server

Last Comment

Zvonko

8/22/2022 - Mon

Zvonko

3/11/2020

Check this:

public static bool WriteToDatabase(IList Codes, string PONumber, string ValidFrom, string ValidTo)
{
  if (Codes.Count < 1 || String.IsNullOrEmpty(PONumber))
  {
    return false;
  }
  string sql = "INSERT INTO gift_codes (code, customerPO, validFrom, validTo) values (@code, @po, @from, @to)";
  string code = "";
  MySqlCommand cmd = new MySqlCommand(sql, Conn);
  cmd.Parameters.AddWithValue("@po", PONumber);
  cmd.Parameters.AddWithValue("@from", ValidFrom);
  cmd.Parameters.AddWithValue("@to", ValidTo);
  cmd.Parameters.AddWithValue("@code", code);
  cmd.Prepare();
  for (int i = 0; i < Codes.Count; i++)
  {
    code = Codes[i].ToString();
    cmd.Parameters[3] = code;
    cmd.ExecuteNonQuery();
  }
  return true;
}

Open in new window

Jim Riddles

3/11/2020

ASKER

@Zvonko
I get an error: Cannot implicitly convert type 'string' to MySql.Data.MySqlClient.MySqlParameter. After a brief search I found a way to create a new MySqlParameter and assign the value to my code, however when I try to use that param, it claims it is null. Here is my revised code:

public static bool WriteToDatabase(IList Codes, string PONumber, string ValidFrom, string ValidTo)
{
  if (Codes.Count < 1)
  {
    return false;
  }
  if (String.IsNullOrEmpty(PONumber))
  {
    return false;
  }
  string sql = "INSERT INTO gift_codes (code, customerPO, validFrom, validTo) values (@code, @po, @from, @to)";
  MySqlCommand cmd = new MySqlCommand(sql, Conn);
  cmd.Parameters.AddWithValue("@code", "");
  cmd.Parameters.AddWithValue("@po", PONumber);
  cmd.Parameters.AddWithValue("@from", ValidFrom);
  cmd.Parameters.AddWithValue("@to", ValidTo);
  cmd.Prepare();
  for (int i = 0; i < Codes.Count; i++)
  {
    MySqlParameter code = new MySqlParameter
    {
      Value = Codes[i].ToString()
    };
    cmd.Parameters[0] = code;
    cmd.ExecuteNonQuery();
  }
  return true;
}

Open in new window

ASKER CERTIFIED SOLUTION

Jim Riddles

3/11/2020

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

GET A PERSONALIZED SOLUTION

Ask your own question & get feedback from real experts

Find out why thousands trust the EE community with their toughest problems.

Zvonko

3/11/2020

Sorry, there was a missing attribute:

public static bool WriteToDatabase(IList Codes, string PONumber, string ValidFrom, string ValidTo)
{
  if (Codes.Count < 1 || String.IsNullOrEmpty(PONumber))
  {
    return false;
  }
  string sql = "INSERT INTO gift_codes (code, customerPO, validFrom, validTo) values (@code, @po, @from, @to)";
  string code = "";
  MySqlCommand cmd = new MySqlCommand(sql, Conn);
  cmd.Parameters.AddWithValue("@po", PONumber);
  cmd.Parameters.AddWithValue("@from", ValidFrom);
  cmd.Parameters.AddWithValue("@to", ValidTo);
  cmd.Parameters.AddWithValue("@code", code);
  cmd.Prepare();
  for (int i = 0; i < Codes.Count; i++)
  {
    code = Codes[i].ToString();
    cmd.Parameters[3].Value = code;
    cmd.ExecuteNonQuery();
  }
  return true;
}