Get output from Stored Procedure using Console

Hi I'm trying to produce an output from console but keep getting the error message

Procedure or function 'p_Date_sel' expects parameter '@DateID', which was not supplied

Suppose to get the DateID and display all the data,please tell me what I'm doing wrong.

Below is my stored procedure

ALTER procedure [dbo].[p_Date_sel]
@DateID int

AS
BEGIN
SELECT DateTypeID , Date , Name, Notes
FROM dbo.Dates
WHERE DateID= @DateID
END

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Text;


namespace ExecuteStoredProcedure
{
    class Program
    {
        private int _dateID;
        private int _dateTypeID;
        private DateTime _date;
        private string _name;
        private string _notes;

        public Program() 
        {
            _dateID = 0;
        }

        public Program(int dateID) {
            _dateID = dateID;
        }
        public Program(int datetypeID,DateTime date,string name,string notes){

            _dateTypeID = datetypeID;
            _date = date;
            _name = name;
            _notes = notes;
        }

       public int dateID 
       { 
           get {return _dateID;} 
       }
       public int dateTypeID 
       {
           get { return _dateTypeID; }
           set { _dateTypeID  = value; } 
       }
       public DateTime date 
       {
           get {return _date ;}
           set { _date = value;} 
       }
       public string name 
       {
           get { return _name;}
           set { _name = value; } 
       }
       public string notes 
       {
           get { return _notes;}
           set { _notes = value; }
       }

        public void LoadData()
        {
            SqlConnection conn = new SqlConnection("Data Source=mycbj01psql03\\sandbox01;Initial Catalog=DBRMS;Integrated Security=True");
            conn.Open();
            SqlCommand command = new SqlCommand("p_Date_sel", conn);
            command.CommandType = CommandType.StoredProcedure;
            SqlDataReader reader = command.ExecuteReader();

            while (reader.Read()) 
            {
                _dateTypeID = Convert.ToInt16(reader["DateTypeID"]);
                _date = Convert.ToDateTime(reader["Date"]);
                _name = reader["Name"].ToString();
                _notes = reader["Notes"].ToString();
            }


            reader.Close();
            conn.Close();

           }
    }
}

My Main Program

    namespace ExecuteStoredProcedure
    {
    class TestClass 
    {
        public static void Main(string[] args)
        {

            Program p = new Program(5);
            p.LoadData();

            Console.WriteLine("DateID = " + "" + p.dateID);
            Console.WriteLine("DateTypeID = " + "" + p.dateTypeID);
            Console开发者_StackOverflow社区.WriteLine("Date = " + "" + p.date);
            Console.WriteLine("Name = " + "" + p.name);
            Console.WriteLine("Notes = " + "" + p.notes);

            Console.ReadLine();
        }
    }
}

Well, you need to supply a value for @DateID!

Your stored proc requires a parameter called @DateID or type INT:

ALTER procedure [dbo].[p_Date_sel]
@DateID int

but in your call, you never ever do supply such a value!

What you need to do is create a SqlParameter and pass in that value to the stored proc. Also, I would strongly recommend to make use of the using() {...} constructs for your sql client code. And as a last recommendation: why not make the DateID a parameter of the LoadData() method?? The way you have it now, you have to create an instance of your class for each value you want to retrieve - not very efficient and useful....

public void LoadData(int dateID)
{
   using(SqlConnection conn = new SqlConnection("Data Source=mycbj01psql03\\sandbox01;Initial Catalog=DBRMS;Integrated Security=True"))
   {
       using(SqlCommand command = new SqlCommand("p_Date_sel", conn))
       {  
           command.CommandType = CommandType.StoredProcedure;

           // define the parameter and give it a value!
           command.Parameters.Add("@DateID", SqlDbType.Int);
           command.Parameters["@DateID"].Value = dateID;

           conn.Open();

           using(SqlDataReader reader = command.ExecuteReader())
           {
              while (reader.Read()) 
              {
                 _dateTypeID = Convert.ToInt16(reader["DateTypeID"]);
                 _date = Convert.ToDateTime(reader["Date"]);
                 _name = reader["Name"].ToString();
                 _notes = reader["Notes"].ToString();
              }

              reader.Close();
           }

           conn.Close();
        }
   }

That way, you should get your stored proc to work properly. Call it from your main app like this:

 Program p = new Program();
 p.LoadData(5);