|
-
Jan 4th, 2020, 04:29 PM
#21
Re: Update a single row in database
Ok,
So if the parent and child dataadapter all only process one database table then the link that jmc provided you in post #6 will show you how to add new rows to parent/child datatable that have an Autonumber field.
Here is jmc's code,
Code:
Imports System.Data.OleDb
Public Class Form1
#Region " Fields "
''' <summary>
''' Contains the tables and the relation between them.
''' </summary>
Private data As New DataSet
''' <summary>
''' Connects to the database.
''' </summary>
Private connection As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\ParentChild.mdb;User Id=admin;Password=;")
''' <summary>
''' Retrieve data from the database and save changes back to the database.
''' </summary>
''' <remarks>
''' The WithEvents keyword is required so that the adapters can be included in the Handles clause of their RowUpdated event handlers.
''' </remarks>
Private WithEvents parentDataAdapter As New OleDbDataAdapter("SELECT * FROM Parent",
connection) With {.MissingSchemaAction = MissingSchemaAction.AddWithKey}
Private WithEvents childDataAdapter As New OleDbDataAdapter("SELECT * FROM Child",
connection) With {.MissingSchemaAction = MissingSchemaAction.AddWithKey}
''' <summary>
''' Generate the InsertCommand, UpdateCommand and DeleteCommand for the adapters when their Update method is called.
''' </summary>
Private parentCommandBuilder As New OleDbCommandBuilder(Me.parentDataAdapter) With {.QuotePrefix = "[",
.QuoteSuffix = "]"}
Private childCommandBuilder As New OleDbCommandBuilder(Me.childDataAdapter) With {.QuotePrefix = "[",
.QuoteSuffix = "]"}
#End Region 'Fields
#Region " Event Handlers "
Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
Try
LoadSchema()
ConfigureAutoIncrements()
ConfigureRelation()
LoadData()
BindData()
Catch ex As Exception
MessageBox.Show(ex.ToString(), "Error")
End Try
End Sub
''' <summary>
''' Raised after a parent row is saved to the database.
''' </summary>
''' <param name="sender">
''' The adapter that saved the row.
''' </param>
''' <param name="e">
''' The data for the event.
''' </param>
''' <remarks>
''' This event handler is used to retrieve an auto-generated ID from the database after a row is inserted and update the corresponding row in the local data set.
''' </remarks>
Private Sub parentDataAdapter_RowUpdated(sender As Object, e As OleDbRowUpdatedEventArgs) Handles parentDataAdapter.RowUpdated
'We are only interested in new records.
If e.StatementType = StatementType.Insert Then
'Get the last ID auto-generated by the database.
Using command As New OleDbCommand("SELECT @@IDENTITY", Me.connection)
'Update the ID of the local row.
e.Row("ParentID") = CInt(command.ExecuteScalar())
End Using
End If
End Sub
''' <summary>
''' Raised after a child row is saved to the database.
''' </summary>
''' <param name="sender">
''' The adapter that saved the row.
''' </param>
''' <param name="e">
''' The data for the event.
''' </param>
''' <remarks>
''' This event handler is used to retrieve an auto-generated ID from the database after a row is inserted and update the corresponding row in the local data set.
''' </remarks>
Private Sub childDataAdapter_RowUpdated(sender As Object, e As OleDbRowUpdatedEventArgs) Handles childDataAdapter.RowUpdated
'We are only interested in new records.
If e.StatementType = StatementType.Insert Then
'Get the last ID auto-generated by the database.
Using command As New OleDbCommand("SELECT @@IDENTITY", Me.connection)
'Update the ID of the local row.
e.Row("ChildID") = CInt(command.ExecuteScalar())
End Using
End If
End Sub
''' <summary>
''' Raised when the user clicks the Save button on the parent tool bar.
''' </summary>
''' <param name="sender">
''' The Save button.
''' </param>
''' <param name="e">
''' The data for the event.
''' </param>
''' <remarks>
''' Saves all parent and child data.
''' </remarks>
Private Sub SaveToolStripButton_Click(sender As System.Object, e As System.EventArgs) Handles SaveToolStripButton.Click
If Me.Validate() Then
Me.parentBindingSource.EndEdit()
Me.childBindingSource.EndEdit()
Me.parentDataAdapter.Update(Me.data, "Parent")
Me.childDataAdapter.Update(Me.data, "Child")
End If
End Sub
#End Region 'Event Handlers
#Region " Methods "
''' <summary>
''' Build the data set schema without actually retrieving any data.
''' </summary>
Private Sub LoadSchema()
Me.parentDataAdapter.FillSchema(Me.data, SchemaType.Source, "Parent")
Me.childDataAdapter.FillSchema(Me.data, SchemaType.Source, "Child")
End Sub
''' <summary>
''' Configure the auto-increment properties of the ID columns of both tables.
''' </summary>
Private Sub ConfigureAutoIncrements()
ConfigureAutoIncrement(Me.data.Tables("Parent").Columns("ParentID"))
ConfigureAutoIncrement(Me.data.Tables("Child").Columns("ChildID"))
End Sub
''' <summary>
''' Configure the auto-increment properties of a column.
''' </summary>
''' <remarks>
''' The column will be configured to generate local IDs that will never class with those generated by the database.
''' Database IDs will start at 1 and increase sequentially while local IDs will start at 0 and decrease sequentially.
''' </remarks>
Private Sub ConfigureAutoIncrement(column As DataColumn)
With column
'The column should automatically generate local IDs.
.AutoIncrement = True
'The first local ID value generated should be 0.
'The database IDs will start at 1, so local IDs will not clash.
.AutoIncrementSeed = 0
'The local IDs should each be 1 less than the previous.
'The database IDs will each be 1 more than the previous, so local IDs will not clash.
.AutoIncrementStep = -1
End With
End Sub
''' <summary>
''' Configure the foreign key relation between the ParentID columns in the Parent and Child tables.
''' </summary>
''' <remarks>
''' The relation is set to cascade updates, which means that when the local ID in a parent
''' row is replaced with the database ID, all related child rows will be updated as well.
''' </remarks>
Private Sub ConfigureRelation()
Me.data.Relations.Add("ParentChild",
Me.data.Tables("Parent").Columns("ParentID"),
Me.data.Tables("Child").Columns("ParentID"),
True).ChildKeyConstraint.UpdateRule = Rule.Cascade
End Sub
''' <summary>
''' Retrieve the data from the database.
''' </summary>
''' <remarks></remarks>
Private Sub LoadData()
Me.parentDataAdapter.Fill(Me.data, "Parent")
Me.childDataAdapter.Fill(Me.data, "Child")
End Sub
''' <summary>
''' Displays the data in the grids.
''' </summary>
''' <remarks>
''' The child grid is bound such that it will only display the records related to the selected parent record.
''' </remarks>
Private Sub BindData()
Me.parentBindingSource.DataSource = Me.data.Tables("Parent")
Me.childBindingSource.DataMember = "ParentChild"
Me.childBindingSource.DataSource = Me.parentBindingSource
Me.parentDataGridView.DataSource = Me.parentBindingSource
Me.childDataGridView.DataSource = Me.childBindingSource
End Sub
#End Region 'Methods
End Class
It's an excellent demo, what part are you having trouble with?
Tags for this Thread
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|