Help with GetChanges/HasChanges DataTable Code 2

[Christineeve]

Below is an example of one of my update procedures. I'd like to capture the changes after update with something like, haschanges or getchanges.

Working with the code as it is, I'm sure it's pretty bad, can I call a procedure that will create a datatable with the updates? Then from there I'd like to write it to a table in my access database.

I've followed the examples here, but I can't seem to figure out how to make it worth with my procedure.

Would any experts be able to help me?

  Public Shared Sub UpdateBank()
        'This procedure executes both select and insert statement.

        Dim cmdUpdBank As New OleDbCommand
               Dim daUpdBank As New OleDbDataAdapter
        Dim dsUpdBank As New DataSet
        Dim dtUpdBank As New DataTable
        Dim m_rowPosition As Integer = 0
        Dim myBuilder As OleDbCommandBuilder = New OleDbCommandBuilder(daUpdBank)

        Try
            Using oConn As New OleDbConnection
                Dim DataSource As String = frmAhmed.gMyDataSource

                'create a connection string
                oConn.ConnectionString = ("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & DataSource & ";User ID=Admin;Password=;")
                oConn.Open()


                If oConn.State = ConnectionState.Open Then
                    Dim sSQL = "SELECT * FROM tblEmpBankInfo Where fk_EmpInfoID =" & "('" & Trim(frmAhmed.txtEmpID.Text) & "')"
                    'Where fk_EmpInfoID = " & Trim(CInt(frmAhmed.txtPsEmplyNo.Text))

                    'Select all from the database and determine if the txtEmplyNo exists.
                    cmdUpdBank = oConn.CreateCommand
                    ';cmdUpdBank1 = oConn.CreateCommand
                    cmdUpdBank.CommandText = sSQL
                    daUpdBank.SelectCommand = cmdUpdBank
                    daUpdBank.Fill(dsUpdBank, "UpdBank")
                    dtUpdBank = dsUpdBank.Tables("UpdBank")

                    myBuilder.GetUpdateCommand()
                    daUpdBank.UpdateCommand = myBuilder.GetUpdateCommand()

                    If dtUpdBank.Rows.Count > 0 Then
                        dtUpdBank.Rows(m_rowPosition)("empBankRoutingNo") = Trim(frmAhmed.txtBankRtNum.Text)
                        dtUpdBank.Rows(m_rowPosition)("empBankAccountNo") = (frmAhmed.txtBankAcctNum.Text)
                        dtUpdBank.Rows(m_rowPosition)("empDateMod") = Trim(frmAhmed.mtxtBankLstMod.Text)
                        daUpdBank.Update(dtUpdBank)
                        

                       'Here I'd like to call the
                      'the procedure to create the datatable
                       'that will write to my MS Access Db.
                        audBankChanges()


                    Else
                        ExecuteBank()
                    End If
                Else
                    MsgBox("Employee record cannot be found. Verify if data has been entered for this employee")
                    'MsgBox("connection state = " & oConn.State)

                End If
                MsgBox("Employee Record modified.", MsgBoxStyle.OkOnly, "Update Successful")
                oConn.Close()
                oConn.Dispose()
            End Using

        Catch ex As Exception
            MsgBox("Error: " & ex.Source & ": " & ex.Message, MsgBoxStyle.OkOnly, "Error!")
        End Try
    End Sub

 

[jebenson]

I'm not sure what you are trying to do with this:

'Here I'd like to call the
'the procedure to create the datatable
'that will write to my MS Access Db.

Your code looks like is already saving data to your Access DB. This line:

daUpdBank.Update(dtUpdBank)

should save any changes to your data to the database, via the dataadapter's UpdateCommand. No creation of a second datatable is needed to save the data.


I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day!

 

[Christineeve]

Jeb,
I need to create an audit table after that table is updated.

For example, say the manager updated the employee's bankaccount number. That change is updated to the bank table. However, for auditing purposes, I need to pull out the manager's ID, employee ID, the field name, the old value, and then the new value and insert it into the audit table.

I have the logistics all worked out. For some reason, I struggle with the getchanges procedure and taking those changes to the new table.

I'm assuming that getchanges will only report only the fields that were changed and thus I can put those changes only in my audit table and of course handle the empID, managerID, datestamp, originalvalue, newvalue, etc once I get the getchanges procedure working.

That procedure referenced in the code, saves OVER any data in that table without creating the legally required audit table. Beyond that,I need to determine what changes were made the employee data and pass them to an audit table.

I do appreciate your time and help.

 

[RiverGuy]

Try something like this:

                       'Here I'd like to call the
                      'the procedure to create the datatable
                       'that will write to my MS Access Db.

Dim AuditSql As String = "INSERT INTO UpdBankAudit (AuditUser, AuditDateTime, OldEmpBankAccountNo, NewEmpBankAccountNo) VALUES (?, ?, ?, ?)
cmdAudit As New OleDB.OleDBCommand(AuditSql, YourConnection)

For Each dr As DataRow In dtUpdBank.GetChanges()
            With cmdAudit
                .Parameters.Clear()
                .Parameters.AddWithValue("AuditUser", My.User.Name)
                .Parameters.AddWithValue("AuditDateTime", DateTime.Now)
                .Parameters.AddWithValue("OldEmpBankAccountNo", dr.Item("empBankAccountNo", DataRowVersion.Original))
                .Parameters.AddWithValue("NewEmpBankAccountNo", dr.Item("empBankAccountNo", DataRowVersion.Current))
                .ExecuteNonQuery()
            End With
Next

 

[jebenson]

Ah, I see.

OK, first you need to do the GetChanges call before the Update call, because the Update call also calls AcceptChanges. So after Update is called, GetChanges will return Nothing.

So, you can get the changes made like so:

Dim dtGetChanges As DataTable

dtGetChanges = dtUpdBank.GetChanges()

The datatable dtGetChanges now contains all the rows in dtUpdBank that were modified.


Second, I highly recommend using a transaction with this procedure, since you are doing 2 updates that depend upon one another. You don't want the employee data updated but the audit table update to fail, or vice versa. A transaction will only save the data if all updates, inserts, etc. are successful, and save nothing if any part fails.

So something like this:

Dim Trans As OleDbTransaction

Trans = oConn.BeginTransaction()


daUpdBank.UpdateCommand.Transaction = Trans

Dim daAudit As OleDbDataAdapter

daAudit = New OleDbDataAdapter(oConn, "Select * from AuditTable") 'note, use correct table name

daAudit.InsertCommand = New OleDbCommand(oConn, "Insert Into.....<use correct syntax for insert here>")

'code here using data in dtGetChanges and other data to insert into the audit table.


daAudit.InsertCommand.Transaction = Trans

Try
daUpdBank.Update(dtUpdBank)
daCopy.Update(dtGetChanges)

'no errors, so commit the transaction and save the data
Trans.Commit()

Catch ex As Error

'there was an error, so rollback the transaction and save nothing
If Trans IsNot Nothing Then
Trans.RollBack()
End If

End Try



Hope this helps. post again if you need clarification.

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day!

 

[Christineeve]

Thank you so much! I so appreciate when you respond to my questions because you take the time to teach me the concepts as well.

I will work on this later today and post my anticipated success!

 

[Christineeve]

Thank you Riverguy for the help as well.

 

[Christineeve]

Hi Experts,
I'm back again with this same issue. If you have time would you please take a look at my code and see if you can see where I've gone wrong?

I picked Jeb's code only because I thought I could merge it into my existing procedure. I would guess both are great suggestons. I do appreciate your help Riverguy.

Here is my procedure where I attempted to pull in the necessary code. I've spent many days researching sites to see just where I've gone wrong and I'm stuck. Here is my code:

 Public Shared Sub UpdateBank()
        'This procedure executes both select and insert statement.

        Dim cmdUpdBank As New OleDbCommand
        ' Dim cmdAudit As New OleDbCommand
        ' Dim cmdUpdBank1 As New OleDbCommand
        Dim daUpdBank As New OleDbDataAdapter
        Dim dsUpdBank As New DataSet
        Dim dtUpdBank As New DataTable
        Dim dtGetChanges As DataTable
        Dim daAudit As New OleDbDataAdapter
        Dim m_rowPosition As Integer = 0
        Dim myBuilder As OleDbCommandBuilder = New OleDbCommandBuilder(daUpdBank)
        Dim myAudBld As OleDbCommandBuilder = New OleDbCommandBuilder(daAudit)
        Dim bankRoutingNo As String
        Dim bankAccountNo As String
        Dim empDateMod As String
        Dim trans As OleDbTransaction

        Try
            Using oConn As New OleDbConnection
                Dim DataSource As String = frmAhmed.gMyDataSource

                'create a connection string
                oConn.ConnectionString = ("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & DataSource & ";User ID=Admin;Password=;")
                oConn.Open()

                If oConn.State = ConnectionState.Open Then
                    Dim sSQL = "SELECT * FROM tblEmpBankInfo Where fk_EmpInfoID =" & "('" & Trim(frmAhmed.txtEmpID.Text) & "')"
                    'Select all from the database and determine if the txtEmplyNo exists.
                    cmdUpdBank = oConn.CreateCommand
                    cmdUpdBank.CommandText = sSQL
                    daUpdBank.SelectCommand = cmdUpdBank
                    daUpdBank.Fill(dsUpdBank, "UpdBank")
                    dtUpdBank = dsUpdBank.Tables("UpdBank")

                    myBuilder.GetUpdateCommand()
                    daUpdBank.UpdateCommand = myBuilder.GetUpdateCommand()

                    If dtUpdBank.Rows.Count > 0 Then
                        dtUpdBank.Rows(m_rowPosition)("empBankRoutingNo") = Trim(frmAhmed.txtBankRtNum.Text)
                        dtUpdBank.Rows(m_rowPosition)("empBankAccountNo") = (frmAhmed.txtBankAcctNum.Text)
                        dtUpdBank.Rows(m_rowPosition)("empDateMod") = Trim(frmAhmed.mtxtBankLstMod.Text)

                        'Data Set that Contains the rows changed.
                        dtGetChanges = dtUpdBank.GetChanges()
                        trans = oConn.BeginTransaction()

                        daAudit = daUpdBank(oConn, "Select * from tblUpdBankAudit") 'Cannot be indexed because it has no default property.

                        daUpdBank.UpdateCommand.Transaction = trans
                        daUpdBank.InsertCommand.Transaction = trans

                        Try
                            daUpdBank.Update(dtUpdBank)
                            daCopy.Update(dtGetChanges) 'Error: daCopy is not declared.
                            trans.Commit()
                        Catch ex As Exception
                            If trans IsNot Nothing Then
                                trans.Rollback()
                            End If
                        End Try

                        daUpdBank.Update(dtUpdBank)
                    Else
                        ExecuteBank()
                    End If
                Else
                    MsgBox("Employee record cannot be found. Verify if data has been entered for this employee")
                End If

                MsgBox("Employee Record modified.", MsgBoxStyle.OkOnly, "Update Successful")

                Dim SaveStatus As Boolean
                SaveStatus = True
                frmAhmed.stiBank.Tag = SaveStatus
                frmAhmed.btnSave.Enabled = False

                oConn.Close()
                oConn.Dispose()
            End Using

        Catch ex As Exception
            MsgBox("Error: " & ex.Source & ": " & ex.Message, MsgBoxStyle.OkOnly, "Error!")
        End Try
    End Sub

When I did get the code to run, it returned all the rows including the column of data that changed.

So starting from here, would you look at the code for the daAudit. There is an error saying that it can't be indexed. And on daCopy, it says it's not declared and I'm not sure what to do with that.

Any help would be appreciated as my head hurts for the banging it on the wall .