none
ADO.net Identity Field - Value xxx is Already Present RRS feed

  • Question

  • Hi

    I'm getting an error very occassionaly when inserting a record with an identity field (which is the PK).  I can't recreate it, I only see the error in customer log files.

    I'm using ADO .Net Datasets.

    The application is running on tablets so using wifi, so when an order is being entererd, I write the data into the dataset and only once the order is complete do I send the data.  The data is tiered Order | Items | Options - not all items have options.  The order record has already been written to the DB, so its ID field is set.  When I write each Item, once I have Item ID field, I then update any Options records point to the Item to the DB's ID value, then write those.

    The problem I'm getting is that sometimes I'm getting an error in the Item table adapter's update, e.g.

    Column 'ID' is constrained to be unique.  Value '15395' is already present.

    The 15395 is the correct ID and the data is in the database but looking at the dataset code-behind, neither the INSERT or UPDATE commands ever try to insert or update the ID columns (as you'd expect).  Code snippet.

                    For Each tiRow In (From f In TransactionDS.tblTransactionItems Where f.RowState <> DataRowState.Deleted And f.RowState <> DataRowState.Detached)
                        Dim LocalTIRowID = tiRow.ID

                        TblTransactionItemsTA.Update(tiRow)

                        '   Set each option's TIID ID for the current Item
                        For Each tioRow In (From f In TransactionDS.tblTransactionItemOptions Where f.TransactionItemID = LocalTIRowID)
                            If tioRow.TransactionItemID <> tiRow.ID Then tioRow.TransactionItemID = tiRow.ID
                        Next

                        '   Then write them
                        For Each tioRow In (From f In TransactionDS.tblTransactionItemOptions Where f.TransactionItemID = LocalTIRowID)
                            TblTransactionItemOptionsTA.Update(tioRow)
                        Next
                    Next

    Even more oddly, as it's wifi so we're expecting connection drops, the exception handler then shows a 'You are out of Wifi Range' type message and tries to create a new connection to SQL.  When it has that, it returns and the table adapters' connection updated to the new connection.  At that point, the first Item which failed writes, but then the next one fails - until it gets a new connection then it works and the next one fails.

    I'm really confused :-)

    Any pointers or help would be much appreciated.

    Regards

    Mike



    • Edited by Mike Hoffman Friday, August 8, 2014 4:36 PM
    • Moved by Carl Cai Monday, August 11, 2014 2:38 AM (from vb.net)more related
    Friday, August 8, 2014 11:56 AM

Answers

  • If you use relational databases then don't use logical identifiers also as physical identifiers, you always come in trouble. 

    Use two of them. I prefer Guid's for physical identifiers.


    Success
    Cor

    Thursday, August 14, 2014 10:58 AM

All replies

  • Hi,

    Sorry for my delay.

    Since you are using ADO.NET DataSet, I have helped you move this thread to ADO.NET DataSet  forum to help you narrow down this issue.

    Thanks for your understanding.

    Regards.


    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.

    Monday, August 11, 2014 2:40 AM
  • Hello,

    Could you please clarify which line it throws this error message in your provided code?

    And which table does it throw this error? If it is possible, please share these three tables with us.

    This error does not only come from the database, the DataTable would also cause this error if you try to add an existed record to it. Please check if you are under this scenario.

    Regards.


    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.



    Monday, August 11, 2014 5:33 AM
    Moderator
  • Many thanks Mark :-)
    Tuesday, August 12, 2014 6:37 PM
  • If you use relational databases then don't use logical identifiers also as physical identifiers, you always come in trouble. 

    Use two of them. I prefer Guid's for physical identifiers.


    Success
    Cor

    Thursday, August 14, 2014 10:58 AM