We help IT Professionals succeed at work.

Issue with Prepared Statement in C# with MySQL

Jim Riddles
Jim Riddles asked
on
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

Comment
Watch Question

View Solution Only

ZvonkoSystems architect
CERTIFIED EXPERT
Top Expert 2006

Commented:
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;
}

Jim RiddlesPrepress/OMS Specialist
CERTIFIED EXPERT

Author

Commented:
@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

Prepress/OMS Specialist
CERTIFIED EXPERT
Commented:
Okay, so after a bit more tinkering I have my answer.  Below is my final code.  I don't know if this is the best or proper way, but it works.
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++)
  {
    cmd.Parameters["@code"].Value = Codes[i].ToString();
    cmd.ExecuteNonQuery();
  }
  return true;
}

Open in new window

I actually tried setting the field type as VarChar, but it did not work...couldn't convert my string to the proper format, I guess.  Setting it to an empty string initially allowed me to override it within the for loop as shown.  If someone can show me the proper way to do this, I will happily use it.  Thanks for your help @Zvonko.
ZvonkoSystems architect
CERTIFIED EXPERT
Top Expert 2006

Commented:
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;
}
ZvonkoSystems architect
CERTIFIED EXPERT
Top Expert 2006

Commented:
But your reference by parameter name is better then by index....

My example is from here:
https://docs.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlcommand.prepare?view=netframework-4.8 


Jim RiddlesPrepress/OMS Specialist
CERTIFIED EXPERT

Author

Commented:
Many thanks Zvonko.  I do appreciate all the help you've provided not only on this question but on several others that I have asked recently.
ZvonkoSystems architect
CERTIFIED EXPERT
Top Expert 2006

Commented:
It's my pleasure  :  )