Link to home
Start Free TrialLog in
Avatar of alexey1219
alexey1219

asked on

Passing an integer parameter from C# to SQL Server gives an error, why?

I have a stored procedure called "GetRequestInfoType"
CREATE PROCEDURE dbo.GetRequestInfoType
@id Int
AS
Select * FROM RequestInfoTypes WHERE id = @id
GO

I am trying to call this procedure with the following C# code:
        SqlConnection con = new SqlConnection(connectionString);
        SqlCommand cmd = new SqlCommand("GetRequestInfoType", con);
        cmd.CommandType = CommandType.StoredProcedure;
     
        try
        {
            con.Open();
            SqlDataReader reader = cmd.ExecuteReader();

            // Get the first row
            reader.Read();
           
            RequestInfoType curr_type = new RequestInfoType((int)reader["id"], reader["request_type"].ToString());
           

            reader.Close();
            return curr_type;
        }
        catch (SqlException err)
        {
            throw new ApplicationException("Data Error");
        }
        finally
        {
            con.Close();
        }


It tried to load the reader and then crashes giving the exception error.

I don't understand what I am doing wrong. Please Help.

Thanks

Avatar of joechina
joechina

Your reader["id"] should be a Null type instead of int

Sorry typo
Nullable or int?
Avatar of alexey1219

ASKER

I am not sure what you mean?
I meant in your code, you have the following,
RequestInfoType curr_type = new RequestInfoType((int)reader["id"], reader["request_type"].ToString());

But the reader["id"] is not int type, it's int? type.

So what you need to do is to add

int intId = ((int?)reader["id"])??-1;  //if it's null use default -1

and create new RequestInfoType with intId.
Of course, you might want to handle the issue if the intId is -1.
ASKER CERTIFIED SOLUTION
Avatar of photowhiz
photowhiz
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks!