I am passing a value to a parameter in a Stored Procedure and also declaring it's direction as ParameterDirection.InputOutput. In the Stored Procedure, the parameter is also declared as an OUTPUT parameter and the value being returned from the Stored Procedure is correct. I know this because I have tested executing the Stored Procedure on its own and using PRINT to display different values throughout the procedure. Also, the final part of the Stored Procedure inserts a record into the database using the value that I am expecting to be returned and that is appearing as expected. However, when I attempt to retrieve the value from the parameter after the SqlCommand has been executed, the value is still the previous value that I passed to the Stored Procedure. I have done this before and I am sure I've done it no differently. Here's a part of my command:
Dim cmd As New SqlCommand("StoredProcedure", Conn)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add("@FileName", SqlDbType.NVarChar).Value = ImageFileNameNoExt
cmd.Parameters("@FileName").Direction = ParameterDirection.InputOutput
cmd.ExecuteNonQuery()
ImageFileName = cmd.Parameters("@FileName").Value
Here, I am expecting ImageFileName to be the value of the @FileName parameter, returned from the Stored Procedure. However, it's still the same value as ImageFileNameNoExt which was passed to the procedure in the first place.
Can someone please tell me what I'm doing wrong? Thanks.
EDIT
ALTER PROCEDURE [dbo].[sp_ContentPages_Images_Update]
@PageID int
,@FileName nvarchar(100) OUTPUT
,@Ext nvarchar(100)
AS
BEGIN
SET NOCOUNT ON;
--As the FileName is a unique key, ensure that the value being entered into the database will be unique.
--If its not, then we can generate a new one
SET @FileName = REPLACE(@FileName, ' ', '-')
DECLARE @i int
,@FileNameCheck nvarchar(200)
SET @i = 2
SET @FileNameCheck = @FileName + @Ext
WHILE (SELECT COUNT(*)
FROM [ContentPages_Images]
WHERE [FileName]=@FileNameCheck
AND (@PageID IS NULL OR NOT @PageID=ID)
) > 0
BEGIN
SET @FileNameCheck = @FileName + '-' + CAST(@i as nvarchar(3)) + @Ext
SET @i = @i + 1
END
SET @FileName = @FileNameCheck
--Get new layout value
DECLARE @Layout int
SET @Layout = 1
IF (SELECT COUNT(*) FROM [ContentPages_Images] WHERE PageID=@PageID) > 0
BEGIN
SET @Layout = (SELECT MAX(Layout) FROM [ContentPages_Images] WHERE PageID=@PageID) + 1
END
INSERT INTO [ContentPages_Images]
(PageID, [FileName], [Layout])
VALUES
(@PageID, @FileName, @Layout)
END
