Prevent duplicate values

[AngelicOne]

I have a database that needs to have unique values wherein it will provide an error saying "Username already exist" and will not add to database. How to do this?

[Momerath]

You can select the user name from the database to see if it is already used:
SELECT COUNT(*) FROM user_table WHERE userid = 'WhateverTheUserTypedIn'
If this returns anything other than zero, that userid is already in use.

In some databases you could make the userid field an identity field, which means all entries have to be unique.

[Geekitygeek]

Alternatively, you can use IF ELSE in queries with SQL Server 2000 or newer.
You can check if the item exists, if it doesn't then insert it. I have done something like this in the past:

IF EXISTS ( SELECT * FROM user_table WHERE userid = @userID )
BEGIN
SELECT False;
END
ELSE
BEGIN
INSERT INTO user_table VALUES ( @userID )
SELECT True;
END

By selecting true or false it returns a boolean value to indicate success. If you use an identity column for primary key you can return Scope_Identity on success or null on fail.

I'm not sure if the syntax is supported in Visual Studio, i was running all my queries in stored procedures.

[AngelicOne]

Could someone post some more sample codes? I'm a bit confused.

[Momerath]

Psuedo code:

Get the username
Use the select from above to get the count of the username
if count == 0 then
add user to the database
else
display "user name already exists"
endif

[AngelicOne]

create procedure usp_qinsertauthor
@authorname nvarchar(75)
AS
select count(*) from Author where Author_Name = @authorname
if count is null
insert into Author values (@authorname)
select true
else
select false

it gives me error. else is wrong and there's no such thing as count

[Momerath]

You'll need to assign the select statement to a variable, and don't check if it is null, check if it is zero.

[AngelicOne]

create procedure usp_qinsertauthor
@authorname nvarchar(75)
AS
declare @count nvarchar(75)
select @count=count(*) from Author where Author_Name = @authorname
if @count=0
insert into Author values (@authorname)
--?
else
--??

This seems to be working now though using bool as the return value(select false) doesn't work. What's the best way for this?

[Lusiphur]

For return values use return...

ie:
if @count=0
insert into Author values (@authorname)
return 1
else
return 0

if 1 is returned then it succeeded, if 0 is returned then it failed.

At that point you simply check the returned value as it is received from the SQL server on completion of the stored proc and act accordingly in your C# program.

That being said, you can also be a bit more expansive with your returns and do an additional checking step to confirm successful insertion of the new record. This would require an additional return value however such that you have one for the name already in use (fail), one for name not in use but insert failed (for whatever unforseen reason) and one for successful insertion.

[AngelicOne]

I have done this

private Int32 check(string u)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "usp_qinsertauthor";
            cmd.Connection = con;
            cmd.Parameters.Add("@authorname", SqlDbType.NVarChar).Value = author.Text;
            SqlParameter p1 = new SqlParameter("@ret", SqlDbType.Int);
            p1.Direction = ParameterDirection.ReturnValue;
            cmd.Parameters.Add(p1);
            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();
            Int32 k = Convert.ToInt32(cmd.Parameters["@ret"].Value);
            cmd.Dispose();
            return k;
        }