Return value from store procedure to dotnet

Vijay Yadav

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! :)

24 Replies

Know the answer? Post it — somebody with the same question will find it here.

Ankit Nandekar15y ago
Accepted answer
write like this
select @Sname=Sname,@City=city from student where Sno=@Sno
here Sname City are output perameters

0
Vijay Yadav15y ago
Hey thanks to you man, you helped me out!! :)
0
Ankit Nandekar15y ago
ok thnx .
0
Vijay Yadav15y ago
Hey Ankit, i have already accepted your answer, you can check it!! :)
0
Ankit Nandekar15y ago
if your problem solved then mark my answer as accepted so other can reffer it.
0
Vijay Yadav15y ago
Ankit, Thanks once again!!! :)
0
Ankit Nandekar15y ago
problem solved or not?
0
Vijay Yadav15y ago
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_Name,City) 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_Name,City from Emp where EmpId=@EmpId

I hope you will get this time! :)
0
Mayur Gujrathi15y ago
Not getting vijay try to tell in other way
0
Vijay Yadav15y ago
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_Name,City from Emp where EmpId=@EmpId
???
0
Vijay Yadav15y ago
Thanks everyone for helping! :)
0
Ankit Nandekar15y ago
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.

0
Mayur Gujrathi15y ago
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
0
Vijay Yadav15y ago
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???
0
Mayur Gujrathi15y ago
problem solved or not? if yes then mark as accepted answer so that others can get it easily
0
Mayur Gujrathi15y ago
or try this
cmd.Parameters.Add("@OutputTest", SqlDbType.int).Direction = ParameterDirection.Output;
string try = cmd.Parameters["@OutputTest"].Value.ToString();
0

Mayur Gujrathi15y ago

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

Vijay Yadav15y ago
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?
0
Mayur Gujrathi15y ago
Try
ParameterDirection.OutPut
0
Karthikeyan Anbarasan15y ago
Did you create a object for cmd?
0

Vijay Yadav15y ago

Hi,
It is throwing error on this line:

cmd.Parameters.Add("@OutputTest", SqlDbType.Int).Direction = ParameterDirection.Output;

Mayur Gujrathi15y ago
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
0
Vijay Yadav15y ago
Hi,
I am getting error, Object reference not set to an instance of an object. Any idea?
0
Mayur Gujrathi15y ago
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;
```
0