Unable to merge 2 mySQL Databases using OdbcDataAdapter.Update

Guest · Sep 30, 2005

Hi Everybody,
I am Updating server database with data from client database, I am using
mySQL Ver 4.1, I am unable to do that. What should I do to change the codes
to make it work? I have also another method that do the opposite data from
server updates the database on client side. I need help. There is no change
in records of target database. I have saw some topics here at this forum. The
problem is Connection to server database is ok, but when
"odAdapter.Update(ds,"<tablename>"); " executes it returns value of 0 and on
changes made to server database, it should have 12 rows. Advanced Thanks.

Code:

public int UpdateServerTable1(DataTable dtObj)
{
DataSet ds = new DataSet();
int iRes = 0;
try
{
this.sqlTextServer = "Select * From <tablename>";

OdbcDataAdapter odAdapter = new
OdbcDataAdapter(this.sqlTextServer,this.odServerConn );

odAdapter.Fill(ds,"<tablename>");

ds.Merge(dtObj,true,MissingSchemaAction.Ignore);

OdbcCommandBuilder cmdBuild = new OdbcCommandBuilder(odAdapter);

iRes = odAdapter.Update(ds,"<tablename>");

}
catch (Exception ex)
{
ds.RejectChanges();

}
return iRes;
}

Regards,
denpsia

Mark Ashton · Oct 3, 2005

It sounds like Update(DataSet, string tablename) didn't find any rows to
update, look at the result of to see what rows it thinks it will attempt to
update.
int count = ds.Tables["<tablename>"].Select("", "",
DataRowState.Added|DataRowState.Deleted|DataRowState.Modified).Length;

Guest · Oct 5, 2005

Hi Mark,
Thanks. I check before the merge and after merge, I check while in Debug
Mode the row count before the destination is 0, while the source has 12 rows
and after the merge the rows cahnge from 0 to 12. So, it seems the approach
is not applicable to mySQL. The Tables are INNODB and MYISAM, even I change
the table to make the same the result is the same.

Regards,
den2005
--
MCP Year 2005, Philippines

Mark Ashton said:
It sounds like Update(DataSet, string tablename) didn't find any rows to
update, look at the result of to see what rows it thinks it will attempt to
update.
int count = ds.Tables["<tablename>"].Select("", "",
DataRowState.Added|DataRowState.Deleted|DataRowState.Modified).Length;

--
This posting is provided "AS IS", with no warranties, and confers no rights.
Please do not send email directly to this alias. This alias is for newsgroup
purposes only.

den 2005 said:

Hi Everybody,
I am Updating server database with data from client database, I am using
mySQL Ver 4.1, I am unable to do that. What should I do to change the
codes
to make it work? I have also another method that do the opposite data from
server updates the database on client side. I need help. There is no
change
in records of target database. I have saw some topics here at this forum.
The
problem is Connection to server database is ok, but when
"odAdapter.Update(ds,"<tablename>"); " executes it returns value of 0 and
on
changes made to server database, it should have 12 rows. Advanced Thanks.

Code:

public int UpdateServerTable1(DataTable dtObj)
{
DataSet ds = new DataSet();
int iRes = 0;
try
{
this.sqlTextServer = "Select * From <tablename>";

OdbcDataAdapter odAdapter = new
OdbcDataAdapter(this.sqlTextServer,this.odServerConn );

odAdapter.Fill(ds,"<tablename>");

ds.Merge(dtObj,true,MissingSchemaAction.Ignore);

OdbcCommandBuilder cmdBuild = new OdbcCommandBuilder(odAdapter);

iRes = odAdapter.Update(ds,"<tablename>");

}
catch (Exception ex)
{
ds.RejectChanges();

}
return iRes;
}

Regards,
denpsia

Click to expand...

Cor Ligthert [MVP] · Oct 5, 2005

Den,

How do you get your datatables that you merge.

The first thing it does when it is using the fill is doing the
acceptchangesduringfill

http://msdn.microsoft.com/library/d...aadapterclassacceptchangesduringfilltopic.asp

I hope this is a start to the solution of your problem

Cor

Guest · Oct 6, 2005

Hi Cor,

Thanks for reply. I used another function to return the Datatable.

Code:

DataTable dt = comp.GetclientTable();

int iRes = comp.UpdateServerTable1(dt);

Code:

public DataTable GetClientTable()
{

.. . .

OdbcDataAdapter odAdapter = new OdbcDataAdapter(sqlTextServer,odServerConn);

if (odServerConn.State == ConnectionState.Closed)
{
odServerConn.Open();
}

DataSet ds = new DataSet();

odAdapter.Fill(ds,"CheckFault");

dt = ds.Tables["CheckFault"];

if (odServerConn.State == ConnectionState.Open)
{
odServerConn.Close();
}

....

return dt;
}

den2005

Cor Ligthert [MVP] · Oct 6, 2005

Den,

As I wrote are with the function you use all rowstate set to not changed,
what means that nothing will be done with it during the update. To merge
something and to add that to another table, you have to tell that it are new
rows in a dataset. That you get by setting the acceptchangesduringfill to
false (default it is true), see for that my previous message.

Cor

den 2005 said:
Hi Cor,

Thanks for reply. I used another function to return the Datatable.

Code:

DataTable dt = comp.GetclientTable();

int iRes = comp.UpdateServerTable1(dt);

Code:

public DataTable GetClientTable()
{

. . .

OdbcDataAdapter odAdapter = new
OdbcDataAdapter(sqlTextServer,odServerConn);

if (odServerConn.State == ConnectionState.Closed)
{
odServerConn.Open();
}

DataSet ds = new DataSet();

odAdapter.Fill(ds,"CheckFault");

dt = ds.Tables["CheckFault"];

if (odServerConn.State == ConnectionState.Open)
{
odServerConn.Close();
}

...

return dt;
}

den2005
--
MCP Year 2005, Philippines

den 2005 said:

Hi Everybody,
I am Updating server database with data from client database, I am
using
mySQL Ver 4.1, I am unable to do that. What should I do to change the
codes
to make it work? I have also another method that do the opposite data
from
server updates the database on client side. I need help. There is no
change
in records of target database. I have saw some topics here at this forum.
The
problem is Connection to server database is ok, but when
"odAdapter.Update(ds,"<tablename>"); " executes it returns value of 0 and
on
changes made to server database, it should have 12 rows. Advanced Thanks.

Code:

public int UpdateServerTable1(DataTable dtObj)
{
DataSet ds = new DataSet();
int iRes = 0;
try
{
this.sqlTextServer = "Select * From <tablename>";

OdbcDataAdapter odAdapter = new
OdbcDataAdapter(this.sqlTextServer,this.odServerConn );

odAdapter.Fill(ds,"<tablename>");

ds.Merge(dtObj,true,MissingSchemaAction.Ignore);

OdbcCommandBuilder cmdBuild = new OdbcCommandBuilder(odAdapter);

iRes = odAdapter.Update(ds,"<tablename>");

}
catch (Exception ex)
{
ds.RejectChanges();

}
return iRes;
}

Regards,
denpsia

Click to expand...

Guest · Oct 7, 2005

Hi Cor,
I have tried setting the DataAdapter.AcceptChangesDuringFill to false
and still there is no records added to target database table, there is no
records in this table and from surce database table there is 12 records. I am
currently using the very long approach which is using select to compare the
databases records and inserting new records and deleting records all this
hard-coded, it is very very long lines of codes. But if you guys have some
very much shorter lines of codes that works with mySQL database Ver 4.1,
please tell me. Thanks for reply.

den2005
--
MCP Year 2005, Philippines

Cor Ligthert said:
Den,

As I wrote are with the function you use all rowstate set to not changed,
what means that nothing will be done with it during the update. To merge
something and to add that to another table, you have to tell that it are new
rows in a dataset. That you get by setting the acceptchangesduringfill to
false (default it is true), see for that my previous message.

Cor

den 2005 said:

Hi Cor,

Thanks for reply. I used another function to return the Datatable.

Code:

DataTable dt = comp.GetclientTable();

int iRes = comp.UpdateServerTable1(dt);

Code:

public DataTable GetClientTable()
{

. . .

OdbcDataAdapter odAdapter = new
OdbcDataAdapter(sqlTextServer,odServerConn);

if (odServerConn.State == ConnectionState.Closed)
{
odServerConn.Open();
}

DataSet ds = new DataSet();

odAdapter.Fill(ds,"CheckFault");

dt = ds.Tables["CheckFault"];

if (odServerConn.State == ConnectionState.Open)
{
odServerConn.Close();
}

...

return dt;
}

den2005
--
MCP Year 2005, Philippines

den 2005 said:

Hi Everybody,
I am Updating server database with data from client database, I am
using
mySQL Ver 4.1, I am unable to do that. What should I do to change the
codes
to make it work? I have also another method that do the opposite data
from
server updates the database on client side. I need help. There is no
change
in records of target database. I have saw some topics here at this forum.
The
problem is Connection to server database is ok, but when
"odAdapter.Update(ds,"<tablename>"); " executes it returns value of 0 and
on
changes made to server database, it should have 12 rows. Advanced Thanks.

Code:

public int UpdateServerTable1(DataTable dtObj)
{
DataSet ds = new DataSet();
int iRes = 0;
try
{
this.sqlTextServer = "Select * From <tablename>";

OdbcDataAdapter odAdapter = new
OdbcDataAdapter(this.sqlTextServer,this.odServerConn );

odAdapter.Fill(ds,"<tablename>");

ds.Merge(dtObj,true,MissingSchemaAction.Ignore);

OdbcCommandBuilder cmdBuild = new OdbcCommandBuilder(odAdapter);

iRes = odAdapter.Update(ds,"<tablename>");

}
catch (Exception ex)
{
ds.RejectChanges();

}
return iRes;
}

Regards,
denpsia

Click to expand...

Click to expand...

Cor Ligthert [MVP] · Oct 7, 2005

Den,

Are you sure that the keys are equal. (And the table name, otherwise there
is just a new table added to the dataset).

I have here a very simple sample in VB about the merge. I am sure that you
as MCP can read it.

http://www.windowsformsdatagridhelp.com/default.aspx?ID=edb1409d-5394-468f-a63f-de3a5d92b14a

I hope this helps,

Cor

den 2005 said:
Hi Cor,
I have tried setting the DataAdapter.AcceptChangesDuringFill to false
and still there is no records added to target database table, there is no
records in this table and from surce database table there is 12 records. I
am
currently using the very long approach which is using select to compare
the
databases records and inserting new records and deleting records all this
hard-coded, it is very very long lines of codes. But if you guys have some
very much shorter lines of codes that works with mySQL database Ver 4.1,
please tell me. Thanks for reply.

den2005
--
MCP Year 2005, Philippines

Cor Ligthert said:

Den,

As I wrote are with the function you use all rowstate set to not changed,
what means that nothing will be done with it during the update. To merge
something and to add that to another table, you have to tell that it are
new
rows in a dataset. That you get by setting the acceptchangesduringfill to
false (default it is true), see for that my previous message.

Cor

den 2005 said:

Hi Cor,

Thanks for reply. I used another function to return the Datatable.

Code:

DataTable dt = comp.GetclientTable();

int iRes = comp.UpdateServerTable1(dt);

Code:

public DataTable GetClientTable()
{

. . .

OdbcDataAdapter odAdapter = new
OdbcDataAdapter(sqlTextServer,odServerConn);

if (odServerConn.State == ConnectionState.Closed)
{
odServerConn.Open();
}

DataSet ds = new DataSet();

odAdapter.Fill(ds,"CheckFault");

dt = ds.Tables["CheckFault"];

if (odServerConn.State == ConnectionState.Open)
{
odServerConn.Close();
}

...

return dt;
}

den2005
--
MCP Year 2005, Philippines

:

Hi Everybody,
I am Updating server database with data from client database, I am
using
mySQL Ver 4.1, I am unable to do that. What should I do to change the
codes
to make it work? I have also another method that do the opposite data
from
server updates the database on client side. I need help. There is no
change
in records of target database. I have saw some topics here at this
forum.
The
problem is Connection to server database is ok, but when
"odAdapter.Update(ds,"<tablename>"); " executes it returns value of 0
and
on
changes made to server database, it should have 12 rows. Advanced
Thanks.

Code:

public int UpdateServerTable1(DataTable dtObj)
{
DataSet ds = new DataSet();
int iRes = 0;
try
{
this.sqlTextServer = "Select * From <tablename>";

OdbcDataAdapter odAdapter = new
OdbcDataAdapter(this.sqlTextServer,this.odServerConn );

odAdapter.Fill(ds,"<tablename>");

ds.Merge(dtObj,true,MissingSchemaAction.Ignore);

OdbcCommandBuilder cmdBuild = new OdbcCommandBuilder(odAdapter);

iRes = odAdapter.Update(ds,"<tablename>");

}
catch (Exception ex)
{
ds.RejectChanges();

}
return iRes;
}

Regards,
denpsia

Click to expand...

Click to expand...

Guest · Oct 10, 2005

Hi Cor,
I am using MYSQL database Ver 4.1 (Freeware Database from dev.mysql.com)
and I think the codes will work ok with Microsoft SQL Server. But, thanks
for reply. I have finished doing the long approach and it is working so far.
But it involves a lot of codes.

den2005
--
MCP Year 2005, Philippines

Cor Ligthert said:
Den,

Are you sure that the keys are equal. (And the table name, otherwise there
is just a new table added to the dataset).

I have here a very simple sample in VB about the merge. I am sure that you
as MCP can read it.

http://www.windowsformsdatagridhelp.com/default.aspx?ID=edb1409d-5394-468f-a63f-de3a5d92b14a

I hope this helps,

Cor

den 2005 said:

Hi Cor,
I have tried setting the DataAdapter.AcceptChangesDuringFill to false
and still there is no records added to target database table, there is no
records in this table and from surce database table there is 12 records. I
am
currently using the very long approach which is using select to compare
the
databases records and inserting new records and deleting records all this
hard-coded, it is very very long lines of codes. But if you guys have some
very much shorter lines of codes that works with mySQL database Ver 4.1,
please tell me. Thanks for reply.

den2005
--
MCP Year 2005, Philippines

Cor Ligthert said:

Den,

As I wrote are with the function you use all rowstate set to not changed,
what means that nothing will be done with it during the update. To merge
something and to add that to another table, you have to tell that it are
new
rows in a dataset. That you get by setting the acceptchangesduringfill to
false (default it is true), see for that my previous message.

Cor

"den 2005" <[email protected]> schreef in bericht
Hi Cor,

Thanks for reply. I used another function to return the Datatable.

Code:

DataTable dt = comp.GetclientTable();

int iRes = comp.UpdateServerTable1(dt);

Code:

public DataTable GetClientTable()
{

. . .

OdbcDataAdapter odAdapter = new
OdbcDataAdapter(sqlTextServer,odServerConn);

if (odServerConn.State == ConnectionState.Closed)
{
odServerConn.Open();
}

DataSet ds = new DataSet();

odAdapter.Fill(ds,"CheckFault");

dt = ds.Tables["CheckFault"];

if (odServerConn.State == ConnectionState.Open)
{
odServerConn.Close();
}

...

return dt;
}

den2005
--
MCP Year 2005, Philippines

:

Hi Everybody,
I am Updating server database with data from client database, I am
using
mySQL Ver 4.1, I am unable to do that. What should I do to change the
codes
to make it work? I have also another method that do the opposite data
from
server updates the database on client side. I need help. There is no
change
in records of target database. I have saw some topics here at this
forum.
The
problem is Connection to server database is ok, but when
"odAdapter.Update(ds,"<tablename>"); " executes it returns value of 0
and
on
changes made to server database, it should have 12 rows. Advanced
Thanks.

Code:

public int UpdateServerTable1(DataTable dtObj)
{
DataSet ds = new DataSet();
int iRes = 0;
try
{
this.sqlTextServer = "Select * From <tablename>";

OdbcDataAdapter odAdapter = new
OdbcDataAdapter(this.sqlTextServer,this.odServerConn );

odAdapter.Fill(ds,"<tablename>");

ds.Merge(dtObj,true,MissingSchemaAction.Ignore);

OdbcCommandBuilder cmdBuild = new OdbcCommandBuilder(odAdapter);

iRes = odAdapter.Update(ds,"<tablename>");

}
catch (Exception ex)
{
ds.RejectChanges();

}
return iRes;
}

Regards,
denpsia

Click to expand...

Click to expand...

Unable to merge 2 mySQL Databases using OdbcDataAdapter.Update

Guest

Mark Ashton

Guest

Cor Ligthert [MVP]

Guest

Cor Ligthert [MVP]

Guest

Cor Ligthert [MVP]

Guest