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

DataAdapter.update not updating

P: n/a
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 cnDevelopmentSQL = new SqlConnection("Data Source=SQLSVR02;
Integrated Security=SSPI; Initial Catalog=Master");

cnDevelopmentSQL.Open();

String strProductionSQL = "SELECT * FROM [Global].[dbo].[Companies] WHERE
CompanyID ='" + strSQLDestDatabase + "'";

SqlDataAdapter daProduction = new SqlDataAdapter(strProductionSQL,
cnProductionSQL);

DataSet dsProduction = new DataSet();

daProduction.Fill(dsProduction,"Companies");

String strDevelopmentSQL = "SELECT * FROM [Global].[dbo].[Companies] WHERE
CompanyID ='" + strSQLDestDatabase + "'";

SqlDataAdapter daDevelopment = new SqlDataAdapter(strDevelopmentSQL,
cnDevelopmentSQL);

DataSet dsDevelopment = new DataSet();

daDevelopment.Fill(dsDevelopment,"Companies");

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

{

DataRow drProd;

DataRow drDev;

drProd = dsProduction.Tables["Companies"].Rows[0];

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

{

drDev = dsDevelopment.Tables["Companies"].Rows[0];

drDev.BeginEdit();

drDev = drProd;

drDev.EndEdit();

daDevelopment.Update(dsDevelopment, "Companies");

}else

{

//insert

}

}
Mar 2 '06 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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:
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 cnDevelopmentSQL = new SqlConnection("Data Source=SQLSVR02;
Integrated Security=SSPI; Initial Catalog=Master");

cnDevelopmentSQL.Open();

String strProductionSQL = "SELECT * FROM [Global].[dbo].[Companies] WHERE
CompanyID ='" + strSQLDestDatabase + "'";

SqlDataAdapter daProduction = new SqlDataAdapter(strProductionSQL,
cnProductionSQL);

DataSet dsProduction = new DataSet();

daProduction.Fill(dsProduction,"Companies");

String strDevelopmentSQL = "SELECT * FROM [Global].[dbo].[Companies] WHERE
CompanyID ='" + strSQLDestDatabase + "'";

SqlDataAdapter daDevelopment = new SqlDataAdapter(strDevelopmentSQL,
cnDevelopmentSQL);

DataSet dsDevelopment = new DataSet();

daDevelopment.Fill(dsDevelopment,"Companies");

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

{

DataRow drProd;

DataRow drDev;

drProd = dsProduction.Tables["Companies"].Rows[0];

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

{

drDev = dsDevelopment.Tables["Companies"].Rows[0];

drDev.BeginEdit();

drDev = drProd;

drDev.EndEdit();

daDevelopment.Update(dsDevelopment, "Companies");

}else

{

//insert

}

}

Mar 2 '06 #2

P: n/a
RSH
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]" <pb*******@yahoo.nospammin.com> wrote in message
news:A6**********************************@microsof t.com...
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:
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 cnDevelopmentSQL = new SqlConnection("Data Source=SQLSVR02;
Integrated Security=SSPI; Initial Catalog=Master");

cnDevelopmentSQL.Open();

String strProductionSQL = "SELECT * FROM [Global].[dbo].[Companies] WHERE
CompanyID ='" + strSQLDestDatabase + "'";

SqlDataAdapter daProduction = new SqlDataAdapter(strProductionSQL,
cnProductionSQL);

DataSet dsProduction = new DataSet();

daProduction.Fill(dsProduction,"Companies");

String strDevelopmentSQL = "SELECT * FROM [Global].[dbo].[Companies]
WHERE
CompanyID ='" + strSQLDestDatabase + "'";

SqlDataAdapter daDevelopment = new SqlDataAdapter(strDevelopmentSQL,
cnDevelopmentSQL);

DataSet dsDevelopment = new DataSet();

daDevelopment.Fill(dsDevelopment,"Companies");

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

{

DataRow drProd;

DataRow drDev;

drProd = dsProduction.Tables["Companies"].Rows[0];

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

{

drDev = dsDevelopment.Tables["Companies"].Rows[0];

drDev.BeginEdit();

drDev = drProd;

drDev.EndEdit();

daDevelopment.Update(dsDevelopment, "Companies");

}else

{

//insert

}

}

Mar 2 '06 #3

P: n/a
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:
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]" <pb*******@yahoo.nospammin.com> wrote in message
news:A6**********************************@microsof t.com...
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:
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 cnDevelopmentSQL = new SqlConnection("Data Source=SQLSVR02;
Integrated Security=SSPI; Initial Catalog=Master");

cnDevelopmentSQL.Open();

String strProductionSQL = "SELECT * FROM [Global].[dbo].[Companies] WHERE
CompanyID ='" + strSQLDestDatabase + "'";

SqlDataAdapter daProduction = new SqlDataAdapter(strProductionSQL,
cnProductionSQL);

DataSet dsProduction = new DataSet();

daProduction.Fill(dsProduction,"Companies");

String strDevelopmentSQL = "SELECT * FROM [Global].[dbo].[Companies]
WHERE
CompanyID ='" + strSQLDestDatabase + "'";

SqlDataAdapter daDevelopment = new SqlDataAdapter(strDevelopmentSQL,
cnDevelopmentSQL);

DataSet dsDevelopment = new DataSet();

daDevelopment.Fill(dsDevelopment,"Companies");

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

{

DataRow drProd;

DataRow drDev;

drProd = dsProduction.Tables["Companies"].Rows[0];

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

{

drDev = dsDevelopment.Tables["Companies"].Rows[0];

drDev.BeginEdit();

drDev = drProd;

drDev.EndEdit();

daDevelopment.Update(dsDevelopment, "Companies");

}else

{

//insert

}

}


Mar 2 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.