none
VB.NET & Access Database Autonumber RRS feed

  • Question

  • Hello,

     

    I am having a similar problem as quite a few others subscribers of this forum: adding a child record just after inserting the parent.

    I have a simple form with a master record and a datagrid with the child records. When I add a new parent (AddNew) I can see that the primary key filled out by VB is wrong (the underlying database is Access), i.e. I have 2 records in the database, autonumber IDs 15 & 16, so tne next ID should be 17. The key proposed by VB is 3, so clicking Save throws an exception. The odd thing though, is that if I click on AddNew again without restarting the app, I get the correct ID (17) and I can add Parent and child.

     

    To me this means that there must be a simple way of getting the correct ID the first time as well, without having to go back to the database after the parent insert in order to get the correct ID for adding children. Does anyone know how to achieve this ?

     

    Sunday, February 13, 2011 10:29 PM

Answers

  • I am using a simple but effective work-around, it will do the job for now since the application is single user. Any better solutions are more then welcome.

     

    Workaround:

    - Parent table Clients, child table Contacts

     Dim lvClientUpdates() As DataRow = Me.AppDBDataSet.t_clients.Select("", "", DataViewRowState.Added Or DataViewRowState.ModifiedCurrent)
     Dim lvClientContactsUpdates() As DataRow = Me.AppDBDataSet.t_clientContacts.Select("", "", DataViewRowState.Added Or DataViewRowState.ModifiedCurrent)

                'First parent, then children
                Me.ClientsTableAdapter.Update(lvClientUpdates)

                'Retreive the Max PK of clients table - That's the fk to be used for the contacts inserts

                'defined a simple query (SELECT MAX(pk))

                Dim lvIntClientID As Integer= Me.ClientsTableAdapter.MaxClient
                'go into ClientContactUpdates and set client IDs for all ClientContacts
                For i As Integer = 0 To lvClientContactsUpdates.Length - 1
                    lvClientContactsUpdates(i)("fk_client") = lvIntClientID
                Next

                Me.ClientContactsTableAdapter.Update(lvClientContactsUpdates)

    Tuesday, February 15, 2011 9:53 PM

All replies

  • I am using a simple but effective work-around, it will do the job for now since the application is single user. Any better solutions are more then welcome.

     

    Workaround:

    - Parent table Clients, child table Contacts

     Dim lvClientUpdates() As DataRow = Me.AppDBDataSet.t_clients.Select("", "", DataViewRowState.Added Or DataViewRowState.ModifiedCurrent)
     Dim lvClientContactsUpdates() As DataRow = Me.AppDBDataSet.t_clientContacts.Select("", "", DataViewRowState.Added Or DataViewRowState.ModifiedCurrent)

                'First parent, then children
                Me.ClientsTableAdapter.Update(lvClientUpdates)

                'Retreive the Max PK of clients table - That's the fk to be used for the contacts inserts

                'defined a simple query (SELECT MAX(pk))

                Dim lvIntClientID As Integer= Me.ClientsTableAdapter.MaxClient
                'go into ClientContactUpdates and set client IDs for all ClientContacts
                For i As Integer = 0 To lvClientContactsUpdates.Length - 1
                    lvClientContactsUpdates(i)("fk_client") = lvIntClientID
                Next

                Me.ClientContactsTableAdapter.Update(lvClientContactsUpdates)

    Tuesday, February 15, 2011 9:53 PM
  • Happy to see the simple work around, i'm learning...
    Wednesday, February 16, 2011 9:00 AM