By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,307 Members | 1,476 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,307 IT Pros & Developers. It's quick & easy.

Update more than 1 table in a DataSet using SqlDataAdapter.Update

P: n/a
LP
Hello,

In the past I used SqlCommandBuilder and SqlDataAdapter .Update method to
apply changes in a DataTable back to its table source in SQL Server. It
worked fine when DataSet had only 1 table, this time I have 2 tables in a
dataset and I want to update them all at once. However it only updates the
first one and fails when attempts to update 2nd table.. Has anyone done
anything like that? It looks like SqlCommandBuilder generates insert command
only for the first table.

Here's the most relevant code, if it helps:

//command that gets 2 tables structure
SqlCommand commandClosedEnd = new SqlCommand("select f1, f2 from table1
where 1=2\r\nselect f1, f2 from table2 where 1=2", connClosedEnd);

commandClosedEnd.CommandType = CommandType.Text;
DataSet dsClosedEnd = new DataSet();
SqlDataAdapter daClosedEnd = new SqlDataAdapter();
daClosedEnd.SelectCommand = commandClosedEnd;//select command

daClosedEnd.Fill(dsClosedEnd); //gets 2 tables

/*
Code inserts new rows to both tables
*/

//Generate insert command
SqlCommandBuilder cmndBuild = new SqlCommandBuilder(daClosedEnd);
daClosedEnd.InsertCommand = cmndBuild.GetInsertCommand(); //should
genereate for 2 tables, but it doesn't
//update all 2 tables -- updates the first 1 and breaks on the second
foreach (DataTable tblUpdate in dsClosedEnd.Tables)
{
daClosedEnd.Update(dsClosedEnd);
}

Please help!!!!
Nov 16 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Hi,

http://msdn.microsoft.com/library/de...andbuilder.asp

Ken
-----------------------------

"LP" wrote:
Hello,

In the past I used SqlCommandBuilder and SqlDataAdapter .Update method to
apply changes in a DataTable back to its table source in SQL Server. It
worked fine when DataSet had only 1 table, this time I have 2 tables in a
dataset and I want to update them all at once. However it only updates the
first one and fails when attempts to update 2nd table.. Has anyone done
anything like that? It looks like SqlCommandBuilder generates insert command
only for the first table.

Here's the most relevant code, if it helps:

//command that gets 2 tables structure
SqlCommand commandClosedEnd = new SqlCommand("select f1, f2 from table1
where 1=2\r\nselect f1, f2 from table2 where 1=2", connClosedEnd);

commandClosedEnd.CommandType = CommandType.Text;
DataSet dsClosedEnd = new DataSet();
SqlDataAdapter daClosedEnd = new SqlDataAdapter();
daClosedEnd.SelectCommand = commandClosedEnd;//select command

daClosedEnd.Fill(dsClosedEnd); //gets 2 tables

/*
Code inserts new rows to both tables
*/

//Generate insert command
SqlCommandBuilder cmndBuild = new SqlCommandBuilder(daClosedEnd);
daClosedEnd.InsertCommand = cmndBuild.GetInsertCommand(); //should
genereate for 2 tables, but it doesn't
//update all 2 tables -- updates the first 1 and breaks on the second
foreach (DataTable tblUpdate in dsClosedEnd.Tables)
{
daClosedEnd.Update(dsClosedEnd);
}

Please help!!!!

Nov 16 '05 #2

P: n/a
LP
Hi,
Thanks for the article it doesn't help me though. It reinforces my concern
about CommandBuilder limitations:

a.. The SelectCommand cannot refer to SQL queries, stored procedures, or
views that contain JOIN operators. This means your SELECT statement must
refer to a single table.

So, I think the answer is I can only update one table at a time or write my
own custom update command logic.
"Ken Tucker [MVP]" <Ke**********@discussions.microsoft.com> wrote in message
news:6A**********************************@microsof t.com...
Hi,

http://msdn.microsoft.com/library/de...andbuilder.asp
Ken
-----------------------------

"LP" wrote:
Hello,

In the past I used SqlCommandBuilder and SqlDataAdapter .Update method to apply changes in a DataTable back to its table source in SQL Server. It
worked fine when DataSet had only 1 table, this time I have 2 tables in a dataset and I want to update them all at once. However it only updates the first one and fails when attempts to update 2nd table.. Has anyone done
anything like that? It looks like SqlCommandBuilder generates insert command only for the first table.

Here's the most relevant code, if it helps:

//command that gets 2 tables structure
SqlCommand commandClosedEnd = new SqlCommand("select f1, f2 from table1
where 1=2\r\nselect f1, f2 from table2 where 1=2", connClosedEnd);

commandClosedEnd.CommandType = CommandType.Text;
DataSet dsClosedEnd = new DataSet();
SqlDataAdapter daClosedEnd = new SqlDataAdapter();
daClosedEnd.SelectCommand = commandClosedEnd;//select command

daClosedEnd.Fill(dsClosedEnd); //gets 2 tables

/*
Code inserts new rows to both tables
*/

//Generate insert command
SqlCommandBuilder cmndBuild = new SqlCommandBuilder(daClosedEnd);
daClosedEnd.InsertCommand = cmndBuild.GetInsertCommand(); //should
genereate for 2 tables, but it doesn't
//update all 2 tables -- updates the first 1 and breaks on the second
foreach (DataTable tblUpdate in dsClosedEnd.Tables)
{
daClosedEnd.Update(dsClosedEnd);
}

Please help!!!!

Nov 16 '05 #3

P: n/a
The CommandBuilder object is a quick and dirty way to get something done.
Even for the simplistic case you speak of, the SQL query it generates is
awfully ugly and inefficient. You are better off implementing your own
queries logic IMO.
--

- Sahil Malik
http://codebetter.com/blogs/sahil.malik/

..

"LP" <lp@a.com> wrote in message
news:%2****************@tk2msftngp13.phx.gbl...
Hi,
Thanks for the article it doesn't help me though. It reinforces my concern
about CommandBuilder limitations:

a.. The SelectCommand cannot refer to SQL queries, stored procedures, or
views that contain JOIN operators. This means your SELECT statement must
refer to a single table.

So, I think the answer is I can only update one table at a time or write
my
own custom update command logic.
"Ken Tucker [MVP]" <Ke**********@discussions.microsoft.com> wrote in
message
news:6A**********************************@microsof t.com...
Hi,

http://msdn.microsoft.com/library/de...andbuilder.asp

Ken
-----------------------------

"LP" wrote:
> Hello,
>
> In the past I used SqlCommandBuilder and SqlDataAdapter .Update method to > apply changes in a DataTable back to its table source in SQL Server. It
> worked fine when DataSet had only 1 table, this time I have 2 tables in a > dataset and I want to update them all at once. However it only updates the > first one and fails when attempts to update 2nd table.. Has anyone
> done
> anything like that? It looks like SqlCommandBuilder generates insert command > only for the first table.
>
> Here's the most relevant code, if it helps:
>
> //command that gets 2 tables structure
> SqlCommand commandClosedEnd = new SqlCommand("select f1, f2 from table1
> where 1=2\r\nselect f1, f2 from table2 where 1=2", connClosedEnd);
>
> commandClosedEnd.CommandType = CommandType.Text;
> DataSet dsClosedEnd = new DataSet();
> SqlDataAdapter daClosedEnd = new SqlDataAdapter();
> daClosedEnd.SelectCommand = commandClosedEnd;//select command
>
> daClosedEnd.Fill(dsClosedEnd); //gets 2 tables
>
> /*
> Code inserts new rows to both tables
> */
>
> //Generate insert command
> SqlCommandBuilder cmndBuild = new SqlCommandBuilder(daClosedEnd);
> daClosedEnd.InsertCommand = cmndBuild.GetInsertCommand(); //should
> genereate for 2 tables, but it doesn't
> //update all 2 tables -- updates the first 1 and breaks on the second
> foreach (DataTable tblUpdate in dsClosedEnd.Tables)
> {
> daClosedEnd.Update(dsClosedEnd);
> }
>
> Please help!!!!
>
>
>


Nov 16 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.