none
Having trouble with Typed Data Sets and SQL Server Identity columns. RRS feed

  • Question


  • I am working with TypedDataSets and the Nerd Dinner project. I have run into another problem when doing inserts with the TableAdapterManager.UpdateAll(..) method: not resolving autogeneriated identity columns when inserting a new Typed Data Row after insert. In my case, DinnerDataRow with the System.Int32 DinnerID column did not contain the new identity value created in SQlServer for the row after the UpdateAll method completed. Is there anyway on the typed data set design surface to resolve this issue? (IE clicking a checkbox option?)

    Best,

    Nick Muhonen
    Monday, February 22, 2010 5:49 PM

Answers

  • Hi Lingzhi, Nick,

    I work for the dataset generator in general and I believe this is a by design as all Web projects by default turn off the refresh data option and so the DataSet code generator will not generate the select command in insert command. Someone from Web Project team will confirm this later.  

    At the meantime, you can work around this by open the dataset designer and reconfig the datatable by the following steps:
    1. Open the dataset designer
    2. Select a table and right click to slect Config
    3. You will see a Wizard come up and the page should have an Advanced Options button.
    4. Click that button and check the Refresh the data table option. 
    5. Save the dataset and you should see the change now. 

    Hope this help. 
     

    John


    John Chen -- See my team blog: http://blogs.msdn.com/vsdata. All my posts are provided "AS IS" with no warranties, and confer no rights.
    Monday, March 1, 2010 8:24 PM

All replies

  • Hi Nick,

     

    I tested the typed dataset in my lab with VS2008 SP1.  The identity column is retrieved correctly when the .UpdateAll() is called.  Please check the certain TableAdapter’s InsertCommand in the properties window.  

     

    E.g. I have a table named IdentityTest which has an identity column IdentityID, other column is named Value which is in type of nvarchar(50).  Then the auto-generated CommandTest is:

    ==================================================================================

    INSERT INTO [dbo].[IdentityTest] ([Value]) VALUES (@Value);

    SELECT IdentityID, Value FROM IdentityTest WHERE (IdentityID = SCOPE_IDENTITY())

    ==================================================================================

     

    TableAdapterManager actually calls each TableAdapter to update their corresponding data tables.  If the TableAdapter’s InsertCommand is set correctly, I believe the identity column value will be retrieved successfully.  

     

    Also I have tested it on VS2010 Beta 2, the testing result is the same. 

     

    Is the InsertCommand generated correctly at your side?   Which SQL Server version in your machine?    

     

    Besides, you can refer to these threads to manually edit the InsertCommand and retrieve the Identity column value. 

    http://forums.asp.net/p/1043381/1471597.aspx

    http://www.eggheadcafe.com/software/aspnet/33424492/strongly-typed-dataset-up.aspx

     

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

     

    Have a nice day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    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, February 23, 2010 3:41 AM
    Moderator
  • Hi Nick,

     

    I am writing to check the status of the issue on your side.  Would you mind letting me 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

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    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, February 25, 2010 2:08 AM
    Moderator
  • Thanks Lingzhi.

    Unfortunately, I am still getting the same results.

    Here's what I did to reproduce the problem:

    1) Created a new Console App C# project in Visual Studio 2010 RC
    2) Added a Service Based Database, and skipped the wizard for choosing either a typed dataset or EF API (CLicked Cancel).
    3) IN VS connection manager in server explorer, connected to the databse and added a table with the following properties:
       -Table Name: SampleTable
       -Columns: SampleID as int Identity, SampleName as vachar (50)
    4) Added a Typed Data Set file.
    5) Dragged the newly created table from the connection manager to the TypedDataSet Design surface.

    Here's what was generated for the insert command on the SampleTable table adapter:

    INSERT INTO [dbo].[Sample] ([SampleName]) VALUES (@SampleName)
    System Info:
    OS: Windows 7
    Dev Env: VS 2010 RC 1
    SQL Version #: 10.1.2531.0
    SQL File Version #: 2007.100.2531.0
    Edition:Express Edition

    I hope this helps

    Friday, February 26, 2010 2:49 PM
  • After further research, my previous example did not have SampleID as a primary key, which fixed that problem.

    I'm still stuck though. I did a test by adding the local NerdDinner.mdf database in both an Console Application and Empty MVC 2 Web Application. IN the console application, the following code was produced for the typed data set code:

    INSERT INTO [dbo].[Dinners] ([Title], [EventDate], [Description], [HostedBy], [ContactPhone], [Address], [Country], [Latitude], [Longitude]) VALUES (@Title, @EventDate, @Description, @HostedBy, @ContactPhone, @Address, @Country, @Latitude, @Longitude);
    SELECT DinnerID, Title, EventDate, Description, HostedBy, ContactPhone, Address, Country, Latitude, Longitude FROM Dinners WHERE (DinnerID = SCOPE_IDENTITY())

    IN the MVC web application, doing pretty much the same thing. The following code was produced:

    INSERT INTO [dbo].[Dinners] ([Title], [EventDate], [Description], [HostedBy], [ContactPhone], [Address], [Country], [Latitude], [Longitude]) VALUES (@Title, @EventDate, @Description, @HostedBy, @ContactPhone, @Address, @Country, @Latitude, @Longitude)

    I have uploaded a zipped vs 2010 RC solution the results of both versions to skydrive, which you can download at this url:
    http://cid-d41e5bec4b54036a.skydrive.live.com/self.aspx/.Public/TypedDataSetProblemExample.zip


    Let me know if you can repoduce the same behavior.

    -Nick

    Friday, February 26, 2010 4:03 PM
  • Hi Nick,

     

    Thank you very much for providing us with the detailed information and repro project!   Yes, I can repro the problem in MVC 2 web application.  But it is fine in console application as you said. 

     

    I will perform some further investigation and open a bug ticket in Microsoft Connect. 

     

    Currently, could you please manually edit the T-SQL command text in the TableAdapter to fix the problem?  

     

    If you have any questions, please feel free to let me know.   I will share the bug ticket link here as soon as I submit it. 

     

    Have a nice day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    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.
    Monday, March 1, 2010 6:55 AM
    Moderator
  • Hi Nick,

     

    I have open a bug ticket here, https://connect.microsoft.com/VisualStudio/feedback/details/537533/msdn-forum-typed-dataset-tableadapter-insertcommand-does-not-contain-sql-commands-to-retrieve-the-identity-column-value-in-mvc-2-web-application.   If you have any questions or other concern, please feel free to let me know.

     

    Have a nice day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    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.
    Monday, March 1, 2010 7:55 AM
    Moderator
  • Hi Lingzhi, Nick,

    I work for the dataset generator in general and I believe this is a by design as all Web projects by default turn off the refresh data option and so the DataSet code generator will not generate the select command in insert command. Someone from Web Project team will confirm this later.  

    At the meantime, you can work around this by open the dataset designer and reconfig the datatable by the following steps:
    1. Open the dataset designer
    2. Select a table and right click to slect Config
    3. You will see a Wizard come up and the page should have an Advanced Options button.
    4. Click that button and check the Refresh the data table option. 
    5. Save the dataset and you should see the change now. 

    Hope this help. 
     

    John


    John Chen -- See my team blog: http://blogs.msdn.com/vsdata. All my posts are provided "AS IS" with no warranties, and confer no rights.
    Monday, March 1, 2010 8:24 PM
  • Thank you so much, John!  I don't have much experience on web application development.  Your post is really helpful to me!

    @Nick, I have followed John's suggestion and it works fine now.   Can you verify it at your side?  

    Have a nice day, both!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    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, March 2, 2010 8:53 AM
    Moderator
  • That did the trick, thanks for you help on this one John and Lingzhi.

    -Nick
    Tuesday, March 2, 2010 2:03 PM