Hi all,
I have a database with a number of parent child relations which i am displaying using a datagrid. Great stuff, works fine.
The problem is when i try to modify it or add a new row to the data base.
I am trying to use the commandbuilder to contruct the appropriate OLE command but it doesn't appear to be working.
I've included the snip of code diong the bulk of the work.
Private Sub OpenToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles OpenToolStripMenuItem.Click
OpenFileDialog1.Filter() = "MS Access files (*.mdb)|*.mdb"
OpenFileDialog1.ShowDialog()
file = OpenFileDialog1.FileName
Dim MyConnectionStr As String = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + file
Dim adapter As New OleDb.OleDbDataAdapter("SELECT * FROM Project", MyConnectionStr)
Dim adapter2 As New OleDb.OleDbDataAdapter("SELECT * FROM Plan", MyConnectionStr)
Dim adapter3 As New OleDb.OleDbDataAdapter("SELECT * FROM Observation", MyConnectionStr)
Dim adapter4 As New OleDb.OleDbDataAdapter("SELECT * FROM Image_", MyConnectionStr)
'Fill adapters with Data
adapter.Fill(data_set, "Project")
adapter2.Fill(data_set, "Plan")
adapter3.Fill(data_set, "Observation")
adapter4.Fill(data_set, "Image_")
'Create relations and bind
Dim rel_pro_plan As New DataRelation("plans", data_set.Tables("Project").Columns("ID"), data_set.Tables("Plan").Columns("ProjectID"))
data_set.Relations.Add(rel_pro_plan)
dg1.SetDataBinding(data_set, "Project")
dg1.ReadOnly = False
dg2.SetDataBinding(data_set, "Project.plans")
Dim rel_plan_obs As New DataRelation("Obs", data_set.Tables("Plan").Columns("ID"), data_set.Tables("Observation").Columns("PlanID"))
data_set.Relations.Add(rel_plan_obs)
Dim rel_obs_image As New DataRelation("image", data_set.Tables("Observation").Columns("ID"), data_set.Tables("Image_").Columns("ObservationID"))
data_set.Relations.Add(rel_obs_image)
'release database for editing
adapter.Dispose()
adapter2.Dispose()
adapter3.Dispose()
adapter4.Dispose()
Private Sub SaveToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles SaveToolStripMenuItem.Click
data_set.EnforceConstraints = False
If data_set.HasChanges() Then
Dim command_builder As New OleDbCommandBuilder
Dim MyConnectionStr As String = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + file
Dim adapter As New OleDb.OleDbDataAdapter("SELECT * FROM Project", MyConnectionStr)
Dim adapter2 As New OleDb.OleDbDataAdapter("SELECT * FROM Plan", MyConnectionStr)
Dim adapter3 As New OleDb.OleDbDataAdapter("SELECT * FROM Observation", MyConnectionStr)
Dim adapter4 As New OleDb.OleDbDataAdapter("SELECT * FROM Image_", MyConnectionStr)
Dim conn As New OleDbConnection(MyConnectionStr)
' Map Table to Contacts.
adapter4.TableMappings.Add("Table", "Image_")
command_builder = New OleDb.OleDbCommandBuilder(adapter4)
adapter4.ContinueUpdateOnError = True
adapter4.Update(data_set)
command_builder = New OleDb.OleDbCommandBuilder(adapter3)
adapter3.Update(data_set)
command_builder = New OleDb.OleDbCommandBuilder(adapter2)
adapter2.Update(data_set)
command_builder = New OleDb.OleDbCommandBuilder(adapter)
adapter.Update(data_set)
End If
End Sub
I can't get the database to update. Clearly i am doing something wrong here.
Any help would be much appreciated.