CODEWITHSUNDEEP
c#asp.netsqlsql-servert-sql
andrew slaughter
andrew slaughter

Reputation: 1127

SQL SERVER 2005 return value

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

Upvotes: 1

Views: 159

Answers (3)

LINQ2Vodka
LINQ2Vodka

Reputation: 3036

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.

Upvotes: 0

Sateesh Pagolu
Sateesh Pagolu

Reputation: 9606

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

Upvotes: 0

Marc Gravell
Marc Gravell

Reputation: 1064054

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

Upvotes: 4

Related Questions