Avatar of Jim Riddles
Jim Riddles
Flag 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

C#MySQL Server

Avatar of undefined
Last Comment
Zvonko

8/22/2022 - Mon
Zvonko

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

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

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

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

This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Zvonko

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 Riddles

ASKER
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.
Zvonko

It's my pleasure  :  )
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.