Unable to update my database, but my dataset and sql data adapter is showing updated values! RSS

• • Reply
  

  ImMohit

  Member

  8 Points

  14 Posts

  Unable to update my database, but my dataset and sql data adapter is showing updated values!

  Dec 14, 2012 02:24 PM|LINK

  hi,

  i have done this code, but unable to get desiable results. i mean dataset is showing updated results but database is not effected at all!

  code: 

  SqlConnection connn = ConnectionProvider.con;
  DataSet ds=new DataSet ();

  SqlDataAdapter adap=new SqlDataAdapter ();
  SqlCommand cmd=new SqlCommand ("Select * From MyTable", ConnectionProvider.con);

  SqlCommand cmdUpdate = new SqlCommand("Update MyTable set MyColumn1=@MyColumn1 where MyColumnId=@MyColumnId", connn);
  adap.SelectCommand= cmd;
  cmdUpdate.Parameters.Add("@MyColumn1", SqlDbType.VarBinary);

  SqlParameter sqlparam = cmdUpdate.Parameters.Add("@MyColumnId", SqlDbType.NVarChar);

  sqlparam.SourceColumn = "MyColumn1";

  sqlparam.SourceVersion = DataRowVersion.Original;

  adap.UpdateCommand = cmdUpdate;
  
  adap.Fill(ds);
  
  foreach(DataRow drs in ds.Tables[0].Rows)
  {
  byte[] dts = new byte[] { 0x20, 0x20, 0x20, 0x20, 0x20, 0x20, 0x20 };
  drs.BeginEdit();

  if (!string.IsNullOrEmpty(Convert.ToString(drs[0])))
  drs["MyColumn1"] = dts;
  drs.EndEdit();
  drs.AcceptChanges();
  }
  
  ds.AcceptChanges();
  adap.Update(ds);
  connn.Close();

  thanks

  MT

• • Reply
  

  bbcompent1

  All-Star

  32992 Points

  8508 Posts

  Moderator

  Re: Unable to update my database, but my dataset and sql data adapter is showing updated values!

  Dec 14, 2012 02:34 PM|LINK

  ImMohit

  SqlConnection connn = ConnectionProvider.con;
  DataSet ds=new DataSet ();

  SqlDataAdapter adap=new SqlDataAdapter ();
  SqlCommand cmd=new SqlCommand ("Select * From MyTable", ConnectionProvider.con);

  Change SqlCommand cmd to this:

  SqlCommand cmd=new SqlCommand ("Select * From MyTable", connn);

  Mark as answer posts that helped you.
  
• • Reply
  

  ImMohit

  Member

  8 Points

  14 Posts

  Re: Unable to update my database, but my dataset and sql data adapter is showing updated values!

  Dec 14, 2012 02:42 PM|LINK

  Actually i have tried this also, but connn is the reference of Connectionprovider.Con which is static sqlconnection! but that is not working in that case also! 

  i dont know where i m missing something! :(

• • Reply
  

  bbcompent1

  All-Star

  32992 Points

  8508 Posts

  Moderator

  Re: Unable to update my database, but my dataset and sql data adapter is showing updated values!

  Dec 14, 2012 02:51 PM|LINK

  So, try your cmdUpdate this way just for a test:

  SqlCommand cmdUpdate = new SqlCommand("Update MyTable set MyColumn1=@MyColumn1 where MyColumnId=@MyColumnId", ConnectionProvider.con);

  If it updates then we may have somethign going on with your data connection delcaration.

  Mark as answer posts that helped you.
  
• • Reply
  

  ImMohit

  Member

  8 Points

  14 Posts

  Re: Unable to update my database, but my dataset and sql data adapter is showing updated values!

  Dec 14, 2012 03:56 PM|LINK

  Hi,

  i converted the code, but still not working

  SqlConnection connn = new SqlConnection(@"Data Source=PCNAME;Initial Catalog=DBName; Trusted_Connection=Yes; connection timeout=600;");
  connn.Open();
  
  DataSet ds=new DataSet ();
  DataTable dt=new DataTable ();
  SqlDataAdapter adap=new SqlDataAdapter ();
  SqlCommand cmd = new SqlCommand("Select * From MyTable", connn);

     SqlCommand cmdUpdate = new SqlCommand("Update MyTable set MyColumn1=@MyColumn1 where MyColumnId=@MyColumnId", connn);
  adap.SelectCommand= cmd;
  cmdUpdate.Parameters.Add("@Illustration", SqlDbType.VarBinary);

  SqlParameter sqlparam = cmdUpdate.Parameters.Add("@MyColumnId", SqlDbType.NVarChar);

  sqlparam.SourceColumn = "MyColumnId";

  sqlparam.SourceVersion = DataRowVersion.Original;

  adap.UpdateCommand = cmdUpdate;
  
  adap.Fill(dt);
  
  foreach(DataRow drs in dt.Rows)
  {
  byte[] dts = new byte[] { 0x20, 0x20, 0x20, 0x20, 0x20, 0x20, 0x20 };
  drs.BeginEdit();
  if (!string.IsNullOrEmpty(Convert.ToString(drs[0])))
  drs["MyColumn1"] = dts;
  drs.EndEdit();
  drs.AcceptChanges();
  }
  dt.AcceptChanges();
  adap.Update(dt);

  Is there any other setting required, which i missed in data server level or application level! 

  Thanks

• • Reply
  

  bbcompent1

  All-Star

  32992 Points

  8508 Posts

  Moderator

  Re: Unable to update my database, but my dataset and sql data adapter is showing updated values!

  Dec 14, 2012 03:59 PM|LINK

  One thing I think I might be seeing and it could be nothing but you are opening the connection and leaving it open for both queries.  Can you try closing the conn and opening it again for the update?

  SqlConnection connn = new SqlConnection(@"Data Source=PCNAME;Initial Catalog=DBName; Trusted_Connection=Yes; connection timeout=600;");
  connn.Open();
  
  DataSet ds=new DataSet ();
  DataTable dt=new DataTable ();
  SqlDataAdapter adap=new SqlDataAdapter ();
  SqlCommand cmd = new SqlCommand("Select * From MyTable", connn);
  connn.Close();
  connn.Open();
     SqlCommand cmdUpdate = new SqlCommand("Update MyTable set MyColumn1=@MyColumn1 where MyColumnId=@MyColumnId", connn);
  adap.SelectCommand= cmd;
  cmdUpdate.Parameters.Add("@Illustration", SqlDbType.VarBinary);
  
  SqlParameter sqlparam = cmdUpdate.Parameters.Add("@MyColumnId", SqlDbType.NVarChar);
  
  sqlparam.SourceColumn = "MyColumnId";
  
  sqlparam.SourceVersion = DataRowVersion.Original;
  
  adap.UpdateCommand = cmdUpdate;
  
  adap.Fill(dt);
  
  foreach(DataRow drs in dt.Rows)
  {
  byte[] dts = new byte[] { 0x20, 0x20, 0x20, 0x20, 0x20, 0x20, 0x20 };
  drs.BeginEdit();
  if (!string.IsNullOrEmpty(Convert.ToString(drs[0])))
  drs["MyColumn1"] = dts;
  drs.EndEdit();
  drs.AcceptChanges();
  }
  dt.AcceptChanges();
  adap.Update(dt);
  connn.Close();

  Mark as answer posts that helped you.
  
• • Reply
  

  ImMohit

  Member

  8 Points

  14 Posts

  Re: Unable to update my database, but my dataset and sql data adapter is showing updated values!

  Dec 14, 2012 04:12 PM|LINK

  hi bbcompent1 

  Actually, i get the updated values in my datatable/dataset as i execute adap.update(dt) method, but that doesnt effect my database at all, even i didnt get any warning or exeception or error! i did changes suggested by u but no luck yet! 

  • Edited by ImMohit on Dec 14, 2012 04:28 PM
  • Edited by ImMohit on Dec 14, 2012 04:28 PM
• • Reply
  

  bbcompent1

  All-Star

  32992 Points

  8508 Posts

  Moderator

  Re: Unable to update my database, but my dataset and sql data adapter is showing updated values!

  Dec 14, 2012 04:28 PM|LINK

  Ok, set a breakpoint at the start of this block and step through it one line at a time and watch the values and see if you get any exceptions. You might want to surround your queries in try catch blocks so if seomthing goes wrong, it will show you.

  Mark as answer posts that helped you.
  
  • Edited by bbcompent1 on Dec 20, 2012 02:55 PM
  • Edited by ImMohit on Jan 03, 2013 02:33 PM
• • Reply
  

  Decker Dong ...

  All-Star

  118619 Points

  18779 Posts

  Re: Unable to update my database, but my dataset and sql data adapter is showing updated values!

  Dec 15, 2012 05:08 AM|LINK

  Hi ImMohit,

  You cannot call AcceptChanges because once you call that, everything will be reguarded as "Updated". So please remove that and Update method will call that automatically:

  foreach(DataRow drs in ds.Tables[0].Rows) {
  byte[] dts = new byte[] { 0x20, 0x20, 0x20, 0x20, 0x20, 0x20, 0x20 };
  drs.BeginEdit();

  if (!string.IsNullOrEmpty(Convert.ToString(drs[0])))
  drs["MyColumn1"] = dts;

  drs.EndEdit();

  drs.AcceptChanges();
  }
  ds.AcceptChanges();
  adap.Update(ds);
  connn.Close();

  • Edited by bbcompent1 on Dec 17, 2012 05:41 PM
  • Edited by decker dong - msft on Dec 20, 2012 12:04 AM
  • Edited by decker dong - msft on Dec 20, 2012 12:04 AM
  • Edited by ImMohit on Dec 20, 2012 02:42 PM
• • Reply
  

  ImMohit

  Member

  8 Points

  14 Posts

  Re: Unable to update my database, but my dataset and sql data adapter is showing updated values!

  Dec 20, 2012 02:44 PM|LINK

  Hi danial,

  i got it now, actually i was not able to generate update command through my command builder (i tried that way). becausae i dont have primary key in my table. once i created primary key (alter the column accordingly), i generate the update command for my adaptor. that it works for me! 

  by the way thanks

  Mohit

  • Marked as answer by ImMohit on Dec 20, 2012 02:44 PM