DataAdapter.update not updating

[RSH]

Hi,

I have a situation in where i have two instances of SQL server, the first is
our Production Environment, the second is our Development environment. Both
servers contain the same databases but I need to write a utility that can
transfer a row of data from the Production to the Development servers.

I wrote the code below, which doesn't error...but it doesn't work...no data
is updated. This is very odd because I can see that both of the datarows
contain the correct value when I check in Debug mode, it just isn't updating
the database.

i have two questions:

1) What am I missing that is causing the script not to work?

2) With respect to a dataset how can I get at the value of a specific
Row/Column?

Thanks for any help!
Ron

SqlConnection cnProductionSQL = new SqlConnection(" Data Source=SQLSVR01 ;
Integrated Security=SSPI; Initial Catalog=Master" );

cnProductionSQL .Open();

SqlConnection cnDevelopmentSQ L = new SqlConnection(" Data Source=SQLSVR02 ;
Integrated Security=SSPI; Initial Catalog=Master" );

cnDevelopmentSQ L.Open();



String strProductionSQ L = "SELECT * FROM [Global].[dbo].[Companies] WHERE
CompanyID ='" + strSQLDestDatab ase + "'";

SqlDataAdapter daProduction = new SqlDataAdapter( strProductionSQ L,
cnProductionSQL );

DataSet dsProduction = new DataSet();

daProduction.Fi ll(dsProduction ,"Companies" );

String strDevelopmentS QL = "SELECT * FROM [Global].[dbo].[Companies] WHERE
CompanyID ='" + strSQLDestDatab ase + "'";

SqlDataAdapter daDevelopment = new SqlDataAdapter( strDevelopmentS QL,
cnDevelopmentSQ L);

DataSet dsDevelopment = new DataSet();

daDevelopment.F ill(dsDevelopme nt,"Companies") ;

if (dsProduction.T ables["Companies"].Rows.Count > 0)

{

DataRow drProd;

DataRow drDev;

drProd = dsProduction.Ta bles["Companies"].Rows[0];

if (dsDevelopment. Tables["Companies"].Rows.Count > 0)

{

drDev = dsDevelopment.T ables["Companies"].Rows[0];

drDev.BeginEdit ();

drDev = drProd;

drDev.EndEdit() ;

daDevelopment.U pdate(dsDevelop ment, "Companies" );

}else

{

//insert

}

}

[Peter Bromberg [C# MVP]]

RE: DataAdapter.upd ate not updating

RSH,
I haven't looked carefully at your code sample, but I'd be willing to bet
that the reason you aren't getting any updates is because the RowStates of
the rows in the
second DataAdapter's table don't reflect any changes.

If you call the GetChanges method on the second DataSet, I bet you'll find
you get an empty DataTable in it.
Peter

--
Co-founder, Eggheadcafe.com developer portal:

http://www.eggheadcafe.com

UnBlog:

http://petesbloggerama.blogspot.com

"RSH" wrote:
[color=blue]
> Hi,
>
> I have a situation in where i have two instances of SQL server, the first is
> our Production Environment, the second is our Development environment. Both
> servers contain the same databases but I need to write a utility that can
> transfer a row of data from the Production to the Development servers.
>
> I wrote the code below, which doesn't error...but it doesn't work...no data
> is updated. This is very odd because I can see that both of the datarows
> contain the correct value when I check in Debug mode, it just isn't updating
> the database.
>
> i have two questions:
>
> 1) What am I missing that is causing the script not to work?
>
> 2) With respect to a dataset how can I get at the value of a specific
> Row/Column?
>
> Thanks for any help!
> Ron
>
> SqlConnection cnProductionSQL = new SqlConnection(" Data Source=SQLSVR01 ;
> Integrated Security=SSPI; Initial Catalog=Master" );
>
> cnProductionSQL .Open();
>
> SqlConnection cnDevelopmentSQ L = new SqlConnection(" Data Source=SQLSVR02 ;
> Integrated Security=SSPI; Initial Catalog=Master" );
>
> cnDevelopmentSQ L.Open();
>
>
>
> String strProductionSQ L = "SELECT * FROM [Global].[dbo].[Companies] WHERE
> CompanyID ='" + strSQLDestDatab ase + "'";
>
> SqlDataAdapter daProduction = new SqlDataAdapter( strProductionSQ L,
> cnProductionSQL );
>
> DataSet dsProduction = new DataSet();
>
> daProduction.Fi ll(dsProduction ,"Companies" );
>
> String strDevelopmentS QL = "SELECT * FROM [Global].[dbo].[Companies] WHERE
> CompanyID ='" + strSQLDestDatab ase + "'";
>
> SqlDataAdapter daDevelopment = new SqlDataAdapter( strDevelopmentS QL,
> cnDevelopmentSQ L);
>
> DataSet dsDevelopment = new DataSet();
>
> daDevelopment.F ill(dsDevelopme nt,"Companies") ;
>
> if (dsProduction.T ables["Companies"].Rows.Count > 0)
>
> {
>
> DataRow drProd;
>
> DataRow drDev;
>
> drProd = dsProduction.Ta bles["Companies"].Rows[0];
>
> if (dsDevelopment. Tables["Companies"].Rows.Count > 0)
>
> {
>
> drDev = dsDevelopment.T ables["Companies"].Rows[0];
>
> drDev.BeginEdit ();
>
> drDev = drProd;
>
> drDev.EndEdit() ;
>
> daDevelopment.U pdate(dsDevelop ment, "Companies" );
>
> }else
>
> {
>
> //insert
>
> }
>
> }
>
>
>[/color]

[RSH]

Re: DataAdapter.upd ate not updating

Peter,

i appreciate your response. i'm rather new in this area so i'm trying to
understand what you are saying.

There was definitely a change, I entered Null values in two of the fields in
the database where the second server had values. This application should
have picked up the changes and inserted them into the other database. I
broke the code right before I set DataRow1 = DataRow2 and the two fields in
DataRow1 did contain the expected values. I also broke the code following
the code where I set DataRow1 = DataRow2 and the changes propogated
correctly to the Second Data Table. But for some reason they were never
written to the database.

Thanks for any help you can offer,
Ron


"Peter Bromberg [C# MVP]" <pbromberg@yaho o.nospammin.com > wrote in message
news:A6BE3B83-C2C4-468B-8A27-6D1FCC980D2E@mi crosoft.com...[color=blue]
> RSH,
> I haven't looked carefully at your code sample, but I'd be willing to bet
> that the reason you aren't getting any updates is because the RowStates of
> the rows in the
> second DataAdapter's table don't reflect any changes.
>
> If you call the GetChanges method on the second DataSet, I bet you'll find
> you get an empty DataTable in it.
> Peter
>
> --
> Co-founder, Eggheadcafe.com developer portal:
> http://www.eggheadcafe.com
> UnBlog:
> http://petesbloggerama.blogspot.com
>
>
>
>
> "RSH" wrote:
>[color=green]
>> Hi,
>>
>> I have a situation in where i have two instances of SQL server, the first
>> is
>> our Production Environment, the second is our Development environment.
>> Both
>> servers contain the same databases but I need to write a utility that can
>> transfer a row of data from the Production to the Development servers.
>>
>> I wrote the code below, which doesn't error...but it doesn't work...no
>> data
>> is updated. This is very odd because I can see that both of the datarows
>> contain the correct value when I check in Debug mode, it just isn't
>> updating
>> the database.
>>
>> i have two questions:
>>
>> 1) What am I missing that is causing the script not to work?
>>
>> 2) With respect to a dataset how can I get at the value of a specific
>> Row/Column?
>>
>> Thanks for any help!
>> Ron
>>
>> SqlConnection cnProductionSQL = new SqlConnection(" Data Source=SQLSVR01 ;
>> Integrated Security=SSPI; Initial Catalog=Master" );
>>
>> cnProductionSQL .Open();
>>
>> SqlConnection cnDevelopmentSQ L = new SqlConnection(" Data Source=SQLSVR02 ;
>> Integrated Security=SSPI; Initial Catalog=Master" );
>>
>> cnDevelopmentSQ L.Open();
>>
>>
>>
>> String strProductionSQ L = "SELECT * FROM [Global].[dbo].[Companies] WHERE
>> CompanyID ='" + strSQLDestDatab ase + "'";
>>
>> SqlDataAdapter daProduction = new SqlDataAdapter( strProductionSQ L,
>> cnProductionSQL );
>>
>> DataSet dsProduction = new DataSet();
>>
>> daProduction.Fi ll(dsProduction ,"Companies" );
>>
>> String strDevelopmentS QL = "SELECT * FROM [Global].[dbo].[Companies]
>> WHERE
>> CompanyID ='" + strSQLDestDatab ase + "'";
>>
>> SqlDataAdapter daDevelopment = new SqlDataAdapter( strDevelopmentS QL,
>> cnDevelopmentSQ L);
>>
>> DataSet dsDevelopment = new DataSet();
>>
>> daDevelopment.F ill(dsDevelopme nt,"Companies") ;
>>
>> if (dsProduction.T ables["Companies"].Rows.Count > 0)
>>
>> {
>>
>> DataRow drProd;
>>
>> DataRow drDev;
>>
>> drProd = dsProduction.Ta bles["Companies"].Rows[0];
>>
>> if (dsDevelopment. Tables["Companies"].Rows.Count > 0)
>>
>> {
>>
>> drDev = dsDevelopment.T ables["Companies"].Rows[0];
>>
>> drDev.BeginEdit ();
>>
>> drDev = drProd;
>>
>> drDev.EndEdit() ;
>>
>> daDevelopment.U pdate(dsDevelop ment, "Companies" );
>>
>> }else
>>
>> {
>>
>> //insert
>>
>> }
>>
>> }
>>
>>
>>[/color][/color]

[Peter Bromberg [C# MVP]]

Re: DataAdapter.upd ate not updating

Ron,
Here's the "thing": When you bring data into a DataTable it doesn't matter
if the data you brought in from a SQL Server table is "Changed" data. Each
row in a DataTable has A RowState property that changes ONLY when the actual
**row in the DataTable** is changed. This is what triggers the DataAdapter to
perform an UPdate, an insert or a Delete on that particular row when you call
the adapter's Update method.
Hope that points you in the right direction!
Peter

--
Co-founder, Eggheadcafe.com developer portal:

http://www.eggheadcafe.com

UnBlog:

http://petesbloggerama.blogspot.com

"RSH" wrote:
[color=blue]
> Peter,
>
> i appreciate your response. i'm rather new in this area so i'm trying to
> understand what you are saying.
>
> There was definitely a change, I entered Null values in two of the fields in
> the database where the second server had values. This application should
> have picked up the changes and inserted them into the other database. I
> broke the code right before I set DataRow1 = DataRow2 and the two fields in
> DataRow1 did contain the expected values. I also broke the code following
> the code where I set DataRow1 = DataRow2 and the changes propogated
> correctly to the Second Data Table. But for some reason they were never
> written to the database.
>
> Thanks for any help you can offer,
> Ron
>
>
> "Peter Bromberg [C# MVP]" <pbromberg@yaho o.nospammin.com > wrote in message
> news:A6BE3B83-C2C4-468B-8A27-6D1FCC980D2E@mi crosoft.com...[color=green]
> > RSH,
> > I haven't looked carefully at your code sample, but I'd be willing to bet
> > that the reason you aren't getting any updates is because the RowStates of
> > the rows in the
> > second DataAdapter's table don't reflect any changes.
> >
> > If you call the GetChanges method on the second DataSet, I bet you'll find
> > you get an empty DataTable in it.
> > Peter
> >
> > --
> > Co-founder, Eggheadcafe.com developer portal:
> > http://www.eggheadcafe.com
> > UnBlog:
> > http://petesbloggerama.blogspot.com
> >
> >
> >
> >
> > "RSH" wrote:
> >[color=darkred]
> >> Hi,
> >>
> >> I have a situation in where i have two instances of SQL server, the first
> >> is
> >> our Production Environment, the second is our Development environment.
> >> Both
> >> servers contain the same databases but I need to write a utility that can
> >> transfer a row of data from the Production to the Development servers.
> >>
> >> I wrote the code below, which doesn't error...but it doesn't work...no
> >> data
> >> is updated. This is very odd because I can see that both of the datarows
> >> contain the correct value when I check in Debug mode, it just isn't
> >> updating
> >> the database.
> >>
> >> i have two questions:
> >>
> >> 1) What am I missing that is causing the script not to work?
> >>
> >> 2) With respect to a dataset how can I get at the value of a specific
> >> Row/Column?
> >>
> >> Thanks for any help!
> >> Ron
> >>
> >> SqlConnection cnProductionSQL = new SqlConnection(" Data Source=SQLSVR01 ;
> >> Integrated Security=SSPI; Initial Catalog=Master" );
> >>
> >> cnProductionSQL .Open();
> >>
> >> SqlConnection cnDevelopmentSQ L = new SqlConnection(" Data Source=SQLSVR02 ;
> >> Integrated Security=SSPI; Initial Catalog=Master" );
> >>
> >> cnDevelopmentSQ L.Open();
> >>
> >>
> >>
> >> String strProductionSQ L = "SELECT * FROM [Global].[dbo].[Companies] WHERE
> >> CompanyID ='" + strSQLDestDatab ase + "'";
> >>
> >> SqlDataAdapter daProduction = new SqlDataAdapter( strProductionSQ L,
> >> cnProductionSQL );
> >>
> >> DataSet dsProduction = new DataSet();
> >>
> >> daProduction.Fi ll(dsProduction ,"Companies" );
> >>
> >> String strDevelopmentS QL = "SELECT * FROM [Global].[dbo].[Companies]
> >> WHERE
> >> CompanyID ='" + strSQLDestDatab ase + "'";
> >>
> >> SqlDataAdapter daDevelopment = new SqlDataAdapter( strDevelopmentS QL,
> >> cnDevelopmentSQ L);
> >>
> >> DataSet dsDevelopment = new DataSet();
> >>
> >> daDevelopment.F ill(dsDevelopme nt,"Companies") ;
> >>
> >> if (dsProduction.T ables["Companies"].Rows.Count > 0)
> >>
> >> {
> >>
> >> DataRow drProd;
> >>
> >> DataRow drDev;
> >>
> >> drProd = dsProduction.Ta bles["Companies"].Rows[0];
> >>
> >> if (dsDevelopment. Tables["Companies"].Rows.Count > 0)
> >>
> >> {
> >>
> >> drDev = dsDevelopment.T ables["Companies"].Rows[0];
> >>
> >> drDev.BeginEdit ();
> >>
> >> drDev = drProd;
> >>
> >> drDev.EndEdit() ;
> >>
> >> daDevelopment.U pdate(dsDevelop ment, "Companies" );
> >>
> >> }else
> >>
> >> {
> >>
> >> //insert
> >>
> >> }
> >>
> >> }
> >>
> >>
> >>[/color][/color]
>
>
>[/color]