TableAdapter.Update() does not update database, only dataset

Crionic

Member
Joined
Feb 1, 2007
Messages
7
Location
Norway
Programming Experience
1-3
EDIT EDIT:
============================================================
Of course, after banging my head against the desk to the point of risking permanent damage I found out what was wrong... Simply the fact that it copies and overwrites the mdf file in bin/debug everytime I run the application..... So, it was working all along, its just that I am looking at two different databases and getting really confused....

Hopefully someone else might learn from my experience too :D
============================================================

Hi everyone,

I am coming from VB and ASP classic and trying to get my head around ADO.NET, and not understanding why this is not working. I call the update function of the tableadapter class, and although the dataset reflects the new row I've added, the database file does not - can anyone point me in the right direction? It seems to me that I am doing exactly what everyone says I am ought to, so excuse me for the tedious amount of "step-by-step" below, I just didn't want to miss anything along the way.

Using VS.NET 2005, this is exactly what I do step by step:

1. Create new project (Visual Basic, Windows Application). I then add as listbox (listbox1) and a button (btnAdd).

2. Create a new SQL Server Express 2005 database file by clicking in the toolbar: Project -> Add New item -> SQL Database -> Add.
The database file database1.mdf is then created and I am presented the "Data Source Configuration Wizard".

3. I name the dataset "dsTest" and check the "tables" item and click "Finsih".

4. Open up "Server Explorer", right click the "Tables" folder in "Database1.mdf" and selects "Add new table"

5. I add two new columns: "id" (primary key, int, identity) and "textField" (nvarchar(50)) and save the new table as tblTest. I open the data view of the table and add a couple of rows, like "item 1" and "item 2"

6. Doubleclick the dsTest.xsd file in the solution explorer and drag the "tblTest" table from the server explorer onto the designer area. This adds a new table adapter called tblTestTableAdapter.

7. I then drag and drop the table from data sources onto the form, this adds dsTest, tblTestBindingSource, tblTestTableAdapter and tblTestBindingNavigator. I delete the binding source and navigator as well as the datagridview and toolbar it created.
8. In form1.load I add the following code:
PHP:
     Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        TblTestTableAdapter.Fill(DsTest.tblTest)
        For Each dRow As DataRow In DsTest.tblTest.Rows
            ListBox1.Items.Add(dRow("textField"))
        Next
    End Sub
9. In btnAdd.click I add the following code:
PHP:
     Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
        Dim myNewValue As String = InputBox("Enter new value")
        TblTestTableAdapter.Insert(myNewValue)
        TblTestTableAdapter.Update(DsTest.tblTest)

        ListBox1.Items.Clear()
        TblTestTableAdapter.Fill(DsTest.tblTest)
        For Each dRow As DataRow In DsTest.tblTest.Rows
            ListBox1.Items.Add(dRow("textField"))
        Next
    End Sub
10. Seemingly, this works perfectly. I can add a new item to the dataset, and by refilling it in the btnAdd_click event it refreshes the dataset with the newly added string. However, when I close the application and re-open it again, the values I entered are gone, how can this be? I have tried this on two different computers, with both SQL Server Express 2005 and MS Access. They both yield the same result. It seems obvious to me that something is missing, but from what I can read on MSDN and the walkthroughs I cannot figure out what is wrong. In my mind, if filling the dataset after updating the adapter returns the data I expect this should mean that it is also physically present in the database?

Can you give me a hand here? I'll be your biggest fan! :)
 
Last edited:
However, when I close the application and re-open it again, the values I entered are gone, how can this be?

So very, very sorry that I didnt get here earlier - I answer this query, on average, 3 times a week..

You have a "clean" development copy of the db that is copied out every time you run the app..
This is sensible because then you dont end up releasing a database with all your testing attempts and rubbish in it..

However, I do think that Microsoft should have made the Copy mode of the database to "Copy if newer" - click the db file in the solution explorer and look at the properties..

..Your dev database instantly becomes newer the moment you update it, so you dont see this database_not_being_updated "problem"


-


That said, Microsoft DID tell you about it - when you added the db to your project, a dialog appeared saying:

"Blah blah, outside the file system, blah do you want to add it into the project, blah, will be copied out each time you start the project blah, press f1 for controlling this behaviour"

I missed it first 3 times too - Grr @ MS ;)
 
That said, Microsoft DID tell you about it - when you added the db to your project, a dialog appeared saying:

"Blah blah, outside the file system, blah do you want to add it into the project, blah, will be copied out each time you start the project blah, press f1 for controlling this behaviour"

I missed it first 3 times too - Grr @ MS ;)
Even I read that, but obviously didn't recognize what it meant before I tried using a MS Access file outside the project folder and finding that it worked... Doh! So much for RTFM, eh? :)

Of course, now that I've had a few hours to calm down and think about it, it definitely makes sense to copy the database file to the debug folder, just as you say. And even though Microsoft warns about not using the "Copy if newer" option, I think I'll go for that - I'll rather just truncate the tables after I've tested it myself.

Thanks for your reply! Cheers!
 
If you use copy if newer, then the design version of the database will always be older, and the debug version will persist until you delete it yourself by doing a Clean from the Build menu. This means your changes will be preserved across debugging sessions :)

Just make sure you Clean before you Build the release version and you will be sure that you have a pure copy of the design database in the release version of your app
 
Last edited:
That I will try, I understood the workings of "Copy if newer" differently, but if it works as you say it is definitely a handy option.

Thanks again :)
 
Copy if newer, for any file, notjust databases, copies the file out during build, if the file is newer than the one it will be replacing.

Your design db is blank
It is copied out to be alongside the EXE when it is built
The exe alters the copy
The alteration causes the copy db to become newer than the development original
Hence, the original db is never copied out again unless the copy is deleted
Hence, your changes are never overwritten
If you edit the design of the original db it becomes newer so it is copied out again
You lose your data inserted etc but then again, it should just be test data and if the design alteration you made was a breaking change then you WANT the new version copied out or your program


This is a good thing and the most sensible option i think. Copy if newer should be the default..
 
Back
Top