CODEWITHSUNDEEP
asp.netsql-serverstored-procedures
RelatedRhymes
RelatedRhymes

Reputation: 428

Stored procedure doesn't return output

I have a simple stored procedure which inserts a users record in a table and should give out its userid.

Here is the stored procedure:

 Alter proc spRegistration
     @UserId nvarchar(10) output,
     @Name nvarchar(20),
     @EmailAdd nvarchar(20)
 as
 begin
     Declare @Count int;
     Declare @ReturnCode int

     Select @Count = Count(EmailAdd) 
     from tblAllUsers 
     where EmailAdd = @EmailAdd

     if @Count > 0
     begin
         Set @ReturnCode = -1
     end
     else
     begin
         Set @ReturnCode = 1

         Insert into tblAllUsers(Name, EmailAdd) 
         values(@Name, @EmailAdd)
     end

     Select @UserId = UserId 
     from tblAllUsers 
     where EmailAdd = @EmailAdd

     Select @ReturnCode as ReturnCode
 end

I try to get the userid into a textbox like below :

 string CS = ConfigurationManager.ConnectionStrings["hh"].ConnectionString;
    using (SqlConnection con = new SqlConnection(CS))
    {
        con.Open();
        SqlCommand cmd = new SqlCommand("spRegistration", con);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@Name", txtName.Text);
        cmd.Parameters.AddWithValue("@EmailAdd", txtEmailAddress.Text);
        var UserID = cmd.Parameters.AddWithValue("@UserId", SqlDbType.NVarChar);
        UserID.Direction = ParameterDirection.Output;
        int ReturnCode = (int)cmd.ExecuteScalar();
        if (ReturnCode == -1)
        {
            lblRegMessage.Text = "This email has already been registered with us.";
        }
        else
        {
            lblRegMessage.Text = "You were registered successfully.";
            txtUserId.Text=(String)UserID.Value;
        }

The table as well as the stored procedure is far too complex,I have simplified it for better understanding of problem.UserId is Alphanumeric.Dont worry about that.

Putting a break point shows a null against var UserID Where am i going wrong?

Upvotes: 0

Views: 152

Answers (1)

Dgan
Dgan

Reputation: 10295

You need to Use .ToString() on UserID

txtUserId.Text= UserID.Value.ToString();

EDIT:

var UserID = cmd.Parameters.AddWithValue("@UserId", SqlDbType.NVarChar,50);

Upvotes: 1

Related Questions