CODEWITHSUNDEEP
c#sqlsql-server
Jamal El Gabari
Jamal El Gabari

Reputation: 9

How do I return values from my stored procedure from SQL to c#

So I am storing a value which is the student reference number which I have done, not I need to return the value from my ID and my DateTime in textbox2 and textbox 3. Whilst inputting and executing the procedure. 

using (SqlConnection con = new *****))
{
    SqlCommand cmd = con.CreateCommand();
    cmd.CommandText = "Exams_BagCheck_ScanIn";
    {
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@StudentReference", SqlDbType.VarChar).Value = textBox1.Text;
        cmd.Parameters.AddWithValue("@ID", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;
        con.Open();
        cmd.ExecuteNonQuery();

The procedure

USE [IFCustom]
GO

DECLARE @return_value Int

EXEC    @return_value = [dbo].[Exams_BagCheck_ScanIn]
        @StudentReference = N'1'

SELECT  @return_value as 'Return Value'

GO

Upvotes: 0

Views: 243

Answers (2)

Dheeraj Sharma
Dheeraj Sharma

Reputation: 709

If you just returning a single value then you can use

this:

string some_value = cmd.ExecuteScalar().ToString(); // if it is string

OR 

Object some_value = cmd.ExecuteScalar(); // convert it to its repective type.

in place of 

cmd.ExecuteNonQuery();

//some_value will contain the value you selected as SELECT @return_value in procedure.

Upvotes: 1

Alex K.
Alex K.

Reputation: 175748

Your procedure returns nothing, you need to remove:

SELECT @return_value as 'Return Value'

And instead:

RETURN @return_value

(If you wanted to select then .ExecuteScalar() would return the value)

Upvotes: 3

Related Questions