Error invoking stored procedure with input parameter from ADO.Net

Question

Hello everyone,

I am using VSTS 2008 + C# + .Net 3.5 + ADO.Net. Here is my code and related error message. The error message says, @Param1 is not supplied, but actually it is supplied in my code. Any ideas what is wrong?

System.Data.SqlClient.SqlException: Procedure or function 'Pr_Foo' expects parameter '@Param1', which was not supplied.

class Program
{
        private static SqlCommand _command;
        private static SqlConnection connection;

        private static readonly string _storedProcedureName = "Pr_Foo";
        private static readonly string connectionString = "server=.;integrated Security=sspi;initial catalog=FooDB";

        public static void Prepare()
        {
            connection = new SqlConnection(connectionString);
            connection.Open();
            _command = connection.CreateCommand();
            _command.CommandText = _storedProcedureName;
            _command.CommandType = CommandType.StoredProcedure;
        }

        public static void Dispose()
        {
            connection.Close();
        }

        public static void Run()
        {
            try
            {
                SqlParameter Param1 = _command.Parameters.Add("@Param1", SqlDbType.Int, 300101);
                Param1.Direction = ParameterDirection.Input;
                SqlParameter Param2 = _command.Parameters.Add("@Param2", SqlDbType.Int, 100);
                portal_SiteInfoID.Direction = ParameterDirection.Input;
                SqlParameter Param3 = _command.Parameters.Add("@Param3", SqlDbType.Int, 200);
                portal_RoleInfoID.Direction = ParameterDirection.Input;

                _command.ExecuteScalar();
            }
            catch (Exception e)
            {
                Console.WriteLine(e);
            }
        }

        static void Main(string[] args)
        {
            try
            {
                Prepare();

                Thread t1 = new Thread(Program.Run);
                t1.Start();
                t1.Join();

                Dispose();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message + "\t" + ex.StackTrace);
            }
        }
    }

Thanks in advance,

George

Answer 1

Try taking the "@" symbol out of your .Add statements. I never prepend the @ when adding parameters.

For example:

SqlParameter Param1 = _command.Parameters.Add("Param1", SqlDbType.Int, 300101);

Answer 2

You did not add value to parameter. Signature of Add is Add(string parameterName, SqlDbType type, int size)... last parameter is size, not value. you can use method AddWithValue.

MSDN Article

Answer 3

Try to replace your function by below code and check :

public static void Run()
        {
            try
            {
                _command.Parameters.AddWithValue("@Param1", 300101);
                _command.Parameters.AddWithValue("@Param2", 100);
                _command.Parameters.AddWithValue("@Param3", 200);

                _command.ExecuteScalar();
            }
            catch (Exception e)
            {
                Console.WriteLine(e);
            }
        }