Update - help putting two bits of code together RSS

• • Reply
  

  RichardLaw

  Member

  464 Points

  626 Posts

  Update - help putting two bits of code together

  Feb 27, 2011 10:28 AM|LINK

  Hi

  I'm trying to update a table (learning) via code behind.

  I've used this method of connection to a database before:

  System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
          string SQL = string.Format("SELECT * FROM tbl_StockControl_GroupItems;");
          System.Data.SqlClient.SqlCommand comm = new System.Data.SqlClient.SqlCommand();

  And I've since found the following code to update a table which (to me) looks simple and clean:

          DataTable dt = ds.Tables["tbl_StockControl_GroupItems"];
  
          // Insert 
          DataRow dr = dt.NewRow();
          dr[0] = 4;
          dr[1] = "New value";
          dr[2] = "New value";
          dr[3] = 3;
          dr[4] = 3000;
          dr[5] = DateTime.Parse("8/14/1999");
          dt.Rows.Add(dr);
          da.Update(ds, "tbl_StockControl_GroupItems");
          ds.AcceptChanges();

  ... And I'd like to join the two.

  Thing is, I realise it's not complete, and I need to tweak the syntax a little to get it working.

  I'd really appreciate some help.

  Many thanks
  Rich 

• • Reply
  

  Lateef045

  Star

  7813 Points

  1541 Posts

  Re: Update - help putting two bits of code together

  Feb 27, 2011 11:25 AM|LINK

  Hi,

   

  Where exactly are you facing the problem.

   

  You have prepared the connection object well. 

  The only part missing between the two code is fetching the data in the dataset using that query and connection. Correct me if I am getting it wrong.

  You can use this code to fetch the data.

  SqlDataAdapter da = new SqlDataAdapter(SQL, conn);
  
          DataSet ds = new DataSet();
          da.Fill(ds);

   

  Mark the post as Answer if it helped you so that others dont waste time looking for solution
  • Marked as answer by RichardLaw on Feb 27, 2011 12:49 PM
• • Reply
  

  RichardLaw

  Member

  464 Points

  626 Posts

  Re: Update - help putting two bits of code together

  Feb 27, 2011 11:41 AM|LINK

  Thanks. I've added your code:

  System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
          string SQL = string.Format("SELECT * FROM tbl_StockControl_GroupItems;");
          System.Data.SqlClient.SqlCommand comm = new System.Data.SqlClient.SqlCommand();
  
          SqlDataAdapter da = new SqlDataAdapter(SQL, conn);
          DataSet ds = new DataSet();
          da.Fill(ds);
  
          DataTable dt = ds.Tables["tbl_StockControl_GroupItems"];
  
          // Insert
          DataRow dr = dt.NewRow();
  
          dt.Rows.Add(dr);
          da.Update(ds, "tbl_StockControl_GroupItems");
          ds.AcceptChanges();

  ... but I get an error: DataRow dr = dt.NewRow(); -

  Object reference not set to an instance of an object

• • Reply
  

  Lateef045

  Star

  7813 Points

  1541 Posts

  Re: Update - help putting two bits of code together

  Feb 27, 2011 12:08 PM|LINK

  Hi,

   

  Just change your line

  DataTable dt = ds.Tables["tbl_StockControl_GroupItems"];

   

  to 

   

   DataTable dt = ds.Tables[0];

  Mark the post as Answer if it helped you so that others dont waste time looking for solution
  • Marked as answer by RichardLaw on Feb 27, 2011 12:48 PM
• • Reply
  

  RichardLaw

  Member

  464 Points

  626 Posts

  Re: Update - help putting two bits of code together

  Feb 27, 2011 12:49 PM|LINK

  Perfect! Many thanks.