Solved

Unique Keys and Error Handling

Posted on 2003-11-10
11
443 Views
Last Modified: 2009-12-16
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
0
Comment
Question by:movoni
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
11 Comments
 
LVL 35

Expert Comment

by:David Todd
ID: 9717252
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
 
LVL 15

Expert Comment

by:namasi_navaretnam
ID: 9717660
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
 
LVL 15

Expert Comment

by:namasi_navaretnam
ID: 9717671
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 15

Expert Comment

by:namasi_navaretnam
ID: 9717695
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
 

Author Comment

by:movoni
ID: 9717735
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
 

Author Comment

by:movoni
ID: 9717774
Sorry.. the above was to answer David... I'll look at everyone else's post tonight.
0
 

Author Comment

by:movoni
ID: 9717833
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
 
LVL 15

Accepted Solution

by:
namasi_navaretnam earned 500 total points
ID: 9718138
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
 

Author Comment

by:movoni
ID: 9718425
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
 

Author Comment

by:movoni
ID: 9726661
I think I got it.  Thanks nn.
0
 
LVL 15

Expert Comment

by:namasi_navaretnam
ID: 9726729
Glad to help, Michael.
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question