sql server programming:
I have a store procedure that adds to a table below but i would like it
to be able
to check if a record exists and if it exists return to user that the
User already exists
Which i added below but i get error:-
Cannot insert duplicate key in object 'Users'. The statement has been
terminated.

Any ideas what i'm doing wrong?
thanks



if exists
-- You cannot register usernames already registered on the database
twice.
(
select username from Employees where username = @username
)
return 1 else

INSERT INTO Users
(
FullName,
EmailAddress,
Password
)
VALUES
(
@FullName,
@Email,
@Password
)
SELECT
@UserID = @@Identity

Thanks Adam for the reply but i still get the error..
I get the error when i INSERT a record the second time..
Don't know what could be wrong...
The funniest thing is if i refresh the page and insert another record it
goes in fine.But the second time when its suppose to return the retunr
value i get that error..
Any ideas??


CREATE Procedure CMRC_UserAdd
(
@FullName nvarchar(50),
@Email nvarchar(50),
@Password nvarchar(50)

)
AS

if exists
-- You cannot register usernames already registered on the database
twice.
(
select FullName from CMRC_Users where FullName = @FullName
)
return 1 else

INSERT INTO CMRC_Users
(
FullName,
EmailAddress,
Password
)
VALUES
(
@FullName,
@Email,
@Password
)

GO

You're not inserting a value for the UserName column. You probably already
have a NULL row, so you can't insert another...


--
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--


[quoted text, click to view]

Can you post DDL for your table?

See the following if you need help getting it:

http://www.aspfaq.com/etiquette.asp?id=5006

--
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--


[quoted text, click to view]

This is it Adam.
But just looking at it i can notice somethings like:-

CONSTRAINT [PK_CMRC_Customers] PRIMARY KEY NONCLUSTERED
(
[UserID]
) ON [PRIMARY] ,
CONSTRAINT [IX_Customers] UNIQUE NONCLUSTERED

it doesn't look alright..
Let me know.
Thanks



CREATE TABLE [CMRC_Users] (
[UserID] [int] IDENTITY (1, 1) NOT NULL ,
[FullName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EmailAddress] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[UserType] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Password] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_CMRC_Customers] PRIMARY KEY NONCLUSTERED
(
[UserID]
) ON [PRIMARY] ,
CONSTRAINT [IX_Customers] UNIQUE NONCLUSTERED
(
[EmailAddress]
) ON [PRIMARY]
) ON [PRIMARY]
GO

[quoted text, click to view]

Looks fine to me. I am unable to reproduce the problem based on what
you've posted. Can you post code to reproduce? Here's what I'm doing:

---
use tempdb
go

CREATE TABLE [CMRC_Users] (
[UserID] [int] IDENTITY (1, 1) NOT NULL ,
[FullName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EmailAddress] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[UserType] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Password] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_CMRC_Customers] PRIMARY KEY NONCLUSTERED
(
[UserID]
) ON [PRIMARY] ,
CONSTRAINT [IX_Customers] UNIQUE NONCLUSTERED
(
[EmailAddress]
) ON [PRIMARY]
) ON [PRIMARY]
GO


CREATE Procedure CMRC_UserAdd
(
@FullName nvarchar(50),
@Email nvarchar(50),
@Password nvarchar(50)

)
AS

if exists
-- You cannot register usernames already registered on the database twice.
(
select FullName from CMRC_Users where FullName = @FullName
)
return 1 else

INSERT INTO CMRC_Users
(
FullName,
EmailAddress,
Password
)
VALUES
(
@FullName,
@Email,
@Password
)

GO

exec cmrc_useradd 'abc def', 'abc@def', 'abcdef'
exec cmrc_useradd 'abc def', 'abc@def', 'abcdef'
go

drop Procedure CMRC_UserAdd
go

drop TABLE [CMRC_Users]
go
---

--
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--

its works for you....hm..
I will check it out tommorow and let you know
But the error i get is on:-
[UserID]
) ON [PRIMARY] ,
CONSTRAINT [IX_Customers] UNIQUE NONCLUSTERED
if i remember i will confirm tommorow and let you know
Patrick

[quoted text, click to view]

(snip)
[quoted text, click to view]

Hi naijacoder,

This constraint says that the same email address can only be once in the
table. But the stored proc you posted tests only for duplicates in the
FullName column:

[quoted text, click to view]

If you call the stored proc with a "new" FullName, but an existing
EmailAddress, you'll pass the IF EXISTS test (since there's not yet any
row with the "new" FullName), and get a violation of the UNIQUE
constraint when you attempt to insert the data (since you attempt to
insert a duplicated EmailAddress).

Best, Hugo
--

Thx for the reply Hugo
I will test it and let you know.
So you reckon i can't insert the same EmailAddress?
Or whats the solution to this?

[quoted text, click to view]

Hi Rote/naijacoder,

Since the DDL you listed includes a UNIQUE constraint on the
EmailAddress, you *SHOULD* be unable to insert the same EmailAddress
twice. If you somehow do manage to get two identical addresses in that
column, then you really have reason to worry!!

The solution would in this case be to redo most of the project from the
start. Make sure that you know what data is required for the business
that your database will be facilitating. Normalize the data to at least
third normal form. Find out which constraints do apply and which don't.
Change your current table definitions to table definitions for a
normalized design, with all relevant constraint included, but none
other.

Then, find out how the workflow in the company will be. Write stored
procedures to support that workflow. If it turns out that the workflow
requires data that violates constraint to be held in a table, decide how
to go around that: either re-design the workflow, or tweak the table
design such that those specific constraints may be violated (but only at
that particular point in the workflow).

Best, Hugo
--

Thx Hugo for shedding me some light on this.
Rote