Errors trying to add users to database using asp.net and c# in visual studio

Question

Like when I add the First name, Last Name, Username and Password. For some reason I cant get it to let me add entries such as the UserId to say 1, 2, 3 and so on with the rest of the information

Instead I am getting this error when I try to register another person or make another entry to GridView1:

An exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.Linq.dll but was not handled in user code. Additional information: Violation of PRIMARY KEY constraint 'PK__User__1788CC4C1B5B052D'. Cannot insert duplicate key

I might not have explained this well. Forgive me.

if your id column is identity column you dont need to insert its value sql will insert automatically. or show your table definition — Preet, Nov 23 '17 at 04:09
@Preet I want to add more users though, but for some reason I cannot. — Raymond Beecham, Nov 23 '17 at 04:15
I think you need to enter the id value as well, first check last id value then add row with incremented id value — Preet, Nov 23 '17 at 04:22
Either you can set autoincrement on id column then it will increment automatically no need to insert it or if you wanna enter id then first check because we cant insert duplicate value for primary key. check this link: https://stackoverflow.com/questions/24425021/violation-of-primary-key-constraint-pk-vehicle-transactions-cannot-insert-du or check this for c#: https://stackoverflow.com/questions/14494462/violation-of-primary-key-constraint-pk-login1-cannot-insert-duplicate-key-in — Preet, Nov 23 '17 at 04:27
@Preet I feel like I almost got it. I get what you are saying, but how do I set it to autoincrement on the id column? — Raymond Beecham, Nov 23 '17 at 04:31

Preet · Accepted Answer · 2017-11-23T06:46:10.133

1

1) Make the column identity: Go to column property and set Identity specification and Is Identity: make it yes and increment by value 1

OR by:

UserID INT IDENTITY(1,1) PRIMARY KEY

2) Check your ID property inside the Item class to ensure that it have attributes like this:

[Column(Storage="_ID", AutoSync=AutoSync.OnInsert,
    DbType="INT NOT NULL IDENTITY", IsPrimaryKey=true, IsDbGenerated=true)]
Look at the IsDbGenerated=true, it is the important guy here.

Maybe you created the DatabaseContext using the designer before adjusting the IDENTITY on the Sql Server, so just regenerate this class (by deleting the table in the designer and dropping it from the Server Explorer again).

OR

In your LINQ to SQL designer, you need to make sure your column is set to:

Auto Generated Value = TRUE
Auto-Sync = ON INSERT

Hope this will help:)

edited Nov 23 '17 at 06:46

answered Nov 23 '17 at 04:33

Preet

984
2
14
34

i think in your table there is already a row with id value 0. you need to delete that or update that as id will start from value 1. then UserID INT IDENTITY(1,1) PRIMARY KEY this will also work – Preet Nov 23 '17 at 04:43
I set identity specification to true, is identity to true and identity increment and identity seed to 1 and got this error: An exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.Linq.dll but was not handled in user code Additional information: Cannot insert explicit value for identity column in table 'User' when IDENTITY_INSERT is set to OFF. – Raymond Beecham Nov 23 '17 at 04:45
yeah is it working now? yes now you cant enter id value in it. just enter first name etc id will increment automatically. also delete first row – Preet Nov 23 '17 at 04:46
Let me try deleting the row with id value 0 – Raymond Beecham Nov 23 '17 at 04:47
Same thing: An exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.Linq.dll but was not handled in user code Additional information: Cannot insert explicit value for identity column in table 'User' when IDENTITY_INSERT is set to OFF. – Raymond Beecham Nov 23 '17 at 04:50
ok i think you are entering id value as well. show your user class. or change identity values to another number say 10. make sure you are not inserting UserId value. dont mention id column in sql query while inserting – Preet Nov 23 '17 at 04:52
even from your logged in page you cant enter or edit id value. just change other fields – Preet Nov 23 '17 at 04:59
I added more images to show my coding. Is my userId still messed up? – Raymond Beecham Nov 23 '17 at 05:01
I think you just simply delete the table and regenerate it with IDENTITY property as true or using UserID INT IDENTITY(1,1) PRIMARY KEY. – Preet Nov 23 '17 at 05:03
If error still there after regenerating table. then there is definitly problem with your User class modal. Its setting userId value – Preet Nov 23 '17 at 05:14
Thank you for your help. I just cannot figure it out. – Raymond Beecham Nov 23 '17 at 05:20
It just doesnt seem to like this code: mydb.SubmitChanges(); – Raymond Beecham Nov 23 '17 at 05:26
It is setting my UserId = 0 but im not sure why – Raymond Beecham Nov 23 '17 at 05:39
yes, do you have modal for user? can you show that. You can add watch for userId and check its value on debugging – Preet Nov 23 '17 at 05:41
I have added an image called Debugger to show it. It's set to zero – Raymond Beecham Nov 23 '17 at 05:44
I also added my usertable again. I'm not sure I did that right either – Raymond Beecham Nov 23 '17 at 05:46
InsertOnSubmit is a function? whats inside it? give values for identity 1 for increment and seed – Preet Nov 23 '17 at 05:47
nUser is inside the parenthesis – Raymond Beecham Nov 23 '17 at 05:48
Sorry m asking is it inbuilt function you using or function in your code? if so then that function is setting id value – Preet Nov 23 '17 at 05:49
It is not inbuilt, but rather a function in my code. You think that cod may be the issue? – Raymond Beecham Nov 23 '17 at 05:50
Should I delete the InsertOnSubmit? – Raymond Beecham Nov 23 '17 at 05:54
No just show the code or find is there any line in it that setting userid value. just delete that line – Preet Nov 23 '17 at 05:55
There is no input from a textbox that is taking a value for userId. I'm not sure why it is setting it automatically if it is. I know it is something that isn't complicated but I cannot figure it out for the life of me. – Raymond Beecham Nov 23 '17 at 05:58
Where is your sql query for insert? or function where you inserting values in table. sure it could be your function InsertOnSubmit. just show lines of code of this function. if no input from textbox then it could be from code like int UserId = 0; or something. you need to show all code else we couldnt find it – Preet Nov 23 '17 at 06:00
So your saying just set a value for userId under the InsertOnSubmit like UserId = 1? – Raymond Beecham Nov 23 '17 at 06:04
No, i am saying dont set value for UserId anywhere. neither 0 nor 1. can you show code lines of InsertOnsubmit()? or show your insert sql query – Preet Nov 23 '17 at 06:04
I see, but I don't have it set to anything anywhere. – Raymond Beecham Nov 23 '17 at 06:06
Show your InsertOnSubmit function or you dont want to? – Preet Nov 23 '17 at 06:09
lol sorry for being a noob, but I have no idea what you mean or how? – Raymond Beecham Nov 23 '17 at 06:10
ok so InsertOnSubmit is a built in function? or stored procedure? i just wanna see your sql insert query part. exp: INSERT INTO users(...) VALUES(..). do you have this anywhere in your code – Preet Nov 23 '17 at 06:13
ok now i got it its linq function sorry not familiar with linq. let me find it – Preet Nov 23 '17 at 06:14
Oh! I think you were talking about using adaptors to create queries. Sorry yes I am using linq functions. – Raymond Beecham Nov 23 '17 at 06:16
see updated answer it will surely do the trick. did you drop and regenerated column? – Preet Nov 23 '17 at 06:19
you need to set autogenerated property or isdbgenerated property for userId to true. Auto Generated Value in the UI, or IsDbGenerated in the xml – Preet Nov 23 '17 at 06:22
Ok so let me re-iterate. I think I know what you mean. You're saying delete the user table that I dragged and dropped into my linqtosql classes and then drag and drop it again but make sure all those attributes are set to those values? How would I set those values though. Just right click properties while i'm in my table on the UserId row? – Raymond Beecham Nov 23 '17 at 06:25
ok wait...just open your linq to sql designer. make sure your column is set to: Auto Generated Value = TRUE Auto-Sync = ON INSERT. no need to do anything else – Preet Nov 23 '17 at 06:27
Ok got it. This is where I drag and drop my table into correct? – Raymond Beecham Nov 23 '17 at 06:27
also i can see in your table script there is : IDENTITY but it should be IDENTITY(1,1) – Preet Nov 23 '17 at 06:30
CREATE TABLE [dbo].[User] ( [UserId] INT IDENTITY(1,1) PRIMARY KEY, [First Name] NVARCHAR (50) NOT NULL, [Last Name] NVARCHAR (50) NOT NULL, [User Name] NVARCHAR (50) NOT NULL, [Password] NVARCHAR (50) NOT NULL, ); This is my table right now though and my identity specification is set to false again. is this correct? – Raymond Beecham Nov 23 '17 at 06:32
IDENTITY(1,1) is setting identity to true not false. after generating table now in linqtosql designer: for your UserId column set Auto Generated Value = TRUE Auto-Sync = ON INSERT – Preet Nov 23 '17 at 06:33
1

Thank you. I learned a lot as well. Can't lie I had a moment where I didn't think It could be done easily. – Raymond Beecham Nov 23 '17 at 06:36
Preet I had one more question. I noticed when I delete an entry and make an entry again it doesn't take it's place. Such as if I delete 1 and then register another user they have the userId of 2 how would I make it revert back to the Id that was deleted? In the real world would it be important not to do that in a company? Thanks. – Raymond Beecham Nov 23 '17 at 06:46
yes, we cant do it. now UserId column is generated automatically. it cant have duplicate value but if you really want to do that then you cant use autoincrement property you have to do it by code like i said previously(select id from last row then increment it and insert incremented value in new row) – Preet Nov 23 '17 at 06:47
1

No problem with that. I would think in a real scenario within a company or university you wouldn't want duplicates. – Raymond Beecham Nov 23 '17 at 06:48

score 0 · Answer 2 · answered Nov 23 '17 at 04:31

0

So looks like your Key isn't auto populated. This will cause a big issue because a primary key can't be null.

In your create table TSQL in picture "User table" change the line to:

[UserID] INT NOT NULL

to

UserID INT IDENTITY(1,1) PRIMARY KEY

That should fix your problem

Caz

answered Nov 23 '17 at 04:31

Caz1224

1,539
1
14
37

Tried it and got this error: Violation of PRIMARY KEY constraint 'PK__User__1788CC4C1B5B052D'. Cannot insert duplicate key in object 'dbo.User'. The duplicate key value is (0). The statement has been terminated. – Raymond Beecham Nov 23 '17 at 04:38
Try clearing the table completely and try again, it may have some muddles data in there.Or you can just write UserID INT IDENTITY(20,1) PRIMARY KEY that will start the PK at 20 not 1 – Caz1224 Nov 23 '17 at 04:47
I cleared the tables data and am getting this with trying to add any entry: An exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.Linq.dll but was not handled in user code Additional information: Cannot insert explicit value for identity column in table 'User' when IDENTITY_INSERT is set to OFF. – Raymond Beecham Nov 23 '17 at 04:49
So what that means is somewhere, you are setting a value for the user model. In your code anywhere does it say UserID = – Caz1224 Nov 23 '17 at 04:51
Could it be this: var deleteQuery = from a in myDB.Users where a.UserId == int.Parse(TextBox1.Text) select a; – Raymond Beecham Nov 23 '17 at 04:54
Nar deleting is fine, that is pretty much the point of ID's - It will only be on insert. Are you adding data on app launch or something? – Caz1224 Nov 23 '17 at 04:56
I added more images for the coding part please let me know what i might be missing – Raymond Beecham Nov 23 '17 at 05:00
Very odd, that all looks fine. Could your right click the table and select "Generate Create Script" and post that... not 100% on the wording but it will look something like that. – Caz1224 Nov 23 '17 at 05:03
Won't let me generate a script it gives me this: Update cannot proceed due to validation errors. Please correct the following errors and try again. SQL71533 :: A table or table-valued function ([dbo].[User]) contains more than one primary key. SQL71531 :: The table or view ([dbo].[User]) has more than one clustered index. – Raymond Beecham Nov 23 '17 at 05:04
We might be getting closer to the issue, seems like something gross is happening with your table. I would drop and remake the table and remember that UserID INT IDENTITY(1,1) PRIMARY KEY at the start. It was only 4 fields right? – Caz1224 Nov 23 '17 at 05:06
5 fields in the table. Source Error: Line 24: Line 25: mydb.Users.InsertOnSubmit(nUser); Line 26: mydb.SubmitChanges(); Line 27: Line 28: var selQuery = from a in mydb.Users – Raymond Beecham Nov 23 '17 at 05:08
And that nUser object, does it have a UserID value in there? Pause your debugger on that line and add nUser into your local watch and see whats specified in there. If you make a new object, based off a model it might be adding the UserID by default and setting it to 0 – Caz1224 Nov 23 '17 at 05:11
Thank you for your help. I'm afraid I just cannot figure it out. – Raymond Beecham Nov 23 '17 at 05:21
+ nUser null User – Raymond Beecham Nov 23 '17 at 05:23
You were right it is adding the UserId value by default to 0, but why? – Raymond Beecham Nov 23 '17 at 05:40

score 0 · Answer 3 · answered Nov 23 '17 at 04:31

You are getting that error is because your User table's primary key column is not set to be an identity column. You are not setting the value of this column from your C# code, but the default is zero. Most likely you inserted one record successfully so a record with zero as the primary key got inserted. Now you are trying to insert more rows and another zero is going in so it is throwing that exception.

Fix

Make sure your User table's primary key is set to an indentity column so it auto increments. If you do not want it to be identity and increment automatically, then you need to pass the value in but you have to make sure it is unique or you will get the same error. I would just make it identity.

How do I set it to be a identity column? – Raymond Beecham Nov 23 '17 at 04:38 — Raymond Beecham, Nov 23 '17 at 04:38

Errors trying to add users to database using asp.net and c# in visual studio

3 Answers3