NullReferenceException trying to return int from stored proc

[bnbertha]

I've been coding for years but I'm relatively new to ASP/C#. This is my code; the exception occurs on the ExecuteScalar() command.

protected void ect_txt_TextChanged(object sender, EventArgs e)
    {
        SqlConnection conn = new SqlConnection(connect_str); // connect_str good as it works elsewhere
        string ecr_num_txt = ecr_txt.Text; // exr_txt is <asp:TextBox ID>
        int ecr_num_int = int.Parse(ecr_num_txt); 
        int ecr_exists = 0;
        SqlCommand cmd = new SqlCommand("ecr_exists_proc", conn); // ecr_exists_proc returns 1 or 0
        SqlParameter param = cmd.Parameters.Add("@ecr_id", SqlDbType.Int);
        cmd.Parameters["@ecr_id"].Value = ecr_num_int;

        cmd.CommandType = CommandType.StoredProcedure;
        param.Direction = ParameterDirection.Input;


        try
        {
            conn.Open();


            ecr_exists = (int)cmd.ExecuteScalar();

            . . .

I've been back through it several times and I can't find anything that I haven't instantiated. Any ideas anyone?

Thanks

Bertha

 

[misterstick]

which exception?

if ExecuteScalar returns something that's not an int (a double, say) you'll get an invalid cast exception even if it's an integer value.

try

ecr_exists = int.Parse(cmf.ExecuteScalar().ToString());

or, even better:

int ecr_exists;
if ( !int.TryParse(cmf.ExecuteScalar().ToString(), out ecr_exists )
{
  // deal with error
}

mr s. <

 

[bnbertha]

It's a NullReferenceException

Bertha

 

[misterstick]

duh, NullReferenceException.

ok, if the error is occuring on the line specified, the only things that are taking a reference are cmd in cmd.ExecuteScalar() and the result of cmd.ExecuteScalar() itself. the second is dealt with in my previous post, the first you'll just have to check yourself.

mr s. <

 

[misterstick]

crosspost, the duh was directed at me

mr s. <

 

[bnbertha]

I was too polite to say it

Running it in the debugger, everything seems to exist and I've changed the stored proc to force it to return an int.

Do you know if any of the attriubutes buried inside the SqlCommand object being null (there are quite a few) can cause this? This C# code has ended up a virual copy of the MSDN example with trying to get it working, which makes it seem even more frustrating.

Bertha

 

[bnbertha]

I've got it to work.

The problem was that I had not added a return value parameter. It needs to map to the @return parameter from the sproc and needs to be of type ReturnValue.

The working code is as follows:

protected void ect_txt_TextChanged(object sender, EventArgs e)
    {

        SqlConnection conn = new SqlConnection(connect_str);
        string ecr_num_txt = ecr_txt.Text; // exr_txt is <asp:TextBox ID>
        int ecr_num_int = int.Parse(ecr_num_txt); 
        int ecr_exists;
        SqlCommand cmd = new SqlCommand("ecr_exists_proc", conn); // ecr_exists_proc returns 1 or 0
        SqlParameter in_param = new SqlParameter("@ecr_id", SqlDbType.Int);
        SqlParameter out_param = new SqlParameter("@return", SqlDbType.Int);
        
        out_param.Direction = ParameterDirection.ReturnValue;
        in_param.Direction = ParameterDirection.Input;
        in_param.Value = ecr_num_int;

        cmd.Parameters.Add(in_param);
        cmd.Parameters.Add(out_param);
        cmd.CommandType = CommandType.StoredProcedure;


        try
        {
            conn.Open();

            ecr_exists = (int)cmd.ExecuteScalar();

            . . .

Thanks

Bertha