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/
