DataAdapter.update not updating

R

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

}

}
 
G

Guest

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
 
R

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 said:
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 said:
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

}

}
Click to expand...
Click to expand...
 
G

Guest

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 said:
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 said:
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 said:
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

}

}
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Checking to see if a column exists in a datatable 2
Verifying a column exists in a datareader 3
ListView DataView, it shows me this letters "System.Data.Row.View" 1
SQL Server security exception 6
I think I'm missing a fundamental concept 1
Creating an Access database from a SQL Database 4
DataSet Not Updating 11
Returning a value from a stored proc 6

Top