handle sql error to stop user reputation

[netsooz Amir Hamidi]

hi I have this webpage called register.aspx. user has a form of 4 fields and completes them to get an activation email and ... . Where I want to optimize my code is a place where I want to use a try & catch block where if user enters an existing username he will divert to catch block that tells him to reconsider about his/her username. I mean I want to stop username duplication, what kind of exeption or sql query do I need to use? Please inform me with details since I am kind of new Thanks

Proper Prepration Prevents Poor Performance! h

[Curtis Schlak]

You can put a primary key or unique constraint on the user name column and then, when the SqlException gets thrown because of violation of the constraint, you can display the error message to the user.

"we must lose precision to make significant statements about complex systems." -deKorvin on uncertainty

[netsooz Amir Hamidi]

hi there, for sure this solution is the best but please tell me how should I do this. I have my primary key on ID field but how can I put unique constraint on a field? and what is the code to to get the thrown sqlexception? please give me coding way too.

Proper Prepration Prevents Poor Performance! h

[Curtis Schlak]

A primary key, by definition, has a unique constraint on the column. The code should look something like this:

try
{
... try to insert new record ...
}
catch(SqlException se)
{
... set error message to something appropriate ...
}

If you go to Google and search on "asp.net simple login page," you'll be amazed at the number of different samples for different databases that exist on the Internet. I don't feel like reproducing that here.

"we must lose precision to make significant statements about complex systems." -deKorvin on uncertainty

[Sam Horne]

Instead of relying on exceptions to catch the error, you should just do a database connection yourself selecting the user with the supplied username, if a row is returned, then that username exists. Following code is in VB.NET

Dim strQuery as string = ""
strQuery should be where you select * from users where username = username you're trying to test
Dim conn As New SqlConnection(ConfigurationManager.ConnectionStrings("mainDBConnection").ConnectionString)
conn.Open()
Dim cmd As New SqlCommand(conn.Database & ".dbo." & strQuery, conn)
Dim dr As SqlDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
if dr.HasRows = true then
'DISPLAY ERROR MSG
end if

Obviously you'll have to change around specifics like database connection string etc. Hope this helps.