Need help in unique key constraints

Question

Hello all, I have built a logic that if i enter duplicate value i should get Message like:"UserName already exists,try another name" but i am getting different message:"Violation of UNIQUE KEY constraint 'UserName'. Cannot insert duplicate key in object 'Login'. The statement has been terminated." In table Login UserName set as Unique key. Here is the code:

protected void BtnSubmit_Click(object sender, EventArgs e)
{
    if (!Page.IsValid)
        return;

    NewuserBAL NUB = new NewuserBAL();

    int intResult = 0;

    string UserName = TxtUserName.Text;
    string Password = PWD.Text;
    string EmailId = Email.Text;


    try
    {
        intResult=NUB.newinsert(UserName, Password, EmailId);
        if (intResult > 0)

            lbldisplay.Text = "New Account Created Successfully";
        else
            lbldisplay.Text = "UserName[<b>"+TxtUserName.Text+"</b>] already exists,try another name";                

        TxtUserName.Text = "";
        PWD.Text = "";
        CnfrmPwd.Text = "";
        Email.Text = "";
    }



    catch(Exception ee)
    {
        lbldisplay.Text = ee.Message.ToString();


    }

    finally
    {
        NUB = null;


    }

Please somebody help me where i made mistake.

Thanks, Masum

Answer 1

Your database is (correctly) catching and preventing the attempt to add a duplicate user because the Username is set as a unique key. This is A Good Thing. Your program should capture the exception , interpert it, and inform the user accordingly.

EDIT: Or, as empi says, check for a duplicate user before attempting the insertion. You will still need to capture the key constraint exception, in case two users try creating the same users at the same time.

Answer 2

You get the exception because you are trying to insert user with same login. You've got two options:

a. before inserting data explicitly check if the user exists (additional select query)

b. catch the exception "Violation of UNIQUE KEY constraint" and then display your custom message