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/
