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,@Password)
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\SQLEXPRESS;Initial Catalog=DB;Integrated Security=True")
sqlCmd = New SqlCommand("RegisterUsers", sqlConn)
sqlCmd.CommandType = CommandType.StoredProcedure

With sqlCmd
Parameters.Add("@return_value", SqlDbType.Int, 4).Direction
= ParameterDirection.ReturnValue
Parameters.AddWithValue("@FName", txtFName.Text)
Parameters.AddWithValue("@LName", txtLName.Text)
Parameters.AddWithValue("@UserID", txtUserID.Text)
Parameters.AddWithValue("@Password", txtPassword.Text)
End With

sqlConn.Open()
sqlCmd.ExecuteNonQuery()

If (sqlCmd.Parameters(0).Value = 1) Then
lblMessage.Text = "UserID Already Exists!"
ElseIf (sqlCmd.Parameters(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.ExecuteNonQuery()

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!
*************************************************