Database not committing changes

[monkeybut]

I can run the program and step through the code and it is showing the right query data, but whenever the process is complete the changes are not committed in the database, I am needing to change teh value of grouping with this web app but the changes are not taking, can anyone give me any pointers on why my data isn't committing, thanks. Here is the method:

if (isUpdatePass)
    {
        oconn.Open();
        updatessql = "update openquery(trimcellat01,'Select GROUPING from WORKCELL.COMPONENT_MATERIAL_ID  where MATERIAL_ID = ''{1}''')SET GROUPING = '{0}; COMMIT; END;'";
        // CommitTransaction
        updatessql = string.Format(updatessql, this.RadioButtonList1.SelectedValue.ToString(), TextBox1.Text.ToUpper());
        ocmd.CommandText = updatessql;
        ocmd.Connection = oconn;
        ocmd.ExecuteNonQuery();
        errormessage.Text = "Material ID grouping has been changed";
        Session["LookupPass"] = "1";
        Session["UpdatePass"] = "0";
        Session.Remove("Material_ID");
        ocmd.Dispose();
        oconn.Close();
        oconn.Dispose();
        return;
    }

[thines01]

That update SQL seems to be malformed.
What is the name of the table it is updating?

[monkeybut]

that update sql seems to be malformed. What is the name of the table it is updating?

component_material_id

[thines01]

OK. Unless you're doing something [ special / unorthodox / proprietary ],
your SQL should start out like:

string updatessql =
   string.Format("update component_material_id set GROUPING='{0}'",
      this.RadioButtonList1.SelectedValue.ToString());

Are you also attempting to do a sub-select?

Anyway: Afterward, you control the commit by either executing and actual Commit on a transaction you create OR by closing the connection to the database. -- but not inside the SQL.

[mani-hellboy]

what is your connection string

[monkeybut]

what is your connection string

string ConnStr = @"SERVER=ATNAPIAP01\SQLEXPRESS;USER=sa;password=***********;initial catalog=Andon;MultipleActiveResultSets=True";

[thines01]

What I'm specifically talking about is removing the excess code from your SQL statement (that tries to do multiple things) and just concentrate on the update.
Let the framework handle the commit (outside of the SQL)
Even though you're using SQL Server Express, this will translate.

Example:

public static bool StoreToMySql(CSomeDataMaster master, ref string strError)
{
   bool blnRetVal = true;

   try
   {
      using (MySqlConnection conn = new MySqlConnection(CDB_X.csb.ToString()))
      {
         conn.Open();
         MySqlTransaction trans = conn.BeginTransaction();

         MySqlCommand cmd = new MySqlCommand("DELETE FROM SOME_TABLE", conn, trans);
         cmd.ExecuteNonQuery();

         string strSQL =
            "insert into SOME_TABLE (FAKE_COLUMN1, FAKE_COLUMN2) " +
            "VALUES(?pFAKE_VALUE1, ?pFAKE_VALUE2)";

         cmd = new MySqlCommand(strSQL, conn, trans);
         
         InitParams(cmd); // my method that inits params (not necessary for this example)

         foreach (CSomeData dataObj in master)
         {
            FillParams(cmd, dataObj); // my method that fills parameters (not necessary for this example)
            cmd.ExecuteNonQuery();
         }

         trans.Commit(); // <<--ONE commit at the end of all grouped actions
         conn.Close();
      }
   }
   catch (Exception exc)
   {
      blnRetVal = false;
      strError = exc.Message;
   }

   return blnRetVal;
}

Also, you might not need to call commit at all.
If you are closing the connection after your command, it will commit.
In either case, remove the COMMIT and also the END from your SQL string.