Solved

Unique Keys and Error Handling

Posted on 2003-11-10
11
440 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
  • 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
 
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

757 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now