C# not returning StoredProcedure Output variable

[gabosom]

Hi! I've been breaking my head trying to get the output variables from my Stored Procedure.

This is my SP code

CREATE PROCEDURE GetKitchenOrderDetail(
@idService int,

--outPut Variables 
@idUser int OUTPUT,
@estTimeDelivery datetime = null OUTPUT,
@timeDelivered datetime = null OUTPUT,
@timeOrder datetime = null OUTPUT,
@estTimePrepare int OUTPUT,
@estatus int OUTPUT  
  
)
AS
BEGIN 

--getting the output variables
SELECT @idUser = s.idUser, @estTimeDelivery = ko.estTimeDelivery, @timeDelivered = ko.timeDelivery, @timeOrder = s.timeOrder, @estTimePrepare = (DATEDIFF(MINUTE, @timeOrder, @estTimeDelivery)), @estatus = ko.estatus FROM ServiceOffered AS s JOIN KitchenOrder AS ko ON ko.idService = s.idService JOIN Area AS a on a.idArea = ko.idArea WHERE s.idService = @idService 

--getting the values I want from a query
SELECT koi.idItem, koi.price, koi.quantity, mi.name, mi.itemDescription, c.name AS category FROM KitchenOrderItems koi JOIN MenuItem mi ON mi.idItem = koi.idItem JOIN Category as c ON c.idCategory = mi.idCategory WHERE koi.idService = @idService END

And this is my C# Code

SqlCommand cmd = new SqlCommand("GetKitchenOrderDetail", new SqlConnection(connectionString));
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            cmd.Connection.Open();

            //add serviceId
            cmd.Parameters.AddWithValue("@idService", idService);
[INDENT]//start adding the output parameters[/INDENT]
[INDENT]//method1 I tried creating a parameter object[/INDENT]
            SqlParameter estTimePrepare = new SqlParameter("@estTimePrepare", System.Data.SqlDbType.Int);
            estTimePrepare.Direction = System.Data.ParameterDirection.Output;

            cmd.Parameters.Add(estTimePrepare);
[INDENT]//method2 I tried creating the parameters directly in the command[/INDENT]
            cmd.Parameters.Add("@estTimeDelivery", System.Data.SqlDbType.DateTime);
            cmd.Parameters["@estTimeDelivery"].Direction = System.Data.ParameterDirection.Output;

            cmd.Parameters.Add("@timeDelivered", System.Data.SqlDbType.DateTime);
            cmd.Parameters["@timeDelivered"].Direction = System.Data.ParameterDirection.Output;

            cmd.Parameters.Add("@timeOrder", System.Data.SqlDbType.DateTime);
            cmd.Parameters["@timeOrder"].Direction = System.Data.ParameterDirection.Output;

            cmd.Parameters.Add("@estatus", System.Data.SqlDbType.Int);
            cmd.Parameters["@estatus"].Direction = System.Data.ParameterDirection.Output;


            //method3, 
            cmd.Parameters.AddWithValue("@idUser", 0);


            SqlDataReader reader = cmd.ExecuteReader(System.Data.CommandBehavior.Default);

If I debug the SP in SQL Management Studio, I get the correct values. But in C#, after executing the reader, whenever I try to access the parameter values (example, cmd.Parameters["@timeOrder "].Value), all output parameters are always 0.

Any ideas?

Thanks!!!

[Plater]

Hmm, as for the .NET code, that all seems correct. Your SQL format is new to me, but it looks like it would be correct.

I would say maybe try making another storedprocedure that just has one output param and see if THAT one works correctly?


Also, have you tried with an SqlDataAdapater and filling a DataSet (normally I would say a DataTable, but since you have output values, I thought *maybe* a DataSet would do something with them)

[mHealth]

Try executing simple SQL queries and see whether you are getting results, else there may be database configuration/connection issues.