6 Replies - 5041 Views - Last Post: 20 November 2011 - 07:50 PM Rate Topic: -----

#1 nyzil  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 6
  • Joined: 14-November 11

Using TableAdapter to insert related data into MS Access Database

Posted 20 November 2011 - 09:50 AM

I was working through Beth Massi's article, "Using TableAdapters to Insert Related Data into an MS Access Database" here: http://blogs.msdn.co...s-database.aspx

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

Me.OrderDetailsTableAdapter.Update(newOrderDetailsRow)

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
        '<summary>
        'Retrieves the primary key autonumber values from Access
        '</summary>
        '<remarks></remarks>
        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)
                    e.Row.AcceptChanges()
                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
            Me.MyDataSet.OrderDetailsTable.Rows.Add(newOrderDetailsRow)
            ' Save the new row to the database
            Me.OrderDetailsTableAdapter.Update(newOrderDetailsRow) <<<<<<<<<<<<<<<<< Error Here
            Me.Close()
            Dim f As Form = New AddNewOrderDetailsForm
            f.Show()
        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.

This post has been edited by Atli: 20 November 2011 - 10:19 AM
Reason for edit:: Use [code] tags when posting code!


Is This A Good Question/Topic? 0
  • +

Replies To: Using TableAdapter to insert related data into MS Access Database

#2 DimitriV  Icon User is offline

  • They don't think it be like it is, but it do
  • member icon

Reputation: 584
  • View blog
  • Posts: 2,738
  • Joined: 24-July 11

Re: Using TableAdapter to insert related data into MS Access Database

Posted 20 November 2011 - 01:06 PM

I think it is because the new row you are generating and attempting to place into the database is empty and its throwing a dummy-spit because it needs a value in the OrderID column before it can continue.
Was This Post Helpful? 0
  • +
  • -

#3 nyzil  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 6
  • Joined: 14-November 11

Re: Using TableAdapter to insert related data into MS Access Database

Posted 20 November 2011 - 05:51 PM

Thanks for replying JimmyBo!

You say that this could be happening because it needs a value for the OrderID.
Well Jimmy, the OrderID is itself an AutoNumber field. Hence it should not throw this kind of an error message. The AutoNumber field should be incremented by itself and I don't need to fill that in I suppose.

What do you think friend ? What could be causing this error and how can I proceed ???
Waiting for your reply...
Was This Post Helpful? 0
  • +
  • -

#4 DimitriV  Icon User is offline

  • They don't think it be like it is, but it do
  • member icon

Reputation: 584
  • View blog
  • Posts: 2,738
  • Joined: 24-July 11

Re: Using TableAdapter to insert related data into MS Access Database

Posted 20 November 2011 - 07:19 PM

Glad I could sort of help! :)
If it is autonumber, still try to set the value of it in the statements.
Was This Post Helpful? 0
  • +
  • -

#5 nyzil  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 6
  • Joined: 14-November 11

Re: Using TableAdapter to insert related data into MS Access Database

Posted 20 November 2011 - 07:33 PM

View PostjimmyBo, on 20 November 2011 - 07:19 PM, said:

Glad I could sort of help! :)
If it is autonumber, still try to set the value of it in the statements.


And how do I do that friend ?? How do I set the value of OrderID in the statement ?? Can you please show me how ???
Would be very grateful to you...

Thanks a lot and waiting...
Was This Post Helpful? 0
  • +
  • -

#6 smohd  Icon User is offline

  • Critical Section
  • member icon


Reputation: 1819
  • View blog
  • Posts: 4,627
  • Joined: 14-March 10

Re: Using TableAdapter to insert related data into MS Access Database

Posted 20 November 2011 - 07:41 PM

View PostjimmyBo, on 21 November 2011 - 08:04 AM, said:

Glad I could sort of help! :)
If it is autonumber, still try to set the value of it in the statements.

Whaat?? What value should he set?

@OP: You said:

Quote

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

The error said about CustomerID field is empty, and do not talk about OrderID
Was This Post Helpful? 0
  • +
  • -

#7 nyzil  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 6
  • Joined: 14-November 11

Re: Using TableAdapter to insert related data into MS Access Database

Posted 20 November 2011 - 07:50 PM

View Postsmohd, on 20 November 2011 - 07:41 PM, said:

View PostjimmyBo, on 21 November 2011 - 08:04 AM, said:

Glad I could sort of help! :)
If it is autonumber, still try to set the value of it in the statements.

Whaat?? What value should he set?

@OP: You said:

Quote

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

The error said about CustomerID field is empty, and do not talk about OrderID


Thanks smohd for pointing out that typo... yes it is OrderDetails.CustomerID instead of OrderDetails.OrderID.

Please forgive for the mistake in typing.

I still need the statement to set as said by jimmyBo... Please help if you can...

This post has been edited by smohd: 20 November 2011 - 07:58 PM
Reason for edit:: removed duplicate posts joined

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1