Update Datasource Using dataadapter.update method

[ramnujs]

I have a problem updating my datasource using update method of dataadapter. I am using command builder for autogenerated sql commands the code fragment look like this:

dim myDataset as Dataset = myOdbcDataset.GetChanges(RowState.Modified)

OdbcDataAdapter.Update(myDataset)
myDataset.AcceptChanges()

 

[jmcilhinney]

And what exactly is the problem you're having?

 

[ramnujs]

the problem is that OdbcCommandBuilder does not genarate automatics commands for INSERT, DELETE and UPDATE. It gives an error message Null Object Reference Exception and I traced it on locals(windows ) Delete,Insert and Update Commands are nothing. But select command is there!

 

[jmcilhinney]

Those properties of the DataAdapter WILL be Nothing when you use a CommandBuilder. No object is ever assigned to those properties. Your CommandBuilder generates the Commands and they are used on-demand. Are you getting an error message when you call Update?

Also, there is no point calling AcceptChanges on myDataset because Update implicitly accepts the changes anyway. It is myOdbcDataset that you need to call AcceptChanges on because it is unaffected by the call to Update. That might be the actual issue: myDataset reflects the committed changes but myOdbcDataset does not because you never tell it to.

 

[ramnujs]

the problem really was not on the Dataset side but on the datasource side.
the moment Update method of the dataAdaper was called, the changes were not reflected on the datasource because there were no command objects generated by the command builder.

 

[jmcilhinney]

When you say "datasource" do you mean the actual database or the DataSource of a control, like a DataGrid?

 

[ramnujs]

datasource meant the database

 

[jmcilhinney]

Have you confirmed that there are rows with the Modified RowState? If there weren't your DataSet should be a null reference, which I think would cause Update to throw an exception. If you don't get an exception then that is probably not the case, but note that you should be checking for a null reference in your code to make sure that's the case.

Update is actually a function, so it returns a value. That value is the number of rows affected by the operation, so if you test what that return value is and it's greater than zero then changes have definitely been made. Try this to see the return value:

 MessageBox.Show(OdbcDataAdapter.Update(myDataset).ToString())