locked
Dataset based on an updateable view RRS feed

  • Question

  • I have a strongly typed dataset with a tableadapter based on updateable view.  I have the view configured in the database based on two tables that have one identity column each.  I have implemented instead of insert, update, and delete triggers on the view in the database to make it updateable.

    When I create the strongly typed dataset tableadapter based on the view, the wizard automatically creates an insert statement capable of filling the table adapter.  I manually create the insert, update, and delete statements on the view in the tableadapter.  I am able to use the dataset tableadapter as any other dataset tableadapter based on a table with one exception. 

    When the save operations are performed, the identity column values do not get updated back into the dataset from database.  I tried to configure the tableadapter in the dataset by going under advanced options and selecting refresh the data table.  Setting this option removes my customized insert, update, and delete statements and generally makes a mess of the tableadapter.

    The real problem comes when I try to insert into a child table of a new row in the view.  As you can imagine a foreign key constraint gets thrown.

    Is there something I can do in the dataset so that the identity values from my view are pulled back into the tableadapter when a row is saved to the database?

    I am using VS 2008 and SQLServer 2005.

    Any help would be appreciated.
    • Moved by Martin Xie - MSFT Tuesday, September 1, 2009 4:46 AM Move it to ADO.NET DataSet forum for better support. (From:Visual Basic General)
    Monday, August 31, 2009 9:07 PM

Answers

  • Hi koby16,

    I’m wondering, why don’t you use the built-in Stored Procedures in the Microsoft® SQL Server 2008 or 2005?

    Stored Procedures can cover these three basic needed functions: Add, Update and Remove.
    However, the database engine which connects with your app, updates the rows automatically.

    For further reading
    Stored Procedures:
    http://msdn.microsoft.com/en-us/library/aa214299%28SQL.80%29.aspx

    I hope this information was helpful…

    Have a nice day…

    Best regards,
    Fisnik


    Coder24.com
    Sunday, September 6, 2009 9:22 AM
  • Hi Lingzhi,

    I approached the problem based on all the feedback on stored procedures.  I created a stored procedure to handle the insert, set up the InsertCommand to StoredProcedure in the dataset, specified the name of my Stored Procedure in the dataset, and set up the collection of parameters in the dataset.  In order to get the identity columns to populate back into the dataset I needed to create 2 (in this case) output parameters that push the identity values back out to the dataset.  The output parameters of course needed to be set up in the Stored Procedure as well.

    I have updated my sample project and posted it here for anyone that is interested in the solution source code.

    http://cid-96ff200fe5f61f34.skydrive.live.com/self.aspx/.Public/MSDN-UpdateableView-Working.zip

    Thanks to everyone for their input on this!

    Koby16
    Tuesday, September 29, 2009 7:16 PM
  • Hi koby16,

     

    I think we need to realize the data table saving process manually instead of using the TableAdapterManager.UpdateAll method, like the way in this MSDN documentation, Walkthrough: Saving Data to a Database (Multiple Tables).  I modified the save button click event handler as the following codes for your references.  Please note that I only realize the Insert process, for other situations, please modify the codes based on your application’s detailed scenario. 

    ==========================================================================
            Me.Validate()

            Me.Adjustmentcostv_appBindingSource.EndEdit()

            Me.Componentdetail_appBindingSource.EndEdit()

     

            Dim newAdjust = DirectCast(Me.TestDataSet.adjustmentcostv_app.GetChanges(DataRowState.Added), testMSDN.testDataSet.adjustmentcostv_appDataTable)

     

            If (Not newAdjust Is Nothing) Then

                Me.Adjustmentcostv_appTableAdapter.Update(newAdjust)

                Me.TestDataSet.componentdetail_app.Clear()

                Me.TestDataSet.adjustmentcostv_app.Clear()

                Me.Adjustmentcostv_appTableAdapter.Fill(Me.TestDataSet.adjustmentcostv_app)

            End If

     

            Dim newComponetDetail = DirectCast(Me.TestDataSet.componentdetail_app.GetChanges(DataRowState.Added), testMSDN.testDataSet.componentdetail_appDataTable)

     

            Me.Adjustmentcostv_appTableAdapter.Update(Me.TestDataSet.adjustmentcostv_app)

     

            If Not newComponetDetail Is Nothing Then

                Me.Componentdetail_appTableAdapter.Update(newComponetDetail)

            End If

            Me.Componentdetail_appTableAdapter.Fill(Me.TestDataSet.componentdetail_app)

            Me.TestDataSet.AcceptChanges()
    ==========================================================================

     

    If we add a master record in the top DataGridView, then save the record.  The certain IDs will be refreshed.  If we don’t press the save button, I don’t think the certain IDs will be refreshed on the DataGridView since they are auto-increased by the SQL Server.  We cannot know the value until the record is inserted successfully at the database side.  Then if we add a new child record in the bottom DataGridView, and press the save button, the records are updated correctly. 

     

    If you have any questions, please feel free to let me know.

     

    Have a nice day!

     

     

    Best Regards,
    Lingzhi Sun


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Thursday, September 3, 2009 4:42 AM
  • Hi,
    use stored prcedure instead of sql statements,
    affter dragging tables in dataset , configure it by selecting your own stored procedures,
    then in save methode you can call the tableadapter.update(dataset)
    I will create it for you .
    mail me on pishkari@hotmail.com or ali@pishkari.com ,
    I will send you the code.
    Pishkari
    Monday, September 7, 2009 12:05 PM

All replies

  • Hi koby16,

     

    Glad to see you again!

     

    If it is convenient, could you please provide us with a demo project and some testing data? 

     

    Thank you so much & have a nice day!

     

     

    Best Regards,
    Lingzhi Sun


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Tuesday, September 1, 2009 9:19 AM
  • Hi Lingzhi,

    I have put together a demo project.  You can download it from:

    http://cid-96ff200fe5f61f34.skydrive.live.com/browse.aspx/.Public?uc=1

    Contents:
    testMSDN - folder - VS2008 Project
    testdb.sql - file - script to generate the database objects

    Directions:
    1. Insert a row into the top datagridview and click save.  Notice the identity columns do not update.
    2. Insert a row into the top (master) datagridview and then insert a child row into the bottom (detail) datagrid view.  Notice the FK constraint violation.

    Notes:
    Insert is the only save operation that is implemented in the tableadapter.

    Thank you for your assistance!
    Tuesday, September 1, 2009 4:17 PM
  • Hi koby16,

     

    Thank you very much for providing us the demo project.  However, after I downloaded the testMSDN.zip file, I found it is empty.  Could you please have a check on it? 

      

     

    Thank you so much & have a nice day!

     

     

    Best Regards,
    Lingzhi Sun


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Wednesday, September 2, 2009 2:53 AM
  • Sorry about that!  The new file is posted.

    Thanks!
    Wednesday, September 2, 2009 2:05 PM
  • Hi koby16,

     

    I think we need to realize the data table saving process manually instead of using the TableAdapterManager.UpdateAll method, like the way in this MSDN documentation, Walkthrough: Saving Data to a Database (Multiple Tables).  I modified the save button click event handler as the following codes for your references.  Please note that I only realize the Insert process, for other situations, please modify the codes based on your application’s detailed scenario. 

    ==========================================================================
            Me.Validate()

            Me.Adjustmentcostv_appBindingSource.EndEdit()

            Me.Componentdetail_appBindingSource.EndEdit()

     

            Dim newAdjust = DirectCast(Me.TestDataSet.adjustmentcostv_app.GetChanges(DataRowState.Added), testMSDN.testDataSet.adjustmentcostv_appDataTable)

     

            If (Not newAdjust Is Nothing) Then

                Me.Adjustmentcostv_appTableAdapter.Update(newAdjust)

                Me.TestDataSet.componentdetail_app.Clear()

                Me.TestDataSet.adjustmentcostv_app.Clear()

                Me.Adjustmentcostv_appTableAdapter.Fill(Me.TestDataSet.adjustmentcostv_app)

            End If

     

            Dim newComponetDetail = DirectCast(Me.TestDataSet.componentdetail_app.GetChanges(DataRowState.Added), testMSDN.testDataSet.componentdetail_appDataTable)

     

            Me.Adjustmentcostv_appTableAdapter.Update(Me.TestDataSet.adjustmentcostv_app)

     

            If Not newComponetDetail Is Nothing Then

                Me.Componentdetail_appTableAdapter.Update(newComponetDetail)

            End If

            Me.Componentdetail_appTableAdapter.Fill(Me.TestDataSet.componentdetail_app)

            Me.TestDataSet.AcceptChanges()
    ==========================================================================

     

    If we add a master record in the top DataGridView, then save the record.  The certain IDs will be refreshed.  If we don’t press the save button, I don’t think the certain IDs will be refreshed on the DataGridView since they are auto-increased by the SQL Server.  We cannot know the value until the record is inserted successfully at the database side.  Then if we add a new child record in the bottom DataGridView, and press the save button, the records are updated correctly. 

     

    If you have any questions, please feel free to let me know.

     

    Have a nice day!

     

     

    Best Regards,
    Lingzhi Sun


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Thursday, September 3, 2009 4:42 AM
  • Hi Lingzhi,

    Thank you for your response!  The solution you provided works for the master table insert.  The problem is with the following scenario as you indicated:

    1. Insert a record into the master table ** Do not save the record yet **
    2. Insert a child record into the detail table
    3. Save the records

    What happens with the above code is the child record disappears and is never saved to the database. 

    Is there a dataset level solution to this problem?  Can we put a Select in the tableadapter Insert statement to refresh the data?

    There are a couple issues with this solution:

    1. I am doing the above programatically.  I cannot implement a save, select the correct parent record, add the children.  As well, I am inserting potentially hundreds of records at a shot so it is not really feasible to do an "insert, save, fill" a hundred times over.
    2. In a non-programatic solution we could not expect the users to save the parent record before adding child records.

    Thanks again!  Any other suggestions would be appreciated.
    Thursday, September 3, 2009 3:26 PM
  • Hi koby16,

    Have you tried to make the adjustmentcostv_appTableAdapter Refresh the data table under TableAdapter Configuration Wizard / Advanced Options? 

    I am also researching other workarounds and will let you know as soon as I have any ideas. 

    Hope you have a nice weekend!

     

    Best Regards,
    Lingzhi Sun


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Saturday, September 5, 2009 5:41 PM
  • Hi koby16,

    I’m wondering, why don’t you use the built-in Stored Procedures in the Microsoft® SQL Server 2008 or 2005?

    Stored Procedures can cover these three basic needed functions: Add, Update and Remove.
    However, the database engine which connects with your app, updates the rows automatically.

    For further reading
    Stored Procedures:
    http://msdn.microsoft.com/en-us/library/aa214299%28SQL.80%29.aspx

    I hope this information was helpful…

    Have a nice day…

    Best regards,
    Fisnik


    Coder24.com
    Sunday, September 6, 2009 9:22 AM
  • Hi,
    use stored prcedure instead of sql statements,
    affter dragging tables in dataset , configure it by selecting your own stored procedures,
    then in save methode you can call the tableadapter.update(dataset)
    I will create it for you .
    mail me on pishkari@hotmail.com or ali@pishkari.com ,
    I will send you the code.
    Pishkari
    Monday, September 7, 2009 12:05 PM
  • Hi koby16,

     

    I think using stored procedure to update the database is really a good idea.   Would you mind letting us know the result of the suggestions? 

     

    If you need further assistance, please feel free to let me know.   I will be more than happy to be of assistance.

     

    Have a nice day!

     

     

    Best Regards,
    Lingzhi Sun


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Tuesday, September 8, 2009 1:53 AM
  • Hi koby16,

     

    How is the problem now? 


    If you need further assistance, please feel free to let me know.   I will be more than happy to be of assistance.

     


    Hope you ave a nice day!

     

     

     

    Best Regards,
    Lingzhi Sun


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Thursday, September 17, 2009 9:05 AM
  • Hi Lingzhi,

    I approached the problem based on all the feedback on stored procedures.  I created a stored procedure to handle the insert, set up the InsertCommand to StoredProcedure in the dataset, specified the name of my Stored Procedure in the dataset, and set up the collection of parameters in the dataset.  In order to get the identity columns to populate back into the dataset I needed to create 2 (in this case) output parameters that push the identity values back out to the dataset.  The output parameters of course needed to be set up in the Stored Procedure as well.

    I have updated my sample project and posted it here for anyone that is interested in the solution source code.

    http://cid-96ff200fe5f61f34.skydrive.live.com/self.aspx/.Public/MSDN-UpdateableView-Working.zip

    Thanks to everyone for their input on this!

    Koby16
    Tuesday, September 29, 2009 7:16 PM
  • Hi Koby16,

     

    I am really glad that the problem has been resolved.  Thank you so much for sharing your solution.  It will definitely benefit other community members!   

     

    Thanks again and have a great day!

     

     

    Best Regards,
    Lingzhi Sun


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Wednesday, September 30, 2009 1:17 AM