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/
