does dataadater updates 2 tables.

9 Oct 2006 11:35 AM

trialproduct2004

Hi all,

i am having code as following:-

SqlCommandBuilder cmd;
da = new SqlDataAdapter("select status,id,mode from test1,test2 where
test1.id = test2.id;", mConn);
cmd = new SqlCommandBuilder(da);
DataSet ds;
ds=new DataSet();
da.Fill(ds);

da.UpdateCommand = new SqlCommand("update test1 set status = @status
where id = @id;update test2 set mode = 'p' where id = @id ",mConn );
da.UpdateCommand.Parameters.Add("@status",SqlDbType.Int,0,"status");
da.UpdateCommand.Parameters.Add("@id",SqlDbType.VarChar ,50,"id");

DataRow [] drc = ds.Tables[0].Select("id = 'O9485330060927155857741 '
");
foreach(DataRow dr in drc)
{
dr["status"] = 333;
}
da.Update(ds.Tables[0]);

Can anyone tell me whether above code is correct or not.

This code is updating 2 tables test1 and test2 using one adapter. it is
setting status in table 1 to '333' and mode in test2 to 'p'. I heard
somewhere that u can't update 2 tables with one adatper. But above
mention code is working properly in my case.

So i am not sure whetehr there is any error in my code or not. Or will
it create any error in future because i want to run this code on
database that has large amount of data.

If i am wrong please correct me.

thanks in advance.

9 Oct 2006 12:39 PM

Patrick Steele

In article <1160393702.079316.323***@k70g2000cwa.googlegroups.com>,
trialproduct2***@yahoo.com says...

> I heard
> somewhere that u can't update 2 tables with one adatper. But above
> mention code is working properly in my case.

I think what you may have heard was that you can't use the
SqlCommandBuilder to automatically generate insert/update/delete
statements for you if your SELECT is coming from two tables.

--
Patrick Steele
http://weblogs.asp.net/psteele

9 Oct 2006 1:38 PM

Miha Markic [MVP C#]

Hi there,

Normal adapter functionallity (mosty supported from IDE) is to update a
single table but nothing prevents to use it for messing with more tables at
once.
So, your code should run just fine.

--
Miha Markic [MVP C#, INETA Country Leader for Slovenia]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

<trialproduct2***@yahoo.com> wrote in message

Show quote

news:1160393702.079316.323080@k70g2000cwa.googlegroups.com...
> Hi all,
>
> i am having code as following:-
>
> SqlCommandBuilder cmd;
> da = new SqlDataAdapter("select status,id,mode from test1,test2 where
> test1.id = test2.id;", mConn);
> cmd = new SqlCommandBuilder(da);
> DataSet ds;
> ds=new DataSet();
> da.Fill(ds);
>
> da.UpdateCommand = new SqlCommand("update test1 set status = @status
> where id = @id;update test2 set mode = 'p' where id = @id ",mConn );
> da.UpdateCommand.Parameters.Add("@status",SqlDbType.Int,0,"status");
> da.UpdateCommand.Parameters.Add("@id",SqlDbType.VarChar ,50,"id");
>
> DataRow [] drc = ds.Tables[0].Select("id = 'O9485330060927155857741 '
> ");
> foreach(DataRow dr in drc)
> {
> dr["status"] = 333;
> }
> da.Update(ds.Tables[0]);
>
> Can anyone tell me whether above code is correct or not.
>
> This code is updating 2 tables test1 and test2 using one adapter. it is
> setting status in table 1 to '333' and mode in test2 to 'p'. I heard
> somewhere that u can't update 2 tables with one adatper. But above
> mention code is working properly in my case.
>
> So i am not sure whetehr there is any error in my code or not. Or will
> it create any error in future because i want to run this code on
> database that has large amount of data.
>
> If i am wrong please correct me.
>
> thanks in advance.
>

10 Oct 2006 3:43 AM

Cor Ligthert [MVP]

Hi,

AFAIK can "The commandbuilder" only handle one table in a time.

Cor

<trialproduct2***@yahoo.com> schreef in bericht
Show quote

news:1160393702.079316.323080@k70g2000cwa.googlegroups.com...
> Hi all,
>
> i am having code as following:-
>
> SqlCommandBuilder cmd;
> da = new SqlDataAdapter("select status,id,mode from test1,test2 where
> test1.id = test2.id;", mConn);
> cmd = new SqlCommandBuilder(da);
> DataSet ds;
> ds=new DataSet();
> da.Fill(ds);
>
> da.UpdateCommand = new SqlCommand("update test1 set status = @status
> where id = @id;update test2 set mode = 'p' where id = @id ",mConn );
> da.UpdateCommand.Parameters.Add("@status",SqlDbType.Int,0,"status");
> da.UpdateCommand.Parameters.Add("@id",SqlDbType.VarChar ,50,"id");
>
> DataRow [] drc = ds.Tables[0].Select("id = 'O9485330060927155857741 '
> ");
> foreach(DataRow dr in drc)
> {
> dr["status"] = 333;
> }
> da.Update(ds.Tables[0]);
>
> Can anyone tell me whether above code is correct or not.
>
> This code is updating 2 tables test1 and test2 using one adapter. it is
> setting status in table 1 to '333' and mode in test2 to 'p'. I heard
> somewhere that u can't update 2 tables with one adatper. But above
> mention code is working properly in my case.
>
> So i am not sure whetehr there is any error in my code or not. Or will
> it create any error in future because i want to run this code on
> database that has large amount of data.
>
> If i am wrong please correct me.
>
> thanks in advance.
>