C# & SQL Server : how would I add validation to stop duplicate usernames being inserted

Question

I have a form in my windows form application that is for admin users so that they can add, edit and delete users. The problem I have is that admin can insert a new user which has the same username as another user which results in both users having duplicate usernames when logging into the system.

I just need some basic validation so that the username won't be inserted if its a duplicate but I'm not sure how I can add it to the dataadapter.

SqlConnection con = new SqlConnection(@"Data Source=(LocalDB)\v11.0;AttachDbFilename=|DataDirectory|Data.mdf;Integrated Security=True");
con.Open();

SqlDataAdapter sda = new SqlDataAdapter("INSERT INTO Login (FirstName, Role, Username, Password, Surname) VALUES ('" + txtfirstname2.Text + "','" + rolecombo.Text + "','" + txtusername2.Text + "','" + txtpassword2.Text + "','" + txtsurname.Text + "')", con);

sda.SelectCommand.ExecuteNonQuery();
con.Close();

MessageBox.Show("SAVED SUCCESSFULLY !!!!!");

Add a unique constraint in the database. Also look into using parameterized queries... — sgeddes, Mar 10 '16 at 00:58
I forgot to mention i'm using visual studio 2012 so i'm not sure how to add unique constraints in that as it works a little different from SQL management studio. does it work similar to how foreign keys are added? — reddevil54, Mar 10 '16 at 01:00
You're connecting to SQL Server, so you can add constraints to the database itself and not try to do it in code. Doing it in the DB means that no matter how the data is accessed (even if it's not through your application) the constraint still applies. And good grief - after the hundreds of posts here and elsewhere about the dangers of SQL concatenation, here's another poster doing it. Search here for *parameterized queries C#* quickly, before the bad habit gets too ingrained. — Ken White, Mar 10 '16 at 01:13
I've added the unique constraint but when a duplicate username is entered i get an error about the unique constraint.. Although this is good as it means the constraint is working, i'd like a messagebox that displays instead of the error so that the user can continue in the application. — reddevil54, Mar 10 '16 at 01:37
Lookup `try catch` -- easy enough to catch the exception and report accordingly. Please look into cleaning up your code though -- look into `using` statements. And don't disregard our previous comments -- parameterized queries are easy to implement and well worth the 5 minute investment. — sgeddes, Mar 10 '16 at 01:49
I will definitely take your advice on board, it's my first time using c# so my code is very basic, i am learning things each day and will definitely make a cleaner application next time. — reddevil54, Mar 10 '16 at 02:15
Make the effort to learn properly from the start, so you don't have to undo bad habits later. :-) It's much easier to be a non-smoker if you never start in the first place; the same goes for learning to write proper code. It's much easier to write good code if you don't learn how to write bad code and use being new as an excuse for doing so. — Ken White, Mar 10 '16 at 02:32

score 1 · Accepted Answer · edited May 23 '17 at 12:31

1

Create an insert trigger or a unique key index:

Trigger to prevent Insertion for duplicate data of two columns

Something like this:

CREATE TRIGGER MyTrigger ON dbo.MyTable
AFTER INSERT
AS

if exists ( select * from table t 
    inner join inserted i on i.name=t.name and i.date=t.date)
begin
    rollback
    RAISERROR ('Duplicate Data', 16, 1);
end
go

That's just for insert, you might want to consider updates too.

Update

A simpler way would be to just create a unique constraint on the table, this will also enforce it for updates too and remove the need for a trigger. Just do:

ALTER TABLE [dbo].[TableName]    
ADD CONSTRAINT [UQ_ID_Name_Date] UNIQUE NONCLUSTERED
(
    [Name], [Date]
)

and then you'll be in business.

edited May 23 '17 at 12:31

Community

1
1

answered Mar 10 '16 at 01:05

Enkode

4,515
4
35
50

I've tried out the unique constraint method instead of the trigger method but when a duplicate username is entered i get an error about the unique constraint.. Although this is good as it means the constraint is working, how can i add a messagebox which displays instead of the error so that the user can continue in the application – reddevil54 Mar 10 '16 at 01:50
You most likely need and should do a try / catch / finally block and again there are several stack overflow examples for this: http://stackoverflow.com/questions/7024109/how-can-i-get-an-error-message-that-happens-when-using-executenonquery – Enkode Mar 10 '16 at 05:40

score 0 · Answer 2 · answered Mar 10 '16 at 01:02

0

Add a WHERE NOT EXISTS clause to your INSERT query to check if the username exists, then use the return value of ExecuteNonQuery (number of rows affected) to figure out if your user was inserted. 0 => not inserted because there was a duplicate.

answered Mar 10 '16 at 01:02

potatopeelings

40,709
7
95
119

C# & SQL Server : how would I add validation to stop duplicate usernames being inserted

2 Answers2