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);
-
//start adding the output parameters //method1 I tried creating a parameter object SqlParameter estTimePrepare = new SqlParameter("@estTimePrepare", System.Data.SqlDbType.Int); -
estTimePrepare.Direction = System.Data.ParameterDirection.Output;
-
-
cmd.Parameters.Add(estTimePrepare);
-
//method2 I tried creating the parameters directly in the command 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!!!
2 3351 Plater 7,872
Recognized Expert Expert
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)
Try executing simple SQL queries and see whether you are getting results, else there may be database configuration/connection issues.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Jacques Wentworth |
last post by:
Hi
I have the following problem. I have a SQL Server (2000) Stored Proc that
with 4 parameter (one being @UnlockKey uniqueidentifier OUTPUT). I run it
from Query Analyzer and the @Key parameter...
|
by: Victor Bazarov |
last post by:
In the project I'm maintaining I've seen two distinct techniques used for
returning an object from a function. One is
AType function(AType const& arg)
{
AType retval(arg); // or default...
|
by: Mike P |
last post by:
If you have a stored procedure that may return one row or many rows, but
you only actually want the first row, should you use a datareader or
dataset, or will just reading the output parameters...
|
by: David Lozzi |
last post by:
Here is the proc:
CREATE PROCEDURE .
@CID as int,
@Netname as nvarchar(25),
@Return as int OUTPUT
AS
IF EXISTS (SELECT DISTINCT netname FROM computers WHERE CompanyID = @CID AND...
|
by: Joe Van Meer |
last post by:
Hi all,
I have an app that currently runs through 3 seperate stored procedures each
returning a count of records. What I would like to do is combine these
calls into one call, however I am...
|
by: scparker |
last post by:
Hello,
We have a stored procedure that does a basic insert of values. I am
then able to retrieve the ID number created for this new record. We are
currently using ASP.NET 2.0 and use N-Tier...
|
by: David Lozzi |
last post by:
Howdy,
ASP.Net 2.0 using VB on SQL 2005
This is a two fold issue.
I have a DetailsView control which users can insert or edit items. Editing
works great. Insert works great however I need...
|
by: Mike P |
last post by:
How do you return a datareader from a stored procedure, but also return
an output parameter?
Here is my code, which is just returning a data reader at the moment :
_conn.Open();
SqlCommand...
|
by: pinman |
last post by:
hi i am trying to implemement forms authentication for my website but can't seem to get the stored procedure to output the correct value when checking a users credentials. the code is
ALTER...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The...
| |