Click here to Skip to main content
16,004,761 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have One Table Call RSN_ALl in Sql database
, I want to Updates its few row by my XML file,based On RSN column Primary key.

I try By sqlcommnadBuilder and other way but its not updating.

Noted: In XMl file I have 2 or 3 rows which have Same "RSN" value as DataBase but other Columns Values are Different Which I need to Update
I Got Exception as

Update requires a valid InsertCommand when passed DataRow collection with new rows.

And When I use CommandBuilder I got Exception as

Violation of PRIMARY KEY constraint 'PK_RSN_All'. Cannot insert duplicate key in object 'dbo.RSN_All'.
The statement has been terminated.


but this Rows Already Present in Sql DataBase TAble
using (SqlConnection cn = new SqlConnection(SqlHelper.ConString))
{
   DataSet DsXmlData = new DataSet();
   DsXmlData.ReadXml(xml_file_path);
   DsXmlData.Tables["RSN_ALL"].PrimaryKey = new DataColumn[] { DsXmlData.Tables["RSN_ALL"].Columns["RSN"] };

   using (SqlDataAdapter da = new SqlDataAdapter("select * from RSN_All", cn))
   {
      DataSet ds = new DataSet();
      da.Fill(ds);

      // SqlCommandBuilder SqlcommBui = new SqlCommandBuilder(da);
      //string Command = SqlcommBui.GetUpdateCommand().CommandText;

      string updataCommand = "update RSN_All set Batch_M_id = @Batch_M_id ,Parent_RSN =@Parent_RSN, Pkg_Location =@Pkg_Location, CompanyId =@CompanyId where RSN =@RSN";
      SqlCommand cmd = new SqlCommand(updataCommand, cn);
      cmd.Parameters.Add("@Batch_M_id", SqlDbType.BigInt, 0, "Batch_M_id");
      cmd.Parameters.Add("@Parent_RSN", SqlDbType.VarChar, 20, "Parent_RSN");
      cmd.Parameters.Add("@Pkg_Location", SqlDbType.NVarChar, 100, "Pkg_Location");
      cmd.Parameters.Add("@CompanyId", SqlDbType.Int, 0, "CompanyId");
      cmd.Parameters.Add("@RSN", SqlDbType.VarChar, 20, "RSN");
      da.UpdateCommand = cmd;

      da.Update(DsXmlData.Tables["RSN_ALL"]);
   }
}
Posted
Updated 11-May-14 23:25pm
v3

1 solution

 
Share this answer
 

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

  Print Answers RSS
Top Experts
Last 24hrsThis month


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