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
1157 Views

Similar Articles

[PageSpeed] 22

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:

Covered Call Options
Hello all, I do I go about dealing with a covered call transaction in Microsoft Money 2007 Premium? Currently what I am doing is inputing the option symbol, and choosing the transaction "Sell to Open" then when I go to expire the option I try to do a Sell to Close. However it does not seem to work? Also what happens if instead of expired I has the position assigned? how would I account for that? Thank you. Shhhh In microsoft.public.money, Shhhh wrote: > >I do I go about dealing with a covered call transaction in Microsoft >Money 2007 Premium? Currently what I am doing is...

2003 store requirements on specialized servers
I have seen bits and pieces of best practices as it relates to the usage of mailbox and public folder stores but wanted to ask here to get more advice. I have a server that will hold only public folders, a server that will act as a bridgehead and internet connector, and a number of mailbox only servers. My questions are: 1. Should I delete the publc folder stores from all but the primary and secondary specialized servers? What is the impact to free/busy etc. As long as the public folder server is specified for each mailbox store is there any reason to hold local copies of the public fold...

Call in?
Do new owners of Office X have to call up Microsoft to register their copy? If yes, what's the number? thanks. Ming <asdf@asdf.com> wrote: > Do new owners of Office X have to call up Microsoft to register their > copy? If yes, what's the number? thanks. Nope. We're not using Windoze :->>> This version does not require activation. Corentin -- - Mac:MS MVP (Francophone) - (MS) MVP: http://support.microsoft.com/default.aspx?ln=FR&scid=fh;FR;mvp Newsgroups produits MS: http://support.microsoft.com/newsgroups/?ln=FR ...

Ctrl+C (copy) doesn't work within GP
I have a user that when using Ctrl+C to copy in GP 9 doesn't work. You can go to edit>>copy and it will, but the hotkey/shortcut doesn't work. In other programs such as Word it works fine. In a test the machine was able to copy info from Word and Paste into GP by using the hotkeys, but another attempt to copy/paste within GP it failed to copy and just pasted what was previously copied from Word. Any ideas? I have run into this a few times but my issue was that if you try to paste some text into a field in GP that is more than the maximum allowed, then it just does not...

Query output in Excel
I am trying to import a database data in excel from db_vista database and it limits the output to 25 rows. Does anybody know how to increase the output? -- Anna ------------------------------------------------------------------------ Anna's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1077 View this thread: http://www.excelforum.com/showthread.php?threadid=540407 Anna Just a guess....try this: Edit the query in MS Query <edit><options> Is "Limit number of records returned to" checked? Does that help? Regards, Ro -- Ron Coderr --...

Exchange 2000 mailbox store
I don't know why my exchange 2000 mailbox store keeps being dismounted by itself. By the time i restart the server its mounted again. It does this every 3/4hours each day. I have Win2000 SP4 and ex2000 running. The exchange server is on the schema master DC there must be something in your application or system event logs on why it's doing this... "tayoajax" <tayoajax@discussions.microsoft.com> wrote in message news:A3129678-76C8-4101-90A9-24F7F1173B56@microsoft.com... > I don't know why my exchange 2000 mailbox store keeps being dismounted by > itself....

Call center tracking and reporting
Anyone use CRM 4.0 for call tracking? Our Sales/Marketing is set up. Now I need to decide how to most efficiently customize CRM 4.0 for our company's call tracking portion. Can someone send me a link to information? thanks ...

Using OUTPUT TO in VBA, then how to modify the XLS from Access?
I'm using the simple "OUTPUTTO" command to send a query over to Excel, however we will have many users and we would like to programmatically add the header rows, date printed, turn on the auto filter and freeze panes, all of which I can do within a Macro in Excel, but how do I call that macro to run from Access and How to run it on the file that they just created? Current Code: DoCmd.OutputTo acOutputQuery, "MainRptWUser", acFormatXLS, , True "ThriftyFinanceGirl" wrote: > I'm using the simple "OUTPUTTO" command to send...

telephone calls
when placing a telephone call using outlook, everything works fine except I can't hear the person i am talking to. They can hear me find. When I use a telephone program not associated with outlook every thing works great. can someone help ...

Echange 2003 Information Store DB
Wanted to see if my functional concept is correct about the way Exchange handles free space. In a hypethetical info store DB say of 50G in size, an admin deleltes a series of old mailboxes which amount to about 8Gs. Even though you delete and purge the mailboxes the overall size of the store does not decrease. For this to occurr you need to run eseutil.exe /d. If you don't then the free space is there but just not release to the OS for use; exchange owns it and will use it as it needs more free space before taking more additional space from outside the Store. Is this functional...

change screen resolution in C#
Hi all, I found the following code on the web. It says it is for C#. Problem is that it seems to be for ASP (it uses the Page_Load procedure) and I need it for a regular Windows form. can anyone suggest how it may be modified for my needs. Thanks ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Page_Load Code Screen Srn = Screen.PrimaryScreen; tempHeight = Srn.Bounds.Width; tempWidth = Srn.Bounds.Height; Page.ClientScript.RegisterStartupScript(this.GetType(), "Error", "<script type=\"text/javascript\">alert('" + &qu...

Free space size of Information store discrepancy in Event viewer
Hi Runnning Exchange 5.5 sr2 on Windows 2000 server. The Hard drive that Exchange is on is 30 GB. I do an offline backup nightly so i stop the services do the backup and then start. When starting I get message in Event Viewer: Event ID 1216 Private Information store is limited to 16 GB. Ok this is fine the size of priv.edb is currently 7.42 GB. Now i notice that during an online defrag I am getting the following message: ID 1221, Private information store has 519 of free space. So as you see that doesn't add up 16 - 7.42 GB should leave more than 519 MB or free space or atleast i am ...

Calling Macro
Hi All, I have macro like this sub unique() '''''''' '''''''' Counter_item = 0 For Each Item In nodups counter_item = counter_item + 1 bic_var = Item Call Ps_Match Next Item end sub I have another macro Sub Ps_Match() ''''' ''''' If counter_item <= 1 Then call another_macro() end if end sub In the first macro i am declaring counter_item equals to 1. In my second macro i need to call that number. At the to of the module put: Public Counter_I...

Comparing two XML files in C#
Hallo everybody, I have the following problem. There are two XML files: first one (FIRST.xml) includes all products definition, the second one (SECOND.xml) is a product instance. What do I need is to compare these two files. Let's see an example: FIRST.xml <Product ID=""> <Element ID=""/> <Element ID=""/> ..... <Element ID=""/> </Product> SECOND.xml <Root> <Products> <Product ID=""> <Module Name=""/> <Module Name="...

output question
#define the properties to be returned ($colPropList is an array) $colProplist = "name","member" foreach ($i in $colPropList) { $Searcher.PropertiesToLoad.Add($i) } Above is the code to set the properties for a directory search but it produces a 0 1 in the outputs. Any one happen to have experienced this and how to get rid of the 0 1 output ? Thanks Redirect result of the method to $null { $Searcher.PropertiesToLoad.Add($i) > $null } -Paul "Chris" wrote: > #define the properties to be returned ($colPropList is an array) > $col...

Deleting mailbox store containing System Attendant mailbox
I've got an Exchange2000 installation where I need to delete a Mailbox store but the store contains the system attendant mailbox. I can not find any info on how to move this mailbox to another mailbox store. Any help would be appreciated. ...

Workflow -Assembly Call
How does add subtract in the Workflow>> assembly call works. I do understand that ypu can write your own custom workflows, register it and then call them from here, but I am pretty confused with the existing functionality. workflows can call other workflows workflows can also call a method in a .net assembly. ie you could write a piece of code in c# or vb.net and then have the workflow call your code. ======================= John O'Donnell Microsoft CRM MVP http://codegallery.gotdotnet.com/crm "SAM" <SAM@discussions.microsoft.com> wrote in message news:DE...

looking for user in a home furnishing store
I have a prospect running a home furnishings store who would like to speak with someone using RMS in a similar type store (sofas, chairs, tables, lighting, and associated decor) My prospect is in Wellesley, MA. How can I find a local user, or at least a similar user that my prospect can talk with? -- FRB I am using rms for out door furniture tables chairs and more what are you looking for what can i do to help "FRB" wrote: > I have a prospect running a home furnishings store who would like to speak > with someone using RMS in a similar type store (sofas, chairs, tab...

Private store has 4GB of mail and its size is 16GB!?
Anyone know why would a Exchange 2000 SE that has 4GB of mail on it have 16GB priv1.edb private store? It had bad backups and a lot of logs - we fixed backup on it and the logs cleared - is now the only way to recover space in the database to do offline defrag (online defrag did not do much at all)??? Thanks! -- Dejan Ristic yes...only way to recover disk space is to perform an offline defrag...before you do that, check for event ID 1221 in your event log to find out how much space you can expect to recover...and before you do the defrag, ensure you have a good full online backup... ...

Async calls to WebService in MFC
Hey guys, I use a webservice over the net which is scripted in VB.Net. This web service receives a username and a password and returns a string structure with multiples infos. If the string is empty, I had the wrong username and/or password. Pretty simple isn't? Ok now the problem is a bit more complex. I'm using an MFC client to connect to these web services, and since they're on the internet, there's a delay between sending the data and receiving an answer. I don't know really how I can tell me client to "wait for the string". The wrapper MFC created for the w...

Where is email stored?? HELP!!
I just reinstalled Outlook and for some reason it is not displaying the emails that i had in my old Inbox. Are all emails that are in your Inbox saved in one file anywhere that I can find and import into the new Outlook? PLEASE HELP ME!!! Thanks! Microsoft Outlook stores all e-mail, tasks, calendar, contacts, .etc in a file with a *.pst extension. Since you didn't state what operating system you are using, you will need to use Windows Explorer to search for it. (Note: If you are using Windows XP, do not forget to configure the search to look in hidden/system folders.) "SPH&qu...

Borland C++ Builder
I use to use Borland C++ builder. What is the closest equivelent in the VC++.Net suite? I basically want drag-n-drop UI construction with automatic generation of code stubs for UI events callbacks Will using the resource editor with an MFC project get me close Thanks ...

Invalid procedure call or argument?
Hello All This is a re-post of a problem I posted a while ago, but to which I got no suggestions: hopefully I'll be more lucky this time!? I have the following code in a Private Sub: If InStr([ResponseXML], "element") > 0 Then errorfield = "Invalid " & Mid([ResponseXML], InStr([ResponseXML], "element"), InStr(InStr(InStr([ResponseXML], "element"), [ResponseXML], "'") + 1, [ResponseXML], "'") - InStr([ResponseXML], "element") + 1) Else .... Most of the time this works fine, but for some...

Store Quantity Check
We are using the Store ops and Hq and we are trying to use the POS to check other stores quantities for products but it is coming up with attempts to contact HQ failed we have checked the config and the system talks to each other when downloading and uploading worksheets Each register needs to be set up to find the HQ Client. Look in Store Ops Administrator, File/Configuration, HQ Client Tab -- Glenn Adams Tiber Creek Consulting http://www.tibercreek.com glenn@tibercreek.com ---------------------------------------------- Please DO NOT respond to me directly but post all responses here...

Where to call SetWindowPos
In my SDI, can I place the following in CMainFrame::OnCreate( )? Or is there a better place? // ensure window opens on top when called by other applications, esp. the installer SetWindowPos(&wndTopMost, 0, 0, 0, 0, SWP_NOMOVE | SWP_NOSIZE); Note that wndTopMost really means it is always on top and can never have another app come on top of it. So wndTopMost is often a poor choice. SetWindowPos would not be the best choice here; SetForegroundWindow would be better. joe On Sat, 11 Aug 2007 17:04:00 -0400, "SteveR" <srussell@removethisinnernet.net> wrote: >...