Unique Keys and Error Handling

How do I trap errors when someone fills out my form and enters in a username (for example) that is already in the database.  Right now, the database throws this back:

Cannot insert duplicate key row in object 'USERS' with unique index 'IX_USERS_1'.

And that's lame.  I want to pop-up a dialog or something.  Can that be done?

PS: I am using Dreamweaver MX 2004 and its built-in bahaviors if that matters.

Thanks,
Michael
movoniAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

David ToddSenior DBACommented:
Hi,

Are you able to trap that error in dreamweaver and inteript it?

Or before you try the insert check if the record exists first

ie pseudo-code

select username
from table1
where UserName = @UserName

if recordcount = 1 then it exists else do the insert

This isn't much for 500 points but it is a start.

I'll have more time later today.

How are you connecting to the database from dreamweaver? What language are you scripting in?

Regards
  David
0
namasi_navaretnamCommented:
You could do few things

You can write a sp to handle this scenario

create procedure update_or_insert_mytable
(
  @User varchar(50) ,
  @Name varchar(50),
  @etc  varchar(50)
)
AS
BEGIN

If Exists(SELECT 1
               from MyTable
               Where User = @User
BEGIN
      Update Mytable
         set Name = @Name,
              Etc = @Etc
       WHERE User = @User
END
   INSERT INTO MyTable (User, Name, Etc) VALUES (@User, @Name, @Etc)
END

Or Insert Statement like

INSERT MyTable (UserId, Name, Etc)
SELECT 'NNN', 'NNN', 'ETC'
FROM MyTable
WHERE NOT EXISTS (SELECT 1
                                FROM MyTable
                                WHERE User <> 'NNN')


 
0
namasi_navaretnamCommented:
Or before updating
You can run a query

select count(*)
from MyTable
Where User = 'MyUser'

If 1 is returned then update other fields
else insert.

But doing this thru sp may be effective.


HTH
0
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

namasi_navaretnamCommented:
Should be "= "not "<>"

INSERT MyTable (UserId, Name, Etc)
SELECT 'NNN', 'NNN', 'ETC'
FROM MyTable
WHERE NOT EXISTS (SELECT 1
                                FROM MyTable
                                WHERE User = 'NNN')



0
movoniAuthor Commented:
Dreamweaver has lots of built-in "behaviors" that are really just plug-n-play for the most part.  It creates a serarate ASP file with the connections string and then includes it as needed. The page that I am dealing with here has a very long form and uses all sort of ASP/VBS and JS in it.  Some my own functions to deal with image uploads and the like and others to deal with db activities.  I'd really rather not post all of it here.

However...

I know where Dreamweaver begins the record insert in the code, so it shouldn't be a problem inserting more code before the insert takes place (actually I've done that to validate form fields already).  

Also, there is more than on field that cannot be duplicated: Username and email (and phone I think).  So checking if the record exists won't do it alone.  Each record is also uniquly identified by an auto-incremented GUID.

Thanks,
Michael
0
movoniAuthor Commented:
Sorry.. the above was to answer David... I'll look at everyone else's post tonight.
0
movoniAuthor Commented:
namasi_navaretnam,

Thanks for the code.  I've never written or called a stored proceedure, just some basic sql.  Can you elaborate on how this can pop-up a dialog saying something like the username is already in use or that email address is already taken?  I kind of get your sp code above, but not 100%.

Your second idea of running the query before the insert makes complete sense to me and sounds like something I can go do real easy.  But how do I actually check if 1 is returned and throw a dialog box up saying that the field is invalide?  Secondly on this same idea, how do I stop the insert from happening if the dialog box is thrown up?

Hopefully that all makes sense :)

Thanks,
Michael
0
namasi_navaretnamCommented:
Michael,

First of all, you mentioned that there are some automatic way fo detecting such errors using Dreamweaver and I am not aware of any features of that nature. I was just thinking that you are asking for a general scenario. But this is how I do same using ASP.

Is there a way to call a stored proc in Dreamwaver? If so there must be a way to get the return code from that stored proc as well.

If stored procs can be called from DreamWeaver then solution will work as outlined below

CREATE PROCEDURE sp_InsertTable
(
  @User varchar(50) ,
  @Name varchar(50),
  @etc  varchar(50)
)
AS
BEGIN

If Exists(SELECT 1
               from MyTable
               Where User = @User
BEGIN
   RETURN -1
END
ELSE
BEGIN
   INSERT INTO MyTable (User, Name, Etc) VALUES (@User, @Name, @Etc)
   RETURN 0
END
RETURN 0
END

Proc listed above returned -1 then you can popup an error message using ALERT function in JAVASCRIPT. If 0 is returned then everything is fine.

Dreamwaever may have some features even to detect the return code from a straight sql. I have not used dreamwaver much so that I cannot comment on that. But search for SQLCODE etc.



0

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
movoniAuthor Commented:
Got it.  When I get home tonight, I will test this stuff.  The person above (David) asked if I could detect the errors and I cannot nor can Dream Weaver that I am aware of.

Thanks for the help thus far.
MT
0
movoniAuthor Commented:
I think I got it.  Thanks nn.
0
namasi_navaretnamCommented:
Glad to help, Michael.
0
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 SQL Server

From novice to tech pro — start learning today.