Link to home
Start Free TrialLog in
Avatar of Andrew Crofts
Andrew CroftsFlag for Ukraine

asked on

Update MySQL from .net with parameters

I have been having problems updating a MySQL database from c# code using parameters.

I have reduced it done to the code below.

This has the effect of updating the 'head' field with NULL

MySQL 5, Visual Studio 2010

I am populating strReturn with some info for debugging and I get this from it

update newsitems set head = @Head where id = 233 ; (command text)
@Head (parameter name)
Match Fees test (parameter value)
1 (rows affected)

Can someone tell me where I am going wrong?

      string strReturn = "";
      string strSql;
      string strHead = "Match Fees test";

      strSql = "update newsitems set head = @Head where id = 233 ;";

      using (MySqlConnection conn = new MySqlConnection(strConstring))
      {
        MySqlCommand cmd = null;

        try
        {

          conn.Open();

          cmd = new MySqlCommand(strSql, conn);
          cmd.CommandType = CommandType.Text;
          cmd.Prepare();

          cmd.Parameters.AddWithValue("@Head", strHead);

          strReturn += cmd.CommandText.ToString() + Environment.NewLine ;

          for (int i = 0; i < cmd.Parameters.Count; i++)
          {
            strReturn += cmd.Parameters[i].ParameterName.ToString() + Environment.NewLine;
            strReturn += cmd.Parameters[i].Value.ToString() + Environment.NewLine;
          }

          int intAffected = cmd.ExecuteNonQuery();
          strReturn += intAffected.ToString() + Environment.NewLine;

        }
        catch (Exception ex)
        {
        }
        finally
        {
          conn.Close();
        }
      }

      return strReturn;

Open in new window

Avatar of Rahul Agarwal
Rahul Agarwal
Flag of India image

You can use like this

Dim cmdText = "UPDATE contact_log SET note = @note WHERE customer = @cust"
Using conn = new MySqlConnection(connString)
Using cmd = new MySqlCommand(cmdText, conn)
    conn.Open()
    cmd.Parameters.AddWithValue("@note",TextBox2.Text)
    cmd.Parameters.AddWithValue("@cust",Request.QueryString("ID"))
    Dim rowsAffected = cmd.ExecuteNonQuery()
End Using
End Using
Avatar of Andrew Crofts

ASKER

So what's different there?

Why is mine not working?
ASKER CERTIFIED SOLUTION
Avatar of Andrew Crofts
Andrew Crofts
Flag of Ukraine 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
Solution not supplied. Found the answer myself as detailed in this comment