How do I check for a SQL server record before attempting and update?

I have an application that updates an SQL Server Express database with values from a web form. This is done in the code behind VB in the button_click event. How do I check for an existing record with a value the same as the one trying to be created. Currectly, if someone trys to create a new record with the same name as a record that already exists the user gets a funky error message and bombed out of the web page. I just want to warn them that the records already exists and send them back to the web form to try again.

I need a good code example of how to do this.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

The below isn't exact code but gives you a good idea... you obviously have to put connect to db strings in etc...

isArray() is VB and just tells you if any results are found whatsoever, if so obviously throw an error, if not continue and UPDATE as you already are.
do a SELECT statement - maybe in a function.
function checkUserExists()
    checkUserExists = SELECT username FROM users WHERE username='request.form("user")'
end function
then in the ASP page do:
if isArray(checkUserExists)<>"" then
    response.write "user exists"
    UPDATE users SET username=request.form("user")
end if

Open in new window

apwbeAuthor Commented:
Would the fuction be put into the default.aspx page or the default.aspx.vb codebehind page?

in particular I  have a SQL Server Express database (Whos_Who.dbo) and a table called "Who" that has "Name" as a primary key. I only want one record per person so when someone trys to input a new record using a name value that already exist they can not insert the new record. The insertion takes place on the button click event and coded on the aspx.vb page. I would like to use an error popup message to alert the user that the record already exists an then upon them acknowledging the fact take them back to the web form.  which is all written in ASP on the default,aspx page.
apwbeAuthor Commented:
PS currently when someone trys to insert a record that already exists they get "violation of Primary Key constraint 'PK_Who" Cannot insert duplicate key in object 'dbo.who' . Do you know of a way to use this error code to my advantage when trying to accomplish what I have described. Maybe I am going about this all wrong and should be using some kind of custom error trapping and messaging.

Any ideas?
If you submit the form to the same page you're working on, above the HTML in ASP tags do the select lookup, if isArray() exists then show a javascript alert which once clicked returns you back a page to your form so you can change your form to a value that doesn't exist. See code example below:
If isArray(yourSELECTlookup)<>"" then
    OnLoadString="alert('Sorry, entry already exists, try again'); document.location='javascript:history.go(-1)'"
End If
<BODY onLoad="OnLoadString">

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
apwbeAuthor Commented:
I never really got this to work but the code looked correct. thanks for the effort and I am sorry I took so long.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Development

From novice to tech pro — start learning today.