Error catching on data duplication in a sql2005 db

Hello, everyone. I am having problems catching a data duplication issue. I hope I can get an answer in this forum. If not, I would appreciate it if someone can direct me to the right forum.

I am working on a vs2005 app which is connected to a sql2005 db. Precisely, I am working on a registration form. Users go to the registration page, enter the data, ie. name, address, email, etc. and submit to register to the site.

The INSERT query works like a charm. The only problem is that I am trying to reject registrations for which an email address was used. I put a constraint on the email field in the table and now if I try to register using an e-mail address that already exists in the database I get a violation error (only visible on the local machine) on the sql's email field, which is expected.

How can I catch that there is already an email address in the database and stop the execution of the code and possibly show a message to the user to use a different address?

Thank you for all your help.

Antonio

you could do something like this in a proc:

declare a parameter @.res of type OUTPUT.

IF NOT EXISTS ( SELET * FROM table WHERE Email = @.EMAIL)

BEGIN

--do the insert

SET @.res = 1

END

ELSE

BEGIN

SET @.res = 2

END

and use the value in @.res in your front end and displaty an appropriate message.

|||

Thank you dinakar. The SQL is working fine. I need help with the front-end. This issue was originally posted on the ASP.NET forum.

Antonio

|||

hi

here's sample code

SqlCommand cmd = new SqlCommand("CustOrderOne", cn);
cmd.CommandType=CommandType.StoredProcedure ;
SqlParameter parm=new SqlParameter("@.CustomerID",SqlDbType.NChar) ;
parm.Value="ALFKI";
parm.Direction =ParameterDirection.Input ;
cmd.Parameters.Add(parm);
SqlParameter parm2=new SqlParameter("@.ProductName",SqlDbType.VarChar);
parm2.Size=50;
parm2.Direction=ParameterDirection.Output;
cmd.Parameters.Add(parm2);
SqlParameter parm3=new SqlParameter("@.Quantity",SqlDbType.Int);
parm3.Direction=ParameterDirection.Output;
cmd.Parameters.Add(parm3);
cn.Open();
cmd.ExecuteNonQuery();
cn.Close();
Response.Write(cmd.Parameters["@.ProductName"].Value);
Response.Write(cmd.Parameters["@.Quantity"].Value.ToString());

thanks,

satish

|||You could also use a TRY/CATCH block to catch any exceptions.