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__AA65E05E1881A0DE'. Cannot insert duplicate key in object 'dbo.QUESTIONARIO1'.
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
13 10604
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.
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 (TestExistingRow) return false. So...why sometimes it return false but then i get the "VIOLATION OF PRIMARY KEY" error?
However, Thanks for your attention! :)
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?
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(ExecuteScalar(Me.ConnString, CommandType.Text, 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_QUESTIONARIO1] PRIMARY KEY CLUSTERED
(
[USER] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = 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.Identity.Name (as you can see in my code above)
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?
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.....why????
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
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.
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...
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__AA65E05E412EB0B6'. Cannot insert duplicate key in object 'dbo.QUESTIONARIO1'. The statement has been terminated."
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.QUESTIONARIO1
- PK__QUESTION__AA65E05E412EB0B6
- PK__QUESTION__AA65E05E1881A0DE
This is the CREATE SCRIPT of the table named QUESTIONARIO1 that has primary key for field USER with PK__QUESTION__AA65E05E412EB0B6 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__AA65E05E412EB0B6] PRIMARY KEY CLUSTERED
(
[USER] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
PK__QUESTION__AA65E05E1881A0DE 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
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.
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.Text, sSQL)
Catch ex As Exception Me.SendMailError(AppSettings("EmailFrom"), AppSettings("EmailTo"), "*** ERRORE ***", "Statement was: " & sSQL & vbCrLf & vbCrLf & ex.Message)
End Try
End Sub
I thrown away old statement "n = CInt(ExecuteScalar(Me.ConnString, CommandType.Text, 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.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
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.Identity.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.EventArgs)
Dim ind As New Indagine(Page, Context)
If TestAccesso(ind, Page, Context) = False Then
Response.Redirect("~/Default.aspx") 'return to homepage instead of enter in the form if the Test returns false
End If
If Not (Page.IsPostBack Or Page.IsAsync) Then
SyncLock (Me)
ind.AddData()
End SyncLock
End If
End Sub
Public Shared Function TestAccesso(ByVal 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.IsAuthenticated Then
test = True
Else
'request params
If (myindagine.IdUser <> "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.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Prince Kumar |
last post by:
Is there any way I can define an Unique constraint or unique index
which allows more than one null values for the same column combination
in DB2?
ie, If my index is defined on (col3, col4) where...
|
by: David Garamond |
last post by:
postgresql 7.4.0, redhat 7.3 (under vmware 4.0 on win2k)
Windows crashed and some of the files on Redhat got corrupted, including
some files in /var/lib/pgsql/data/pg_xlog/. When I tried to start...
|
by: Mike Hunter |
last post by:
(Please CC me on any replies as I'm not on the list)
Hi,
After a recent power failure, a program that uses a pgsql backend
(netdisco) started to send me nastygrams. I tried the author's...
|
by: borajetta |
last post by:
ERROR: duplicate key violates unique constraint "pg_class_oid_index"
I found a post earlier but wanted to post up my info as the other persons did not look like the same type of error:
I tried...
|
by: andro |
last post by:
Hi everybody!
I have several tables from which I want to exract the SAME value (along with
other referenced data).
All the values are in the same column within the tables.
How can I achieve...
|
by: embarkr |
last post by:
I am getting the error:
"Violation of PRIMARY KEY constraint 'PK_tblCustomsTariffTreeMap'. Cannot insert duplicate key in object 'dbo.tblCustomsTariffCodeTreeMap'."
However, the record I am...
|
by: Pietro Cerutti |
last post by:
Dear all,
Consider:
include <stdio.h>
int main(void)
{
printf("%d%d%d");
return (0);
}
|
by: Good Guy |
last post by:
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Customers_Contact".
The conflict occurred in database "BreakAway", table "dbo.Contact", column 'ContactID'.
The statement has...
|
by: dowlingm815 |
last post by:
I am attempting to create a table with an index. however, i am receiving an error code "3371: Cannot find table or constraint" as soon as it reads the "create index" statement. The code is as...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
| |