ASP, SQL Insert Problem

[DNKMCA]

Hi,

I'm facing typical problem in SQL Server SP & ASP
When ever i try to insert new record, the record has been inserted and gives
error " User Already Found"
How to solve this issue?

Thanks in Advance.
-DNK


My SP:

-------------------------
CREATE PROCEDURE [dbo].[sp_InsertUser]
( @puserid varchar(50) , @pfname varchar(255), @plname varchar(255),
@pudept varchar(100), @urights varchar(100), @upassw varbinary(50),
@status varchar(50) )
as
if exists(select userid from SALES_USERS where USERID = @puserid)
return 55555
else
insert into SALES_USERS
(USERID,FIRSTNAME,LASTNAME,DEPARTMENT,RIGHTS,UPASSWORD,STATUS)
values (@puserid,@pfname,@plname,@pudept,@urights,@upassw,@status)
return @@error
GO
-------------------------


My ASP:

---------------------------
set connect = server.createobject("adodb.connection")
set cmd = server.createobject("adodb.command")
connect.open SQLConString
set cmd.activeconnection = connect
cmd.commandtype = 4 'sp
cmd.commandtext = "sp_InsertUser"
cmd.parameters(1) = uid
cmd.parameters(2) = ufn
cmd.parameters(3) = uln
cmd.parameters(4) = udt
cmd.parameters(5) = urt
cmd.parameters(6) = upa
cmd.parameters(7) = "Active"
cmd.execute
returnvalue = cmd.Parameters(1)
Set cmd = Nothing
Set connect = Nothing
if returnvalue=0 then
Response.write "User Added"
else
Response.write "User Already Found"
end if
--------------------------------

 

[Bob Barrows [MVP]]

Hi,

I'm facing typical problem in SQL Server SP & ASP
When ever i try to insert new record, the record has been inserted
and gives error " User Already Found"
How to solve this issue?

Thanks in Advance.
-DNK


My SP:

It is a bad idea to use "sp_" to prefix user stored procedures. SQL Server
assumes that any procedure using that prefix is a system procedure and
accordingly looks in the Master database to see if it exests there, causing
a slight performance penalty. Additionally, if you happen to give one of
your procesures the same name as an existing procedure, you will have a hard
time figuring out why your procedure never gets executed ...

( @puserid varchar(50) , @pfname varchar(255), @plname varchar(255),
@pudept varchar(100), @urights varchar(100), @upassw varbinary(50),
@status varchar(50) )

No output parameters ....

as

The lack of a "SET NOCOUNT ON" statement here may prevent you from being
able to retrieve your returned value. You should make it a practice to begin
your procedures with that statement.

if exists(select userid from SALES_USERS where USERID = @puserid)
return 55555
else
insert into SALES_USERS
(USERID,FIRSTNAME,LASTNAME,DEPARTMENT,RIGHTS,UPASSWORD,STATUS)
values (@puserid,@pfname,@plname,@pudept,@urights,@upassw,@status)
return @@error
GO

OK, so you want to use the Return parameter to return the result ...

Do you know the difference between return and output parameters? If not,
read this:
http://groups-beta.google.com/group/microsoft.public.inetserver.asp.general/msg/2c935bd7c531d82b

-------------------------


My ASP:

---------------------------
set connect = server.createobject("adodb.connection")
set cmd = server.createobject("adodb.command")
connect.open SQLConString
set cmd.activeconnection = connect
cmd.commandtype = 4 'sp
cmd.commandtext = "sp_InsertUser"
cmd.parameters(1) = uid
cmd.parameters(2) = ufn
cmd.parameters(3) = uln
cmd.parameters(4) = udt
cmd.parameters(5) = urt
cmd.parameters(6) = upa
cmd.parameters(7) = "Active"
cmd.execute

You should prevent ADO from creating a recordset by telling it that you are
not expecting records to be returned from your procedure by using 128
(adExecuteNoRecords) as the <options> argument in the call to the Execute
method:

cmd.execute ,,128

returnvalue = cmd.Parameters(1)

This parameter is an input parameter. It will not contain the value returned
by your RETURN statements. The first parameter, cmd.Parameters(0), will
contain the return value if you configure it correctly. You might wish to
try my Command object code generator available here:
http://www.thrasherwebdesign.com/index.asp?pi=links&hp=links.asp&c=&a=clear

Set cmd = Nothing
Set connect = Nothing
if returnvalue=0 then
Response.write "User Added"
else
Response.write "User Already Found"

There could be another reason for your procedure to fail besides a duplicate
key violation. Even though your user will probably not need to see the
actual value returned from your procedure, you might want to consider
logging the non-zero value as a troubleshooting aid.

Bob Barrows