Saving data to multibase tables.

[gwboolean]

I have been unable to find a way so save data the way I want to for a multi-base table. What I have is an employee table with a one-to-one relationship with several other tables, each of which contain specific employee information.

So, I have a query for a specific employee record.

#Region "Establish Connection and execute Query"
Try
MasterBaseCommand = New OleDbCommand("SELECT tblEmployee.chrEmployeeID, tblEmployee.chrFullName, tblEmployee.blnObsolete, tblEmployee.blnActive, " &
"lnkEmployeeInformation.chrJobTitle, lnkEmployeeInformation.chrDepartment, lnkEmployeeInformation.chrUserName,lnkEmployeeInformation.chrPassword " &
"FROM lnkEmployeeInformation INNER JOIN tblEmployee ON tblEmployee.chrEmployeeID = lnkEmployeeInformation.chrEmployeeID " &
"WHERE tblEmployee.chrEmployeeID = '" & _strEmployeeID & "'", MasterBaseConnection)
MasterBaseAdapter.SelectCommand = MasterBaseCommand
MasterBaseCommand = CType(MasterBaseCommand, OleDbCommand)
MasterBaseAdapter = New OleDbDataAdapter()
MasterBaseAdapter.SelectCommand = MasterBaseCommand
'Binding Source
MasterBaseBIndingSource.DataSource = MasterBaseTable
Dim MasterBaseCommandBuild As OleDbCommandBuilder = New OleDbCommandBuilder(MasterBaseAdapter)
MasterBaseAdapter.Fill(MasterBaseTable)
'Currency Manager
MasterBaseManager = DirectCast(BindingContext(MasterBaseTable), CurrencyManager)
ConfigureForm.SetStateEmployeeManager(_SetState, _strMasterBase, _strTable)
Catch ex As Exception
MessageBox.Show("Failed to load the table, " + _strTable + ControlChars.CrLf + ex.ToString, "Load Table Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
Me.Close()
mnuMain.Show()
End Try
#End Region

This works fine and provides me with a displayed record matching the criteria that can now be edited.

At this point I would like to save the edited record. and this is where a problem arises. I have figured out that I can save the data by saving the data to each individual table.

#Region "Establish Connection and execute Query"
Try
FileIDConn.MasterBaseOpen()
Dim FileIDTable As DataTable = New DataTable
FileIDCommand = New OleDbCommand("Select * FROM tblEmployee", FileIDConn.MasterBaseConnection)
FileIDAdapter = New OleDbDataAdapter()
FileIDAdapter.SelectCommand = FileIDCommand
Dim FileIDBuild As OleDbCommandBuilder = New OleDbCommandBuilder(FileIDAdapter)
FileIDAdapter.Fill(FileIDTable)
Dim FileDataRow As DataRow = FileIDTable.NewRow()
FileDataRow("chrEmployeeID") = CStr(_strEmployeeID)
FileDataRow("blnActive") = CBool(True)
FileIDTable.Rows.Add(FileDataRow)
FileIDAdapter.Update(FileIDTable)

FileIDCommand = New OleDbCommand("Select * FROM lnkEmployeeInformation", FileIDConn.MasterBaseConnection)
FileIDAdapter = New OleDbDataAdapter()
FileIDAdapter.SelectCommand = FileIDCommand
Dim EmployeeIDBuild As OleDbCommandBuilder = New OleDbCommandBuilder(FileIDAdapter)
FileIDAdapter.Fill(FileIDTable)
Dim EmployeeDataRow As DataRow = FileIDTable.NewRow()
EmployeeDataRow("chrEmployeeID") = CStr(_strEmployeeID)
FileIDTable.Rows.Add(EmployeeDataRow)
FileIDAdapter.Update(FileIDTable)


FileIDConn.MasterBaseClose()
FileIDCommand.Dispose()
FileIDAdapter.Dispose()
FileIDTable.Dispose()
Catch ex As Exception
MessageBox.Show("Failed to add Employee to Database." + ControlChars.CrLf + ex.Message + ControlChars.CrLf + ex.ToString, "SiTech, Save Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
Exit Sub
End Try
#End Region

However, this just seems to be wrong in everyway that I can think of to achieve what I am after. I have been trying to research a better process than the one above, but have been unable to find how to do that.

What is the appropriate way to save data to a parent and child table without having to deal with them separately?


I just noticed that the example I provided was for adding a new record. But I believe the same principle applies.



gwboolean

Continue reading...