Call stored procedure with paremeter output in c#

Hi,
I have the next stored procedure in Sql Server 2008:

CREATE PROCEDURE [dbo].[GetNumTiquet]
@IdEmpresa int,
@NumTiquet int OUTPUT
AS
BEGIN
	SET NOCOUNT ON;

	UPDATE ConfGen
	SET @NumTiquet=NumTiquet = (SELECT MAX(NumTiquet) +1 FROM ConfGen)
	WHERE Id = @IdEmpresa
END

If i execute this stored procedure with Sql Server 2008 all is ok.
But how can i call this stored procedure with c#?

Thanks
0
Paul
7/1/2010 3:39:46 PM
dotnet.languages.csharp 1931 articles. 0 followers. Follow

5 Replies
1846 Views

Similar Articles

[PageSpeed] 26

Paul wrote:
> Hi,
> I have the next stored procedure in Sql Server 2008:
> 
> CREATE PROCEDURE [dbo].[GetNumTiquet]
> @IdEmpresa int,
> @NumTiquet int OUTPUT
> AS
> BEGIN
> 	SET NOCOUNT ON;
> 
> 	UPDATE ConfGen
> 	SET @NumTiquet=NumTiquet = (SELECT MAX(NumTiquet) +1 FROM ConfGen)
> 	WHERE Id = @IdEmpresa
> END
> 
> If i execute this stored procedure with Sql Server 2008 all is ok.
> But how can i call this stored procedure with c#?
> 
> Thanks

<http://www.google.com/#hl=en&q=how+to+get+output+parameter+from+stored+procedure+ado.net&aq=f&aqi=&aql=&oq=how+to+get+output+parameter+from+stored+procedure+ado.net&gs_rfai=&fp=c0cfdbfb1e48170b>
0
Mr
7/1/2010 6:18:36 PM
On 01-07-2010 11:39, Paul wrote:
> I have the next stored procedure in Sql Server 2008:
>
> CREATE PROCEDURE [dbo].[GetNumTiquet]
> @IdEmpresa int,
> @NumTiquet int OUTPUT
> AS
> BEGIN
> 	SET NOCOUNT ON;
>
> 	UPDATE ConfGen
> 	SET @NumTiquet=NumTiquet = (SELECT MAX(NumTiquet) +1 FROM ConfGen)
> 	WHERE Id = @IdEmpresa
> END
>
> If i execute this stored procedure with Sql Server 2008 all is ok.
> But how can i call this stored procedure with c#?

Something like (untested):

SqlCommand cmd = new SqlCommand("dbo.GetNumTiquet", con);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter prm1 = new SqlParameter("@IdEmpresa", SqlDbType.Int);
cmd.Parameters.Add(prm1);
SqlParameter prm2 = new SqlParameter("@NumTiquet", SqlDbType.Int);
prm2.Direction = ParameterDirection.Output;
cmd.Parameters.Add(prm2);
cmd.Parameters["@IdEmpresa"].Value = v;
cmd.ExecuteNonQuuery();
int res = (int)cmd.Parameters["@NumTiquet"].Value);

Arne
0
ISO
7/1/2010 9:52:58 PM
On 01-07-2010 17:52, Arne Vajh�j wrote:
> On 01-07-2010 11:39, Paul wrote:
>> I have the next stored procedure in Sql Server 2008:
>>
>> CREATE PROCEDURE [dbo].[GetNumTiquet]
>> @IdEmpresa int,
>> @NumTiquet int OUTPUT
>> AS
>> BEGIN
>> SET NOCOUNT ON;
>>
>> UPDATE ConfGen
>> SET @NumTiquet=NumTiquet = (SELECT MAX(NumTiquet) +1 FROM ConfGen)
>> WHERE Id = @IdEmpresa
>> END
>>
>> If i execute this stored procedure with Sql Server 2008 all is ok.
>> But how can i call this stored procedure with c#?
>
> Something like (untested):
>
> SqlCommand cmd = new SqlCommand("dbo.GetNumTiquet", con);
> cmd.CommandType = CommandType.StoredProcedure;
> SqlParameter prm1 = new SqlParameter("@IdEmpresa", SqlDbType.Int);
> cmd.Parameters.Add(prm1);
> SqlParameter prm2 = new SqlParameter("@NumTiquet", SqlDbType.Int);
> prm2.Direction = ParameterDirection.Output;
> cmd.Parameters.Add(prm2);
> cmd.Parameters["@IdEmpresa"].Value = v;
> cmd.ExecuteNonQuuery();
> int res = (int)cmd.Parameters["@NumTiquet"].Value);

Note that the use of:

SELECT MAX()+1

usually is the wrong approach and instead SCOPE_IDENTITY() should
be used in the following SQL statements.

Arne
0
ISO
7/1/2010 9:54:02 PM
On 1 jul, 23:54, Arne Vajh=F8j <a...@vajhoej.dk> wrote:
> On 01-07-2010 17:52, Arne Vajh j wrote:
>
>
>
>
>
> > On 01-07-2010 11:39, Paul wrote:
> >> I have the next stored procedure in Sql Server 2008:
>
> >> CREATE PROCEDURE [dbo].[GetNumTiquet]
> >> @IdEmpresa int,
> >> @NumTiquet int OUTPUT
> >> AS
> >> BEGIN
> >> SET NOCOUNT ON;
>
> >> UPDATE ConfGen
> >> SET @NumTiquet=3DNumTiquet =3D (SELECT MAX(NumTiquet) +1 FROM ConfGen)
> >> WHERE Id =3D @IdEmpresa
> >> END
>
> >> If i execute this stored procedure with Sql Server 2008 all is ok.
> >> But how can i call this stored procedure with c#?
>
> > Something like (untested):
>
> > SqlCommand cmd =3D new SqlCommand("dbo.GetNumTiquet", con);
> > cmd.CommandType =3D CommandType.StoredProcedure;
> > SqlParameter prm1 =3D new SqlParameter("@IdEmpresa", SqlDbType.Int);
> > cmd.Parameters.Add(prm1);
> > SqlParameter prm2 =3D new SqlParameter("@NumTiquet", SqlDbType.Int);
> > prm2.Direction =3D ParameterDirection.Output;
> > cmd.Parameters.Add(prm2);
> > cmd.Parameters["@IdEmpresa"].Value =3D v;
> > cmd.ExecuteNonQuuery();
> > int res =3D (int)cmd.Parameters["@NumTiquet"].Value);
>
> Note that the use of:
>
> SELECT MAX()+1
>
> usually is the wrong approach and instead SCOPE_IDENTITY() should
> be used in the following SQL statements.
>
> Arne- Ocultar texto de la cita -
>
> - Mostrar texto de la cita -

Hi,
I don't understant.
What do you menan with SCOPE_INDENTITY() with this example?
Thanks
0
Paul
7/2/2010 9:22:11 AM
Paul wrote:
> On 1 jul, 23:54, Arne Vajh�j <a...@vajhoej.dk> wrote:
>> On 01-07-2010 17:52, Arne Vajh j wrote:
>>
>>
>>
>>
>>
>>> On 01-07-2010 11:39, Paul wrote:
>>>> I have the next stored procedure in Sql Server 2008:
>>>> CREATE PROCEDURE [dbo].[GetNumTiquet]
>>>> @IdEmpresa int,
>>>> @NumTiquet int OUTPUT
>>>> AS
>>>> BEGIN
>>>> SET NOCOUNT ON;
>>>> UPDATE ConfGen
>>>> SET @NumTiquet=NumTiquet = (SELECT MAX(NumTiquet) +1 FROM ConfGen)
>>>> WHERE Id = @IdEmpresa
>>>> END
>>>> If i execute this stored procedure with Sql Server 2008 all is ok.
>>>> But how can i call this stored procedure with c#?
>>> Something like (untested):
>>> SqlCommand cmd = new SqlCommand("dbo.GetNumTiquet", con);
>>> cmd.CommandType = CommandType.StoredProcedure;
>>> SqlParameter prm1 = new SqlParameter("@IdEmpresa", SqlDbType.Int);
>>> cmd.Parameters.Add(prm1);
>>> SqlParameter prm2 = new SqlParameter("@NumTiquet", SqlDbType.Int);
>>> prm2.Direction = ParameterDirection.Output;
>>> cmd.Parameters.Add(prm2);
>>> cmd.Parameters["@IdEmpresa"].Value = v;
>>> cmd.ExecuteNonQuuery();
>>> int res = (int)cmd.Parameters["@NumTiquet"].Value);
>> Note that the use of:
>>
>> SELECT MAX()+1
>>
>> usually is the wrong approach and instead SCOPE_IDENTITY() should
>> be used in the following SQL statements.
>>
>> Arne- Ocultar texto de la cita -
>>
>> - Mostrar texto de la cita -
> 
> Hi,
> I don't understant.
> What do you menan with SCOPE_INDENTITY() with this example?
> Thanks

If the key of the record was using Identity Incremental on the 
primary-key to the record, an int field type, then when a new record is 
inserted the incremental int key is assigned to the inserted record. The 
Scope_Identity allows one to get the key of the record inserted at the 
time of record insertion.

This allows you to pass the Identity key to be passed as output from the 
sproc back to the C# program so it can use in other C# code processing, 
like retrieve the record by its just inserted record-key ID as an example.


0
Mr
7/2/2010 11:14:05 AM
Reply:

Similar Artilces:

Formatting Output Text
I'm using Dom to save a hierarchical structure (similar to a tree) to xml format in this way oXmlNode = XMLDOM.createElement("VALUECONDITION") 'Attribute Name oXmlAttribute = XMLDOM.createAttribute("Name") oXmlAttribute.value = oValueCondition.Name oXmlNode.attributes.setNamedItem(oXmlAttribute) Everything works fine. The only problem is that output text is not formatted and when i open XML file with a text editor it's impossible to read it (the are no tabs and no carriage return) Is there a way to format text in a easy way? Thanks ...

How to call MFC dialog from win32 console ?
Hi all, How to call MFC dialog from win32 console ? With unbelievable difficulty. There is an essay on my MVP Tips site showing how to call an ordinary MessageBox from a console app, and It Isn't A Pretty Sight. You would additionally have to initialize the MFC runtime. This sounds like a finalist in the task competition of the "Make My Life Miserable, Please!" TV reality show. joe On Tue, 30 Sep 2003 21:26:18 -0700, "Hanamantappa" <hanu_np@yahoo.co.in> wrote: >Hi all, > >How to call MFC dialog from win32 console ? Joseph M. Newcomer [MVP] email...

Store Does Not Mount Automatically #2
Hello! For the last 3 years I did not have any problem with my X2k server in W2k server. Last 2 weeks ago when the X2k was restarted one of the information stores did not mount automatically and had to mount it manually. Last Monday it had to be restarted again and this time all the stores mounted automatically. Last night after applying the critical updates and had to restart the server, that particular store again had to mount manually. Nothing in event viewer indicates a problem. Can someone tell where to look for errors? How do I solve this problem. SP, patches are up to date on al...

C:\Documents and Settings\user name\.thumbnails\normal
With Windows XP Home edition: I have notice that under C:\Documents and Settings\user name/.thumbnails\normal that there are a lot of recent thumbnail photos. What's this all about???? Any knowledge on this? Thanks in advance, hb No! Fill us in on just what websites you have been visiting! >WT< "H.B." <hblair@sktc.net> wrote in message news:uxGxGOupKHA.5328@TK2MSFTNGP04.phx.gbl... > With Windows XP Home edition: > I have notice that under C:\Documents and Settings\user > name/.thumbnails\normal that there are a lot of recent thumb...

The MAPI call 'OpenMsgStore' failed with the following error
Hey, I have made a recovery storage group and I try to restore a single mailbox but I get this failure and Had tryed to set permissions for mailbox stores and public folders store, but it still gives me these failures. Is there anyone that can help me??? Se these failures.... The MAPI call 'OpenMsgStore' failed with the following error: The attempt to log on to the Microsoft Exchange Server computer has failed. The MAPI provider failed. Microsoft Exchange Server Information Store ID no: 8004011d-0512-00000000 Unable to get an administrative interface to the mailbox store on serve...

How to connect to Message Store in Exchange 2007
Hi How do we connect to Exchange server 2007 message store in C++. Can we use MAPI?. If not how do we connect Thanks Ravi You can still write C++ applications and use MAPI. However to do so you will need to first install the standalone MAPI/CDO component on the exchange 2007 server. http://blogs.msdn.com/dgoldman/archive/2006/06/19/636996.aspx -- This posting is provided "AS IS" with no warranties, and confers no rights. Dgoldman http://blogs.msdn.com/dgoldman "Ravi" <Ravi@discussions.microsoft.com> wrote in message news:5D7B4EC2-D43E-4909-9557-DE5185D04D58...

calling dll from c like app
I ma not sure if this is the appropriate group to post. If not please point me to the right group I have created a dll using c# from something like this using System; using System.Collections.Generic; using System.Text; using System.Text.RegularExpressions; namespace IeString { public class ClassIeString { Regex myregexp; public int setRegexp(String argRegexp) .... From an application build from C with capability to access external function by means of declaring public FUNCTION int setRegexp(REF String argRegexp)...

Consolidated Closing Procedure on a Multi-Lane Store
Has anyone streamlined the close out procedures on a Muli-Lane Store? i.e. Consolidated closing amount entry. Consolidated batching(Z-reports). I understand we can use the RegAnaly.rpt but we are looking for a way to streamline the whole process getting up to that point. Thanks Rob ...

CRM for Service Calls
Hello, We're planning on using CRM for keeping track of our service calls. I'm wondering what is the best way to keep track of Cases that need to be billed, after they have been Resolved? When you Resolve a Case you can specify how much billable time there was, so I'm assuming this can be brought up in a report. However we need to be able to let accounting keep of which Cases are "ready for billing" and then mark a Case as "billing completed". My initial idea is to have the default Resolved Status Reason be "Ready for Billing", and then have...

Entourage Database vs Exchange Store, who wins?
Version: 2004 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel Email Client: imap If this question has been asked already I apologize for repeating it, however, I can't think of a search phrase for this. <br><br>Suppose you have users with large email boxes (>15GB) using Entourage 2k4 to connect to Exchange 2003. <br><br>We decided to reduce the size of the mailboxes by using outlook to export all mail prior to 2007 to a pst. After the PST was exported we used Outlook to delete all those messages. <br><br>After this we did an Offline ...

Global client not in new store
We created a Global customer in HQ but when the new store sync, the customer didn't appear. Th store has all operations data, but that specific customer is in the store. How can we make that customer appear in the store? This is a multi-part message in MIME format. ------=_NextPart_000_00A8_01CA436E.ECE5D7F0 Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: quoted-printable RicoTowers, Has it moved over yet? If not, make sure the clocks in both stores are = correct and then change any information in the Customer's record. That = should move it on...

10 input cells and 1 output cell
I have a large spreadsheet model that essentially has 10 inputs and 1 output. I'd like to create a second spreadsheet set up with hundreds of unique rows where the first 10 columns represent 1 input and the 11th column represents the output. How in the world would i do this? thanks, Neil Unfortunately, though it's been proposed, using a worksheet as a template has not been implemented in XL. One way would be to use a macro to transfer inputs to the first sheet, then transfer the result to the second sheet. Something like: Assume inputs in Sheet1!A1:J1, the output in Shee...

Adding unique store items in HQ
All: I am new to HQ, just getting used to the concept of Centrally Maintained Data. That's fine, but how do I handle those items that are unique to a store? Is there a worksheet to upload these to HQ? If so, I can't identify it.) thanks for your help, -- Robert Gross If you are using HQ I assume you have more then a single location. I believe you should be creating new item codes in HQ and then sending them to the stores VIA a 250 Worksheet. If the items are unique only to store 1 and not other locations, that worksheet would only be sent to store 1. But again codes are crea...

Table variable populated from stored procedure [SS2000]
Hi, in SQL Server 2000, I can't use stored procedure to populate a table variable (@MyTable). The error is: Execute cannot be used as a source of a table variable How can I solve? PS I my stored procedure I have also Insert statement nested. Thanks in advance. Luigi You can use temp table instead of table variable (or go the long way and user INSERT EXEC with temp table and then insert to the table variable using the temp table as source). -- Plamen Ratchev http://www.SQLStudio.com "Plamen Ratchev" wrote: > You can use temp table instead of t...

Encrypting Stored Procedures question
Hello everyone, Merry Christmas. One quick question. I understand that it is relatively easy to work around encrypted stored procedures. If I want to send my application into the wild with a couple of hundred stored procedures, how do I keep protect my code? Or is it just not possibly to do so? Is that why some folks do not use stored procs, but code everything in their applications? Just as a standard deployment guideline, would you recommend not encrypting the stored procedures? How do you deal with protecting your intellectual property? I am just looking for a rule-of-...

Native XML Support for Non-Managed C++ Code #2
Since XML is becoming so important in main stream programming, is there any possiblity that Microsoft would add native XML support to it's non-managed VC++ 7.1 development environment. It would be nice to have native MFC classes to access and manipulate XML similar to the functionality that is already available in .Net. It is a real pain for C++ programmers to use smart-pointers, VARIANTS, and/or building class wrappers for COM components just to be able to access this core programming functionality. Thanks, Bruce ...

Remote Procedure Call Failed
When I have the CRM client installed on my computer, I am unable to send attachments to my e-mail message. When I uninstall CRM, I can send my messages just fine. Why would the CRM cause Outlook to be unable to send attachments to messages? -- dberryhill ...

C# 2008 .net framework 3.5 Windows form CrystalReport question
Hi, In my Dotnet Framework 3.5 C# Windows Form project, I have a Crystal Report 10 report document. I also have an installation project on that project. My question is what Assemblies do I need to add into the installation project, so that the client can open the Crystal Report document? Thanks for help. Jason Jason Huang wrote: > Hi, > > In my Dotnet Framework 3.5 C# Windows Form project, I have a Crystal Report > 10 report document. > I also have an installation project on that project. > My question is what Assemblies do I need to add into the...

Information Store won't start after installing exchange (in child domain)
We are installing exchange 2003 in a child domain of a domain tree where an exchange 2003 server already exists in the parent domain. Netdiag and DCDiag were both performed before installation and indicated no problems with the general setup. The account used to install exchange has Full Exchange Administration rights at the org. level. The installation completes without any error messages, but it is impossible to start the information store. The store refuses to start and events 9153, 1121 and 5000 are then logged in the event log. Event 1121: Error 0x8004010f connecting to the Microsoft Ac...

CDhtmlDialog: call java applet method
Hi, In an mfc application having a CDhtmlDialog class dialog, I need to have a java applet in the html file. From the mfc application, i need to call a method ( setData(string strData)) of the java applet or pass data to java applet dynamically. Can you suggest the best way? Cheers! neo ...

Visual C++ 6.0 And OpenGL
Hey ! Usualy you can see all available instructions if you press CTRL + SPACE ( SHIFT + SPACE for Argiment list ) I can't see any OpenGL instructions, but I can use them. I have included "gl.h" and "glu.h" in "StdAfx.h" but still can't see any glXXX commands. What am I missing? Thanks, Damjan That's the way it is. Library functions don't appear in the list. "Damjan Vengu�t" <damjan.vengust@guest.arnes.si> wrote in message news:fl7Tb.3722$%x4.389472@news.siol.net... > Hey ! > > Usualy you can see all available i...

Strange Output
Hello, everybody !!! Why this fragment of code shows : ac cout<<"a\??/??/bc"; Thanks All !!! "Alex Dmitriev" <firejump@mail.ru> wrote in message news:ecTgPp8pEHA.3800@TK2MSFTNGP14.phx.gbl... > Hello, everybody !!! > Why this fragment of code shows : ac > > cout<<"a\??/??/bc"; http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dv_vccelng4/html/elconTrigraphs.asp '??/' is a '\' so you end up with "a\\\bc" http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dv_vccelng4/html/elc...

Printing Hyperlinks stored in fields
I have a list of hyperlinks that are stored in fields(Range A1 through A200, for example). I would like to print them(essentially send it to Acrobat Distiller and convert to PDF files). When i click on the link, it opens an IE window and i can do the print from there however i cannot automate this using a macro. As a result, i am unable to automate the process of creating the PDFs. If there was a way of opening the webpages inside of Excel to the macro could actually control it, that would be ideal. Is there any other way that this can be done? Any ideas are much appreciated Thanks in ...

Storing and Accessing Data
I'm in the process of constructing a workbook where only certain people will have access to certain worksheets within the workbook. Depending on the user, I want some worksheets to be unviewable. The best way I've found to implement this idea is to hide the various worksheets (Format -> Sheet -> Hide) and then password protect the workbook. Then, I've created macro functions that prompts the user for a password. (This is not the same password used to protect the workbook.) When the password is entered, the function unprotects the workbook, unhides the targe...

Import Wizard for Store Operations
I have an end user who wants to import new itemslookupcodes from the supplier catalog on a ongoing basis. Does someone have an add-on for this? Thanks, marc Not that I know of, but we could make one for you. -- -Jerry JR Data Inc. "phil" wrote: > I have an end user who wants to import new itemslookupcodes from the supplier > catalog on a ongoing basis. Does someone have an add-on for this? > > Thanks, > marc I know there is one out ther, cannot remember who I got it from, but if you send me your email address, I will send you a copy. It is called QSCimp I t...