I obtained data from a remote database.
Two tables DEPT and EMP
I created a DATATABLE DTEMP
I created a table within a dataset (now) as DTDEPT
I originally planned to use dataset with tow tables, but I was unable to
use a parent child relation as I was obtaining error after error so I went
to the DATAVIEW for the children of which seems to be workiing nicely.
However, once I have updated data locally I need to update the DB with the
changes. This...I do not know how to complete. I need to update both tables with
changes....
Remote Table - DEPT with Local Table - now.DTDept
Remote Table - EMP with Local Table - DTEmp
Note that my local changes with regards to DTEmp seem to stay as if I move through
the parent grid...dgMain the changes are still inplace with respect to child grid
=================================================================================
Dims
Dim oOleDbConnection As OleDbConnection
Dim sConnString As String = _
"Provider=OraOLEDB.Oracle;" & _
"Data Source=Main;" & _
"User ID=scott;" & _
"Password=tiger"
Dim ds = New DataSet
Dim deptRow As DataRow
Dim empRow As DataRow
Dim custOrderRel As DataRelation
Dim DTEmp As New DataTable("DTEmp")
Dim DTDept As DataTable
Dim now = New DataSet
Dim DV As New DataView
'Update Child data
Dim CrntRowInd As Integer
Dim ColEmpNo As String
Dim ColEmpNm As String
Dim emp2Row As DataRow
Dim strEMP, strDEPT, strDTEmp, strDTDept As String
=================================================================================
btnData_Click
oOleDbConnection = New OleDb.OleDbConnection(sConnString)
oOleDbConnection.Open()
If ConnectionState.Open Then
TextBox1.Text = "Connection is made to Main"
TextBox1.BackColor = Color.Green
Else
TextBox1.Text = "Unable to make connection"
TextBox1.BackColor = Color.Red
End If
Button2.Visible = True
Button2.Enabled = True
strDTEmp = "Select * from EMP"
strDTDept = "Select * from DEPT"
Dim daDTEmp = New OleDb.OleDbDataAdapter(strDTEmp, oOleDbConnection)
Dim daDTDept = New OleDb.OleDbDataAdapter(strDTDept, oOleDbConnection)
daDTEmp.Fill(DTEmp) 'with datatable be as new....
daDTDept.Fill(now, "DTDept")
oOleDbConnection.Close()
End Sub
=================================================================================
Button2_Click --populate dgMain with dataset table DTDept / title / colors
btnData.Visible = False
dgMain.SetDataBinding(now, "DTDept")
TextBox1.BackColor = Color.Violet
TextBox1.Text = "You are now working with local data."
dgMain.CaptionText = "DEPT table"
dgMain.CaptionForeColor = Color.Black
dgMain.CaptionBackColor = Color.LightBlue
dgDeptEmp.CaptionText = "EMP table"
dgDeptEmp.CaptionForeColor = Color.Black
dgDeptEmp.CaptionBackColor = Color.LightBlue
dgMain.BackColor = Color.LightBlue
dgMain.SelectionBackColor = Color.Red
dgDeptEmp.SelectionBackColor = Color.Red
=================================================================================
dgMain_CurrentCellChanged - populate child grid based on DeptNo
Dim DeptNo As Integer
Dim john As String
DeptNo = dgMain.CurrentRowIndex
john = dgMain.Item(DeptNo, 0)
txtdgMain.Text = john
txtItem.Text = john
DV.Table = DTEmp
DV.RowFilter = "DEPTNO ='" & john & "'"
Dim row As DataRowView
Dim intCounter As Integer
For intCounter = 0 To DV.Count - 1
row = DV(intCounter)
'txtCount.Text = DV.Count.ToString 'count of child records
Next
dgDeptEmp.DataSource = DV 'populate child datagrid
dgDeptEmp.BackColor = Color.LightBlue 'gives white / blue lines
End Sub
=================================================================================
dgDeptEmp_CurrentCellChanged --ondgDeptEmp populate textboxes for editing
CrntRowInd = dgDeptEmp.CurrentRowIndex
ColEmpNo = dgDeptEmp.Item(CrntRowInd, 0)
ColEmpNm = dgDeptEmp.Item(CrntRowInd, 1)
txtEmpNo.Text = ColEmpNo 'this is instantiated on grid load
txtEmpNm.Text = ColEmpNm 'this is instantiated on grid load
End Sub
=================================================================================
btn_Save_Locale_Change --update column value based on text box value
dgDeptEmp.Item(CrntRowInd, 1) = txtEmpNm.Text
ColEmpNm = dgDeptEmp.Item(CrntRowInd, 1)
txtEname_RO.Text = ColEmpNm 'this updates per change of txtEmpNm
emp2Row.EndEdit()
=================================================================================