471,114 Members | 1,441 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,114 software developers and data experts.

C# not returning StoredProcedure Output variable

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,
  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  
  12. )
  13. AS
  14. BEGIN 
  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 
  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();
  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;
  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;
  14.             cmd.Parameters.Add("@timeDelivered", System.Data.SqlDbType.DateTime);
  15.             cmd.Parameters["@timeDelivered"].Direction = System.Data.ParameterDirection.Output;
  17.             cmd.Parameters.Add("@timeOrder", System.Data.SqlDbType.DateTime);
  18.             cmd.Parameters["@timeOrder"].Direction = System.Data.ParameterDirection.Output;
  20.             cmd.Parameters.Add("@estatus", System.Data.SqlDbType.Int);
  21.             cmd.Parameters["@estatus"].Direction = System.Data.ParameterDirection.Output;
  24.             //method3, 
  25.             cmd.Parameters.AddWithValue("@idUser", 0);
  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?

Sep 24 '08 #1
2 3128
7,872 Expert 4TB
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
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.

Similar topics

4 posts views Thread by Jacques Wentworth | last post: by
8 posts views Thread by Mike P | last post: by
6 posts views Thread by David Lozzi | last post: by
1 post views Thread by Joe Van Meer | last post: by
4 posts views Thread by scparker | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.