Updating tables in a database, why does this single line of code make it work?-VBForums
Results 1 to 17 of 17

Thread: Updating tables in a database, why does this single line of code make it work?

  1. #1

    Thread Starter
    New Member
    Join Date
    Aug 2013
    Posts
    9

    Updating tables in a database, why does this single line of code make it work?

    Hi, Im new to VB.NET and I have developed and application that works fine but in order to make it work as expected I added a line of code but I am unsure why it makes it work. I would really like a good understanding of whats happening and why redefining the dataview is making the database be updated as expected. I have posted the relevant code below, any help would be much appreciated

    Heres where I populate my datatables.

    Public Sub FillTables()
    'Fills datatables with data from the database
    Try
    CountryRuleDataAdapter.Fill(RulesDataset, "database.PSCT_CRULE")
    EncRuleDataAdapter.Fill(RulesDataset, "database.PSCT_ENCRULE")
    CountryRuleTable = RulesDataset.Tables("database.PSCT_CRULE")
    EncRuleTable = RulesDataset.Tables("database.PSCT_ENCRULE")
    Catch ex As Exception
    MessageBox.Show(ex.ToString)
    End Try

    End Sub

    Heres where I setup the dataview when loading a particular form

    Private Sub Countryrulesform_load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    'sets up the dataview when form opens
    CountryRuleEditingView = New DataView(CountryRuleTable)
    CountryRuleEditingView.AllowEdit = True
    CountryRuleEditingView.AllowDelete = False
    CountryRuleEditingView.AllowNew = False
    End Sub

    Heres where I populate the dataview using a row filter so the user can make changes to the data

    Public Sub CountryPopulate(ByVal Ccode As String)
    'runs a rowfilter to populate the view then populates the form with the results
    CountryRuleEditingView.RowFilter = "Country_Code = '" & Ccode & "'"
    If CountryRuleEditingView.Count = 0 Then
    MessageBox.Show("Country " & Ccode & " not found!")
    Else
    CountryRulesWindow.CodeTextBox.Text = CountryRuleEditingView.Item(0).Row.Field(Of String)(0)
    CountryRulesWindow.NewChartCombo.Text = CountryRuleEditingView.Item(0).Row.Field(Of String)(1)
    CountryRulesWindow.NewEditionCombo.Text = CountryRuleEditingView.Item(0).Row.Field(Of String)(2)
    CountryRulesWindow.UpdateCombo.Text = CountryRuleEditingView.Item(0).Row.Field(Of String)(3)
    CountryRulesWindow.NewChartCombo.Enabled = True
    CountryRulesWindow.NewEditionCombo.Enabled = True
    CountryRulesWindow.UpdateCombo.Enabled = True
    End If
    End Sub

    Heres where I edit the values in the dataview once the user has made a change, from my understanding this also edits the datatables, I know the data table has been edited after this as I have another form showing the datatable and the edit has happened.


    Private Sub NewChartCombo_SelectedValueChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles NewChartCombo.SelectedValueChanged
    'when a value is changed in the form the corresponding value is changed in the view/datatable
    CountryRuleEditingView(0)("New_Cell") = NewChartCombo.Text
    End Sub

    Private Sub NewEditionCombo_SelectedValueChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles NewEditionCombo.SelectedValueChanged
    'when a value is changed in the form the corresponding value is changed in the view which amends datatable
    CountryRuleEditingView(0)("New_Edition") = NewEditionCombo.Text
    End Sub

    Private Sub UpdateCombo_SelectedValueChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles UpdateCombo.SelectedValueChanged
    'when a value is changed in the form the corresponding value is changed in the view which amends datatable
    CountryRuleEditingView(0)("UPDT") = UpdateCombo.Text
    End Sub

    I commit the changes when closing the form using these 2 Subs but without the line CountryRuleEditingView = New DataView(CountryRuleTable) the update doesnt happen. Why does resseting the dataview make it work when Im updating from the datables not the view?

    Private Sub Countryrulesform_close(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.FormClosing
    'Upon closing resets variables and uploads changes to tables to the database
    CountryRuleTableForm = Nothing
    IsTableOpen = False
    Try
    CountryRuleEditingView = New DataView(CountryRuleTable) 'This is the line that is frustrating me!
    OpenConnection()
    Updatetables()
    CloseConnection()
    Catch ex As Exception
    MessageBox.Show(String.Format("There was a problem updating the database.{0}" & ex.ToString, Environment.NewLine))
    Finally
    SDRAConnect.Close()
    End Try
    End Sub


    Public Sub Updatetables()
    'Applies edits to datatables back to database
    Try
    CountryRuleDataAdapter.Update(RulesDataset, "database.PSCT_CRULE")
    EncRuleDataAdapter.Update(RulesDataset, "database.PSCT_ENCRULE")
    Catch ex As Exception
    MessageBox.Show(ex.ToString)
    End Try
    End Sub


    So in my understanding when I update tables it updates the database by looking at modified rows on the datatable, the dataview should not be involved, the dataview is just used so the user can edit the datatables so why does it need the view to be redefined using CountryRuleEditingView = New DataView(CountryRuleTable) for the database to be updated?

    Any help explaining this to me is very much appreciated.

    Jav
    Last edited by Javrel; Aug 19th, 2013 at 06:52 AM.

  2. #2
    PowerPoster dunfiddlin's Avatar
    Join Date
    Jun 2012
    Posts
    8,241

    Re: Updating tables in a database, why does this single line of code make it work?

    Why does resseting the dataview make it work when Im updating from the datables not the view?
    Because you're not updating from the tables and you are updating from the dataview. That's where you commit the changes so that's where the changes are drawn from and validated. This ...

    'when a value is changed in the form the corresponding value is changed in the view which amends datatable
    ... is not completely true until the Update is executed.
    As the 6-dimensional mathematics professor said to the brain surgeon, "It ain't Rocket Science!"

    Reviews: "dunfiddlin likes his DataTables" - jmcilhinney

    Please be aware that whilst I will read private messages (one day!) I am unlikely to reply to anything that does not contain offers of cash, fame or marriage!

  3. #3

    Thread Starter
    New Member
    Join Date
    Aug 2013
    Posts
    9

    Re: Updating tables in a database, why does this single line of code make it work?

    Thanks for the reply but Im still missing something here, there something I dont quite undestand.

    I have Database Table, which is loaded into a datatable which is part of a dataset, which has a dataviews pointed at its datatables.

    The user sees and edits data using the dataview, I thought this edits the data in the datatable?

    I have 2 dataviews on the datatable and once one view is edited this can be seen in the other dataview on the datatable so I assumed the datatable must have been edited by the 1st dataview for the 2nd dataview to see it as it is pointed at the datatable not the 1st dataview?

    Why would just redefining the dataview commit changes to the datatable? Why can the second view already see the changes if they have not been commited to the datatable? I havent asked the dataview to commit I assumed it was automatic, originally my application was writing to XML files and this extra line wasnt needed.

    Why when updating a database does the view need to be redefined? Does just redefining it commit changes to the datatable?

    How am I updating from the dataview? my update statements update from a dataset which contains the datatables and not the views
    CountryRuleDataAdapter.Update(RulesDataset, "database.PSCT_CRULE")
    EncRuleDataAdapter.Update(RulesDataset, "database.PSCT_ENCRULE")

    Sry if Im sounding a bit dumb but Im trying to learn and even though Ive made it work its driving me a bit nuts that I dont know why what Ive done has made it work.
    Last edited by Javrel; Aug 19th, 2013 at 09:32 AM.

  4. #4
    PowerPoster dunfiddlin's Avatar
    Join Date
    Jun 2012
    Posts
    8,241

    Re: Updating tables in a database, why does this single line of code make it work?

    The bindings will indeed transmit data to the datatable but in some ways this is nothing more than provisional. When it comes to an update all the dataviews must be synchronised to ensure that there are no edits as yet uncommitted among other things. In practice then the update must start with the dataviews and flow back through the datatable to the database. Update always involves the whole binding structure not just the datatable designated.
    As the 6-dimensional mathematics professor said to the brain surgeon, "It ain't Rocket Science!"

    Reviews: "dunfiddlin likes his DataTables" - jmcilhinney

    Please be aware that whilst I will read private messages (one day!) I am unlikely to reply to anything that does not contain offers of cash, fame or marriage!

  5. #5

    Thread Starter
    New Member
    Join Date
    Aug 2013
    Posts
    9

    Re: Updating tables in a database, why does this single line of code make it work?

    OK that makes a bit more sense, so how do I commit the changes to the dataview back to the table?

    At the moment Im just redefining the view by pointing it at the table again with CountryRuleEditingView = New DataView(CountryRuleTable) so I guess this must be causing the dataview to commit to the datatable?

    If so this seems like I may be using the wrong method to commit the dataview changes to the datatable before the update, atm it seems like Im incidentally commiting them by redefining the dataview rather than actually telling it to.

    Thanks for the help!

  6. #6

    Thread Starter
    New Member
    Join Date
    Aug 2013
    Posts
    9

    Re: Updating tables in a database, why does this single line of code make it work?

    Aaaaah I think I found it, datatable.acceptchanges means I dont need to redefine the dataview and the database is updated.

    Thanks for the help man, I think I have a much better understanding of whats going on now, cheers

  7. #7
    Loquacious User Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    24,591

    Re: Updating tables in a database, why does this single line of code make it work?

    This seems a bit off. I can see that you would be altering the datatables, but you don't update the database until you call UpdateTables. That doesn't happen until two lines later. Are you saying that, if you comment out the line in question, the call to UpdateTables doesn't happen, or that the call to the method happens, but the database doesn't update? If it is the former, that would certainly be surprising. If it is the latter, have you put a breakpoint in there and stepped through the code? If so, then do it again. Just prior to the call to Update, take a detailed look at the datatable using Shift+F9 (and whatever else you need to see the datatable itself). The key is the Rows. If there are thousands, you may have a hard time finding the right one, but while in Shift+F9 (whatever that form is called), you can add the .GetChanges call to the datatable, which will return a new datatable with JUST the rows for which the rowstate is not UnChanged. In that table, is your changed row there? If it is not, then the change didn't propagate from the dataview to the datatable, if it IS there, that would be interesting, also.
    My usual boring signature: Nothing

  8. #8
    Loquacious User Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    24,591

    Re: Updating tables in a database, why does this single line of code make it work?

    AcceptChanges is something you almost never want to call yourself. What that does is resets the rowstate from whatever it is to UnChanged, which means that the row will NOT update the database when you call dataadapter.update. That shouldn't be fixing anything.
    My usual boring signature: Nothing

  9. #9

    Thread Starter
    New Member
    Join Date
    Aug 2013
    Posts
    9

    Re: Updating tables in a database, why does this single line of code make it work?

    yeah if I comment out the row the the database update doesnt happen, the only reason I realised adding this made it work is because if I then open and close the form the update would happen, the only code that runs when I open the form is defining the dataview which is why I threw that bit of code in there and it seemed to work.

    the call to update tables happens but database isnt updated 1st time round. open and close the window and the database did update, the only extra thing the second attempt is the defining of the view. I became confused as to why redefining the view made it work which led me to post all this.

    I will try the things you mentioned but at the moment it seems that calling datatable.acceptchanges just before the function that runs dataadapter.update is allowing the database to be updated without me redefining the view. I will do a bit more investigation.

  10. #10
    PowerPoster dunfiddlin's Avatar
    Join Date
    Jun 2012
    Posts
    8,241

    Re: Updating tables in a database, why does this single line of code make it work?

    To be honest I'm a little bit confused by your whole approach to this. It's not immediately clear that you actually need a dataview at all. A dataview is designed to present a new view of data (a different sorting, a subset of values, etc.) It really doesn't have any place in selecting a single record for editing. Is there a reason for not simply binding the whole table to a DataGridView, allowing editing of all values at any time? If you'd prefer to keep the individual records approach then you should be binding each control individually to the relevant 'column' and using a selection control such as a combobox to synchronise the selections, eg.

    vb.net Code:
    1. ' set up combobbox to act as record synchroniser
    2.         ComboBox1.DisplayMember = "Names" ' a column which has unique values
    3.         ComboBox1.DataSource = DBSet.Tables(0) ' bind to the datatable
    4. ' set up textbox to provide editing
    5.         TextBox1.DataBindings.Add(New Binding("Text", DBSEt.Tables(0), "Address1"))

    This does mean that you must assign edited values direct to the datatable manually unfortunately but at least you know exactly what's where!
    As the 6-dimensional mathematics professor said to the brain surgeon, "It ain't Rocket Science!"

    Reviews: "dunfiddlin likes his DataTables" - jmcilhinney

    Please be aware that whilst I will read private messages (one day!) I am unlikely to reply to anything that does not contain offers of cash, fame or marriage!

  11. #11

    Thread Starter
    New Member
    Join Date
    Aug 2013
    Posts
    9

    Re: Updating tables in a database, why does this single line of code make it work?

    seems your right, the acceptchanges makes it not update the database. It only updates the database if the change is a new row! not if I edit a row which is why I assumed it was working. I will go back to the previous working version but now Im unsure how its working again. I guess the changes are not propogating back through from the view.

    My whole approach is just because Im new and its the 1st application Ive written. The problem may be that Im not using databindings at all, only read about them after I had mostly finished and have not played with them yet. Im just a novice but as I do some VBS scripting I got asked if I could solve another problem and said I could probably manage it, I have managed it but would like to completely understand whats going on.

    So really my problem now is to confirm that the changes to the dataview are propogating back before the update, and also why does redefining the view make them propogate back?

  12. #12

    Thread Starter
    New Member
    Join Date
    Aug 2013
    Posts
    9

    Re: Updating tables in a database, why does this single line of code make it work?

    I want the users to be able to search and edit single values, currently there are 6000 values and increasing weekly in one table so displaying them all and letting user scroll through is a bit unweildly.

    I decided on the view approach as its the 1st solution I came accross that seemed to work

  13. #13
    PowerPoster dunfiddlin's Avatar
    Join Date
    Jun 2012
    Posts
    8,241

    Re: Updating tables in a database, why does this single line of code make it work?

    letting user scroll through is a bit unweildly
    True, but then remembering a value in each of the 6000 records so that they can call it up for editing is no picnic either! The combobox does have several autocomplete options which makes it a reasonable compromise!
    As the 6-dimensional mathematics professor said to the brain surgeon, "It ain't Rocket Science!"

    Reviews: "dunfiddlin likes his DataTables" - jmcilhinney

    Please be aware that whilst I will read private messages (one day!) I am unlikely to reply to anything that does not contain offers of cash, fame or marriage!

  14. #14
    Loquacious User Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    24,591

    Re: Updating tables in a database, why does this single line of code make it work?

    Good that you aren't using databindings. Things can become such a total black box that you never can tell why the change you made had the result that it did. Leave binding till later.

    As it stands, what you really need to do is to take a few steps of debugging. In the simpler, 'out-of-the-box' approach, which you are taking (and which I prefer myself, for the most part), there need not be any mystery as to what is happening. The call to Update does something so simple that you could do it yourself, though it would take MANY lines of code to replace that single method call.

    The first thing to look at is to do the GetChanges call that I suggested. This will give you all the rows in the datatable that have a rowstate other than Unchanged. If the row you think you edited is not in that collection, then your edit hasn't hit the datatable yet (which seems likely), in which case, THAT is the problem rather than the update being the problem. After all, what the dataadapter does when you call update could be done by you with these steps:

    1) Call GetChanges to get a datatable with all the changed rows.
    2) For each row, examine the row state.
    a) If the row state is Added, then set up a command with an INSERT statement and run it with the values in the row.
    b) If the row state is Deleted (or is it Removed?), then set up a command with a DELETE statement and run it with whatever in the row is sufficient to identify the row (probably just the primary key).
    c) If the row state is Modified, then set up a command with an UPDATE statement and run it with the values in the row.
    3) Call AcceptChanges to set all the rowstates back to UnChanged.

    Only the final one can be difficult, and then it will only be difficult if you have multiple concurrent users such that you have to check whether some other app already modified the record in the database since the time that you filled the datatable. If you don't have to worry about that, then the update is simple.

    So, since none of this is a black box, exactly, the question you have to ask first is: Does the datatable have the changes I expect it to have? You can answer this with .GetChanges and examining the rows returned (all of which can be done with Shift+F9, and other means). Once you know the answer to that, then you know whether you are looking at a problem of updating the database from the datatable, or a problem of getting the changes into the datatable in the first place.
    My usual boring signature: Nothing

  15. #15

    Thread Starter
    New Member
    Join Date
    Aug 2013
    Posts
    9

    Re: Updating tables in a database, why does this single line of code make it work?

    Quote Originally Posted by dunfiddlin View Post
    True, but then remembering a value in each of the 6000 records so that they can call it up for editing is no picnic either! The combobox does have several autocomplete options which makes it a reasonable compromise!
    the users are prompted with which rows they have to change by the teams that wish them changed so they dont need to remember them, these rows are basically sets of rules for how another program treats incoming data when processing it, they shouldnt need to edit these rules that often once they are setup.

    Im probably only here for another hour or so so I will have a play before I get home and determine wether the the datable is being updated correctly from the dataview.

    Im still slightly confused again why redefining the view makes it work, will update tomorrow how my fiddling goes, thanks for the help so far guys.

    I am really enjoying this though think I missed my calling should of studied programming

  16. #16

    Thread Starter
    New Member
    Join Date
    Aug 2013
    Posts
    9

    Re: Updating tables in a database, why does this single line of code make it work?

    My code now seems to be working as expected with or without redefining the view, my problem seems to have gone away....

    This morning it wasnt working without this line and now it is, at least its doing what I expect it to do know, I cant think of any other changes I have made other than standardising variable names.

    I have been playing about with various bits of code in front of the update statement but I cant think why its now behaving as expected.

    Thanks for the help, much appreciated, at least this has made me try a few things and I think Ive gleaned a bit more understanding.

  17. #17
    PowerPoster dunfiddlin's Avatar
    Join Date
    Jun 2012
    Posts
    8,241

    Re: Updating tables in a database, why does this single line of code make it work?

    my problem seems to have gone away
    Beware the Ninja Bug!

    other than standardising variable names
    Don't knock it! A cure is often found in the most unlikely of things!
    As the 6-dimensional mathematics professor said to the brain surgeon, "It ain't Rocket Science!"

    Reviews: "dunfiddlin likes his DataTables" - jmcilhinney

    Please be aware that whilst I will read private messages (one day!) I am unlikely to reply to anything that does not contain offers of cash, fame or marriage!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.