Passing Integer values to SqlParameter in .net

Question

i want to pass integer value to the SqlParameter which is executing an SqlDataReader, but when i pass integer value it says, the Parameter is not supplied, below is my code:

Common SqlDataReader Function

public static SqlDataReader ExecuteReader(string procedure, SqlParameter[] parameters, CommandType commandType)
    {
        SqlDataReader reader = null;
        SqlConnection connection = new SqlConnection(connectionString);
        using (SqlCommand command = new SqlCommand(procedure, connection))
        {
            if (connection.State == ConnectionState.Closed)
            {
                connection.Open();
            }
            if (parameters != null)
            {
                if (commandType == CommandType.StoredProcedure)
                    command.Parameters.AddRange(parameters);
            }
            reader = command.ExecuteReader(CommandBehavior.CloseConnection);
        }
        return reader;
    }

Calling code:

SqlDataReader obj = SqlHelper.ExecuteReader("sp_TheWireUser", new SqlParameter[] { new SqlParameter("@USERID", Convert.ToInt32(1)), new SqlParameter("@ROLEID", Convert.ToInt32(6)) }, System.Data.CommandType.StoredProcedure);
        Response.Write(obj.HasRows);

i tried entering 1 as a parameter also "1" but all does not work

ALTER PROCEDURE [dbo].[sp_TheWireUser]
    @USERID INT,
    @RoleID INT
AS
BEGIN
    SELECT USR.USERID FROM Users USR
    INNER JOIN UserRoles UR
    ON USR.UserID = UR.UserID
    INNER JOIN Roles R
    ON UR.RoleID = R.RoleID
    WHERE R.RoleID = @RoleID
    AND USR.UserID = @USERID
END

Shyju · Accepted Answer · 2012-08-21 21:08:39Z

1

Why are you converting 1 to Integer by calling Convert.ToInt32() .1 is already integer. So you do not need to do that.

SqlDataReader obj = ExecuteReader("sp_TheWireUser", 
                new SqlParameter[] { 
                    new SqlParameter("@USERID", 1),
                    new SqlParameter("@ROLEID", 6)
                },
                System.Data.CommandType.StoredProcedure);

This code should not throw any compile time errors. If you are getting a runtime exception, The possible reason is your Stored Procs paramter's data type is different than what you are passing here. It should be Integer as per your code.

Note : Hope you are closing the DataReader after use.

EDIT : You have to tell the CommandType

 using (SqlCommand command = new SqlCommand(procedure, connection))
 {
    command.CommandType = commandType; //passed from your method parameter
    //your remaining code
 }

edited Aug 21, 2012 at 21:08

answered Aug 21, 2012 at 20:46

Shyju

220k106 gold badges421 silver badges499 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Abbas Over a year ago

hi @Shyju i have updated my question to also include my Stored Procedure, i also passed value without converting that too does not work

Shyju Over a year ago

Are you getting the exception when you hard code the values (1 and 6) ?

Abbas Over a year ago

yes, i gets the message with hardcode values as well as dynamic values

Abbas Over a year ago

i am passing the commandType in my calling code... System.Data.CommandType.StoredProcedure

Shyju Over a year ago

@Abbas:Yes. But you have to Set the CommandType Property value of the command object you are executing. Add that line and it will work. I tested it

|

Community · Accepted Answer · 2017-05-23 12:12:54Z

0

Take a look at a similar question I asked in the past. Cleaning Up Resources via Using Statement & Dispose for DataTable, SqlConnection, SqlCommand, & SqlDataAdapter

It will show you how to clean up your data and also how to add a parameter. Also, wait until the last minute to Open your connection, i.e., add the parameter first.

edited May 23, 2017 at 12:12

CommunityBot

11 silver badge

answered Aug 21, 2012 at 20:59

Mark

1,5375 gold badges28 silver badges53 bronze badges

Collectives™ on Stack Overflow

Passing Integer values to SqlParameter in .net

2 Answers 2

6 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related