SQL SERVER 2005 return value

Question

Hi all I have the following stored procedure

@UserName varchar(150),
    @UserEmail varchar(300),
    @UserPassword varchar(150), 
    @ContactNumber varchar(150),
    @ContactMobile varchar(150),
    @AreaOfCountry varchar(150),
    @UserId int OUTPUT,
    @AllreadyReg int OUTPUT
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    --DECLARE @UserId int, @AllreadyReg int
    IF (SELECT COUNT(UserId) FROM Users WHERE (UserName = @UserName) OR (UserEmail = @UserEmail)) > 0
    BEGIN
        SET @UserId = 0
        SET @AllreadyReg = 1    
    END
    ELSE 
    BEGIN
        INSERT INTO Users (UserName,UserEmail,UserPassword,ContactNumber,ContactMobile,AreaOfCountry) VALUES (@UserName,@UserEmail,@UserPassword,@ContactNumber,@ContactMobile,@AreaOfCountry)
        SELECT @UserId = SCOPE_IDENTITY()
        SET @AllreadyReg = 0    

    END

however when I use it using c# and asp.net its not returning anything, however when I just execute it it does have a results @UserId and @AllreadyReg but the return value is 0 and a single field.

my c# code is below but it never has any rows

using (SqlConnection con = new SqlConnection(connectionString))
        {
            Response.Write("Line 61");
            using (SqlCommand cmd = new SqlCommand("spR_Register", con))
            {
                Response.Write("line 64");
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@UserName", TxtUsername.Text.Trim());
                cmd.Parameters.AddWithValue("@UserEmail", TxtEmail.Text.Trim());
                cmd.Parameters.AddWithValue("@UserPassword", TxtPassword.Text.Trim());
                cmd.Parameters.AddWithValue("@ContactNumber", TxtPhone.Text);
                cmd.Parameters.AddWithValue("@ContactMobile", TxtMobile.Text);
                cmd.Parameters.AddWithValue("@AreaOfCountry", TxtAreaOfCountry.SelectedValue);
                cmd.Parameters.AddWithValue("@UserId", ParameterDirection.Output);
                cmd.Parameters.AddWithValue("@AllreadyReg", ParameterDirection.Output);
                con.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                if (reader.HasRows)
                {
                    Response.Write("line 78");
                    etc etc

can anyone help

thanks

You shouldn't be calling `ExecuteReader` - your stored proc doesn't generate a *resultset* - it places its responses in output parameters. Marc's [answer](http://stackoverflow.com/a/19930816/15498) shows how it should be done. — Damien_The_Unbeliever, Nov 12 '13 at 13:46
if you want to get output parameter you can refer to [This answer][1] [1]: http://stackoverflow.com/questions/3433694/how-to-run-the-stored-procedure-that-has-output-parameter-from-c — Patrick Zeng, Nov 12 '13 at 13:46
btw - if the command *did* generate a result set, you should use `using` around the reader. It doesn't apply in this case because there *is no resultset*. — Marc Gravell, Nov 12 '13 at 13:48

score 4 · Accepted Answer · answered Nov 12 '13 at 13:44

4

With the edit: the mistake is using ExecuteReader on a command that doesn't select a data grid - that should be done with ExecuteNonQuery.

The important thing here is how the parameter is added. For example:

var alreadyReg = cmd.CreateParameter();
alreadyReg.Direction = System.Data.ParameterDirection.Output;
alreadyReg.ParameterName = "AllreadyReg";
alreadyReg.DbType = DbType.Int32;
cmd.Parameters.Add(alreadyReg);
//...
cmd.ExecuteNonQuery();
//...
int val = (int)alreadyReg.Value;

Depending on the type of DbCommand object, there may be an overload that accepts all of these in one line - the above assumes just DbCommand. With SqlCommand, you can simplify a bit:

var alreadyReg = cmd.Parameters.Add("AllreadyReg", SqlDbType.Int);
alreadyReg.Direction = ParameterDirection.Output;
//...
cmd.ExecuteNonQuery();
//...
int val = (int)alreadyReg.Value

answered Nov 12 '13 at 13:44

Marc Gravell

1,026,079
266
2,566
2,900

I understand what your saying but the if(reader.HasRows) is always null – andrew slaughter Nov 12 '13 at 13:45
@andrewslaughter I would expect that - this query doesn't `SELECT` anything. Look carefully - you'll see I'm using `ExecuteNonQuery`. – Marc Gravell Nov 12 '13 at 13:46
Brilliant, you were half right in that it doesn't select anything. Ive modified it a bit and added this to the end of the stored procedure SELECT @UserId,@AllreadyReg which then works great – andrew slaughter Nov 12 '13 at 13:58
@andrewslaughter it would have been fine with output parameters too... and slightly more efficient too. But making them a `select` works too – Marc Gravell Nov 12 '13 at 14:02

LINQ2Vodka · Answer 2 · 2013-11-12T13:52:49.183

0

Here

cmd.Parameters.AddWithValue("@UserId", ParameterDirection.Output);

wrong usage of AddWithValue. The second parameter is parsed as parameter value, not direction type. Use proper parameter contructor and the add the result to collection.

edited Nov 12 '13 at 13:52

answered Nov 12 '13 at 13:38

LINQ2Vodka

2,996
2
27
47

score 0 · Answer 3 · answered Nov 12 '13 at 13:44

0

You might have already handled it, but just in case, make sure to specify direction of parameter in c# code.

answered Nov 12 '13 at 13:44

Sateesh Pagolu

9,282
2
30
48

SQL SERVER 2005 return value

3 Answers3