Datatable row doesn't get updated

[clausowitz]

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?

   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

[jmcilhinney]

It does happen because you're doing it right here:

row.Item("Document Name") = sFinalName
row.Item("DISnummer") = sDISnummer
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.

[clausowitz]

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:

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!")

[jmcilhinney]

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.

[techgnome]

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.

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

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

[clausowitz]

Got it to work.

           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

[wes4dbt]

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.

[clausowitz]

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

[wes4dbt]

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

Here is a simple example,

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

[clausowitz]

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

[techgnome]

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

[wes4dbt]

'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.

'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)

[jmcilhinney]

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.

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.

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.