Click here to Skip to main content
15,905,563 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi, I have a problem when using the OleDbAdapter.Update(table), what I'm trying to do is fill a table from a SQL server database and copy the contents of that table to a SqlServerCe (.sdf) table with the same schema first, I'll handle differnet schemas later but right now I'm testing it like this.

I've tried the update on the SQL Server by creating a new row on the table and calling the .Update Method and the changes are reflected on my database, but when trying to do the exact same thing on the .sdf, the application stops working, (vshost.exe stopped working, windows is searching for....) so I have no idea where the error is, I can't debug.

I can do inserts with the command.ExecuteNonQuery(), but I was thinking that the update method could improve performance since we're trying to migrate from DTS to CLR SPs and some resources are limited, so I'm looking for the performance alternatie here.

Here's my code:

<code>
 SqlConnection conn2 = new SqlConnection(ConfigurationManager.ConnectionStrings["PC"].ConnectionString);
            SqlCommand command2 = new SqlCommand("Select * from t_i_Association", conn2);
            DataTable table2 = new DataTable();
            conn2.Open();
            SqlDataAdapter adapter2 = new SqlDataAdapter(command2);
            adapter2.Fill(table2);
            
            OleDbConnection conn = new OleDbConnection(ConfigurationManager.ConnectionStrings["Movil"].ConnectionString);
            OleDbCommand command = new OleDbCommand("Select * from t_i_Association", conn);
            DataTable table = new DataTable();
            conn.Open();
            OleDbDataAdapter adapter = new OleDbDataAdapter(command);
            adapter.Fill(table);
            OleDbCommandBuilder builder = new OleDbCommandBuilder(adapter);
            
            table.Merge(table2, true);

            //foreach (DataRow r in table.Rows)
            //{
            //    r.SetAdded();
            //}

            int rows = adapter.Update(table);
            conn.Close();
            conn2.Close();
</code>


The overall idea is to make a select from both tables, one contains data, the other is created but has no data, so I fill both tables, then I merge the empty table with the data and try to update the .sdf database.
If I leave the comment on the r.setAdded() the Update throws no error but returns 0 rows updated. If I call the SetAdded() on each row, the application stops working and it does the same when adding a new row by code to the table and updating the changes. I tried the table.copy, adding a new row from the table with data, almost everything I could think of and I have no idea what's the problem :/

Should I stick to the update method or is making an insert for each row better? what do you think?

thanks in advance!
Posted
Comments
mario_silent 20-Jun-11 14:44pm    
Update! By calling row.SetModified() instead of SetAdded(), the Update throws no error and returns the number of rows updated which is correct, now the problem is I don't see the changes reflected in the .sdf... Is there anything else missing???

1 solution

 
Share this answer
 
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900