none
Problem with Table Adapter Add Row Method where the Table ID Column is an Identity with the Minimum Value (-2147483648) for the Seed RRS feed

  • Question

  • I am working on a VB.NET 2008 project accessing a SQL SERVER 2005 EXPRESS database. The table I am accessing has an identity column defined as "int IDENTITY(-2147483648, 1) NOT NULL".  I used the Data Adapter Configuration Wizard to create the data adapter and then tried to use the auto-generated AddRow method, but when I do that I get the "Value was either too large or too small for an Int32" exception.  I think I see where the problem is.  When the Wizard created the data adapter it set the AutoIncrementSeed and AutoIncrementStep column properties for the ID column as -1.  I'm guessing the Wizard did this to avoid conflicts with already assigned ID values, but doing so will not work if there is already a value in the ID column with the minimum (-2147483648) because the AutoIncrementStep of -1 will result in an ID for the new record that is less than the minimum allowed.  Is this a bug or am I doing something wrong?

    Here is an example to clarify exactly where the exception is occurring.  Suppose you have a database (MyDatabase) with a table (MyTable) in which there are two fields defined as follows:

    	[MyTableId] [int] IDENTITY(-2147483648,1) NOT NULL,
    	[Description] [char](50) NOT NULL,
    

    Suppose also that you have inserted at least one record in the table which would by definition have MyTableId = -2147483648.  Suppose further that you create a VB 2008 project and use the Wizard to create the data set adapter (and table adapters, etc.).  With all of this the exception will occur on the fourth line in the following code:

            Dim myDataSet As New MyDatabaseDataSet
            Dim myTableAdapter As New MyDatabaseDataSetTableAdapters.MyTableTableAdapter
            myTableAdapter.Fill(myDataSet.MyTable)
            myDataSet.MyTable.AddMyTableRow("Some Description")
    

    • Moved by Youjun Tang Friday, September 11, 2015 2:36 AM ADO.NET DataSet related
    • Edited by mposh372 Friday, September 11, 2015 12:21 PM
    Thursday, September 10, 2015 9:10 PM

Answers

  • Hi mposh372,

    Yes, I think it should be 1 instead. You could change it to 1 in the designer window.

    Choose the MyTableID column in dataset designer window. then change the AutoIncrementStep to 1.

    I changed it to 1, then it will work well in my side.

    If you have any other concern regarding this issue, please feel free to let me know.

    Regards,
    Youjun Tang


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    • Edited by Youjun Tang Monday, September 14, 2015 9:11 AM
    • Marked as answer by mposh372 Monday, September 14, 2015 1:38 PM
    Monday, September 14, 2015 9:10 AM

All replies

  • Hi mposh372,

    Yes, I think it should be 1 instead. You could change it to 1 in the designer window.

    Choose the MyTableID column in dataset designer window. then change the AutoIncrementStep to 1.

    I changed it to 1, then it will work well in my side.

    If you have any other concern regarding this issue, please feel free to let me know.

    Regards,
    Youjun Tang


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    • Edited by Youjun Tang Monday, September 14, 2015 9:11 AM
    • Marked as answer by mposh372 Monday, September 14, 2015 1:38 PM
    Monday, September 14, 2015 9:10 AM
  • Thank you!  That worked.  So it is a "bug" in the wizard-generated code, but it's one that's easy to fix.
    Monday, September 14, 2015 1:41 PM