locked
Data binding doesn't update dataBASE from dataSET. RE : Walkthrough: Saving Data to a Database (Single Table) RRS feed

  • Question

  • Hi, 

    I am fairly new to VB.net although I have used VBA for a long time. 

    The issue I am having is am trying to make use of data binding to tie my forms controls / gridviews directly to the data.

    I have been following this tutorial (see below) , to the letter, and yet I cannot get the data to save back to the database. 

    the walk through is "Walkthrough: Saving Data to a Database (Single Table)" found here

    I set up the database connection, the dataset is created. all is going well. I run the application and the data loads from the database. but anything i add to the database via the bound forms will stay in memory as long as I don't close down visual studio, but once that is close and reopen (or I restart the machine) the data is lost. 

    I am clicking on the floppy disk icon on the bound controls, and I can see the code below fires. and completes without error.

    If is go to the data source and use the "Show Table data" i can see that the data I added is not there. 

    Am I missing something fundamental from the code below? 

    Try 
        Me.Validate()
        Me.CustomersBindingSource.EndEdit()
        Me.CustomersTableAdapter.Update(Me.NorthwindDataSet.Customers)
        MsgBox("Update successful")
    
    Catch ex As Exception
        MsgBox("Update failed")
    End Try

    I have tried adding things along the lines of

    NorthwindDataSet.Acceptchanges()

    , but that doesn't help at all. 

    I know the solution must be trivial , but I cannot understand why this walk through doesn't work for me. 

    I have a few versions of VS Express on my machine and SQL Management studio installed also, but I don't see how that would have any effect ? 

    The code above runs flawlessly, there are no errors flagged, and I don't get any of the syntax errors (the blue squiggly lines i mean) 

    While I am here, are there any draw back to using databinding in this way. I am not a fan of writing code I don't have to, so setting up SQL commands to delete, insert, update etc. etc. seems silly when I can use this wonderful drag and drop functionality - if it only worked :-) - to make all my data management automatic, clean and error free. 

    Thanks a million for any suggestions. 

    Monday, April 28, 2014 8:07 AM

Answers

  • Well, I think I have answered my own question, but I will leave it here in case anyone else has the same problem. 

    So, as far as I have been able to track down, it all went wrong when I was running through the connection wizard. 

    Under the section titled "Creating the Data Source" is describes how to find your database file and create the appropriate connection string. However, on my version of VS Express 2010 . It offered me a prompts , saying something like, "would you like to move this database file into the application directory and change the connection string" this sounded very sensible to me, so I said yes.

    All proceeded accordingly. And the database file now appeared in the solution explorer. 

    The app config file said that the connection string was 

    Data Source=|DataDirectory|\Database1.sdf"

    I presumed this would be interpreted correctly by the rest of the app, as it was generated by VS.

    But it didn't, what I cannot understand is how no error was generated. And data seemed to pull into the bound controls. 

    But I have been testing it for a while now. and it seems that if I manually override the config file with the actual directory where the file exists , then there is not a problem. Data is retained in the file 

    This is more of a VB.net question, but I couldn't find it in the drop down. I will try and move it there now

    Thanks guys for your patience. 

    p.s. RSingh , the code I posted above did come from the SaveItem_Click event handler

    • Marked as answer by B Wai Tuesday, April 29, 2014 12:39 PM
    Tuesday, April 29, 2014 12:37 PM

All replies

  • Turn on SQL Server profiler ( add User error event as well) and see what is going on

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Monday, April 28, 2014 11:42 AM
    Answerer
  • This has nothing to do much in SQL Server. You should post your query to .NET forum.

    However it seems like you have not fully implemented the functionality. You have not mentioned the source code for "Binding Navigator".

    Search the below text in the link you have specified,


    Regards, RSingh

    • Edited by Ch. Rajen Singh Monday, April 28, 2014 11:53 AM
    • Proposed as answer by Sofiya Li Tuesday, April 29, 2014 8:45 AM
    Monday, April 28, 2014 11:53 AM
  • Well, I think I have answered my own question, but I will leave it here in case anyone else has the same problem. 

    So, as far as I have been able to track down, it all went wrong when I was running through the connection wizard. 

    Under the section titled "Creating the Data Source" is describes how to find your database file and create the appropriate connection string. However, on my version of VS Express 2010 . It offered me a prompts , saying something like, "would you like to move this database file into the application directory and change the connection string" this sounded very sensible to me, so I said yes.

    All proceeded accordingly. And the database file now appeared in the solution explorer. 

    The app config file said that the connection string was 

    Data Source=|DataDirectory|\Database1.sdf"

    I presumed this would be interpreted correctly by the rest of the app, as it was generated by VS.

    But it didn't, what I cannot understand is how no error was generated. And data seemed to pull into the bound controls. 

    But I have been testing it for a while now. and it seems that if I manually override the config file with the actual directory where the file exists , then there is not a problem. Data is retained in the file 

    This is more of a VB.net question, but I couldn't find it in the drop down. I will try and move it there now

    Thanks guys for your patience. 

    p.s. RSingh , the code I posted above did come from the SaveItem_Click event handler

    • Marked as answer by B Wai Tuesday, April 29, 2014 12:39 PM
    Tuesday, April 29, 2014 12:37 PM
  • p.s. I can't seem to move it to a .net thread. 

    Tuesday, April 29, 2014 12:50 PM