Output Parameter?

[Arpan]

A SQL Server 2005 DB table named "Users" has the following columns:

ID - int (IDENTITY)
FirstName - varchar(50)
LastName - varchar(50)
UserID - varchar(20)
Password - varchar(20)

Before inserting a new record in the DB table, ASP.NET first checks
whether the UserID supplied by the new record already exists or not. If
it exists, the new record shouldn't be inserted in the DB table & the
user should be shown a message saying "UserID already exists". To do
this, ASP.NET uses a stored procedure. If the value returned by the
stored procedure is 1, it means that the UserID already exists. If the
value returned by the stored procedure is 0, then the new record should
be inserted in the DB table. This is how I have framed the stored
procedure:

CREATE PROCEDURE RegisterUsers
@FName varchar(50),
@LName varchar(50),
@UserID varchar(50),
@Password varchar(50),
@return_value int OUTPUT
AS
IF EXISTS(SELECT UserID FROM Users WHERE UserID=@UserID)
BEGIN
SET @return_value=1
END
ELSE
BEGIN
SET @return_value=0
INSERT INTO Users VALUES (@FName,@LName, @UserID,@Passwo rd)
END

& this is how I am invoking the stored procedure in the ASPX page:

<script runat="server">
Sub btnSubmit(ByVal obj As Object, ByVal ea As EventArgs)
Dim sqlCmd As SqlCommand
Dim sqlConn As SqlConnection

sqlConn = New SqlConnection(" Data
Source=MyDB\SQL EXPRESS;Initial Catalog=DB;Inte grated Security=True")
sqlCmd = New SqlCommand("Reg isterUsers", sqlConn)
sqlCmd.CommandT ype = CommandType.Sto redProcedure

With sqlCmd
Parameters.Add( "@return_value" , SqlDbType.Int, 4).Direction
= ParameterDirect ion.ReturnValue
Parameters.AddW ithValue("@FNam e", txtFName.Text)
Parameters.AddW ithValue("@LNam e", txtLName.Text)
Parameters.AddW ithValue("@User ID", txtUserID.Text)
Parameters.AddW ithValue("@Pass word", txtPassword.Tex t)
End With

sqlConn.Open()
sqlCmd.ExecuteN onQuery()

If (sqlCmd.Paramet ers(0).Value = 1) Then
lblMessage.Text = "UserID Already Exists!"
ElseIf (sqlCmd.Paramet ers(0).Value = 0) Then
lblMessage.Text = "Thank You For Registering
End If

sqlConn.Close()
End Sub
</script>
<form runat="server">
<%-- the 4 TextBoxes come here -->
</form>

When I execute the above ASPX code, if the UserID I entered already
exists in the DB table, then ASPX generates the following error:

Procedure or Function 'RegisterUsers' expects parameter
'@return_value' , which was not supplied.

pointing to the line

sqlCmd.ExecuteN onQuery()

Can someone please point out where am I going wrong?

Thanks,

Arpan

[Cowboy \(Gregory A. Beamer\)]

Type is output, not returnvalue.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

*************** *************** *************** ****
Think outside of the box!
*************** *************** *************** ****
"Arpan" <ar******@hotma il.comwrote in message
news:11******** *************@i 3g2000cwc.googl egroups.com...

>A SQL Server 2005 DB table named "Users" has the following columns:

ID - int (IDENTITY)
FirstName - varchar(50)
LastName - varchar(50)
UserID - varchar(20)
Password - varchar(20)

Before inserting a new record in the DB table, ASP.NET first checks
whether the UserID supplied by the new record already exists or not. If
it exists, the new record shouldn't be inserted in the DB table & the
user should be shown a message saying "UserID already exists". To do
this, ASP.NET uses a stored procedure. If the value returned by the
stored procedure is 1, it means that the UserID already exists. If the
value returned by the stored procedure is 0, then the new record should
be inserted in the DB table. This is how I have framed the stored
procedure:

CREATE PROCEDURE RegisterUsers
@FName varchar(50),
@LName varchar(50),
@UserID varchar(50),
@Password varchar(50),
@return_value int OUTPUT
AS
IF EXISTS(SELECT UserID FROM Users WHERE UserID=@UserID)
BEGIN
SET @return_value=1
END
ELSE
BEGIN
SET @return_value=0
INSERT INTO Users VALUES (@FName,@LName, @UserID,@Passwo rd)
END

& this is how I am invoking the stored procedure in the ASPX page:

<script runat="server">
Sub btnSubmit(ByVal obj As Object, ByVal ea As EventArgs)
Dim sqlCmd As SqlCommand
Dim sqlConn As SqlConnection

sqlConn = New SqlConnection(" Data
Source=MyDB\SQL EXPRESS;Initial Catalog=DB;Inte grated Security=True")
sqlCmd = New SqlCommand("Reg isterUsers", sqlConn)
sqlCmd.CommandT ype = CommandType.Sto redProcedure

With sqlCmd
Parameters.Add( "@return_value" , SqlDbType.Int, 4).Direction
= ParameterDirect ion.ReturnValue
Parameters.AddW ithValue("@FNam e", txtFName.Text)
Parameters.AddW ithValue("@LNam e", txtLName.Text)
Parameters.AddW ithValue("@User ID", txtUserID.Text)
Parameters.AddW ithValue("@Pass word", txtPassword.Tex t)
End With

sqlConn.Open()
sqlCmd.ExecuteN onQuery()

If (sqlCmd.Paramet ers(0).Value = 1) Then
lblMessage.Text = "UserID Already Exists!"
ElseIf (sqlCmd.Paramet ers(0).Value = 0) Then
lblMessage.Text = "Thank You For Registering
End If

sqlConn.Close()
End Sub
</script>
<form runat="server">
<%-- the 4 TextBoxes come here -->
</form>

When I execute the above ASPX code, if the UserID I entered already
exists in the DB table, then ASPX generates the following error:

Procedure or Function 'RegisterUsers' expects parameter
'@return_value' , which was not supplied.

pointing to the line

sqlCmd.ExecuteN onQuery()

Can someone please point out where am I going wrong?

Thanks,

Arpan