locked
MySQL Update does not work with parameters, ASP.net with C# RRS feed

  • Question

  • User-759310006 posted

    Hello, I've been working on a website and i am having a problem with updating the account table.

    The insert and select procedure works just fine.

    This is where thing get wierd, i use this sql command 

    string sql = "update `crm_customers` set `crm_s0`= @USERNAME , `crm_s1`=@PASS ,`crm_name`= @NAME ,
    
    `crm_surname`= @SURNAME , `crm_email`= @EMAIL , `crm_addressStreet` = @ADDRESS , `crm_area` = @COUNTRY ,
    
    `crm_PostCode` = @POSTCODE , `crm_city`= @CITY , `crm_MobilePhone1` = @MOBILE , `crm_s4` = @OCCUPATION
    
    WHERE `crm_ID` = @CRMID;";



    And then i set the values of the parameters

       using (MySqlConnection cn = new MySqlConnection(ConnectionString))
            {
    
                // Create a SqlCommand.
                MySqlCommand cmd = new MySqlCommand(sql, cn);
    
                cmd.Parameters.Add("@USERNAME", MySqlDbType.VarChar).Value = crm_s0;
                cmd.Parameters.Add("@PASS", MySqlDbType.VarChar).Value = crm_s1;
                cmd.Parameters.Add("@NAME", MySqlDbType.VarChar).Value = crm_name;
                cmd.Parameters.Add("@SURNAME", MySqlDbType.VarChar).Value = crm_surname;
                cmd.Parameters.Add("@EMAIL", MySqlDbType.VarChar).Value = crm_email;
                cmd.Parameters.Add("@ADDRESS", MySqlDbType.VarChar).Value = crm_AddressStreet;
                cmd.Parameters.Add("@COUNTRY", MySqlDbType.VarChar).Value = crm_area;
                cmd.Parameters.Add("@POSTCODE", MySqlDbType.Int32).Value = crm_PostCode;
                cmd.Parameters.Add("@CITY", MySqlDbType.VarChar).Value = crm_City;
                cmd.Parameters.Add("@MOBILE", MySqlDbType.VarChar).Value = crm_MobilePhone1;
    
                if (!(crm_HomePhone == ""))
                {
                    cmd.Parameters.Add("", MySqlDbType.VarChar).Value = crm_HomePhone;
                }
                else
                {
                    cmd.Parameters.Add("", MySqlDbType.VarChar).Value = "";
                }
    
                if (!(crm_s4 == ""))
                {
                    cmd.Parameters.Add("@OCCUPATION", MySqlDbType.VarChar).Value = crm_s4;
    
                }
                else
                {
                    cmd.Parameters.Add("@OCCUPATION", MySqlDbType.VarChar).Value = "";
                }
                cmd.Parameters.Add("@CRMID", MySqlDbType.Int32).Value = crm_ID;
    
                try
                {
                    // Open the connection.
                    cn.Open();
    
                    cmd.ExecuteNonQuery();
                    isEverythingOk = true;
                }

    In step By step debugging (F11) it shows that the command is excecuted normally, no exceptions happen. But the funny thing is that in MySql Workbench i run this query

    select * from crm_customers;

    and the account is not changed. 

    I tried to do this the other way, by setting the update command without any variables and it worked it updated the values.

    string sql= "UPDATE `crm_customers`
    SET
    `crm_name` = 'name',`crm_surname` = 'surname',
    `crm_AddressStreet` = 'address',
    `crm_PostCode` = 123456,
    `crm_City` = 'City',
    `crm_area` = 'country',
    `crm_HomePhone` = '2103636365',
    `crm_MobilePhone1` = '6366699696',
    `crm_email` = 'asda@asdas.grr',
    `crm_s0` = 'username',
    `crm_s1` = 'password',
    `crm_s4` = 'occupation'
    WHERE crm_ID=6;";

    What is wrong?

    Tuesday, February 19, 2013 8:37 AM

Answers

  • User-759310006 posted
    anyway, today i searched it a little bit more and discovered that when the button is clicked the page reloads and creates again the  customer object but with id -1 so when it tries to update the database it doesn't find a record with id=-1. That is why nothing happens.I saved the customer id in a hidden field in my page and now it is working fine.

    thanks for your replies :)
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, February 20, 2013 12:43 PM

All replies

  • User1221318268 posted

    The qutations is not required use the below code.

     string sql= "UPDATE crm_customers 
    SET 
    crm_name = 'name',crm_surname = 'surname', 
    crm_AddressStreet = 'address', 
    crm_PostCode = 123456, 
    crm_City = 'City', 
    crm_area = 'country', 
    crm_HomePhone = '2103636365', 
    crm_MobilePhone1 = '6366699696', 
    crm_email = 'asda@asdas.grr', 
    crm_s0 = 'username', 
    crm_s1 = 'password', 
    crm_s4 = 'occupation' 
    WHERE crm_ID=6;"
    ;

     

    regards,

    thotakura

    Tuesday, February 19, 2013 8:58 AM
  • User-759310006 posted

    I erased them but the problem remains the same. it passes the comand cmd.ExcecuteNonQuery(); and no change in the database. Does this have to do with this part of code:

     protected void Page_Load(object sender, EventArgs e)
        {
            if (!Page.IsPostBack)
            {
            
                if (auth.isAuthorized())
                {
                    customer.crm_s0 = auth.getUsername();
                    customer.select();
                    loadData();
                }
                else
                {
                    Response.Redirect("~/Account/LogIn.aspx");
                }
            }
        }



    Tuesday, February 19, 2013 9:10 AM
  • User-759310006 posted
    anyway, today i searched it a little bit more and discovered that when the button is clicked the page reloads and creates again the  customer object but with id -1 so when it tries to update the database it doesn't find a record with id=-1. That is why nothing happens.I saved the customer id in a hidden field in my page and now it is working fine.

    thanks for your replies :)
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, February 20, 2013 12:43 PM