Not reading the return value from SQL Server

[adalli]

HI,

I am trying to read the return value from an SQL Server 2005 store procedure. Following is my code. The return value always return a value of 0.

Store Procedure
================
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

alter procedure [dbo].[InsertClass_SP]
@ReturnValue int OUTPUT,
@ClassName nchar(20),
@UserCode nchar(30)

as

BEGIN
set NOCOUNT ON;

if exists(select ClassName from Classes_V where ClassName = @ClassName)
set @ReturnValue = 9999

else
insert into Classes_V(ClassName, UserCode, Table_Updated)
values (@ClassName, @UserCode, GETDATE());

set @ReturnValue = @@error
END
GO


ASP.NET
=======
Dim dbConnection As New System.Data.OleDb.OleDbConnection(ConfigurationManager.ConnectionStrings("dbConnection_User").ConnectionString.ToString)
Dim myCommand As New System.Data.OleDb.OleDbCommand("InsertClass_SP", dbConnection)
myCommand.CommandType = CommandType.StoredProcedure
Dim retValParam As New OleDbParameter("@ReturnValue", SqlDbType.Int)
retValParam.Direction = ParameterDirection.Output
myCommand.Parameters.Add(retValParam)

myCommand.Parameters.Add(New OleDbParameter("@ClassName", OleDbType.VarChar)).Value = txtClassName.Text.Trim
myCommand.Parameters.Add(New OleDbParameter("@UserCode", OleDbType.VarChar)).Value = Session("sessionUserName")

dbConnection.Open()
myCommand.ExecuteNonQuery()

Dim test As Integer = Convert.ToInt32(retValParam.Value)

myCommand.Dispose()
dbConnection.Close()


Can any one please help?


Many thanks in advance,

 

[jbenson001]

are you sure you should be getting a value of 99999 back? Does your data meet that criteria? To debug, run your queruy in query analyzer and make sure it returns what you expect.