Click here to Skip to main content
14,699,609 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
Hi,
I am a newbie and am learning both sql and programming.....i tried to write a code which calls a stored procedure from which it gets the record and displays, i dont know where im going wrong.......
The stored procedure is
create PROCEDURE [dbo].[profdata]
	@username nvarchar (50) OUTPUT, 
	@password nvarchar (50) output,
	@firstname NVARCHAR(50) output,
    @lastname NVARCHAR(50) output,
    @email NVARCHAR(50) OUTPUT
AS
BEGIN
	
	SET NOCOUNT ON;

SELECT * FROM PROFILE WHERE USERNAME=@Username

END




Do i need to add something next to select so that it can return the values to the calling variable?


Here is the code in C#

string sCon = ConfigurationManager.AppSettings["ConString"];
           SqlConnection con = new SqlConnection(sCon);
           SqlParameter p1 = new SqlParameter("@Username", SqlDbType.NChar);
           SqlParameter p2 = new SqlParameter("@Password", SqlDbType.NChar);
           SqlParameter p3 = new SqlParameter("@FirstNAME ", SqlDbType.NChar);
           SqlParameter p4 = new SqlParameter("@LastName", SqlDbType.NChar);
           SqlParameter p5 = new SqlParameter("@EMail", SqlDbType.NChar);
           //p1.Direction = ParameterDirection.Output;
           p1.Direction = ParameterDirection.InputOutput;
           p2.Direction = ParameterDirection.Output;
           p3.Direction = ParameterDirection.Output;
           p4.Direction = ParameterDirection.Output;
           p5.Direction = ParameterDirection.Output;
           con.Open();
           SqlCommand cmd = new SqlCommand();
           cmd.Parameters.Add(p1);
           cmd.Parameters.Add(p2);
           cmd.Parameters.Add(p3);
           cmd.Parameters.Add(p4);
           cmd.Parameters.Add(p5);
           cmd.CommandType = CommandType.StoredProcedure;
           cmd.CommandText = "profdata";
           cmd.Connection = con;
           con.Close();
          p1.Value = TextBox1.Text;
          p2.Value = TextBox2.Text;
          p3.Value = TextBox3.Text;
          p4.Value = TextBox4.Text;
          p5.Value = TextBox5.Text;

I know its kind of basic but i dont know where im making the mistaking, thanks in advance for clarifying.


Thanks for the link guys but i dont see anything regarding returning of value from the stored procedure to the function that called it. i even tried adding


SET @lastname = (SELECT [Last Name] FROM PROFILE WHERE @username=Username)
SET @firstname = (SELECT [First Name] FROM PROFILE WHERE @username=Username)
SET @email = (SELECT [E-Mail] FROM PROFILE WHERE @username=Username)


to the stored procedure but still i am getting

Object reference not set to an instance of an object.

i know that im doing something wrong at assigning the returned values.....
any help is appreciated...

p1.Value = TextBox1.Text;
p2.Value = TextBox2.Text;
p3.Value = TextBox3.Text;
p4.Value = TextBox4.Text;
p5.Value = TextBox5.Text;




--
Regards
Prasanth
Posted
Updated 3-Aug-11 22:41pm
v2

I have corrected your code, Please try this,


string sCon = ConfigurationManager.AppSettings["ConString"];

      SqlConnection con = new SqlConnection(sCon);
      SqlParameter p1 = new SqlParameter("@Username", SqlDbType.NVarChar,50);
      SqlParameter p2 = new SqlParameter("@Password", SqlDbType.NVarChar, 50);
      SqlParameter p3 = new SqlParameter("@FirstNAME ", SqlDbType.NVarChar, 50);
      SqlParameter p4 = new SqlParameter("@LastName", SqlDbType.NVarChar, 50);
      SqlParameter p5 = new SqlParameter("@EMail", SqlDbType.NVarChar, 50);
            //p1.Direction = ParameterDirection.Output;
            p1.Direction = ParameterDirection.InputOutput;
            p2.Direction = ParameterDirection.Output;
            p3.Direction = ParameterDirection.Output;
            p4.Direction = ParameterDirection.Output;
            p5.Direction = ParameterDirection.Output;
            con.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.Parameters.Add(p1);
            cmd.Parameters.Add(p2);
            cmd.Parameters.Add(p3);
            cmd.Parameters.Add(p4);
            cmd.Parameters.Add(p5);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "profdata";
            cmd.Connection = con;
            
            p1.Value = TextBox1.Text;
           p2.Value = TextBox2.Text;
           p3.Value = TextBox3.Text;
           p4.Value = TextBox4.Text;
           p5.Value = TextBox5.Text;

            cmd.ExecuteNonQuery();

           TextBox3.Text = p3.Value.ToString() ;
           TextBox4.Text = p4.Value.ToString() ;
           TextBox5.Text = p5.Value.ToString() ;

            con.Close();


Modify the SP
create PROCEDURE [dbo].[profdata]
	@username nvarchar (50) OUTPUT, 
	@password nvarchar (50) output,
	@firstname NVARCHAR(50) output,
    @lastname NVARCHAR(50) output,
    @email NVARCHAR(50) OUTPUT
AS
BEGIN
	
	SET NOCOUNT ON;
 
SET @lastname = (SELECT [Last Name] FROM PROFILE WHERE @username=Username)
SET @firstname = (SELECT [First Name] FROM PROFILE WHERE @username=Username)
SET @email = (SELECT [E-Mail] FROM PROFILE WHERE @username=Username)
 
END
   
v3
Comments
Prasanth Vignesh 4-Aug-11 4:51am
   
Dude, no offence but that's just the code i gave in the question with the assigning of the variable before the connection closed. and the sp has not been modified.
sudeshchandram 4-Aug-11 5:21am
   
see the lines 3,4,5,6,7 and cmd.ExecuteNonQuery(); statement,,,,
You have to understand the basic. Please go through the below link.

http://www.csharpfriends.com/articles/getarticle.aspx?articleid=78[^]
   
Hello Prashant,

Please follow the link. take a study on store porcedure and Ado.net. it will help you.
Click Here[^]

thanks
sanjeev
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900