Violation of UNIQUE KEY

[vipinkrshamra]

I am using adapter.update method to insert a datatable. One of the field in database table to which datatable ois referring is UNIQUE key.

here is complete error message :
Error : Violation of UNIQUE KEY constraint 'IX_tblJunk'. Cannot insert duplicate key in object 'tblJunk'.
The statement has been terminated.

I am using follwing command to update data :

da.ContinueUpdateOnError = True
da.Update(ds, TableName)

I am using ContinueUpdateOnError becasue I want to insert records irrespective of errros. After dataadapter.update command I am using ds.Tables(TableName).HasErrors to browse through the errors. I am getting UNIQUE KEY constraint error for records which are actually not there in database for example I am gettign error for row which has indexfield as "abcd" but if search database for "abcd" record it is not there.

I can not manually search or minimise number of insertion records to troubleshoot this issue.

Any help would be greatly apprecriated.

 

[ThatRickGuy]

Are you sure you are not trying to insert 2 rows with the key of "abcd"?

-Rick

VB.Net Forum forum796 forum855 ASP.NET Forum
I believe in killer coding ninja monkeys.

 

[vipinkrshamra]

Yes. I am sure about it. There are no duplicate recrods either my Datatable or database table.

 

[ThatRickGuy]

Are you positive? Not to be an ass, it's just like my old teacher SSgt Craven used to say:

If yours is just like mine, and it doesn't work, then yours is not just like mine

Are there any records that are missing the primary key? Are you positive that the primary key is set to the field you are looking at? can you pull up the data you are inserting and manually inspect it?

-Rick

VB.Net Forum forum796 forum855 ASP.NET Forum
I believe in killer coding ninja monkeys.

 

[vipinkrshamra]

Is it possible that actuall error is different but during scanning the errorrows I am getting wrong error message

Here is function I am uisng to scan through rows having error

*********Function Start********
Private Sub HandleSQlError(ByVal ErrorTable As DataTable)
'Dim myTable As DataTable
Dim rowsInError() As DataRow
Dim dr As DataRow
Dim i As Integer
Dim myCol As DataColumn
'myTable = ds.Tables(TableName)
Dim strDupIndexNumber As String = ""
Dim strDupIndexNumber2 As String = ""
Try
If ErrorTable.HasErrors Then
rowsInError = ErrorTable.GetErrors()
For Each dr In rowsInError
'MessageBox.Show("IndexNumber in Error row : " & dr.Item(1))
WriteLogFile("Could not insert Row number/IndexNumber : " & dr.Item(0) & "/" & dr.Item(1) & ". Error : " & dr.RowError.ToString, "HandleSQlError")
If strDupIndexNumber = "" Then
strDupIndexNumber = "'" & dr.Item(1) & "'"
strDupIndexNumber2 = dr.Item(1)
Else
strDupIndexNumber += "'," & dr.Item(1) & "'"
strDupIndexNumber2 += "," & dr.Item(1)
End If
dr.ClearErrors()
'myTable.Rows.Remove(dr)
Next
WriteLogFile("Duplicate IndexNumbers are : " & strDupIndexNumber, "HandleSQlError")
WriteLogFile("Duplicate IndexNumbers2 are : " & strDupIndexNumber2, "HandleSQlError")
End If
Catch ex As Exception
WriteLogFile("Could not fetch error rows. Error : " & ex.ToString, "HandleSQlError")
End Try

End Sub
*********Function End********

 

[vipinkrshamra]

Let me give you more information about my problem. I am parsing some txt files with 1000's of line into database. If the file from which I am getting error is inserting in blank table then I dont get error but if the file is processed into table with existing data then I get this exception. I have searched databse for row having issue but I could find that data in database ...

 

[vipinkrshamra]

I would like to clear one thing that I get error only for few records. For example if I isnert 300 records in database, I may get error for let say 10/12 records. And I am very sure data whcih is breaking the Unique constraint is not there in database.