TableAdapter.Update does not save data

Question

How can I save data entered in a dataset via the UI? I tried the code suggested in the "VB Guided Tour",

Private Sub Form1_FormClosing(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing

Me.AddressesBindingSource.EndEdit()

Me.AddressesTableAdapter.Update(Me.SQLdbDataSet.Addresses)

End Sub

but all changes are lost when I restart the application.

Thanks.

Answer 1

I have the same problem in C# even if you don't use a tableAdapter but a sqlCommand object instead.  Though affectedRecords reflects the correct rows affected.

Answer 2

I had the same problem and have posted this to the feedback center:
http://lab.msdn.microsoft.com/productfeedback/viewfeedback.aspx?feedbackid=91c8e752-3d93-4ac9-b508-4b275e7e0ed0

Answer 3

I believe AddressesTableAdapter.Update invokes the AddressTableAdapter.UpdateCommand. Prior to calling Update you'll need to set AddressesTableAdapter.UpdateCommand = new SQLUpdateCommand...

Hope this helps,
Hal

Answer 4

Thanks. But I guess I'm too new to this to understand your suggestion. I added a line,

AddressesTableAdapter.UpdateCommand = new SQLUpdateCommand

just before the update, but that just generated two compiler errors:

Error 1 'UpdateCommand' is not a member of 'VBdatabase.SQLdbDataSetTableAdapters.AddressesTableAdapter'. 

Error 2 Type 'SQLUpdateCommand' is not defined. 

What am I missing?

Thanks.

Answer 5

Found the solution in the free downloadable book 'Build a program now' at the 'Registration Benifit Program' p154 (text in blue).

In brief there are 2 copies of the database: 1 in the application folder and 1 in the bin\debug folder. At design time the database in the app folder is used while at run time the other one.

By default in the solution explorer the database is set to COPY ALWAYS to the bin\debug folder which means that the database is alwys overwritten by the one in the application folder.

Solution:
You can change this property to DO NOT COPY and copy it yourself if when needed or leave the database out of the project. When asked to bring it in the project when creating a connection, sympy say no.

I hope you are as pleased as I am now to have found that annoyance.

Answer 6

Thanks! I thought there must be a setting somewhere that I was missing. I find that if I set the copy property to "do not copy" I get an unhandled exception whenever I try to open the dataset. But "copy if newer" works just fine. The only problem is remembering to copy from App\bin\debug to App when needed.

Answer 7

I must have missed something in the documentation.  Everytime I would run this program The data would disappear.  Thanks for pointing out that there are two copies.

 

 

Answer 8

This definitely sounds like my problem. I hate to show my ignorance but can you tell me how I change these options?

 

Thanks,

Answer 9

can u plz explain what the command in UpdateCommand and how to make this cuz adressTableadapter.update has to return a value in int

thanks

 

I believe AddressesTableAdapter.Update invokes the AddressTableAdapter.UpdateCommand. Prior to calling Update you'll need to set AddressesTableAdapter.UpdateCommand = new SQLUpdateCommand...

Hope this helps,

Hal

Answer 10

Hi all !

Man, forget all !!!

If u using a bindindsource, verify your's components databindings. If they connected direct from dataset the updates not work. Bind using bindingsource object and done!

Sorry my english!!!

I hope helpful.

Answer 11

Hi Guys, ran into the same problem last night and wasted 2 hours trying to figure out what was wrong.

As was posted above, this issue can be fixed by clicking on the database in the solution explorer and adjusting the property to read 'Copy if Newer' instead of 'Copy Always'.  If you set it to 'Do Not Copy', you need to manually copy it or you an exception saying that it can't find the database file.

Thanks again to all who figured this out.

Answer 12

Nice one, spent days scratching my head over this one, thanks

Answer 13

I'm having a similar issue wher the data is not updating. I don't have a local copy of the database at all. I'm connecting to a remotly hosting SQL Server 2005 database. Everything was working fine until yesterday and now it's as if the tables are all read-only. The TableAdapter.Update command never invokes the underlying Update method. The underlying update method that should be invoked is

public virtual int Update(DataTable dataTable) {

return this.Adapter.Update(dataTable);

}

 

I set a breakpoint on the code and it never hit! ANyone have a clue why this would be??

Answer 14

"Found the solution in the free downloadable book 'Build a program now' at the 'Registration Benifit Program' p154 (text in blue)."

Where can I download this book? I to am having problems with the TableAdapter. My problem is when I try to update it tells me that .update is not a member of my workbook.

Thank you

Tom

 

Answer 15

Thanks Frank so much.. I lost my head couple of hours to find out what is happening. It works now, thanks again.

Answer 16

I also have a problem updating the database:

I manually changed the datapath so I'm working directly on the database and for my customers table, it works fine but I've added another bindingadapter for the customercars table and when I add, it works fine but when I try to modify a record I get the following message:

 Update requires a valid UpdateCommand when passed DataRow collection with modified rows.

The update code is identical to the update code for the customers table and yet that updates fine. Any ideas guys?

Answer 17

Hi It seems like I can't grab this book (I've registered the prod.). Is there a link?  

On the page there are just pics of books (in yellow or somethink). No downloads.

I'd appreciate if you were so kind to give me some more details.    

 

Thanks in advance!    

 

 

 

 

Answer 18

I am still confused. I understand about the database having two locations and that the property 'DO NOT COPY/ALWAYS COPY can be changed but my data never gets back to the application database, whichever option is used.

I can see the data in the database residing in the bin\debug but I wany it to be permanently sent to the copy of the database in the application.

Do I need to add an SQL query to make this happen or what? Is there any code that copies the datatable from one location to the other.

Nobody else seems to be having the same problem .....are you?

Thanks for any help

 

Neil345

Answer 19

 

You don't have to have a copy of database in your project - just use the same one as always.

1. In Data sources tab click Add datasource

2. Select the type of DB and which files to import to the project. (test it just to see)

3. This will create a nice dataset which will be a copy of the original DB files you select.

4. You can select names as you please.

5. When connecting to a database dont use "ok"  use "no" when the message about including a copy of database pops-in.

6. You're done! Almost!!!

7. Also you will have to mark your new dataset in Solution explorer as "Copy if newer" if you would want to update old data with new one.

8. If you double click on a form with added table from Data sources (like Datagridview). You will get a code window for that form. Then choose the righ event for binding navigator (exactly when you want your dataset to be updated) like let's say:

Private Sub YourBindingSource_CurrentItemChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles YourBindingSource.CurrentItemChanged

Me.YourBindingSource.EndEdit()

Me.yourTableAdapter.Update(Me.YourDataSet.YourTable)

End Sub

Or any other event wth Binding source.

 

9. Reply to this post if it helps you so I will know if I was any good in helping.

 

 

 

 

 

Answer 20

Thanks Kdee

I appreciate your efforts in explaining but my problem is slightly different to the method that you are proposing.

I am creating a new dataset with an empty table but I am not using another database as my datasource.  I am using XML to fill my datagrid from an external XML file. This works ok and I end up with a populated datagrid.

My problem is that I cannot update my database with this data. I can see the table correctly in the temporary database residing in bin/debug but I cannot get the data permanently stored in the other 'copy' of the database. When I exit my application the data is wiped.

I can change the data and then save it in a new xml file but I want to permanently save it in the applications database table.

Thanks for your help, please come back

 

Neil345

Answer 21

I believe my answer was correct after all.

It's a different case but...  I believe ...    the same problem.  If you have created a copy of original DB which is generaly put in bin folder then .. you have to update the original DB MANUALY!!!!!! Copy the BIN DB to original DB.

Yes...  at least as I understand it. I read some article about it, which ended my one and a half months headache.

The other solution is not to include original db to bin folder in this case you have to also set up all other things like I proposed in my last reply. Then the updated data from "project dataset" which is a "virtual mirror" of your original DB gets copied into the original DB and this automatically updates your original DB.

Many people have already asked about this problem with many headaches also ( almost everyone with more than several days of trying for themselves). See the past few months for articles in this forum and "VOILA" ....  many people like you and me.

Maybe you will also find other solutions.

See ya!

 

 

 

 

 

 

 

 

Answer 22

Thanks for coming back again Kdee but you have completely lost me.

Can we just do a rewind and go through it again

I am not using a database as a datasource, but I have a dataset with an empty table. I get my data from an XML file which is designed to fit nicely into this empty tables datagrid on my form. I then pull in the data ok and press the 'save' button but the update does not get propagnated to the underlying database. The correct does however then reside in a temporary file in the bin/debug. (WHY?)

You say that I have to manually copy it to my underlying database (still empty at this stage)

I don't understand why I should have to manually copy it and I don't know how to do that either.

There is something wrong with my basic understanding of what happens. Why isn't the table adaptor doing its job? Is it because it knows that it didn't supply the data via a data source? Why should it care?

I think its a fault in Visual Studio

 

Regards

 

Neil345

 

 

 

 

Answer 23

 

sorrry

Try to look further if anyone had the same problem as you. I believe I can not help you more. I hope you'll find the solution fast.

 

 

 

 

Answer 24

I tried all sorts of thing but in the end what worked was this

Go to the Data Sources Sidebar

Right click on the DataSet and click Edit DataSet with Designer

Right click on the Database and click Configure

Click Advanced Options and tick Generic Insert, Update and Delete Statements

I also ticked Use optimistic concurrency although I dont't think that was needed

Hopefully this helps someone else out of a headache

 

Answer 25

So Helpful, so helpful.

Answer 26

Thanks much....I lost hours on that one

Answer 27

I had the same problem and that works, thanx.

Answer 28

The Copy Always / Copy if Newer did the trick for me too. I was following the lessons in the Visual Studio Express Edititions learning center, but the lesson doesn't mention this option. I suppose that is because the lessons were created using a Beta version of Visual Studio Express.
I'm very happy I've found the solution here, cause it was driving me crazy

Answer 29

1. go to your solution explorer
2. click your database.mdf icon
3. go to its properties
4. change the copy to output directory copy always to do not copy.
5. then go to the folder where you save your project copy the .mdf into debug which can be found in the bin folder

hope it helps

Answer 30

this thread is still working.

 

Have spent hours on this problem - and setting the property on the databse to "copy if newer" also worked for me.

 

GREAT.

 

thanks all.

 

 

 

Answer 31

 

You are my hero

I have spent a whole week trying to find out why this was happening with C#

I have tried several solutions in an effort to understand the architecture

I understand it now that you pointed out this annoyance

Thanks

Answer 32

kdee wrote:

You don't have to have a copy of database in your project - just use the same one as always. 1. In Data sources tab click Add datasource 2. Select the type of DB and which files to import to the project. (test it just to see) 3. This will create a nice dataset which will be a copy of the original DB files you select. 4. You can select names as you please. 5. When connecting to a database dont use "ok"  use "no" when the message about including a copy of database pops-in. 6. You're done! Almost!!! 7. Also you will have to mark your new dataset in Solution explorer as "Copy if newer" if you would want to update old data with new one. 8. If you double click on a form with added table from Data sources (like Datagridview). You will get a code window for that form. Then choose the righ event for binding navigator (exactly when you want your dataset to be updated) like let's say: Private Sub YourBindingSource_CurrentItemChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles YourBindingSource.CurrentItemChanged Me.YourBindingSource.EndEdit() Me.yourTableAdapter.Update(Me.YourDataSet.YourTable) End Sub Or any other event wth Binding source.   9. Reply to this post if it helps you so I will know if I was any good in helping.

Hello, i've done the exact procedure. I've binded it to a datagridview, it worked! but the downside is I have to select another row inorder to complete the update. Do you know any work arounds with this? thanx a lot. 

Answer 33

 Great solution!! This works pretty well

Answer 34

This is because when you made the initial connection to the database with the wizard, it asked if you wanted to make this database part of your project, and you answered yes.  When you do that, it copies the database to your project's directory.  Everytime you run the application, it copies that blank database to the debug folder and connects to it.  You can add data to it, and it does save it.  When you run the program again, it copies the blank database again to the debug folder and overwrites the one that you saved the data.  That is why the data is being lost.

It is best not to select that option to copy it to your project.  That option is only good if you are deploying a project to people that don't have a copy of the database.  When you publish the release it with that option, it would copy it one time to the installed directory of the applicaiton.  They would not loose their data each time unless they installed it again.

Answer 35

Here is what I have:

Case 2
  BtnUpdate2.Visible =True
  BindingNavigator1.BindingSource = MasterEmployeeBindingSource
  MasterEmployeeDataGridView.Dock = DockStyle.Fill
  MasterEmployeeDataGridView.Visible =True
  Me.MasterEmployeeTableAdapter.Fill(Me.DbmasopsDataSet.MasterEmployee)

End Select
End Sub

Private Sub BtnUpdate2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnUpdate2.Click
  Me.MasterEmployeeBindingSource.EndEdit()
  Me.MasterEmployeeTableAdapter.Update(Me.DbmasopsDataSet.MasterEmployee)
  BtnUpdate2.Visible = False
End Sub

 

 

I get an error at the .Update that reads "Update requires a valid UpdateCommand when passed DataRow collection with modified rows."
If I make the change in the table and then go to the next line it gives me the same error.
I have the tables set the way Grant says to do it above and I have the database property = Copy if newer

My database is not local.  I also did not get the choice of whether I could make this a part of my project, maybe there is a property I can change or something?  Or maybe because it's remote I don't have a choice?  Anyway, any ideas on what I am doing wrong that I get this error on the update?

Thanks!!!