Member Avatar for ehrendreich
ehrendreich 0 Newbie Poster

I am rather new to vb and could use some help. I am using the latest version of vb and am working with a database to add some records. Simply, I would like to have a form that adds a new record into the tblContact table then enter a few fields into the tblContactLog table. The tblContactLog table is tied to the tblContact table with a key called ContactID.

tblContact     tblContactLog


ContactID      ContactID
FirstName      ContactPurpose
LastName       ContactNotes

I realize that I have to create the contact in the tblContact table first. Then I need to read that ContactID from the current record. After I read that value from the current record I need to open the tblContactPurpose table and insert a new record with the ContactID from previous operation.

I have an existing dataset called Clubhouse_Management_SystemDataSet that contains both tables. I have 2 table adapters. One is called TblContactTableAdapter & the other is TblContactLogTableAdapter. For each there is also a binding source. One is TblContactBindingSource and the other TblContactLogBindingSource.

When the form loads I have it filling the table adapters with

'TODO: This line of code loads data into the 'Clubhouse_Management_SystemDataSet.tblContactLog' table. You can move, or remove it, as needed.
Me.TblContactLogTableAdapter.Fill(Me.Clubhouse_Management_SystemDataSet.tblContactLog)
'TODO: This line of code loads data into the        'Clubhouse_Management_SystemDataSet.tblContact' table. You can move, or remove it, as needed.
Me.TblContactTableAdapter.Fill(Me.Clubhouse_Management_SystemDataSet.tblContact)
TblContactBindingSource.AddNew()

I have a button (btnAdd) that does

    TblContactLogBindingSource.EndEdit()
    TblContactTableAdapter.Update(Clubhouse_Management_SystemDataSet.tblContact)
    TblContactLogBindingSource.AddNew()

This is where I get stuck I need to take the value from the txtContactPurpose.text and the txtContactNotes.text along with the ContactID that was created in the first step and populate the tblContactLog table.

My actual full code at this point:

Public Class frmAddContactLog

    Private _ContactID As Integer = 0

    Private Sub TblContactLogBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        Me.Validate()
        Me.TblContactLogBindingSource.EndEdit()
        Me.TableAdapterManager.UpdateAll(Me.Clubhouse_Management_SystemDataSet)

    End Sub

    Private Sub frmAddContactLog_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'TODO: This line of code loads data into the 'Clubhouse_Management_SystemDataSet.tblContactLog' table. You can move, or remove it, as needed.
        Me.TblContactLogTableAdapter.Fill(Me.Clubhouse_Management_SystemDataSet.tblContactLog)
        'TODO: This line of code loads data into the 'Clubhouse_Management_SystemDataSet.tblContact' table. You can move, or remove it, as needed.
        Me.TblContactTableAdapter.Fill(Me.Clubhouse_Management_SystemDataSet.tblContact)
        TblContactBindingSource.AddNew()


    End Sub

    Private Sub TblContactBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        Me.Validate()
        Me.TblContactBindingSource.EndEdit()
        Me.TableAdapterManager.UpdateAll(Me.Clubhouse_Management_SystemDataSet)

    End Sub

    Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
        TblContactLogBindingSource.EndEdit()
        TblContactTableAdapter.Update(Clubhouse_Management_SystemDataSet.tblContact)
        TblContactLogBindingSource.AddNew()

    End Sub
End Class
Edited by Nick Evan because: Fixed formatting
  • 1 Contributor
  • 0 Replies
  • 81 Views

Dani AI

Generated

— two reliable patterns will solve this cleanly: let the typed DataSet/TableAdapterManager do the work, or perform the parent insert explicitly and then use the returned identity for the child rows.

If using the typed DataSet approach, add the parent row and then add child rows in the in-memory DataTables (link them with a DataRelation or by assigning the child foreign-key to the parent DataRow). Do not call AcceptChanges() on the DataSet. Call EndEdit on any BindingSources and then run the single update that updates both tables in the correct order. Configure the parent TableAdapter so the InsertCommand returns the generated identity (for example by returning SCOPE_IDENTITY()) and refreshes the inserted row; the TableAdapterManager will then propagate the real ContactID into the child rows before their inserts run.

If you prefer explicit control, insert the parent row with a command that returns the new identity (ExecuteScalar on the same open connection), then use that value when inserting child rows. Wrap both operations in a transaction so the identity is guaranteed to belong to your insert.

Troubleshooting checklist: confirm the DB column is an IDENTITY, confirm the adapter InsertCommand is returning the identity and that UpdatedRowSource is set to return it, avoid calling AcceptChanges before Update, and inspect DataRow.RowState in the debugger to ensure rows are still "Added" when Update runs.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.