Update Query problem in asp.net c# and Mysql using

Question

when i specify values in my update query the query works fine and the database gets updated, but when i use parameters in my query the database does not update

here is the code i have written

try
        {
            OdbcConnection MyConnection = new OdbcConnection(ConfigurationManager.ConnectionStrings["myconn"].ConnectionString);

MyConnection.Open();
            String MyString = "UPDATE orddetpabak SET jud1=@jud1,jud2=@jud2,jud3=@jud3,adv=@adv where fil_no=@fil_no AND orderdate=@orderdate";
            OdbcCommand MyCmd = new OdbcCommand(MyString, MyConnection);
            String j1=DropDownList4.SelectedValue;
            String j2=DropDownList5.SelectedValue;
            String j3=DropDownList6.SelectedValue;
            String j4=TextBox4.Text;
            String j5 = HiddenField1.Value;
            String j6 = TextBox3.Text;

            MyCmd.Parameters.AddWithValue("@jud1",j1);
            MyCmd.Parameters.AddWithValue("@jud2",j2);
            MyCmd.Parameters.AddWithValue("@jud3",j3);
            MyCmd.Parameters.AddWithValue("@adv",j4);
            MyCmd.Parameters.AddWithValue("@fil_no",j5);
            MyCmd.Parameters.AddWithValue("@orderdate",j6);

            Response.Write(DropDownList4.SelectedValue);
            Response.Write("    " + DropDownList5.SelectedValue);
            Response.Write("    " + DropDownList6.SelectedValue);
            Response.Write("    " + TextBox4.Text);
            Response.Write("    " + HiddenField1.Value);
            Response.Write("    " + TextBox3.Text);


            MyCmd.ExecuteNonQuery();
            //MyConnection.Close();
        }
        catch(Exception epp)
        {
            Response.Write(epp);
        }

Please Help

Answer 1

It can be like the following: (I'm using the ADO.NET driver for MySQL version 6.3.7.0, latest one had some issues).

public bool UpdateCustomerIAR(IAR oIAR)
{
    bool bRetVal = false;
    try
    {
       MySqlConnection dbConnection = new MySqlConnection(APPSConn.ConnectionString);
       MySqlCommand dbCommand = dbConnection.CreateCommand();
       string szSQL = string.Empty;
       szSQL = "UPDATE schema.table_name SET field_name_one=?field_name_one";
       szSQL += " WHERE field_name_two=?field_name_two";
       using (MySql.Data.MySqlClient.MySqlConnection conn = new
MySql.Data.MySqlClient.MySqlConnection(APPSConn.ConnectionString))
        {
          MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand();
          cmd.Connection = conn;
          cmd.CommandText = szSQL;
          cmd.Parameters.AddWithValue("?field_name_one", oIAR.Title);
          cmd.Parameters.AddWithValue("?field_name_two", oIAR.IARID.ToString());
          conn.Open();
          cmd.ExecuteNonQuery();
          bRetVal = true;
      }
      return bRetVal;
   }
   catch (MySqlException ex)
   {
      ErrorHandler(ex.ToString());
      return bRetVal;
   }
   catch (Exception ex)
   {
      ErrorHandler(ex.ToString());
      return bRetVal;
   }
}

Answer 2

As far as I know you cannot use named parameters in MySQL. If you change your string to be

String MyString = "UPDATE orddetpabak SET jud1=?,jud2=?,jud3=?,adv=? 
where fil_no=? AND orderdate=?";

and your parameters as:

MyCmd.Parameters.AddWithValue("",j1);
MyCmd.Parameters.AddWithValue("",j2);
MyCmd.Parameters.AddWithValue("",j3);
MyCmd.Parameters.AddWithValue("",j4);
MyCmd.Parameters.AddWithValue("",j5);
MyCmd.Parameters.AddWithValue("",j6);

Hope this helps.