Going crazy updating database

I'm about to go crazy about this. I have a database with a couple of tables. I have inserted all tables into a dataset. The data is modified partially by databound fields and partially by manual adding/deletion/modifying.

The problems come when i try to transfer dataset to database. I have tried a shitload of different ways but it won't work. This last time i tried

OleDbCommandBuilder mybuilder = new OleDbCommandBuilder(MyAdapter);
MyConnection.Open();
DataSet ch = MyDataSet.GetChanges();
MyAdapter.Update(ch, "Currencies");
MyConnection.Close();

where Currencies is a table in my database. I have also tried

OleDbCommandBuilder mybuilder = new OleDbCommandBuilder(MyAdapter);
MyConnection.Open();
DataTable changes = MyDataSet.Tables["Currencies"].GetChanges();
MyAdapter.Update(changes);
MyConnection.Close();

In both cases i get an invalidOperationException with message

"Missing the DataColumn 'categoryName' in the DataTable 'Currencies' for the SourceColumn 'categoryName'."

now; categoryName is a column in another table that should in no way be related to the table i am currently trying to update.

I have looked at the changed rows by printing them in a MessageBox and that looks fine.

Please help. been trying to fix this for a couple of days now.

regards, Kristian

Answer this question

Going crazy updating database

Matt_h1977
Hey. since you guys were so helpful with my last issue, maybe you can help me with one last thing

I have some textboxes with databinding to fields in my dataset. I do this by:

txtCategoryName.DataBindings.Add(new Binding("Text", Categories, "categoryName"));

where Categories in this case is a DataTable.

What i'm wondering is: how do i bind to a field with integer datatype Since the type of a textbox.text is string this becomes a problem. The value is read ok from the field, but it won't edit the field in dataset when i edit the contents of the textbox.
Xcel

Hi,

When you used the commandbuilder, your adapter's delete, insert and update commands are being automatically generated based on your select statement. In your code, I think its not needed to use GetChanges. Just automatically pass the table to your adapter. And also check if you have already provided a SELECT statement in your adapter before using the Command Builder. Coz as I said earlier your update,insert and delete commands would be based on your SELECT statement...

OleDbDataAdapter MyAdapter = new OleDbDataAdapter("SELECT * FROM TableName", Conn_object);
MyAdapter.Fill(MyDataSet);

OleDbCommandBuilder mybuilder = new OleDbCommandBuilder(MyAdapter);
MyConnection.Open();
MyAdapter.Update(MyDataSet.Tables["TableName"]);
MyConnection.Close();

cheers,

Paul June A. Domag
bezlali
I realized that it was not the same adapter. But i altered the SelectCommand as you said and now my Exception reads:

Syntax error in INSERT INTO statement.

(not using any reserved words)

// Edit: Actually i am using a reserved word: currency.
i'll come back if problem persists
hammar
Is 'MyAdapter' the one used to populate the Currencies table in the first place

The OleDbCommandBuilder needs an adapter with a SELECT command set so that it can generate the appropriate commands.

You could set the SelectCommand of MyAdapter to "SELECT * From Currencies" and then try it to prove the point.

Steve
rpierry
Don't know if it will work any different but try this:

txtCategoryName.DataBindings.Add(New Binding("Text", YourDataSet, "Categories.categoryName"));

You don't have to do anything special for integer values as far as I can tell...

Steve
imdqa
Pretty much what I said!

You only need to use GetChanges() if you want to process your inserts, updates and deletions in a certain order. This is often required but I suspect not in your case.

An example would be:

tblDeletions = MyDataSet.Tables("TableName").GetChanges(DataRowState.Deleted)
tblAdditions = MyDataSet.Tables("TableName").GetChanges(DataRowState.Added)
tblModifications = MyDataSet.Tables("TableName").GetChanges(DataRowState.Modified)

Steve
rv_saraswathi
Looks OK to me...

I never bind in this way so I've never had that problem...

Steve
steveareno
Ok. The problem was a combination of me using the reserved word currency and not using the same dataadapter for updating a table.

Thanks for your help! Finally i managed to store data into database after two days of ripping my hair off.
Going crazy updating database

Answer this question