How to fix "Violation of PRIMARY KEY constraint" error?

[Silvia Mattarel]

I have two function:

Public Sub AddData()
 Dim sSQL As String = ""
 Dim check As Boolean = Me.TestExistingRow("QUESTIONARIO1")
 If check = False Then
  sSQL = "INSERT INTO QUESTIONARIO1([USER]) VALUES ('" & 
           Me.IdUser & "');"
  ExecuteNonQuery(Me.ConnString, CommandType.Text, sSQL)
 End If
End Sub
 
Public Function TestExistingRow(ByVal NameTable As String) As Boolean
            Dim sSQL As String = ""
            Dim n As Integer = 0
            Dim check As Boolean = True 'la riga esiste
            sSQL = "SELECT Count([USER]) as tot FROM " & NomeTabella & " where [USER]='" & Me.IdUser & "';"
            Try
                n = CInt(ExecuteScalar(Me.ConnString, CommandType.Text, sSQL))
                If n = 0 Then
                    check = False
                End If
            Catch ex As Exception
                check = True
            End Try
            Return check
        End Function
 

The two function are in a Class named Pippo with this Inizializing New Function:

Public Sub New(ByVal p As Page, ByVal c As HttpContext)
            SyncLock (Me)
                Me.IdUser = "VOID"
                If Not p.Request.Params("IdUser") Is Nothing Then Me.IdUser = p.Request.Params("IdUser").ToString
                If ((Not c.User Is Nothing) And (Me.IdUser = "VOID" Or Me.IdUser = "")) Then Me.IdUser = HttpContext.Current.User.Identity.Name
                If Me.IdUser = "" Then Me.IdUser = "VOID"              
                Me.ConnString = Me.GetCN()
            End SyncLock
        End Sub
 

Sometimes, a user connect to my application and get this error (that I receive in my mail):
Violation of PRIMARY KEY constraint 'PK__QUESTION__ AA65E05E1881A0D E'. Cannot insert duplicate key in object 'dbo.QUESTIONAR IO1'.
The statement has been terminated.

[...]

AUTH_TYPE = Forms

AUTH_USER = BL0210

AUTH_PASSWORD =

LOGON_USER =

REMOTE_USER = BL0210

Etc.

I'm not figuring why this error occures and why it occures only sometimes...

Thank you for your help

[Rabbit]

You put a primary key constraint on the field USER for the table QUESTIONARIO1. You can't have duplicate values on a primary key. If you need to have duplicates, then you have to remove the primary key.

[Silvia Mattarel]

Yes, of course. But I test if the key i want to insert is unique with the function TestExistingRow - as you can see - and it returns to me false, even if then the application gets the error. This situation occures only sometimes, not always.

The function TestExistingRow counts how many records are existing in the same table with the key that i want to insert and return FALSE ONLY if the numer of row is 0 (zero). The "AddData" function then tries to insert the key ONLY IF the first one (TestExistingRo w) return false. So...why sometimes it return false but then i get the "VIOLATION OF PRIMARY KEY" error?

However, Thanks for your attention! :)

[Rabbit]

Have you checked the table itself? You should have it print out the SQL insert command and check the table itself. What is the definition of the primary key on that table? Is it a compound key?

[Silvia Mattarel]

Yes I check the table QUESTIONARI1 with the sql statement "SELECT Count([USER]) as tot FROM " & NomeTabella & " where [USER]='" & Me.IdUser & "';" where NomeTabella is passed to the function as QUESTIONARI1

(have you seen my code above? PLEASE take a look at my code above. There are three function AddData, TestExistingRow and Sub New of the class that calls the two function)

Then I try to insert the same user only if tot=0:
n = CInt(ExecuteSca lar(Me.ConnStri ng, CommandType.Tex t, sSQL))
If n = 0 Then
check = False
End If
If check = False Then
sSQL = "INSERT INTO QUESTIONARIO1([USER]) VALUES ('" &
Me.IdUser & "');"

The table Questionari1 is defined as:

CREATE TABLE [dbo].[QUESTIONARIO1](
[USER] [nvarchar](50) NOT NULL,
[SITO] [nvarchar](max) NULL,
[TELEFONO1] [nvarchar](max) NULL,
CONSTRAINT [PK_QUESTIONARIO 1] PRIMARY KEY CLUSTERED
(
[USER] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORE COMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCK S = ON) ON [PRIMARY]
) ON [PRIMARY]

My doubt is that in some cases Me.User is not the same in both function, even if in local test all works fine...
Me.User is the Context.User.Id entity.Name (as you can see in my code above)

[Rabbit]

I wasn't asking you if the code is checking for duplicates. I was saying that you should check the table manually. By which I mean, on an error, print out the SQL string and then go to the SQL Server Management Studio and check the table itself.

What do yout mean by "Me.User is not the same in both function"? They need to be the same. What's the point of checking for a duplicate of a record if it's not the name you're trying to insert?

[Silvia Mattarel]

For example: I tried to put a try catch in the functions above and in case of error I send an email to my mailaddress. This was the result:

"The value of TestExistingRow is: False.
The istruction for verifying this was: SELECT Count([USER]) as tot FROM QUESTIONARIO1 where [USER]='MIW0230'.
I tried to run the istruction: INSERT INTO QUESTIONARIO1 ([USER]) VALUES ('MIW0230');"

So the function TestExistingRow returned False (this means that "SELECT Count([USER]) as tot FROM QUESTIONARIO1 where [USER]='MIW0230'" has return tot=0)
But the code has tried however to run the insert statement.....w hy????
I can't figured out why....
If I try the code in local and try to enter the application as MIW0230 (the user that then results in Me.User), I get no error!!!!
Me.User (=Context.User. Identity.Name in Public Sub New) can change during the code if many users enter the application? Because if yes, this is the only answer that I can think.
My pages are asynchron. Can this fact causes some delay and so the error? Mahhhhh???Bohh
Please, help me. This issue is a mistery for me by several days. Thank you

[Jerry Winston]

I'm not sure I understand your requirement or perhaps your reason for distributing so much of your logic into many,separate ad-hoc SQL queries.

The easiest solution is to wrap the good ol' TRY...CATCH block in a single stored procedure. eg:

CREATE PROCEDURE sp_addIfNew(@userVar VARCHAR(32))
AS
BEGIN
BEGIN TRY
INSERT INTO QUESTIONARIO1([USER]) VALUES (@userVar)
END TRY
BEGIN CATCH
SELECT
    ERROR_NUMBER() AS ErrorNumber
    ,ERROR_SEVERITY() AS ErrorSeverity
    ,ERROR_STATE() AS ErrorState
    ,ERROR_PROCEDURE() AS ErrorProcedure
    ,ERROR_LINE() AS ErrorLine
    ,ERROR_MESSAGE() AS ErrorMessage;
END CATCH
END
 

The SELECT code in the CATCH...END CATCH section should be removed in favor of your on error-handling SP's.

You don't need to iterate(count) all your users to determine whether or not you can insert a row. With TRY/CATCH you just try the insert and respond to the errors(if any) in the CATCH.


TRY..CATCH

I'm required by law to discourage you from using ad-hoc queries in your code. especially around login/authentication related tables. This only encourages SQL injection attacks. Using SP's is always preferred.

Just thinking out loud here, SQL is so good at comparing, filtering, combining, and checking existence of it's data, it would be a waste to burden your application with data manipulation tasks better left inside SQL. I always try interface my UI applications to my databases through stored procedures written on SCHEMAs separate from the tables the data is actually stored in. This lets me write the UI language independent because my data logic isn't tangled in the UI code. Also the separate SCHEMA thing supports security because the SQL account that the UI uses wouldn't have rights on the tables if the database ever got injected with malicious code through the UI.

Hello (again) Bytes!
long time no see.

[Silvia Mattarel]

Thanks for the lesson. I also often prefer Stored Procedure, but in this case I need a rude sql statement because I use this function for many cases and i need to parametrize it a lot.
By the way, It's interesting how you catch the error in the SP. But I would prefer to understand why the error occures, instead of avoid it. This is because I want to be sure that the error couldn't be the symptom of a most tragical error in my application.
However I tried to change the sql statement as this:

"IF NOT EXISTS (SELECT 1 FROM QUESTIONARIO1 WHERE [USER] = '" & Me.IdUser & "') INSERT INTO QUESTIONARIO1 ([USER]) VALUES ('" & Me.IdUser & "')"

This statement return to me an error (I sent the error, occured by an user, in my mail) of VIOLATION OF PRIMARY KEY. I just can't figure out why...

[Silvia Mattarel]

I got an error in this moment in my mail address:

"Statement was: IF NOT EXISTS (SELECT 1 FROM QUESTIONARIO1 WHERE [USER] = 'UD06835') INSERT INTO QUESTIONARIO1 ([USER]) VALUES ('UD06835')
Violation of PRIMARY KEY constraint 'PK__QUESTION__ AA65E05E412EB0B 6'. Cannot insert duplicate key in object 'dbo.QUESTIONAR IO1'. The statement has been terminated."