none
How to Update MS Access Databases in Batch RRS feed

  • Question

  • Hello everyone

    I am using VB 2008 & MS Access Databases to create a WinForm Application.

    Also a Strongly Typed Dataset & Bound Controls.

    My application form is of Master-Details type where the Primary Key in Master Table is AutoNumber Column (Orders & OrderDetails Tables).

    I have defined both Relation & Foreign key Constraint on Tables in Dataset as well as in the Database.

    Now when i try to Update a record using TableAdapterManager, I get OleDbException saying

    You cannot add or change a record because a related record is required in table 'Orders'.

    I am unable to save even a single record so please tell me what do i need to do to overcome this problem.


    Monday, April 18, 2011 8:24 PM

All replies

  • Hello SANDEEP,

     

    Welcome!

    The error is telling you that the underlying database has a foreign key relationship between your two tables that isn't being met during your insert, please check this. 

    Please see this related thread, though it's old thread and the link in it is disabled, I think it will be helpful.

    I hope this can help you.

     

    Good day,


    Jackie Sun [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, April 20, 2011 8:04 AM
    Moderator
  • Hello Jackie

    I know that the Foreign key value is a cause of error.

    With the same application if i drop a AutoNumber Column (Primary Key Column) from Orders Table in the Database & Add a column with just a Number DataType & write SQL query to get new Order No (Select Max(OrderId) + 1 From Orders) & use same for Foreign Key column of Related Table (OrderDetails) i wont't get Any Error.

    But this approch could give me Duplicate Value Error if Multiple Instances (Multi User) of Application is running tring to add new record at the same time, so the use of AutoNumber column in the Database is must.


    Wednesday, April 20, 2011 9:45 AM
  • Again i found one way to overcome this situation is to immidiate Update of Record as soon as it is changed or Added before opening or adding Another.

    For this to work i used AutoNumber Column only in the Master Table, & changed the way to update tables that is by calling a Master table's Update first then get a last generated id for PK column and change FK column value of Details table with the generated value and then update the Details Table.

    This works fine if only One Record is added and update is performed.

    what if there are multiple records added & i want to update them in batch

    while updating i would only have a last id generated for last record being inserted then how would i update Related tables

    I found the batch updating is easily possible with SQL Server Databases however same techniques with access database gives error so there must have some way to achive this with Access DB too.

    SANDEEP

    Wednesday, April 20, 2011 10:03 AM
  • Hi Sandeep,

    Try to add the RowUpdated handler to "Order" adapter. In the handler, you can retrieve back the real OrderID number from access database and assign that to the local "Order" table. 

    Once the local "Order" table updated, your local version "OrderDetail" table's OrderID will be updated immediately (I assume you has relation between these two tables in your dataset already.)

    All of those processes are occurred per record, so it can update more records in one go.

    At the point the "OrderDetail" adpater update database, local copy "OrderDetail" has correct value in OrderID already. So won't cause that problem anymore.

    Below is code adding handler 

    orderAdp = New DataSet1TableAdapters.OrdersTableAdapter
    AddHandler orderAdp.Adapter.RowUpdated, _
            New OleDbRowUpdatedEventHandler(AddressOf OnOrdersRowUpdated)
    

    Below is code in the handler

     

    Private Sub OnOrdersRowUpdated( _
    		ByVal sender As Object, ByVal e As OleDbRowUpdatedEventArgs) 
    	If e.StatementType = StatementType.Insert Then
    		' Retrieve the Autonumber and update local OrderID column in Order Table 
    		Dim cmdNewID As New OleDbCommand("SELECT @@IDENTITY", orderAdp.Adapter.SelectCommand.Connection)
    		e.Row("OrderID") = CInt(cmdNewID.ExecuteScalar)
    		e.Status = UpdateStatus.SkipCurrentRow
    	End If
    End Sub
    
    Regards

     

    Wednesday, April 20, 2011 2:32 PM
  • Hello

    I understand the Code in the Handler however please clear Code for Adding Handler

    as i am using Bound Controls & Typed Dataset, a TableAdapter for table in dataset is already present in the Component Tray may i use this TableAdapter or i have to declare new one as shown in you code?

    also i have tried your code sample but it showing error in hadler telling 'RowUpdated' is not event of Object (AddHandler orderAdp.Adapter.RowUpdated), also tell me which event should i include the Add Handler Statement?


    Thursday, April 21, 2011 10:48 AM
  • Hi,

    No need to declare the adapter again. The adapter is created in the designer generated code file aleady.

    I am not quite sure why there is error "RowUpdated in not event of object", Do you use "orderAdp.RowUpdated" or "orderAdp.Adapter.RowUpdated"? First clause give me the same error.

    I mocked up the same scenario code and below is the snippet in my environment, you can modify some variable\object name and try again

    Put AddHandler in form load, 

     

    AddHandler Me.TableAdapterManager.OrdersTableAdapter.Adapter.RowUpdated, _
            New OleDbRowUpdatedEventHandler(AddressOf OnOrdersRowUpdated) 
    

     

    Then add this handler in your form class

     

    Private Sub OnOrdersRowUpdated( _
    	  ByVal sender As Object, ByVal e As OleDbRowUpdatedEventArgs)
    	' Conditionally execute this code block on inserts only.
    	If e.StatementType = StatementType.Insert Then 
    		Dim cmdNewID As New OleDbCommand("SELECT @@IDENTITY", Me.TableAdapterManager.OrdersTableAdapter.Connection, Me.TableAdapterManager.OrdersTableAdapter.Transaction)
    		e.Row("OrderID") = CInt(cmdNewID.ExecuteScalar)
    		e.Status = UpdateStatus.SkipCurrentRow
    	End If
    End Sub
    
    Regards

     

    Friday, April 22, 2011 4:31 AM
  • Hello

    I got the error where is. Since i am using TableAdapter to Update to Database, i had turned off Hierarchical Update Property in the Dataset Designer set to False so have no Property/Event Named '.Adapter.RowUpdated'.

    Now that i have Set it to True i have now available those Events to the Object (TableAdapter).

    However no TableAdapterManager is still got available to me so i have just deleted it from code & simply kept Me.taOrders.Connection, Me.taOrders.Transaction

    This worked just Fine & i got what i wanted. I still want to know that may i add TableAdapterManager to the form Now after all designing is done (complited)? If yes then how do i?

    Also one more thing i want to clear is in the similar codes i found serfing the Net there is "e.Row.AcceptChanges()" statement after command Execution insted of "e.Status = UpdateStatus.SkipCurrentRow" what is the diffrence here?

     

     

    Friday, April 22, 2011 10:22 AM
  • TableAdapterManager is available in from VS 2008. If you use VS2005, you wouldn't see TableAdapterManager.

    Both "AcceptChanges" and "SkipCurrentRow" are doing same thing which is to avoid your dataset change the current row state to be "modified". It may confuse adapter to do some update for this record again. But even adapter to update again, I don't think it is harmful because the updated new value is actual same as what is in the database.

    Once the adapter update the dataset, all rows in the dataset state are being reset to "no-change".

    But the program immediately updatea ID to that real automumber. So if without that line, the state would be changed to "modified" once real autonumber is assigned.

    "SkipCurrentRow" is just preventing the state to be changed.

    "AcceptChanges" is reset the state from "modified" to "no-change" again, so this way takes two round to achieve the same result. 

    I believe SkipCurrentRow should have very minor better performance.

     

    Friday, April 22, 2011 2:31 PM
  • hello

    i am using VB 2008 only & unable to put TableAdapterManager back to Component Tray once it is deleted.

    Also i tried both statements & found some trouble with 'SkipCurrentRow' that is when i try to Delete the newly added row i get Concurrency Error where as Using AcceptChanges if i delete Any Newly Added Row, i wont get any error & the row gets deleted successfully.

    SANDEEP


    Friday, April 22, 2011 6:26 PM
  • Hi Sandeep,

    Sorry, the code isn't working actually. Should use AcceptChanges.

    I didn't release that AcceptChanges not only reset the row status but also overwrite original value using the current value. 

    I found below thread has complete implementation of the similiar scenario, you can refer to subroutine daOrd2_MyRowUpdated and daOrd2_MyRowUpdated.

    How to update parent-child data with an Identity column from a Windows Forms application

    by using a Web service in Visual Basic .NET

    http://support.microsoft.com/kb/310350

    Regarding that "put TableAdapterManager back to Component Tray once it is deleted",  what I tried is to drag and drop a table from datasource into the form again, then I can see that TableAdapterManager being recreated on the component tray again.  Not sure there is better method or not.

    Regards

     

    Saturday, April 23, 2011 1:05 PM
  • hello

    Thanks for the treak to add TableAdapterManager back to Component Tray.

    Ok now that the Primary key can be obtained this way, if a large application is being build then there are multiple forms which will need this Subroutine to Auto set Generated ID

    so if we can put the Subroutine in the Public Module Or a new Public Class to share it centraly then what changes should i have to do in Subroutine & what changes will be needed to Add Handler Statement?

    somthing i found for Public Class on the net is as follows however it has tobe modify to remove bug in the code as well as what Parameters will be needed to pass to call the Subroutine & how?

    Public Class AccessIDHelperClass
      ' 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 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
    End Class
    Saturday, April 23, 2011 6:18 PM
  • I can see there are two variations in the subroutine, they are e.Row("OrderID")" and "OrdersTableAdapter.Transaction".

    So if can remove the two variations, then the subroutine would be static and can be used for all of similiar adapters.

    I tried to get the transaction from sender object for removing transaction variation,

    Attempt to remove "OrderID", it may get primary key name by using FillSchema.

    The code snippet like blow

    Get Primary Key Name

    Dim pkName As String
    Dim tbSchema As New DataTable
    CType(sender, System.Data.OleDb.OleDbDataAdapter).FillSchema(tbSchema, SchemaType.Source)
    pkName = tbSchema.PrimaryKey(0).ColumnName 
    Get transaction from Sender Object
    Dim cmdNewID As New OleDbCommand("SELECT @@IDENTITY", Me.TableAdapterManager.OrdersTableAdapter.Connection, CType(sender, System.Data.OleDb.OleDbDataAdapter).UpdateCommand.Transaction) '
    e.Row(pkName) = CInt(cmdNewID.ExecuteScalar)      

     

    Tuesday, April 26, 2011 1:04 PM
  • Hello

    Please tell me how do i call above Public Class from diffrent forms & what parameters should i pass to it & how?

     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
    End Class

    SANDEEP

    Friday, May 6, 2011 3:37 AM