none
Can't get a dataset to update in vb8 RRS feed

  • Question

  • I have a form in vb8 that is set up as follows:

     

    I have a table in my Access database calledUsers that has a name and a password (encrypted).

    Using the toolbar I created aBindingSource with a name of UsersBindingSource and a  dataSource ofUsers

    Using the toolbar I created aDataset called  with a Name and a DatasetName called Users_Dataset

    Using the toolbar I created atableAdapter called UsersTableAdapter

    I added two textboxes on the form and changed theirDatabindings…Text property to the corresponding field in the Database

     

    I am trying to figure out how to now update the database when I click Update. What the program needs to do first is encrypt the Password before saving it.

     

    I wrote the code in the TextBox2.Lostfocus event to encrypt the data a place it back into the TestBox2 field. I can’t figure out the code to update. The only samples I find on the net refer to DataGridView controls (which work) but I don’t want to use them. How can I get the database to accept these changes as well as changes to radio buttons or comboboxes?

     

     

    Ronald S. Levy, M.D.

    Professor of Anesthesiology

    Professor of Anaotmy and Neurosciences

    University of Texas Medical Branch at Galveston

     

    President

    Levy and Associates

    Friday, April 16, 2010 8:09 PM

Answers

  • Could you post some of the relevant code? Like the code in your Update button click event and the code in your TextBox Lostfocus event. If you already have code in your button click event to run the TableAdapter.Update() command, I suspect that the problem is uncommitted proposed changes (a common issue). Please take a look at my blog post about this and see if it helps.

    http://geek-goddess-bonnie.blogspot.com/2009/09/fun-with-datasets.html

    If that fixes the problem, you don't have to post any relevant code here!!  ;0) Just let me know if that solved the issue or not.


    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    Saturday, April 17, 2010 6:11 PM
  • AcceptChanges is virtually never needed. It does not do what some folks seem to think it does--like taking too much Vitamin C.

    I don't see an Update method call. This posts changes to the database. It's applied to a TableAdapter or DataAdapter which contains DataTables to be updated. The TA or DA contains the needed UpdateCommand, InsertCommand and DeleteCommand SQL that passes the changes on to the DBMS engine. These can be easily created in the IDE using drag-drop or programmatically (at least the DA can be).


    __________________________________________________________________
    William Vaughn
    Mentor, Consultant, Trainer, MVP
    http://betav.com
    http://betav.com/blog/billva
    http://www.hitchhikerguides.net

    “Hitchhiker’s Guide to Visual Studio and SQL Server (7th Edition)”

    Please click the Mark as Answer button if a post solves your problem!

    Tuesday, April 20, 2010 9:46 PM
    Moderator
  • Hi RonLevy,

    You are still calling AcceptChanges on your DataRow/DataTable/DataSet before you call Update on your DataAdapter or TableAdapter.

    CommitProposedChanges(Ds_ICUData) -->
    ds.Tables(nTable).Rows(nRow).AcceptChanges()
    UsersTableAdapter.Update(Ds_ICUData)

    When you call ds.Tables(nTable).Rows(nRow).EndEdit(),  the current value will be replaced with proposed value(actually new value), if you call AcceptChanges, it will commit all changes made which means that if row that was marked as Modified, it will now be Unchanged. When you call Update, it will not recognize that the row has changes that need to be submitted to the database. The same applies for inserted and deleted rows as well.

    If you want to determine whether the current value of a column and the proposed value are the same, I think you need to do like this:

        row.BeginEdit
        row(1) = “new value”
        If row.HasVersion(datarowversion.Proposed) Then
           If row(1, DataRowVersion.Current) Is row(1, DataRowversion.Proposed) Then
              Console.WriteLine("The original and the proposed are the same")
              row.CancelEdit()
              Exit Sub
           Else
              row.
    EndEdit()
           End If
        Else
           Console.WriteLine("No new values proposed")
        End If
     

    Best regards,
    Alex Liang


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Thursday, April 22, 2010 3:41 AM
    Moderator

All replies

  • Could you post some of the relevant code? Like the code in your Update button click event and the code in your TextBox Lostfocus event. If you already have code in your button click event to run the TableAdapter.Update() command, I suspect that the problem is uncommitted proposed changes (a common issue). Please take a look at my blog post about this and see if it helps.

    http://geek-goddess-bonnie.blogspot.com/2009/09/fun-with-datasets.html

    If that fixes the problem, you don't have to post any relevant code here!!  ;0) Just let me know if that solved the issue or not.


    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    Saturday, April 17, 2010 6:11 PM
  • I tried the code you posted but it didn't work either. (I code in VB so I think I converted it correctly from C#).

    I have a dataset called ds_ICUData, A UserBindingSource (with a datasource of ds_ICUData and a datamember of Users [the table name] from my Access database) and a UsersTableAdapter. Each of the Textbox fields has the DataBindingField set to UserBindingSource.[field Name].

    There is no special code in the Lost_focus event. My Update button code looks like this:

    ' encrypt the password here and assign to encryptedtext
    txtPassword.Text = encryptedText
    CommitProposedChanges(Ds_ICUData)

     

    If Ds_ICUData.HasChanges Then
         Ds_ICUData.AcceptChanges()
    End If

     

     

    Private Sub CommitProposedChanges(ByVal ds As DataSet)
       Dim nTable As Integer, nRow As Integer
       For nTable = 0 To ds.Tables.Count - 1
          For nRow = 0 To ds.Tables(nTable).Rows.Count - 1
             If ds.Tables(nTable).Rows(nRow).HasVersion(DataRowVersion.Proposed) Then
                ds.Tables(nTable).Rows(nRow).EndEdit()
                ds.Tables(nTable).Rows(nRow).AcceptChanges()
             End If
          Next nRow
       Next nTable

     

     

    End Sub

    I'm sure I'm missing something simple.

    Monday, April 19, 2010 8:08 PM
  • AcceptChanges is virtually never needed. It does not do what some folks seem to think it does--like taking too much Vitamin C.

    I don't see an Update method call. This posts changes to the database. It's applied to a TableAdapter or DataAdapter which contains DataTables to be updated. The TA or DA contains the needed UpdateCommand, InsertCommand and DeleteCommand SQL that passes the changes on to the DBMS engine. These can be easily created in the IDE using drag-drop or programmatically (at least the DA can be).


    __________________________________________________________________
    William Vaughn
    Mentor, Consultant, Trainer, MVP
    http://betav.com
    http://betav.com/blog/billva
    http://www.hitchhikerguides.net

    “Hitchhiker’s Guide to Visual Studio and SQL Server (7th Edition)”

    Please click the Mark as Answer button if a post solves your problem!

    Tuesday, April 20, 2010 9:46 PM
    Moderator
  • Hi RonLevy,

    "You are calling AcceptChanges on your DataRow/DataTable/DataSet before you call Update on your DataAdapter or TableAdapter. AcceptChanges will commit all changes made since the data was loaded or since AcceptChanges was last called. This means that if you had a row that was marked as Modified, it will now be Unchanged. When you call Update, it will not recognize that the row has changes that need to be submitted to the database. The same applies for inserted and deleted rows as well. For more information, see the AcceptChanges documentation: http://msdn2.microsoft.com/en-us/library/system.data.dataset.acceptchanges.aspx. This applies to all database backends and application types (ASP.NET, Windows Forms, etc)." And just as William said, it seems that you do not call an update method to make changes to the database. Please have a check.

    Best regards,
    Alex Liang


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Wednesday, April 21, 2010 1:47 PM
    Moderator
  • So I tried putting in the command

    Me

     

     

    .UsersTableAdapter.Update(ds_ICUData)

    before the CommitProposedChanges call and it still did not work. Maybe I'm not understanding how to make the call or where to put it. I also don't see how to drag-drop a DataAdapter in the IDE. Also, is there a difference between using the DA method versus the TA method if I'm only using a single table in the dataset?

    Ron

    Wednesday, April 21, 2010 4:27 PM
  • It should go *after* the CommitProposedChanges.
    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    Wednesday, April 21, 2010 4:31 PM
  • I tried that also and still doesn't save. This is very frustrating. Could someone take the code I have and show me how the Update button click event should look like.

    to summarize. I drag-dropped the following onto the form.

    The connnectionString "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\ICUData.mdb" works.
    DataSet called ds_ICUData
    BindingSource called UsersBindingSource with a Datasource of ds_ICUData and DataMember "Users" (the name of the table)
    TableAdapter called UsersTableAdapter
    Two textboxes txtUser and txtPassword each with the DataBindings set to User and Password fields from the table.

    All the fields fill correctly.

    The pertinent code:

    Imports System.Data.Oledb
    Public Class frmUsers
    Private Sub frmUsers_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Me.UsersTableAdapter.Fill(Me.Ds_ICUData.Users)
    End Sub

    Private Sub btnUpdateUser(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdateUser.Click
      
    txtPassword.Text = encryptedText
       CommitProposedChanges(Ds_ICUData)
       UsersTableAdapter.Update(Ds_ICUData)
      
    If Ds_ICUData.HasChanges Then
          Ds_ICUData.AcceptChanges()
      
    End If
    End Sub

     

     
    Private Sub CommitProposedChanges(ByVal ds As DataSet)
       Dim nTable As Integer, nRow As Integer
       For nTable = 0 To ds.Tables.Count - 1
          For nRow = 0 To ds.Tables(nTable).Rows.Count - 1
             If ds.Tables(nTable).Rows(nRow).HasVersion(DataRowVersion.Proposed) Then
                ds.Tables(nTable).Rows(nRow).EndEdit()
                ds.Tables(nTable).Rows(nRow).AcceptChanges()
             End If
          Next nRow
       Next nTable 
    End Sub
    End Class

     

    Wednesday, April 21, 2010 6:06 PM
  • Hi RonLevy,

    You are still calling AcceptChanges on your DataRow/DataTable/DataSet before you call Update on your DataAdapter or TableAdapter.

    CommitProposedChanges(Ds_ICUData) -->
    ds.Tables(nTable).Rows(nRow).AcceptChanges()
    UsersTableAdapter.Update(Ds_ICUData)

    When you call ds.Tables(nTable).Rows(nRow).EndEdit(),  the current value will be replaced with proposed value(actually new value), if you call AcceptChanges, it will commit all changes made which means that if row that was marked as Modified, it will now be Unchanged. When you call Update, it will not recognize that the row has changes that need to be submitted to the database. The same applies for inserted and deleted rows as well.

    If you want to determine whether the current value of a column and the proposed value are the same, I think you need to do like this:

        row.BeginEdit
        row(1) = “new value”
        If row.HasVersion(datarowversion.Proposed) Then
           If row(1, DataRowVersion.Current) Is row(1, DataRowversion.Proposed) Then
              Console.WriteLine("The original and the proposed are the same")
              row.CancelEdit()
              Exit Sub
           Else
              row.
    EndEdit()
           End If
        Else
           Console.WriteLine("No new values proposed")
        End If
     

    Best regards,
    Alex Liang


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Thursday, April 22, 2010 3:41 AM
    Moderator
  • OH ... sorry!!!   I did not even notice that you had put an .AcceptChanges() in the CommitProposedChanges() method!!!  That was NOT in the code I posted on my blog. That's your whole problem!
    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    Friday, April 23, 2010 3:01 PM