Results 1 to 13 of 13

Thread: Datatable row doesn't get updated

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Apr 2021
    Posts
    66

    Datatable row doesn't get updated

    I have a Datatable that uses an MS Access database table.
    In my program I select a row in the datatable, perform some procedures checking the name of the field and changing the text.
    Then in the end I want to update the columns in the row but it doesn't happen. What am I doing wrong?

    Code:
       Private Sub BtnCompare_Click(sender As Object, e As EventArgs) Handles BtnCompare.Click
            'and now for the hard part. compare the filenames
            Dim DocName As String
            Dim olddocname As String
            Dim newdocname As String
            Dim sFinalName As String
            Dim sGetDatum As String
            Dim sDISnummer As String
            Dim sXPPost As String
            Dim sExt As String
            Dim i, j As Integer
            Dim dt4 As DataTable = GetMyTable("SELECT * FROM Documents;")
            Dim row As DataRow
            For Each row In dt4.Rows
                DocName = row.Item("Document Name")
                --> doing some procedures here.........
    
                'lets update the table
                row.Item("Document Name") = sFinalName
                row.Item("DISnummer") = sDISnummer
                row.Item("XPostnummer") = sXPPost
                newdocname = ""
                sFinalName = ""
            Next
     
           UpdateListview()
    End Sub

  2. #2
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    106,733

    Re: Datatable row doesn't get updated

    It does happen because you're doing it right here:
    vb.net Code:
    1. row.Item("Document Name") = sFinalName
    2. row.Item("DISnummer") = sDISnummer
    3. row.Item("XPostnummer") = sXPPost
    If you had debugged your code like you should have then you would see that.

    Maybe what you actually mean is that you want to save those changes back to the database. Of course that's not going to happen because you have no code to do so. The DataTable is not the database table. Changes to one do not affect the other. The DataTable is just a local copy of whatever data you retrieved. You presumably have code in that GetMyTable method that calls Fill on a data adapter to retrieve the data. You need to call Update on the same data adapter to save the changes. I suggest that you follow the CodeBank link in my signature below and check out my Retrieving & Saving Data thread for examples.

  3. #3

    Thread Starter
    Lively Member
    Join Date
    Apr 2021
    Posts
    66

    Re: Datatable row doesn't get updated

    Thank you for the explanation. I am not using a Datagridview. Don't know if that matters.
    Do I need to run an Update query like this one:

    Code:
    Dim ObjConnection As New OleDbConnection()
           Dim i As Integer
    
            viewrecordsfrombackup.DataGridView1.SelectedRows(i).Cells("teamcolor").Value = ComboBox1.Text
           ObjConnection.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & browserec.txtpath.Text & "'"
           Dim ObjCommand As New OleDbCommand()
           ObjCommand.Connection = ObjConnection
           For i = viewrecordsfrombackup.DataGridView1.SelectedRows.Count - 1 To 0 Step -1
    
               ObjCommand.CommandText = "UPDATE table1 SET teamcolor = '" & viewrecordsfrombackup.DataGridView1.SelectedRows(i).Cells("teamcolor").Value & "' where ID like '" & viewrecordsfrombackup.DataGridView1.SelectedRows(i).Cells("ID").Value & " '"
               ObjConnection.Open()
               ObjCommand.ExecuteNonQuery()
               ObjConnection.Close()
    
               Next
    MsgBox("Record(s) updated successfully!")

  4. #4
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    106,733

    Re: Datatable row doesn't get updated

    I wasn't joking in my previous post when I told you what you needed to do so I'm not sure why we're discussing something else.

  5. #5
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    33,354

    Re: Datatable row doesn't get updated

    Yes and no...
    Yes, you need an update command...
    No... not quite like that...
    First you should be using parameters...
    Secondly... you shouldn't be opening and closing your connection like that... that's a total waste. If you're making something and you know you need milk, eggs, and butter, do you open the fridge, grab the milk, close the door, put the milk down, open the fridge again, grab the eggs, close the door, put the eggs down, then open the door again, grab the butter, then close the door again... no... not likely. Odds are, you open the fridge, grab the milk, eggs, and butter, then close the door. Same with database connections. You should open it once... do all your database actions... then and only then when you're don should you close it. Now, this doesn't mean you should leave it open the entire time your app is open, that would be like leaving the fridge open as soon as you get home. No, this means you open it, do what you need to do, and get out.

    Code:
    Dim ObjConnection As New OleDbConnection()
           Dim i As Integer
    
            viewrecordsfrombackup.DataGridView1.SelectedRows(i).Cells("teamcolor").Value = ComboBox1.Text
           ObjConnection.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & browserec.txtpath.Text & "'"
           Dim ObjCommand As New OleDbCommand()
           ObjCommand.Connection = ObjConnection
               ObjConnection.Open()
           For i = viewrecordsfrombackup.DataGridView1.SelectedRows.Count - 1 To 0 Step -1
    
               ObjCommand.CommandText = "UPDATE table1 SET teamcolor = '" & viewrecordsfrombackup.DataGridView1.SelectedRows(i).Cells("teamcolor").Value & "' where ID like '" & viewrecordsfrombackup.DataGridView1.SelectedRows(i).Cells("ID").Value & " '"
               ObjCommand.ExecuteNonQuery()
    
               Next
               ObjConnection.Close()
    MsgBox("Record(s) updated successfully!")
    And with parameters, it looks something like this (shooting from the hip, no editor here
    Code:
    Dim ObjConnection As New OleDbConnection()
           Dim i As Integer
    
            viewrecordsfrombackup.DataGridView1.SelectedRows(i).Cells("teamcolor").Value = ComboBox1.Text
           ObjConnection.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & browserec.txtpath.Text & "'"
           Dim ObjCommand As New OleDbCommand()
           ObjCommand.Connection = ObjConnection
           ObjConnection.Open()
           ObjCommand.CommandText = "UPDATE table1 SET teamcolor = ? where ID = ?"
           ObjCommand.Parameters.AddWithValue("@TeamColor", "Color") ' This is just to seed the parameter
           ObjCommand.Parameter.AddWithValue("@ID", "ID") ' This is just to seed the parameter
           For i = viewrecordsfrombackup.DataGridView1.SelectedRows.Count - 1 To 0 Step -1
    
                ObjCommand.Parameters("@TeamColor").Value = viewrecordsfrombackup.DataGridView1.SelectedRows(i).Cells("teamcolor").Value
                ObjCommand.Parameters("@ID").Value = viewrecordsfrombackup.DataGridView1.SelectedRows(i).Cells("ID").Value
               ObjCommand.ExecuteNonQuery()
    
               Next
               ObjConnection.Close()
    MsgBox("Record(s) updated successfully!")

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  6. #6

    Thread Starter
    Lively Member
    Join Date
    Apr 2021
    Posts
    66

    Re: Datatable row doesn't get updated

    Got it to work.

    Code:
               SQLstr = "UPDATE Documents SET DISnummer = @disnummer, XPostnummer = @xpostnummer, [Document Name] = @doc_name WHERE OldName = @oldname"
                Command = New OleDbCommand(SQLstr, con)
                With Command
                    .CommandText = SQLstr
                    .Parameters.AddWithValue("@disnummer", sDISnummer)
                    .Parameters.AddWithValue("@xpostnummer", sXPPost)
                    .Parameters.AddWithValue("@doc_name", sFinalName)
                    .Parameters.AddWithValue("@oldname", row.Item("Document Name"))
                    .Connection = con
                    .ExecuteNonQuery()
                End With

  7. #7
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    4,676

    Re: Datatable row doesn't get updated

    How is the data loaded into the Datatable? If you have used a DataAdapter or TableAdapter then the simplest way is to use the adapters Update method. If your also using a bindingsource then you'll need to call EndEdit on the Bindingsource.

  8. #8

    Thread Starter
    Lively Member
    Join Date
    Apr 2021
    Posts
    66

    Re: Datatable row doesn't get updated

    Code:
    Public Function GetMyTable(strSQL As String) As DataTable
            ' Create new DataTable instance.
            Dim table As New DataTable
            Dim cmd As New OleDbCommand(strSQL, con)
            Using dr As OleDbDataReader = cmd.ExecuteReader
                table.Load(dr)
            End Using
    
            Return table
        End Function

  9. #9
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    4,676

    Re: Datatable row doesn't get updated

    If you want to use a Datatable then why are you creating a DataReader? Create a Dataadapter.

    Here is a simple example,

    Code:
    Imports System.Data.SqlClient
    
    Public Class DataAdapterDemo
        'I always store my connectionstrings in the project Setting
        Private con As New SqlConnection(My.Settings.BooksDBConnectionString)
        Private da As New SqlDataAdapter("Select * From Books ORDER BY BookName", con)
        Private dt As New DataTable
    
        'I prefer using a BindingSource with the DataGridView because it has many built in function for working/manipulating the data
        Private bs As New BindingSource
    
        'If your only working with one table then you can use a CommandBuilder to create the SQL Add/Update/Delete commands
        Private cmdBuilder As New SqlCommandBuilder(da)
    
        Private Sub DataAdapterDemo_Load(sender As Object, e As EventArgs) Handles Me.Load
    
            Try
                con.Open()
    
                da.Fill(dt)
                bs.DataSource = dt
                Me.DataGridView1.DataSource = bs
    
                'Bind a Textbox to the bs Bindingsource
                'Me.BookIdTextBox.DataBindings.Add("Text", bs, "BookId")
            Catch ex As Exception
                MessageBox.Show(ex.ToString)
            End Try
        End Sub
    
        Private Sub SaveButton_Click(sender As Object, e As EventArgs) Handles SaveButton.Click
            Try
                bs.EndEdit()
                da.Update(dt)
            Catch ex As Exception
                MessageBox.Show(ex.ToString)
            End Try
        End Sub
    End Class

  10. #10

    Thread Starter
    Lively Member
    Join Date
    Apr 2021
    Posts
    66

    Re: Datatable row doesn't get updated

    Quote Originally Posted by wes4dbt View Post
    If you want to use a Datatable then why are you creating a DataReader? Create a Dataadapter.
    Because I am new at VB.NET. Used to work with VBA.


    'I prefer using a BindingSource with the DataGridView because it has many built in function for working/manipulating the data
    I work mostly with a Listview but also use Datatable to read rows in my code without displaying

    'If your only working with one table then you can use a CommandBuilder to create the SQL Add/Update/Delete commands
    I use more then one table from my database

  11. #11
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    33,354

    Re: Datatable row doesn't get updated

    Quote Originally Posted by clausowitz View Post
    Because I am new at VB.NET. Used to work with VBA.


    'I prefer using a BindingSource with the DataGridView because it has many built in function for working/manipulating the data
    I work mostly with a Listview but also use Datatable to read rows in my code without displaying

    'If your only working with one table then you can use a CommandBuilder to create the SQL Add/Update/Delete commands
    I use more then one table from my database
    Honestly, you're probably making this harder on yourself than you really need to be. Especially when it comes to the displaying of the data. It doesn't take much to grab a datatable, bind it to a DataBindingSource object, and then set that as the datasource of the grid. Instantly you've bound your grid. All in a couple lines of code. Want to hide some data? Fine, simply hide the columns you don't want to show. Odds are it's still easier than shuttling data around into and out of datatables.

    Using the CommandBuilder can be a bit tricky at times, depending on the complexity of the data you're trying to deal with... BUT... still, doesn't negate the fact that you can set the .UpdateCommand, .DeleteCommand, .InsertCommand, of the Adaptor and then use that to update the database by passing the datatable back in when calling the .Update function.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  12. #12
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    4,676

    Re: Datatable row doesn't get updated

    'If your only working with one table then you can use a CommandBuilder to create the SQL Add/Update/Delete commands
    I use more then one table from my database
    Does your SELECT statement return data from more than one table? You can use as many tables as you want from your database and still use a commandbuilder if the DataAdapter SELECT command only return data from one table.

    Code:
    'Commandbuilder works with this
    Private da As New SqlDataAdapter("Select field1, field2 from TableA", con)
    
    'CommandBuilder does NOT work with this
    Private da As New SqlDataAdapter("Select tableA.field1, tableA.field2, tableB.field3 from tableA Inner Join tableB On tableA.field1=tableB.field1", con)

  13. #13
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    106,733

    Re: Datatable row doesn't get updated

    Quote Originally Posted by clausowitz View Post
    Because I am new at VB.NET. Used to work with VBA.
    I told you to use a data adapter in post #2 and I also told you where to find examples of doing so, but you seem to have completely ignored that. Being new in VB.NET should mean that you're open to learning how to do things the right way, but you seem determined not to do so.
    Quote Originally Posted by clausowitz View Post
    I work mostly with a Listview
    You almost certainly shouldn't. Many people use the ListView in Details view as though it was a grid control when they should be using an actual grid control instead. If you're not a view other than Details and you're not using grouping then you should be using a DataGridView and binding it to your DataTable via a BindingSource. You won't lose anything but you will gain much.
    Quote Originally Posted by clausowitz View Post
    I use more then one table from my database
    It's not about whether you are using multiple tables but, rather, whether you are using multiple tables in a single query. If your query involves a single table and it includes that table's primary key column then you can use a command builder to generate the action commands to save changes back to the database. If you are joining multiple tables or don't retrieve a table's primary key then you have to create your own action commands. The CodeBank thread that I have already directed you to provides examples of both. I wrote that thread in the CodeBank to help people like you learn how to do this stuff without the rest of us having to write the same answers and code over and over, yet here we are again, providing the same answers and code.

Tags for this Thread

Posting Permissions

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



Click Here to Expand Forum to Full Width