Click here to Skip to main content
15,943,233 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
C#
  connection()
{
      OleDbConnection nwindConn=new OleDbConnection();
            MySql.Data.MySqlClient.MySqlConnection con= new MySqlConnection();
            MySqlCommand cmd;
            con.ConnectionString ="server=localhost;" +
                                   "uid=root;"+
                                   "pwd=;" +
                                   "database=globasys;" ;
            DateTime dt=DateTime.Now;
          string select = "SELECT CategoryID, CategoryName FROM categories";
            MySqlDataAdapter catDA = new MySqlDataAdapter(select, con);
            string insert = "insert into categories(CategoryID,CategoryName) VALUES(@CategoryID,@CategoryName)ON DUPLICATE KEY UPDATE CategoryID=@CategoryID";

=

            catDA.InsertCommand = new MySqlCommand(insert, con);
            catDA.InsertCommand.Parameters.Add("@CategoryID", MySqlDbType.Int32, 11, "CategoryID");
            catDA.InsertCommand.Parameters.Add("@CategoryName", MySqlDbType.VarChar, 250, "CategoryName");

            DataSet catDS = new DataSet();
            catDA.MissingSchemaAction = MissingSchemaAction.AddWithKey;
            catDA.Fill(catDS, "Categories");
            DataSet newdt = new DataSet();
            newdt = getnewdata();
           int i= catDA.Update(newdt, "Categories");
        }

        public DataSet getnewdata()
        {
           DataSet catDS=new DataSet();
           DataTable dt = new DataTable();
            DataColumn col1 = new DataColumn("CategoryID", typeof(int));
            dt.Columns.Add(col1);
                     DataColumn col=new      DataColumn("CategoryName",typeof(string));
            dt.Columns.Add(col);
            //catDS.Tables[0].Columns.Add(col);
            DataColumn[] Cols = { dt.Columns[0] };
            dt.PrimaryKey =Cols;
            DataRow crow = dt.NewRow();
                crow["CategoryID"]=1;
                crow["CategoryName"]="ffg";
                dt.Rows.Add(crow);
                dt.TableName = "Categories";
               DataRow crow1 = dt.NewRow();
                crow1["CategoryID"] = 3;
                crow1["CategoryName"] = "tejfgfdashri";
                dt.Rows.Add(crow1);
                dt.TableName = "Categories";
                catDS.Tables.Add(dt);
            return catDS;
        }

i will be used the ON DUPLICATE KEY UPDATE insert query for update the already exist but it will be execute the catda.update(newdt) it will be return 2 row affected but table values are not changed it will be remains as it is.
tanks in advance

[Edit: Unchecked "Ignore HTML in text"]
Posted
Updated 28-May-11 1:20am
v2

1 solution

Is column CategoryID or CategoryName marked as UNIQUE or PRIMARY KEY in MySql ?
If not, then INSERT ... ON DUPLICATE KEY UPDATE will not works as you expected.

Please see INSERT ... ON DUPLICATE KEY UPDATE Syntax[^]

Try to execute the INSERT statement your self and see what happens.
 
Share this answer
 
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900