6 Replies - 1177 Views - Last Post: 18 August 2010 - 01:19 AM Rate Topic: -----

#1 bly  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 8
  • Joined: 24-March 09

Produce Output from Stored Procedure

Posted 16 August 2010 - 10:00 PM

Hi all
Below is my code to get data from a stored procedure but i keep getting error on this line

SqlDataReader reader = command.ExecuteReader();

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;

        public Program(int dateID) {
            _dateID = dateID;
        }

       public int dateID { get; set; }

        public void LoadData(int p)
        {
           
            SqlConnection conn = new SqlConnection("SOMEDATABASE");
            SqlCommand command = new SqlCommand("p_Date_sel", conn);
            SqlParameter parameter = new SqlParameter();
            parameter.ParameterName = "@DateID";
            
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add(parameter);

            conn.Open();
            SqlDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                Console.WriteLine(reader["Dates"]);
            }
            conn.Close();

           }
    }
}




Here's a main class if your intrested

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace ExecuteStoredProcedure
{
    class TestClass
    {
        public static void Main(string[] args)
        {
            Program p = new Program(56);
            p.LoadData(56);
            Console.WriteLine("DateID = " + "" + p.dateID);
            Console.ReadLine();
        }
    }
}




I'm a new developer still under training, the next step for me would be creating a insert,update and delete method.Thanks

Is This A Good Question/Topic? 0
  • +

Replies To: Produce Output from Stored Procedure

#2 PsychoCoder  Icon User is offline

  • Google.Sucks.Init(true);
  • member icon

Reputation: 1658
  • View blog
  • Posts: 19,853
  • Joined: 26-July 07

Re: Produce Output from Stored Procedure

Posted 16 August 2010 - 10:29 PM

Well first, what error are you getting and on what line are you getting it in? I have a slight idea based on your first set of code

namespace ExecuteStoredProcedure
{
    class Program
    {
       
        public void LoadData(int p)
        {
           
            SqlConnection conn = new SqlConnection("SOMEDATABASE");
            SqlCommand command = new SqlCommand("p_Date_sel", conn);
            SqlParameter parameter = new SqlParameter();
            parameter.ParameterName = "@DateID";
            
            //GOOD you set the CommandType as Stored Procedure
            command.CommandType = CommandType.StoredProcedure; 
            //now you need to set the CommandText to the name of your procedure
            command.CommandText = "YourProcedureName";

            //now you add your stored procedure parameters (I see one only)
            command.Parameters.Add(parameter);

            conn.Open();
            SqlDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                Console.WriteLine(reader["Dates"]);
            }
            conn.Close();
           }
    }
}



So what are the exact error messages you're receiving and where are they happening?
Was This Post Helpful? 1
  • +
  • -

#3 bly  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 8
  • Joined: 24-March 09

Re: Produce Output from Stored Procedure

Posted 16 August 2010 - 11:42 PM

Hi Psycho Coder ok i've rewrite the code this is the error
"Index Out of range exception" at While

I have some more parameter in my table just wanted to try with the date id first that's why there's only one,can i do that or it should have all the parameters?

Ok here's the code now with some minor adjustments rather than just getting one data,i'm planning on retrieving all the parameter just by getting the DateID,not sure if its the correct way...
namespace ExecuteStoredProcedure
{
    class Program
    {
        private int _dateID;
        private int _dateTypeID;
        private DateTime _date;
        private string _name;
        private string _notes;


        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; set; }
       public int dateTypeID { get; set; }
       public DateTime date { get; set; }
       public string name { get; set; }
       public string notes { get; set; }
       //suppose to retrieve all the data from DateID and show all
       // the attributes in table Date
       //Am i doing this the right way?


        
        public void LoadData(int p)
        {
           
            SqlConnection conn = new SqlConnection("SOMEDATABASE");
            SqlCommand command = new SqlCommand("p_Date_sel", conn);
            SqlParameter parameter = new SqlParameter();
            parameter.ParameterName = "@DateID";
            parameter.Value = p;// just added this and created a new //error at dates

            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "p_Date_sel";//should i be calling //this twice
            
            command.Parameters.Add(parameter);


            conn.Open();
            SqlDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                Console.WriteLine(reader["Dates"]); //error on this line 
            }
            conn.Close();

           }
    }
}




public static void Main(string[] args)
        {
            /*No 56 is already in the table with all the parameters,
              i cant even show the dateID*/
            Program p = new Program(56); 
            p.LoadData(56);
            
            Console.WriteLine("DateID = " + "" + p.dateID);
            Console.WriteLine("DateTypeID = " + "" + p.dateTypeID);
            Console.WriteLine("Date = " + "" + p.date);
            Console.WriteLine("Name = " + "" + p.name);
            Console.WriteLine("Notes = " + "" + p.notes);
            
            Console.ReadLine();
        }


Was This Post Helpful? 0
  • +
  • -

#4 Imdsm  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 104
  • View blog
  • Posts: 362
  • Joined: 21-March 09

Re: Produce Output from Stored Procedure

Posted 17 August 2010 - 01:12 AM

Quote

SqlConnection conn = new SqlConnection("SOMEDATABASE");
SqlCommand command = new SqlCommand("p_Date_sel", conn);
SqlParameter parameter = new SqlParameter();
parameter.ParameterName = "@DateID";
parameter.Value = p;// just added this and created a new //error at dates

command.CommandType = CommandType.StoredProcedure;
command.CommandText = "p_Date_sel";//should i be calling //this twice

command.Parameters.Add(parameter);

conn.Open();
SqlDataReader reader = command.ExecuteReader();


Try changing this:
SqlCommand command = new SqlCommand("p_Date_sel", conn);
SqlParameter parameter = new SqlParameter();
parameter.ParameterName = "@DateID";
parameter.Value = p;// just added this and created a new //error at dates

command.Parameters.Add(parameter);


To this:
SqlCommand command = new SqlCommand("p_Date_sel", conn);
command.Parameters.Add("@DateID", SqlDbType.DateTime);
command.Parameters["@DateID"].Value = p;


And see if it helps.
Was This Post Helpful? 1
  • +
  • -

#5 bly  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 8
  • Joined: 24-March 09

Re: Produce Output from Stored Procedure

Posted 17 August 2010 - 08:21 PM

Hi All ,
Someone suggest me to assign the values to their relevant variables
Do i need to change the DB and insert DateID
I've change the code to receive the dateID parameter for overload method

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

AS
BEGIN   like this
SELECT DateID,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(int dateID)// change this
        {
            SqlConnection conn = new SqlConnection('somedatabase');
            conn.Open();
            SqlCommand command = new SqlCommand("p_Date_sel", conn);
            command.CommandType = CommandType.StoredProcedure;
            SqlDataReader reader = command.ExecuteReader();
//i know i missing something in this area
//either using this //command.Parameters.Add("@DateID", //SqlDbType.DateTime);
//3	command.Parameters["@DateID"].Value = //dateID;     
//or
//using this
//command.Parameters["@DateID"]   ;   
            while (reader.Read())  //change this
            {
                _dateTypeID = Convert.ToInt16(reader["DateTypeID"]);
                _date = Convert.ToDateTime(reader["Date"]);
                _name = reader["Name"].ToString();
                _notes = reader["Notes"].ToString();
            }
            
           
            reader.Close();
            conn.Close();
            
           }
    }
}



 class TestClass 
    {
        public static void Main(string[] args)
        {
            
            Program p = new Program(5);
            p.LoadData();

            Console.WriteLine("DateID = " + "" + p.dateID.ToString());
            Console.WriteLine("DateTypeID = " + "" + p.dateTypeID.ToString());
            Console.WriteLine("Date = " + "" + p.date.ToString());
            Console.WriteLine("Name = " + "" + p.name.ToString());
            Console.WriteLine("Notes = " + "" + p.notes.ToString());
            
            Console.ReadLine();
        }
    }


This post has been edited by bly: 17 August 2010 - 10:22 PM

Was This Post Helpful? 0
  • +
  • -

#6 bly  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 8
  • Joined: 24-March 09

Re: Produce Output from Stored Procedure

Posted 18 August 2010 - 01:02 AM

Problem Solved thanks to all
:bananaman:
especially to psychocoder and Imdsm

This post has been edited by bly: 18 August 2010 - 01:04 AM

Was This Post Helpful? 0
  • +
  • -

#7 Imdsm  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 104
  • View blog
  • Posts: 362
  • Joined: 21-March 09

Re: Produce Output from Stored Procedure

Posted 18 August 2010 - 01:19 AM

View Postbly, on 18 August 2010 - 12:02 AM, said:

Problem Solved thanks to all
:bananaman:
especially to psychocoder and Imdsm


You're welcome :)
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1