Primary Key Error when Update DB from Data Adapter

[jmeckley]

I have a process where a user will click a button and load an xml file on their machine to the database. The code errors out on the red line. Stating:

The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again.


Here is the code

Dim cnn As New OleDbConnection(AppSettings("cnn") & Server.MapPath(AppSettings("MapPath")))
        Dim File As New FileInfo("c:\Items.xml")
        Dim cmdText As New System.Text.StringBuilder()
        Dim DS As New DataSet()
        Dim DA As OleDbDataAdapter
        Dim cmdB As OleDbCommandBuilder
        Dim PK(1) As DataColumn
        Dim Tbl As New DataTable("Catalog_tbl")
 
        If File.Directory.Exists Then
            If File.Exists Then
                ''create tbl to load xml data into
                'Tbl.Columns.Add(New DataColumn("ItemNumber", GetType(String)))
                'Tbl.Columns.Add(New DataColumn("Artist", GetType(String)))
                'Tbl.Columns.Add(New DataColumn("Title", GetType(String)))
                'Tbl.Columns.Add(New DataColumn("Bid", GetType(Double)))
                'Tbl.Columns.Add(New DataColumn("Medium", GetType(String)))
                'Tbl.Columns.Add(New DataColumn("Height", GetType(String)))
                'Tbl.Columns.Add(New DataColumn("Width", GetType(String)))
                'Tbl.Columns.Add(New DataColumn("Description", GetType(String)))
                'Tbl.Columns.Add(New DataColumn("ArtMarket", GetType(String)))
                'Tbl.Columns.Add(New DataColumn("AuctionTime", GetType(String)))
                'Tbl.Columns.Add(New DataColumn("ArtistWebSite", GetType(String)))
                'Tbl.Columns.Add(New DataColumn("ArtMarketWebSite", GetType(String)))
                'Tbl.Columns.Add(New DataColumn("LECurrent", GetType(Integer)))
                'Tbl.Columns.Add(New DataColumn("LEPrevious", GetType(Integer)))
                'Tbl.Columns.Add(New DataColumn("JudgeAward", GetType(Integer)))
                'Tbl.Columns.Add(New DataColumn("JudgeCitation", GetType(Integer)))
                'Tbl.Columns.Add(New DataColumn("PeoplesChoice", GetType(Integer)))
                'DS.Tables.Add(Tbl)
 
                'select sql
                cmdText.Append("SELECT ")
                cmdText.Append("ItemNumber, ")
                cmdText.Append("Artist, ")
                cmdText.Append("Title, ")
                cmdText.Append("Bid, ")
                cmdText.Append("Medium, ")
                cmdText.Append("Height, ")
                cmdText.Append("Width, ")
                cmdText.Append("Description, ")
                cmdText.Append("ArtMarket, ")
                cmdText.Append("AuctionTime, ")
                cmdText.Append("ArtistWebSite, ")
                cmdText.Append("ArtMarketWebSite, ")
                cmdText.Append("LECurrent, ")
                cmdText.Append("LEPrevious, ")
                cmdText.Append("JudgeAward, ")
                cmdText.Append("JudgeCitation, ")
                cmdText.Append("PeoplesChoice ")
                cmdText.Append("FROM Catalog_tbl ")
                cmdText.Append("ORDER BY ItemNumber")
 
                DA = New OleDbDataAdapter(cmdText.ToString, cnn)
                'auto generate insert, update, delete statements
                cmdB = New OleDbCommandBuilder(DA)
                DA.Fill(DS, Tbl.TableName) 'load current records
                'create primary key
                PK(0) = Tbl.Columns("ItemNumber")
                Tbl.PrimaryKey = PK
                'delete all records
                Tbl.Rows.Clear()
                DS.ReadXml(File.FullName) 'load xml file
                'insert/update records in web database
                [color=red]DA.Update(DS, "Catalog_tbl")[/color]
                Response.Write("all done")
            End If
        End If
 
        DS.Dispose()
        DS = Nothing
        DA.Dispose()
        DA = Nothing
        cnn.Dispose()
        cnn = Nothing
        cmdB.Dispose()
        cmdB = Nothing
        Tbl.Dispose()
        Tbl = Nothing

[pax]

Hi.

Well, aparently one or more of your fields in your DB is set to be unique. And when you try to add the next row, some of the fields are not unique, meaning that two rows contain the same data in one of the unique fields.

Either check that the data unique, or alter your DB to allow duplicates.