Return value from store procedure to dotnet

Question

Hi I am using stored procedure to check whether the data is exist in the table or not here i am not able to return value from database to my function in .net. And also want to how to store multiple value of select query in a variable because i want to perform some calculation in stored procedure. Please Help! :)

Ankit Nandekar · Answer

write like this select @Sname=Sname@City=city from student where Sno=@Sno here Sname City are output perameters

Vijay Yadav · Answer

Hey thanks to you man you helped me out!! :)

Ankit Nandekar · Answer

ok thnx .

Vijay Yadav · Answer

Hey Ankit i have already accepted your answer you can check it!! :)

Ankit Nandekar · Answer

if your problem solved then mark my answer as accepted so other can reffer it.

Vijay Yadav · Answer

Ankit Thanks once again!!! :)

Ankit Nandekar · Answer

problem solved or not?

Vijay Yadav · Answer

Ok! In stored procedure i want to store data of select query in a variable For ex: set @Id = (select EmpId from Emp where EmpId=@EmpId) In this example i am able to get the EmpId in a variable named @Id but instead of one column i want to fetch three columns data(i.e. EmpId Emp_NameCity) and i want to create three variables @EmpId@EmpName and @City so that it will store the select query data in these variable for ex my select is like this: select EmpId Emp_NameCity from Emp where EmpId=@EmpId I hope you will get this time! :)

Mayur Gujrathi · Answer

Not getting vijay try to tell in other way

Vijay Yadav · Answer

Hey Mayur & Ankit thanks a lot Man!!! and i have asked one more query that if there is more than one column to fetch using store procedure than how to do that i mean For ex:- for one column i have written the store procedure like this: Declare @Id varchar(50) set @Id = (select EmpId from Emp where EmpId=@EmpId) and its working fine but if there is more column than how to get multiple columns in a variable for ex: select EmpId Emp_NameCity from Emp where EmpId=@EmpId ???

Vijay Yadav · Answer

Thanks everyone for helping! :)

Ankit Nandekar · Answer

try this its working public int SP_AdminLogin(string strUserName string strPassword) { SqlConnection con = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings[MyCon].ConnectionString); con.Open(); SqlCommand cmdPro = new SqlCommand(); cmdPro.Connection = con; cmdPro.CommandType = CommandType.StoredProcedure; cmdPro.CommandText = SP_AdminLogin; cmdPro.Parameters.AddWithValue(@UserName strUserName); cmdPro.Parameters.AddWithValue(@Pwd strPassword); cmdPro.Parameters.Add(@OutputTest SqlDbType.Int).Direction = ParameterDirection.Output; cmdPro.ExecuteNonQuery(); int returnvalue = Convert.ToInt32( cmdPro.Parameters[@OutputTest].Value); return returnvalue ; } if problem solved then mark as accepted answer.

Mayur Gujrathi · Answer

something missing a very small thing which we are not getting. keep working and inform after solved and check your other values used on front end this error will generally raise when we are not using new operator while creating object of anything

Vijay Yadav · Answer

Hi Mayur I have tried both the solutions but both of them didn't work throeing error Object reference not set to an instance of an object even though i have changed cmd to cmdPro. Any other solution???

Mayur Gujrathi · Answer

problem solved or not? if yes then mark as accepted answer so that others can get it easily

Mayur Gujrathi · Answer

or try this cmd.Parameters.Add( @OutputTest  SqlDbType.int).Direction = ParameterDirection.Output; string try = cmd.Parameters[ @OutputTest ].Value.ToString();

Mayur Gujrathi · Answer

try this int retunnvalue = Int32.Parse(cmd.Parameters[  @OutputTest  ].Value.ToString());

Vijay Yadav · Answer

Hi Mayur thanks for reply i changed it to ParameterDirection.OutPut but it gives result zero only whether the data exist or not. I want to it should return 1 or 2 that i have set to the @OutputTest. Any idea?

Mayur Gujrathi · Answer

Try ParameterDirection.OutPut

Karthikeyan Anbarasan · Answer

Did you create a object for cmd?

Vijay Yadav · Answer

Hi It is throwing error on this line: cmd . Parameters . Add (  @OutputTest   SqlDbType . Int ). Direction = ParameterDirection . Output ;

Mayur Gujrathi · Answer

This error will generally raise when you do not use new operator suppose you are creating object of form then do like this form1 obj=new form or as reportdocument obj=new reportdocument

Vijay Yadav · Answer

Hi I am getting error Object reference not set to an instance of an object. Any idea?

Mayur Gujrathi · Answer

write stored procedure in this way CREATE PROCEDURE dbo.spInputAndOutputTest @InputTest int @OutputTest int OUTPUT AS SET @OutputTest = @InputTest * 2 and in front end cmd . Parameters . Add (  @OutputTest   SqlDbType . Int ). Direction = ParameterDirection . Output ;