Link to home
Start Free TrialLog in
Avatar of Jim Riddles
Jim RiddlesFlag for United States of America

asked on

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

Avatar of Zvonko
Zvonko
Flag of North Macedonia image

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


Avatar of Jim Riddles

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
Avatar of Jim Riddles
Jim Riddles
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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;
}



Open in new window

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 


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.
It's my pleasure  :  )