Why a SqlParameter does not get value?

Question

I am trying to initialize the value of a SqlParameter with a string. but why does it not get the value ?

This is what I tried:

 int loadChart(string status)
 {
        connect();

        SqlParameter[] parameters ={
                                      new SqlParameter("status", SqlDbType.VarChar, 10),
                                      new SqlParameter("count", SqlDbType.Int, 4)
                                  };

        parameters[0].Value = status;
        parameters[1].Direction = ParameterDirection.Output;

        SqlCommand cmd = new SqlCommand("sp_Arsenic_getSourceStatusCount", objConnection);

        foreach (SqlParameter param in parameters)
        {
            cmd.Parameters.Add(param);
        }

        cmd.ExecuteNonQuery();

        disConnect();

        int count;
        count =(int) parameters[1].Value;

        return count;
    }
}

The stored procedure:

alter procedure sp_Arsenic_getSourceStatusCount
@status varchar(10),
@count int
as
select @count=COUNT(status) from Arsenic_WaterSource where status=@status
return 1

Inserting a breakpoint I have discovered that the string variable status gets its value "safe" but at parameters[0].Value = (string) status; line parameters[0].value gets null. How to resolve this issue ?

What are you passing as Status? I used the string "Test" and it set the value. — Decker97, Oct 01 '12 at 20:08
try removing that.. i dont see anything weird apart from that — Thousand, Oct 01 '12 at 20:08
I am passing "safe" as status value and yes its length is <=10 — Md. Arafat Al Mahmud, Oct 01 '12 at 20:11
Seems to work for me - are you not getting it in the stored proc? Maybe an @ sign before the variable name? — Decker97, Oct 01 '12 at 20:14

score 3 · Answer 1 · answered Oct 01 '12 at 20:29

You did not define your @count parameter as an OUTPUT parameter in your procedure:

alter procedure sp_Arsenic_getSourceStatusCount
@status varchar(10),
@count int OUTPUT  /* <--------------  */
as
select @count=COUNT(status) from Arsenic_WaterSource where status=@status
return 1

Lawrence Johnson · Answer 2 · 2012-10-01T20:34:15.390

1

Try this:

connect();

SqlCommand cmd = new SqlCommand("sp_Arsenic_getSourceStatusCount", objConnection);

cmd.Parameters.Add(new SqlParameter("@status", status));
SqlParameter pCount = new SqlParameter("@count", 0);
pCount.Direction = System.Data.ParameterDirection.Output;
cmd.Parameters.Add(pCount);

cmd.ExecuteNonQuery();

disConnect();

int count = Convert.ToInt32(parameters[1].Value);

return count;

And this:

alter procedure sp_Arsenic_getSourceStatusCount
@status varchar(10),
@count int = 0 OUTPUT
as
set nocount on
select @count=COUNT(status) from Arsenic_WaterSource where status=@status
go

edited Oct 01 '12 at 20:34

answered Oct 01 '12 at 20:18

Lawrence Johnson

3,924
2
17
30

1

I updated it. You need to either set a value for the output parameter or define it with a default value in the sproc. – Lawrence Johnson Oct 01 '12 at 20:38

Why a SqlParameter does not get value?

2 Answers2