Using TableAdapter to insert related data into MS Access Database

Posted by Swampmonster under VB.NET on 11/20/2011 | Points: 10 | Views : 3163 | Status : [Member] | Replies : 0
I was working through Beth Massi's article, "Using TableAdapters to Insert Related Data into an MS Access Database" here:

And I configured the TableAdapter exactly as shown by Beth, yet I am getting an error here:

The Error says: "You must enter a value in the 'OrderDetails.CustomerID' field."

I am using this code for inserting a related record:

Namespace MyDataSetTableAdapters

Public Class AccessIDHelper
'Retrieves the primary key autonumber values from Access
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 CustomerID = e.Row.Table.PrimaryKey

' and a primary key column exists...
If CustomerID IsNot Nothing AndAlso CustomerID.Count = 1 Then

Dim cmdGetIdentity As New OleDbCommand("SELECT @@IDENTITY", trans.Connection, trans)

' Execute the post-update query to fetch new @@Identity
e.Row(CustomerID(0)) = CInt(cmdGetIdentity.ExecuteScalar)
End If
End If
End Sub
End Class

Partial Public Class CustomerTableAdapter

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

and this for this code for inserting a new record:

Private Sub cmdSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdSave.Click
If MsgBox("Yes or No", MsgBoxStyle.YesNo) = MsgBoxResult.Yes Then

Dim newOrderDetailsRow As MyDataSet.OrderDetailsRow
newOrderDetailsRow = MyDataSet.OrderDetailsTable.NewOrderDetailsRow()

newOrderDetailsRow.OrderCategory = txtOrderCategory.Text
'newOrderDetailsRow.Discounts = Val(ComboDiscounts.SelectedValue)
newOrderDetailsRow.Region = txtRegion.Text
----------------- etc etc
' Save the new row to the database
Me.OrderDetailsTableAdapter.Update(newOrderDetailsRow) <<<<<<<<<<<<<<<<< Error Here
Dim f As Form = New AddNewOrderDetailsForm
End If
End Sub

I am not able to insert and move on as I am getting the error marked above.

What could be possibly wrong with the above code and why is this giving an error ?? It should work as Beth explains.

Please help.


(No response found.)

Login to post response