By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,178 Members | 2,144 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,178 IT Pros & Developers. It's quick & easy.

C# not returning StoredProcedure Output variable

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

This is my SP code

Expand|Select|Wrap|Line Numbers
  1. CREATE PROCEDURE GetKitchenOrderDetail(
  2. @idService int,
  3.  
  4. --outPut Variables 
  5. @idUser int OUTPUT,
  6. @estTimeDelivery datetime = null OUTPUT,
  7. @timeDelivered datetime = null OUTPUT,
  8. @timeOrder datetime = null OUTPUT,
  9. @estTimePrepare int OUTPUT,
  10. @estatus int OUTPUT  
  11.  
  12. )
  13. AS
  14. BEGIN 
  15.  
  16. --getting the output variables
  17. 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 
  18.  
  19. --getting the values I want from a query
  20. 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

Expand|Select|Wrap|Line Numbers
  1. SqlCommand cmd = new SqlCommand("GetKitchenOrderDetail", new SqlConnection(connectionString));
  2.             cmd.CommandType = System.Data.CommandType.StoredProcedure;
  3.             cmd.Connection.Open();
  4.  
  5.             //add serviceId
  6.             cmd.Parameters.AddWithValue("@idService", idService);
  7. //start adding the output parameters
    //method1 I tried creating a parameter object
                SqlParameter estTimePrepare = new SqlParameter("@estTimePrepare", System.Data.SqlDbType.Int);
  8.             estTimePrepare.Direction = System.Data.ParameterDirection.Output;
  9.  
  10.             cmd.Parameters.Add(estTimePrepare);
  11. //method2 I tried creating the parameters directly in the command
                cmd.Parameters.Add("@estTimeDelivery", System.Data.SqlDbType.DateTime);
  12.             cmd.Parameters["@estTimeDelivery"].Direction = System.Data.ParameterDirection.Output;
  13.  
  14.             cmd.Parameters.Add("@timeDelivered", System.Data.SqlDbType.DateTime);
  15.             cmd.Parameters["@timeDelivered"].Direction = System.Data.ParameterDirection.Output;
  16.  
  17.             cmd.Parameters.Add("@timeOrder", System.Data.SqlDbType.DateTime);
  18.             cmd.Parameters["@timeOrder"].Direction = System.Data.ParameterDirection.Output;
  19.  
  20.             cmd.Parameters.Add("@estatus", System.Data.SqlDbType.Int);
  21.             cmd.Parameters["@estatus"].Direction = System.Data.ParameterDirection.Output;
  22.  
  23.  
  24.             //method3, 
  25.             cmd.Parameters.AddWithValue("@idUser", 0);
  26.  
  27.  
  28.             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!!!
Sep 24 '08 #1
Share this Question
Share on Google+
2 Replies


Plater
Expert 5K+
P: 7,872
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)
Sep 24 '08 #2

P: 13
Try executing simple SQL queries and see whether you are getting results, else there may be database configuration/connection issues.
Jun 8 '14 #3

Post your reply

Sign in to post your reply or Sign up for a free account.