I hope this will finally satisfy your problem.
Here you go one more time.
Stored procedure :
CREATE procedure Requistions_add
( @RequistionId int OUTPUT
, @CreatedTs datetime
...
)
...
...
SET @RequistionId = scope_identity()
RETURN 0
GO
Declare paremeter objects to map to your stored procedures. Remember,
paramNewReqID.Direction = ParameterDirection.Output
You need to use the ExecuteNonQuery method to get your value.
' Define and add the output parameter to the parameters collection
param = SQLCmd.Parameters.Add(New SQLParameter("@RequistionId ",
SQLDBType.Int))
' Specify the parameter direction
param.Direction = ParameterDirection.Output
' Open the connection
SQLConn.Open()
' Execute the command
sqlCmd. ExecuteNonQuery ()
' Store returned data
RequistionId = sqlCmd.Parameters("@RequistionId").Value.ToString
Kiran.
-----Original Message-----
From: Eliezer Broder [mailto:[EMAIL PROTECTED]
Sent: Friday, February 04, 2005 1:33 PM
To: [email protected]
Subject: Re: [AspNetAnyQuestionIsOk] SQL error: "Procedure
'Requistions_add' expects parameter '@RequistionId', which was not
supplied."
Kiran,
Almost... The problem is that my DBA won't let me leave that little
SELECT-statement in the SP. He wants my VB.NET code to retrieve the
Output parameter *without* using a SELECT-statement. And on that, I
ask, how should I do that?
Thanks so much (again)!
Eliezer
On Fri, 4 Feb 2005 13:07:17 -0500, [EMAIL PROTECTED]
<[EMAIL PROTECTED]> wrote:
> 1)
> In your case you do not need the output parameter.
>
> You can modify the procedure as
> CREATE procedure Requistions_add
> ( @CreatedTs datetime
> , @PersonId_Creator smallint
> .....
> )
> ...
> select @RequistionId --this line added by Eliezer to get it to run
> with ExecuteScalar
>
> The final select statement will take care of retrieving the scalar
> value.
>
> Remember to remove your parameter object too when you remove the
> output parameter in stored procedure.
>
> Just have the return value stored in a variable. Reducing number of
> objects being used will improve the performance.
>
> 2) Output parameters are generally used in conjunction with
> DataReaders. Also note that you can read values from your output
> parameters only after your datareader object is closed.
>
> Hope this helps.
>
> Kiran.
>
>
> -----Original Message-----
> From: Eliezer Broder [mailto:[EMAIL PROTECTED]
> Sent: Friday, February 04, 2005 10:49 AM
> To: [email protected]
> Subject: Re: [AspNetAnyQuestionIsOk] SQL error: "Procedure
> 'Requistions_add' expects parameter '@RequistionId', which was not
> supplied."
>
> Kiran,
>
> You were correct! Thank you. That solved one problem. After fixing
> that, it compiled and ran. But the only other problem is that it
> returns only "0". I added a Select at the end of the SP to get it to
> return a real value - and it does. But the DBA says that's not how an
> output parameter should be used. Here's the full code of the SP:
>
> CREATE procedure Requistions_add
> ( @RequistionId int OUTPUT
> , @CreatedTs datetime
> , @PersonId_Creator smallint
> , @OrganizationId_Charge smallint
> , @OrganizationId_Install smallint
> , @FacilityId_Install smallint
> , @BusinessNeedDescr varchar( 255 )
> , @ContactLastName varchar( 255 )
> , @ContactFirstName varchar( 255 )
> , @Phone_Contact varchar( 255 )
> , @Email_Contact varchar( 255 )
> , @PCScope varchar( 255 )
> , @PCName varchar( 255 )
> , @HelpDeskTicket varchar( 255 )
> , @InstallationInstructions text
> )
> as set nocount on set xact_abort on
>
> if @CreatedTs is null
> set @CreatedTs = current_timestamp
>
> Insert into dbo.Requistions
> ( CreatedTs
> , PersonId_Creator
> , OrganizationId_Charge
> , OrganizationId_Install
> , FacilityId_Install
> , BusinessNeedDescr
> , ContactFirstName , ContactLastName
> , Phone_Contact
> , Email_Contact
> , PCScope
> , PCName
> , HelpDeskTicket
> , InstallationInstructions
> )
> values
> ( @CreatedTs
> , @PersonId_Creator
> , @OrganizationId_Charge
> , @OrganizationId_Install
> , @FacilityId_Install
> , @BusinessNeedDescr
> , @ContactFirstName , @ContactLastName
> , @Phone_Contact
> , @Email_Contact
> , @PCScope
> , @PCName
> , @HelpDeskTicket
> , @InstallationInstructions
> )
>
> set @RequistionId = scope_identity()
>
> select @RequistionId --this line added by Eliezer to get it to run
> with ExecuteScalar
>
> return 0
> GO
>
> WITH that Select at the end, my ExecuteScalar works; without it, I
> always get a "0" value from my ExecuteScalar.
>
> What is, then, the proper way to get the value of an output parameter?
>
> Thanks very much!
> Eliezer
>
> On Thu, 3 Feb 2005 17:08:44 -0500, [EMAIL PROTECTED]
> <[EMAIL PROTECTED]> wrote:
> >
> > You need to change the
> > paramNewReqID.Direction = ParameterDirection.ReturnValue
> >
> > to
> > paramNewReqID.Direction = ParameterDirection.Output
> >
> > since you declared it as an output parameter in stored procedure.
> >
> > Kiran.
> >
> > -----Original Message-----
> > From: Eliezer Broder [mailto:[EMAIL PROTECTED]
> > Sent: Thursday, February 03, 2005 5:03 PM
> > To: [email protected]
> > Subject: [AspNetAnyQuestionIsOk] SQL error: "Procedure
> > 'Requistions_add' expects parameter '@RequistionId', which was not
> > supplied."
> >
> > I've got a SQL Server 2000 stored procedure called 'Requistions_add'
> > that takes a whole bunch of input parameters and one output
> > parameter:
> >
> > CREATE procedure Requistions_add
> > ( @RequistionId int OUTPUT
> > , @CreatedTs datetime
> > , @PersonId_Creator smallint
> > , @OrganizationId_Charge smallint
> > , @OrganizationId_Install smallint
> > , @FacilityId_Install smallint
> > , @BusinessNeedDescr varchar( 255 )
> > , @ContactLastName varchar( 255 )
> > , @ContactFirstName varchar( 255 )
> > , @Phone_Contact varchar( 255 )
> > , @Email_Contact varchar( 255 )
> > , @PCScope varchar( 255 )
> > , @PCName varchar( 255 )
> > , @HelpDeskTicket varchar( 255 )
> > , @InstallationInstructions text
> > )
> >
> > [ etc ...]
> >
> > The VB.NET code looks like this:
> >
> > Dim cmSaveReq As New SqlCommand
> > cmSaveReq.Connection = cnDeliveryAndInstallation
> > cmSaveReq.CommandType = CommandType.StoredProcedure
> > cmSaveReq.CommandText = "dbo.Requistions_add"
> >
> > 'set up output parameter
> > Dim paramNewReqID As New SqlParameter
> > paramNewReqID.ParameterName = "@RequistionId"
> > paramNewReqID.SqlDbType = SqlDbType.Int
> > paramNewReqID.Direction = ParameterDirection.ReturnValue
> > cmSaveReq.Parameters.Add(paramNewReqID)
> >
> > cmSaveReq.Parameters.Add("@CreatedTs", Now)
> > cmSaveReq.Parameters.Add("@PersonId_Creator",
> personID.Text)
> > cmSaveReq.Parameters.Add("@OrganizationId_Charge",
> > costCenterDeptCharged.Text)
> >
> > [ some more input params added ]
> >
> > If cnDeliveryAndInstallation.State =
> > ConnectionState.Closed
>
> > Then
> > cnDeliveryAndInstallation.Open()
> > Dim intNewReqID As Integer = cmSaveReq.ExecuteScalar
> >
> > On that last line of ExecuteScalar - I get that error (see subject
> > line, please). What is going on here? I AM supplying it with the
> > parameter!
> >
> > Any help would be more than appreciated!
> >
> > Thanks,
> > Eliezer
>
Yahoo! Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/AspNetAnyQuestionIsOk/
<*> To unsubscribe from this group, send an email to:
[EMAIL PROTECTED]
<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/
