Can't update a DataAdapter

[sfx]

Hello All,

I am having problems using the Update method of a DataAdapter. Can anyone tell me why the below code does not work?

cmdUpdateCustomers = New OleDbCommand
cmdUpdateCustomers.Connection = conCustomers
cmdUpdateCustomers.CommandText = "UPDATE tblCustomers SET FName = @FName WHERE CustomerID = @CustomerID;"
cmdUpdateCustomers.Parameters.Add("@FName", OleDbType.VarChar, 50).Value = txtFName.Text
cmdUpdateCustomers.Parameters.Add("@CustomerID", OleDbType.Integer, 16).Value = txtCustomerID.Text
 
daCustomers.UpdateCommand = cmdUpdateCustomers
daCustomers.Update(dsCustomers, "tblCustomers")

When placed in the click event of a button the code appears to execute without error but no changes occur to the underlying database. However, if I execute the code by calling the 'executenonquery' method of the command object I am able to update the underlying database. I know there must be something that I am missing. Any ideas?

Cheers,

sfx

 

[vis781]

cmdUpdateCustomers.CommandText = "UPDATE tblCustomers SET FName = @FName WHERE CustomerID = @CustomerID"

In oledb you use the '?' as the placeholder for the parameter.

cmdUpdateCustomers.CommandText = "UPDATE tblCustomers SET FName = ? WHERE CustomerID = ?

 

[sfx]

Hi Vis781,

Thanks for your reply. Unfortunately, this does not seem to work either. Replacing the placeholders results in the same problem. The underlying database is still not being updated. Should I be adding the parameters a different way?

Cheers,

sfx

 

[vis781]

There's nothing wrong with the parameters as far as i can see, i've never tried to use a dataadapter in that way. Usually i modify some rows in the datatable then use the dataadapter to update the datasource. But if executenonquery works then why not use it.

 

[TechGnome]

Oddly... you are misusing the updateCommand.... the update command isn't for updating one record.... but rather it should be a command that will update ALL fields of ALL modified rows in the DataTable.... You've set it up to update one specific row, so it doesn't know how to cope with it, so it doesn't run (although it's a bit odd that you don't get *some* kind of error.)

When you create an UpdateCommand, you supply it place holders (?) and then when adding the parameters, rather than supplying the actual values, bind it to a field in the table.... Because I use stored procs, I don't have a good sample to give you, but you should be able to pick up the clues from the intellisense.

-tg

 

[sfx]

Hi Vis,

I thought about just using the executenonquery method of a command object, but I just needed to know why the DataAdapter was not working for me. However, I think I will use a command for most updates when coding in the future.

Cheers,

sfx

 

[vis781]

Don't give up on the way you were doing it first, atleast not completely. As Techgnome said it's just not ment for the way you were trying to make an update. The executenonquery method will work fine for updates like the one in your example but for updates from a datatable you will want to get familiar with the dataadapters update method.

 

[sfx]

Hi TechGnome,

Thanks for your reply to my question. You are right - I was misusing the DataAdapter. However, after some testing, I realised that I was not informing the DataAdapter that changes had been made to the DataTable and therefore it wasn't updating anything since it believed there were no changes that needed to be made. Once the DataAdapter was notified of changes to a single DataRow (i.e. .beginedit, .endedit) it was able to propagate these modifications to the underlying database.

My biggest mistake was in thinking that the execution of a DataAdapter’s ‘update’ method was synonymous with the ‘executenonquery’ method of a command object.

Thank again for you help,

sfx

 

[TechGnome]

It's supposed to be seamless.... I've never had to use the BeginEdit/EndEdit when dealing with updating changes.... Now I'm curious...


-tg

UPDATE: Look at the last code example in this tutorial: http://www.developerkb.com/modules/wfsection/article.php?articleid=52 in it, I've used the UpdateCommand to update a datatable.... the difference being, I used the CommandBuilder to allow it to build the update for me. *shrug*

 

[sfx]

Hi TechGnome,

You have some very helpful information on your site!

Yes, I thought it was supposed to be seamless too. However, if I code everything manually, I cannot get the underlying database to be updated unless I 1) actually edit the datarow values with corresponding parameters through BeginEdit/EndEdit, or 2) trick the DataAdapter into thinking that an edit has taken place through the use of the BeginEdit/EndEdit methods if the parameters are being wired to different values i.e. textbox values.

Oh well...

sfx