Get Return value

[chicdog]

I'm having problems getting the return value from the stored procedure that inserts a set of records into a table.

Here is the code (abreviated) that I use to insert parameters:

Dim InsertCmd As New SqlCommand("stpInsertNewFunction", cnMeetings)
InsertCmd.CommandType = CommandType.StoredProcedure

InsertCmd.Parameters.Add(New SqlParameter("@CreateID", SqlDbType.VarChar, 30))
InsertCmd.Parameters("@CreateID&quot.Value = UserName

InsertCmd.Parameters.Add(New SqlParameter("@Location", SqlDbType.VarChar, 100))
InsertCmd.Parameters("@Location&quot.Value = txtLocation.Text

..........
..........
..........
..........

InsertCmd.Parameters.Add(New SqlParameter("@ContactEmail", SqlDbType.VarChar, 30))
InsertCmd.Parameters("@ContactEmail&quot.Value =txtC_Email.Text

InsertCmd.Parameters.Add(New SqlParameter("@MeetingKey", SqlDbType.Decimal).Direction=ParameterDirection.ReturnValue)

Dim Meetings As Decimal = InsertCmd.ExecuteScalar()

 

[dragonwell]

Let's see that sproc. You might want to consider using an OUTPUT param instead of a scalar value, too.

 

[chicdog]

ALTER PROCEDURE stpInsertNewFunction
(@FacilityKey decimal (8)=null,
@FacilityName Varchar (100),
@CreateID varchar(30),
@Location Varchar (100),
..........
..........
@ContactEmail varchar(30),
@MeetingKey Int OUTPUT)

AS
/* Insert information into tblMeetings*/
INSERT INTO dbo.tblMeetings (Description, Location, Facility, MeetingStartDate, MeetingEndDate,
RoomsStartDate, RoomsEndDate, MaximumAttendees,MaximumWarning,Active,CreateDate,CreateID)
Values (@Description,@Location,@FacilityName,@MStart,@MEnd,@RStart,@REnd,@MaxAttendees,
@MaximumWarning,'Y',(convert(char(10),getdate(),101)),@CreateID)

set @MeetingKey = @@Identity

Select @MeetingKey
...<Code in between that inserts data into additional tables>
.......
RETURN @MeetingKey

 

[chicdog]

I changed it to now read :
InsertCmd.Parameters.Add(New SqlParameter(&quot;@MeetingKey&quot;, SqlDbType.Int, ParameterDirection.Output))

It now gives me the error:
Procedure 'stpInsertNewFunction' expects parameter '@MeetingKey', which was not supplied.

I did supply it though.What did I do wrong?

Chikey

 

[dragonwell]

I'm not sure if you can create the SqlParameter like that. Try this:

Dim parameterMeetingKey As new SqlParameter("@MeetingKey", SqlDbType.Int, 4)

parameterMeetingKey.Direction = ParameterDirection.Output

InsertCmd.Parameters.Add(parameterMeetingKey)

 

[chicdog]

Thank you that worked perfectly.

Chikey