Question Saving changes to 2 datasets but only 1 of them updates

[marlboro7313]

Hi, newbie here! :nevreness:

I've been try for days to see what I've done wrong but can't figure it out for the life of me. Here my problem.

I'm using 2 sets of DataAdapter and Datasets. When I click my "Apply" button, the program does a set of basic calculation and save the changes in the database. I did not create the database, I'm just trying to automate calculation that are done by hand in an Access database (which is used like an excel sheet). Anyway, that's besides the point. My problem is that only one table is updated out of the 2.

Here's my code:

---

Public Class frmMain
    'Declaring Public Variables
    Dim ConnString As String
    Dim DaysOfWeek() As String = {"L", "MA", "ME", "JE", "VE"} 'these are in french L=Monday, MA=Tuesday, etc...
    Dim BTorRT() As String = {"BT", "RT"}
    Dim dbTables() As String = {"BAT24HRES(BOITES_DETAILS_R_BRISSON)", "BAT24HRES(PRESENTOIR_METRO_DETAILS)", "BAT24HRES"}
    Dim intBoites, intPresentoirs, AmountDelivered As Integer


    Dim dsBoites, dsPresentoirs As New DataSet
    Dim daBoites, daPresentoirs As New OleDbDataAdapter
    Dim cn As New OleDbConnection

'************
'Other subs here
'************

Private Sub btnApply_Click(sender As Object, e As EventArgs) Handles btnApply.Click
        Dim openedConnectionString As Boolean = False
        Dim strSelectBoites, strSelectPresentoirs As String


        If cn.State = ConnectionState.Closed Then
            cn.ConnectionString = ConnString
            cn.Open()
        End If


        Cursor = Cursors.WaitCursor


        For Each Day In DaysOfWeek
            strSelectBoites = ""
            strSelectPresentoirs = ""
            Select Case Day
                Case "L"
                    If chkLundi.Checked Then
                        dsBoites = SumUpColumns(Day, dsBoites)
                        strSelectBoites = QueryBuilder(Day, intBoites, dbTables(0))


                        dsPresentoirs = SumUpColumns(Day, dsPresentoirs)
                        strSelectPresentoirs = QueryBuilder(Day, intPresentoirs, dbTables(1))
                    Else
                        GoTo MoveNext
                    End If


                Case "MA"
                    If chkMardi.Checked Then
                        dsBoites = SumUpColumns(Day, dsBoites)
                        strSelectBoites = QueryBuilder(Day, intBoites, dbTables(0))


                        dsPresentoirs = SumUpColumns(Day, dsPresentoirs)
                        strSelectPresentoirs = QueryBuilder(Day, intPresentoirs, dbTables(1))
                    Else
                        GoTo MoveNext
                    End If
                Case "ME"
                    If chkMercredi.Checked Then
                        dsBoites = SumUpColumns(Day, dsBoites)
                        strSelectBoites = QueryBuilder(Day, intBoites, dbTables(0))


                        dsPresentoirs = SumUpColumns(Day, dsPresentoirs)
                        strSelectPresentoirs = QueryBuilder(Day, intPresentoirs, dbTables(1))
                    Else
                        GoTo MoveNext
                    End If
                Case "JE"
                    If chkJeudi.Checked Then
                        dsBoites = SumUpColumns(Day, dsBoites)
                        strSelectBoites = QueryBuilder(Day, intBoites, dbTables(0))


                        dsPresentoirs = SumUpColumns(Day, dsPresentoirs)
                        strSelectPresentoirs = QueryBuilder(Day, intPresentoirs, dbTables(1))
                    Else
                        GoTo MoveNext
                    End If
                Case "VE"
                    If chkVendredi.Checked Then
                        dsBoites = SumUpColumns(Day, dsBoites)
                        strSelectBoites = QueryBuilder(Day, intBoites, dbTables(0))


                        dsPresentoirs = SumUpColumns(Day, dsPresentoirs)
                        strSelectPresentoirs = QueryBuilder(Day, intPresentoirs, dbTables(1))
                    Else
                        GoTo MoveNext
                    End If
            End Select


            Dim cbBoites As New OleDbCommandBuilder(daBoites)
            Dim cbPresentoirs As New OleDbCommandBuilder(daPresentoirs)


            Dim commandBoites As New OleDbCommand
            Dim commandPresentoirs As New OleDbCommand


            commandBoites = New OleDbCommand(strSelectBoites, cn)
            commandPresentoirs = New OleDbCommand(strSelectPresentoirs, cn)


            daBoites.SelectCommand = commandBoites
            daPresentoirs.SelectCommand = commandPresentoirs


            daBoites.TableMappings.Clear()
            daBoites.TableMappings.Add(dbTables(0), "BOITES")


            With daBoites.TableMappings(0).ColumnMappings
                .Add("COMPTEUR", "COMPTEUR")


                For i = 1 To intBoites
                    .Add("BT" & i & Day, "BT" & i & Day)
                    .Add("RT" & i & Day, "RT" & i & Day)
                Next


                .Add("QT?" & Day, "QT?" & Day)
                .Add("RT" & Day, "RT" & Day)
            End With


            daPresentoirs.TableMappings.Clear()
            daPresentoirs.TableMappings.Add(dbTables(1), "PRESENTOIRS")


            With daPresentoirs.TableMappings(0).ColumnMappings
                .Add("COMPTEUR", "COMPTEUR")


                For i = 1 To intPresentoirs
                    .Add("BT" & i & Day, "BT" & i & Day)
                    .Add("RT" & i & Day, "RT" & i & Day)
                Next


                .Add("QT?" & Day, "QT?" & Day)
                .Add("RT" & Day, "RT" & Day)
            End With




            'Make sure field names are bracketed
            cbBoites.QuotePrefix = " ["
            cbBoites.QuoteSuffix = "] "
            cbPresentoirs.QuotePrefix = " ["
            cbPresentoirs.QuoteSuffix = "] "


            'Save changes
            daBoites.UpdateCommand = cbBoites.GetUpdateCommand <====================== This line updates the table
            daPresentoirs.UpdateCommand = cbPresentoirs.GetUpdateCommand <====================== This one doesn't




            'cbBoites.Dispose()
            'commandBoites.Dispose()


            'cbPresentoirs.Dispose()
            'commandPresentoirs.Dispose()


MoveNext:
        Next


        Cursor = Cursors.Default
        MsgBox("Op?ration termin?e", vbOKOnly, "Calculs et Enregistrement")


        cn.Close()


        DataGridView1.DataSource = dsPresentoirs.Tables(0)
    End Sub

'***********************
'Other functions and subs here
'***********************

---

Do any of you see what I'm doing wrong ?

Thank you so much (in advance) for your help!!!

 

[jmcilhinney]

Firstly, I have fixed the code formatting in your post. Please always wrap code snippets in formatting tags, i.e.

[xcode=vb]your cocde here[/xcode]

As you can see, it makes the code eminently more readable.

As for the issue, let's start with the fact that nothing in that code updates any database. There's no code to actually save the changes from the DataTable(s) back to the database. Your assertion that "This line updates the table" is false. In fact, both of these lines:

daBoites.UpdateCommand = cbBoites.GetUpdateCommand <====================== This line updates the table
daPresentoirs.UpdateCommand = cbPresentoirs.GetUpdateCommand <====================== This one doesn't

are completely useless and should be removed. There's absolutely no point ever call GetUpdateCommand on a command builder unless you intend to make some sort of change to the command it returns, e.g. enrol it in a transaction. The way you save changes from a DataTable to a database is by calling Update on a data adapter. If you have created a command builder for that data adapter then it will automatically use the generated commands. There is no need for you to assign them directly.

So, there's no call to Update in that code so that code doesn't save anything. If you actually do call Update somewhere then that is the code that you should have been showing us. The call to Update will return a number that represents the number of database records affected by the operation. The way to determine whether data is saved or not is check whether that number is zero or not.

What you will find is that calling Update will implicitly call AcceptChanges on the DataTable or DataSet that you're saving changes from. It's quite possible that you are accepting all the changes in your DataSet on your first save so there is nothing left to save the second time. In that case, you can either pass a DataTable to Update instead of a DataSet or else set the AcceptChangesDuringUpdate property of the first data adapter at least to False and then either rely on the second Update to call AcceptChanges or call it manually.

 

[marlboro7313]

Thank you very much for your help jmcilhinney. it's noted for the tags for the code... I was wondering how to do it

I've added the following lines in my code:

MsgBox("Number of row updated: " & daBoites.Update(dsBoites, "BOITES"), vbOKOnly, "daBoites.Update")
MsgBox("Number of row updated: " & daPresentoirs.Update(dsPresentoirs, "PRESENTOIRS"), vbOKOnly, "daPresentoirs.Update")

Now both my tables got updated. I'll make further test with what you told me to do. I took a sample I found on the internet and changed it for whatever I was trying to do. Thanks again for your quick reply and especially for you precious help!