Re: Output Parameter?
- From: "Cowboy \(Gregory A. Beamer\)" <NoSpamMgbworld@xxxxxxxxxxxxxxxxxx>
- Date: Fri, 25 Aug 2006 12:55:11 -0500
Type is output, not returnvalue.
--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
*************************************************
Think outside of the box!
*************************************************
"Arpan" <arpan_de@xxxxxxxxxxx> wrote in message
news:1156516469.274853.51230@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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
.
- References:
- Output Parameter?
- From: Arpan
- Output Parameter?
- Prev by Date: Re: Convert date to: dd mon yyyy hh:mi(24h)
- Next by Date: Setting the Session timeout for a specific page.
- Previous by thread: Output Parameter?
- Next by thread: Re: Confused with autoeventwireup
- Index(es):
Relevant Pages
|
