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."

[Jerry Winston]

O.k. let's put on our debugging hats!

I have an unfounded suspicion it has something to do with the collation of your table/database but let's be thorough anyway. Can you post the CREATE scripts for:

• dbo.QUESTIONARI O1
• PK__QUESTION__A A65E05E412EB0B6
• PK__QUESTION__A A65E05E1881A0DE

[Silvia Mattarel]

This is the CREATE SCRIPT of the table named QUESTIONARIO1 that has primary key for field USER with PK__QUESTION__A A65E05E412EB0B6 CONSTRAINT (in the create sql statement I have cut off all others fields - about 100 fields - that are not important and I have left only the primary and another one - TELEFONO1 - not important):

CREATE TABLE [dbo].[QUESTIONARIO1](
[USER] [nvarchar](50) NOT NULL,
[TELEFONO1] [nvarchar](max) NULL,
CONSTRAINT [PK__QUESTION__A A65E05E412EB0B6] 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]

PK__QUESTION__A A65E05E1881A0DE is the CONSTRAINT of another table (also named QUESTIONARIO1) in another db, in another application - the same as the above.
This is because i got error from all my application, all running the statement above and all having the same structure and the same problems.
If I solve the problem for one of them, I will put the right SQL statement in all of them.
Thanks a lot

[Jerry Winston]

In the error message you receive in your email, which XXX_USER is the [User] we are INSERTING INTO QUESTIONARIO1? lOGIN_USER, AUTH_USER or REMOTE_USER?

AUTH_TYPE = Forms

AUTH_USER = BL0210

AUTH_PASSWORD =

LOGON_USER =

REMOTE_USER = BL0210

What is your DB COLLATION?

Also, in your application code. You have the line:

                n = CInt(ExecuteScalar(Me.ConnString, CommandType.Text, sSQL))

ExecuteScalar evaluates to Nothing in VB which when Cint'ed equals 0. Therefore, if your query evaluates Nothing for ANY reason the code (IF n = 0 THEN...) will be entered. I can't say that's a problem per se, just an interesting functionality.

[Silvia Mattarel]

Ok, let me make order:
1. I have change the function as it (see my post above):

Public Sub AddData()
Dim sSQL As String = ""
Try
sSQL = "IF NOT EXISTS (SELECT 1 FROM QUESTIONARIO1 WHERE [USER] = '" & Me.IdUser & "') INSERT INTO QUESTIONARIO1 ([USER]) VALUES ('" & Me.IdUser & "')"
ExecuteNonQuery (Me.ConnString, CommandType.Tex t, sSQL)
Catch ex As Exception Me.SendMailErro r(AppSettings(" EmailFrom"), AppSettings("Em ailTo"), "*** ERRORE ***", "Statement was: " & sSQL & vbCrLf & vbCrLf & ex.Message)
End Try
End Sub

I thrown away old statement "n = CInt(ExecuteSca lar(Me.ConnStri ng, CommandType.Tex t, sSQL))" for testing the existing row, but I got the same error... (I go crazy...I have tried many methods!! Arghhh)

2. The Class (called "Indagine") has "sub new" as it:

Public Sub New(ByVal p As Page, ByVal c As HttpContext)
SyncLock (Me)
Me.IdUser = "VOID"
If Not p.Request.Param s("IdUser") Is Nothing Then Me.IdUser = p.Request.Param s("IdUser").ToS tring
If ((Not c.User Is Nothing) And (Me.IdUser = "VOID" Or Me.IdUser = "")) Then Me.IdUser = HttpContext.Cur rent.User.Ident ity.Name
If Me.IdUser = "" Then Me.IdUser = "VOID"
Me.ConnString = Me.GetCN()
End SyncLock
End Sub

So Me.IdUser is the request params when I (=user "silvia") enter in the form for the user "pippo". Me.IdUser is the Context.User.Id entity.Name when the user "pippo" enter itself in his form. The error occures in both cases. In the error above (see my post above), user itself got the error, so I think he was authenticated (Me.IdUser=AUTH _USER), right?

3. The page that calls the functions New and AddData has this Page_Load function:

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArg s)
Dim ind As New Indagine(Page, Context)
If TestAccesso(ind , Page, Context) = False Then
Response.Redire ct("~/Default.aspx") 'return to homepage instead of enter in the form if the Test returns false
End If
If Not (Page.IsPostBac k Or Page.IsAsync) Then
SyncLock (Me)
ind.AddData()
End SyncLock
End If
End Sub

Public Shared Function TestAccesso(ByV al myindagine As Indagine, ByVal p As Page, ByVal c As HttpContext) As Boolean
Dim test As Boolean = False
Dim ref As String = ""
If Not p.IsPostBack Then
'1) authenticated context.user
If c.User.Identity .IsAuthenticate d Then
test = True
Else
'request params
If (myindagine.IdU ser <> "VOID") Then test = True
End If
End If
Return test
End Function

What is the DB Collation? Do you mean the entire definition? The DB is very large, but the involved table is only QUESTIONARIO1. My pages are asynch, because I use UpdatePanel.