7 Replies - 9962 Views - Last Post: 01 March 2013 - 07:40 PM Rate Topic: -----

#1 Mitja Bonca  Icon User is offline

  • D.I.C Head

Reputation: 9
  • View blog
  • Posts: 82
  • Joined: 07-May 09

How to update multiple tables with dataSet?

Posted 15 June 2009 - 04:34 AM

This is my code so far:
DataSet dataSet = new DataSet();
			SqlDataAdapter dataAdapter = new SqlDataAdapter("SELECT CurrentCycle FROM Components, Aircrafts WHERE Aircrafts.IDAircraft = '" + IDAvijona + "' " +
															"AND Aircrafts.IDAircraft = Components.IDAircraftFK", povezava);
			dataAdapter.Fill(dataSet);
			foreach (DataRow dataRow in dataSet.Tables[0].Rows)
			{
				if (dataRow["CurrentCycle"] != null)
				{
					dataRow["CurrentCycle"] = Convert.ToInt32(dataRow["CurrentCycle"]) + Convert.ToInt32(textBoxCycles.Text);
				}
			}


As you can see, I am doing an update on one table. But If I try to update it with commandBuilder:
SqlCommandBuilder cmb = new SqlCommandBuilder(dataAdapter);
			dataAdapter.Update(dataSet, "CurrentCycle");

I got an error, that multiple tables can not be updated.
What is the code then for updating multiple tables?
Do I need all 3 commands (insert, delete, update)?

Is This A Good Question/Topic? 0
  • +

Replies To: How to update multiple tables with dataSet?

#2 Mitja Bonca  Icon User is offline

  • D.I.C Head

Reputation: 9
  • View blog
  • Posts: 82
  • Joined: 07-May 09

Re: How to update multiple tables with dataSet?

Posted 15 June 2009 - 06:50 AM

Quote: "TableAdapters require valid UPDATE, INSERT, and DELETE commands to perform a successful save operation."
I would like to know how to manual create all these 3 commands! Can someone please help me?
Was This Post Helpful? 0
  • +
  • -

#3 Mitja Bonca  Icon User is offline

  • D.I.C Head

Reputation: 9
  • View blog
  • Posts: 82
  • Joined: 07-May 09

Re: How to update multiple tables with dataSet?

Posted 15 June 2009 - 03:19 PM

I see this problem of mine is quite an issue. I am searching the web whole day long to get sometihng useful, which would salve my problem. So far with no luck. The main problem is that I can not update dataSet with sqlCommandBuilder, becuase I use more then one table. So I need to create a code for updating manually.
But I am going to need some help from you guys.

Lets see what I have:
I already created a code which retrieves the cycles into a dataSet from a database.
And I did a code which increases all the values in dataRow for what the user writes into textBox (these are number - integers).
Example: Before a particular cycle value was 655, now user writes into textBox number 6, so the new value is now 661.
So, I would like to know how to put this number "661" back to the same place where 655 was before.
So, now the dataSet holds the new value(or values - if there are more then one cycle).

Every cycle has it`s own ID, and I would like to know to, if cycles`s ID is needed when putting the new values back to database? Because, somehow the program has to know where to put a particular value. Or am I wrong?

This is the code I have now:
DataSet dataSet = new DataSet();
				SqlDataAdapter dataAdapter = new SqlDataAdapter("SELECT CurrentCycle FROM Components, Aircrafts WHERE Aircrafts.IDAircraft = '" + IDAvijona + "' " +
																"AND Aircrafts.IDAircraft = Components.IDAircraftFK", povezava);
				dataAdapter.Fill(dataSet, "CurrentCycle");
				dataSet.Tables[0].TableName = "Cycles";
				foreach (DataRow dataRow in dataSet.Tables["Cycles"].Rows)
				{
					if (dataRow["CurrentCycle"].Equals(DBNull.Value))
					{
					   //IF there is a NULL value in the database, code goes through here! 
					}
					else
					{
						dataRow["CurrentCycle"] = Convert.ToInt32(dataRow["CurrentCycle"]) + Convert.ToInt32(textBoxCycles.Text);
					}

Was This Post Helpful? 0
  • +
  • -

#4 nighttrain  Icon User is online

  • D.I.C Regular

Reputation: 6
  • View blog
  • Posts: 331
  • Joined: 22-September 10

Re: How to update multiple tables with dataSet?

Posted 01 March 2013 - 02:51 AM

Old topic but is there anyone who could answer that question?
Was This Post Helpful? 0
  • +
  • -

#5 Momerath  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 1010
  • View blog
  • Posts: 2,444
  • Joined: 04-October 09

Re: How to update multiple tables with dataSet?

Posted 01 March 2013 - 10:21 AM

I'd create a stored procedure that does the update as a transaction.
Was This Post Helpful? 1
  • +
  • -

#6 Skydiver  Icon User is offline

  • Code herder
  • member icon

Reputation: 3574
  • View blog
  • Posts: 11,114
  • Joined: 05-May 12

Re: How to update multiple tables with dataSet?

Posted 01 March 2013 - 12:34 PM

Sorry to pile on to this necro post, but a stored procedure is not the only solution. Transactions can be started, committed, or rolled back on the connection object, and a connection object can be passed in to the adapter constructors.
Was This Post Helpful? 0
  • +
  • -

#7 Momerath  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 1010
  • View blog
  • Posts: 2,444
  • Joined: 04-October 09

Re: How to update multiple tables with dataSet?

Posted 01 March 2013 - 04:57 PM

AFAIK, SqlDataAdapter can only execute one SQL statement during an insert, update, delete and you can't modify multiple tables with a single SQL statement (dropping them doesn't count :)). So, stored procedure.
Was This Post Helpful? 0
  • +
  • -

#8 Skydiver  Icon User is offline

  • Code herder
  • member icon

Reputation: 3574
  • View blog
  • Posts: 11,114
  • Joined: 05-May 12

Re: How to update multiple tables with dataSet?

Posted 01 March 2013 - 07:40 PM

Yes. That is true. That is why I was saying that you can share a single connection among all your calls and bracket the calls within a transaction on the connection.

Sent from my T-Mobile G2 using Tapatalk 2
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1