CODEWITHSUNDEEP
c#sqlsql-server
reddevil54
reddevil54

Reputation: 53

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

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 !!!!!");

Upvotes: 1

Views: 457

Answers (2)

Enkode
Enkode

Reputation: 4783

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.

Upvotes: 1

potatopeelings
potatopeelings

Reputation: 41065

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.

Upvotes: 0

Related Questions