Help wth getting primary keys from access

[Paulsburbon]

I have to work on a database that was already set up incorrectly. I have to fix the database and move the data. For example I have a "customer" table with many fields one of them being "notes" my boss would like the customers to have unlimited notes so I created a notes table and a cust_Notes table as a many to many table. I fill the customer table with the data and have it check for anything in the notes field. I then create a new notes row and copy the data over to the new row. I then create a new cust_note row but I have no idea how to get the access primary key for the would be new record in the "notes" table. I would like to get the data complete in the dataset before updating to the database. Any help is much appreciated!!!

Paul

 

[Paulsburbon]

Here is my code I'm trying to use.. I get a error that is "Object reference not set to an instance of an object"

    Private Function MoveNoteToDestination() As Boolean
        Try
            'Move note to new table
            For Each CustomerRow As Dataset.CustomersRow In Me.Dataset1.Customers
                If CustomerRow.IsNotesNull = False Then

                    'Create new row
                    Dim NewNotesRow As Dataset.tblNotesRow = Me.Dataset1.tblNotes.NewtblNotesRow
                    'Populate with data
                    With NewNotesRow
                        .memNoteText = CustomerRow.Notes
                        .dtmNoteCreatedDate = Today()
                        If CustomerRow.IsEmployeeIDNull = False Then
                            .lngNoteCreatedByID = CustomerRow.EmployeeID
                        Else
                            .lngNoteCreatedByID = 0
                        End If
                        .lngNoteTypeID = 1
                    End With

                    'Create New Row
                    Dim NewCustNoteRow As Dataset.tblCustNotesRow = Me.Dataset1.tblCustNotes.NewtblCustNotesRow
                    'Populate With Data
                    With NewCustNoteRow
                        .lngCustomerID = CustomerRow.idsCustomerID
                        '.lngNotesID = NotesID
                    End With

                    'Add new rows to dataset
                    Me.Dataset1.tblNotes.AddtblNotesRow(NewNotesRow)
                    Me.Dataset1.tblCustNotes.AddtblCustNotesRow(NewCustNoteRow)

                    'Update
                    Me.Validate()
                    Me.dgvCustomers.EndEdit()
                    Me.dgvNotes.EndEdit()
                    Me.dgvCustNotes.EndEdit()

                    Me.TblNotesTableAdapter1.Update(Me.Dataset1.tblNotes)

                    With NewCustNoteRow
                        .lngNotesID = NewNotesRow.idsNoteID
                    End With
                End If
            Next

            Me.Validate()
            Me.dgvCustomers.EndEdit()
            Me.dgvNotes.EndEdit()
            Me.dgvCustNotes.EndEdit()
            Me.TblCustNotesTableAdapter1.Update(Me.Dataset1.tblCustNotes)
            MessageBox.Show("Complete")

            Return True
        Catch ex As Exception
            MessageBox.Show(ex.Message.ToString)
            Return False
        End Try
    End Function

 

[Paulsburbon]

Here is the code I use to ge tthe primary key after the update:

Private Sub _adapter_RowUpdated(ByVal sender As Object, ByVal e As System.Data.OleDb.OleDbRowUpdatedEventArgs) Handles _adapter.RowUpdated
            AccessHelper.SetPrimaryKey(Me.Transaction, e)
        End Sub

Public Shared Sub SetPrimaryKey(ByVal trans As OleDbTransaction, _
                                    ByVal e As OleDbRowUpdatedEventArgs)
        If e.Status = UpdateStatus.Continue AndAlso _
           e.StatementType = StatementType.Insert Then
            ' If this is an INSERT operation...
            Dim pk = e.Row.Table.PrimaryKey
            ' and a primary key column exists...
            If pk IsNot Nothing AndAlso pk.Count = 1 Then
                Dim cmdGetIdentity As New OleDbCommand("SELECT @@IDENTITY", trans.Connection, trans)
                ' Execute the post-update query to fetch new @@Identity
                e.Row(pk(0)) = CInt(cmdGetIdentity.ExecuteScalar)
                e.Row.AcceptChanges()
            End If
        End If
    End Sub

 

[JuggaloBrotha]

I think you're going about this wrong. I would create a new table with 3 fields:
ID - AutoNumber - PK
CustID - Long Int - FK
Notes - varchar(2000) or whatever size you need, if it's an Access DB then use Memo

Then make an insert query that could insert a record in the new table to set the CustID and Notes fields from the Customer's table

Than after briefly comparing both tables, delete the notes field from the customer table as it's not used anymore.

The relationship is one-to-many between the customer table and the cust_notes table (the new one) obvisously the one field is the ID field in the customer table and the many is the CustID field in your new table.

 

[Paulsburbon]

Oh and thanks fo rgetting back to me!

That is just one of the types of notes I need to move over. There are order notes and like three other tables... Should I do it that way for all the tables? like an ordernote table and so on? I was hoping to find out how to do this anyways. I have to move other data in this table and want to find out how to do this.

 

[Paulsburbon]

Oh and the other data I need to move. The person who built this added a customer table and in the order table he put ship to information instead of adding another customer or adding more fields in the customer field.. So I have to take the shipto info in the order table and if it's not in the customer table I have to create it and then link it to the order table by the new customerID I just created... Same thing as the above problem.

 

[JuggaloBrotha]

It all depends on how the system is used really, but typically I'll have the customer/account info in one table, products in another, order's in a 3rd with a FK to the customer table and an FK to a junction table (2 columns, both make the PK) to get to the products table. That's just a simple setup and I don't mind having multiple tables and later having to join them together again to get the full data.

 

[cjard]

Seems logical; theres nothing that says an order has to be shipped to a customer. I'd have said the customer address is the billing address, not the shipping address. you could adopt the convention that "if the shipping address is empty, use the customer address"

 

[cjard]

I think youre going to struggle with this using access; it's not a good DB. If you upgrade it to SQLServer then you get enxtra options in your visual stuio for automatically retrieving the calculated ID number of the newly-inserted-customer, you can then insert a note using that new ID. It would basically go like:

Have a dataset that contains customer and note tables and a datarelation between them
Write a row in for the customer
Write a row in for the note
Save the customer row
The note row auto updates with the new customer ID
Save the note row


NB; i DIDNT have a linking table because the relationship of customer:notes is NOT Many:many, it is 1:many. You only need a link table to decompose a M:M into a 1:M
The note has an ID of the customer


If you chose to have one common sequence number for all your customers, order, etc, then it gets very easy to have a notes table that stores notes for everything. Otherwise you have a NoteType column that stores whether it is a Customer note, an Order note etc.. NoteType + EntityID (the ID from customer or order table) + NoteID are a compound primary key to Notes table and the relation is formed from CustomerID:EntityID or OrderID:EntityID