OUTPUT parameters in Sybase Return Null value

Question

Hi everybody

I have a Stored procedure(SyBase 15.0) that returns a OUTPUT value as integer, but it always returns a DBNull. I tried to change the returns a OUTPUT value as Varchar and it returns the correct value. 

I am using VS 2005 and below the sample code and store procedure

Dim connString As String = String.Format("{0}UID='{1}';PWD='{2}';", CONNECTIONSTRING, AvantXGlobal.Helper.UserName, AvantXGlobal.Helper.UserPassword)
        Dim conn As AseConnection = Nothing
        Dim outputId As Integer = 0

        Try

            conn = New AseConnection(connString)
            If conn.State = ConnectionState.Closed Then
                conn.Open()
            End If

            Dim sproc As String = "proc_CountProspectus"
            'Dim sproc As String = "Proc_Test1"
            Dim cmd As New AseCommand(sproc, conn)
            cmd.CommandType = CommandType.StoredProcedure



            Dim userParam As AseParameter = cmd.Parameters.Add("@companyKeyCode", AseDbType.Integer)
            userParam.Direction = ParameterDirection.Input
            userParam.Value = companyKeyCode

            Dim userParamOut As AseParameter = cmd.Parameters.Add("@Total", AseDbType.Integer)
            userParamOut.Direction = ParameterDirection.Output
          
            'cmd.Parameters("@Total").Direction = ParameterDirection.InputOutput

            'Dim userParamOut As AseParameter = cmd.Parameters.Add("@result", AseDbType.Integer)
            'userParamOut.Direction = ParameterDirection.Output
            ' cmd.Parameters.Add(userParamOut)


            cmd.ExecuteNonQuery()
            'cmd.Dispose()

            outputId = DataHelper.ToNullableInt32(userParamOut.Value)

        Catch ex As AseException
            Throw New Exception(ex.Message)
        Finally
            conn.Dispose()
        End Try
        Return outputId

companyKeyCode int, @Total int OUTPUT 


AS 


set @Total = 0
select @Total = count(*) from Prospectus where companyKeyCode=@companyKeyCode 
select @Total

Thanks is advance.

Regards,

Syukur

---

akois

Answer 1

Hi everybody

I have a Stored procedure(SyBase 15.0) that returns a OUTPUT value as integer, but it always returns a DBNull. I tried to change the returns a OUTPUT value as Varchar and it returns the correct value. 

I am using VS 2005 and below the sample code and store procedure

companyKeyCode int, @Total int OUTPUT 


AS 


set @Total = 0
select @Total = count(*) from Prospectus where companyKeyCode=@companyKeyCode 
select @Total

 Dim connString As String = String.Format("{0}UID='{1}';PWD='{2}';", CONNECTIONSTRING, AvantXGlobal.Helper.UserName, AvantXGlobal.Helper.UserPassword)
        Dim conn As AseConnection = Nothing
        Dim outputId As Integer = 0

        Try

            conn = New AseConnection(connString)
            If conn.State = ConnectionState.Closed Then
                conn.Open()
            End If

            Dim sproc As String = "proc_CountProspectus"
            'Dim sproc As String = "Proc_Test1"
            Dim cmd As New AseCommand(sproc, conn)
            cmd.CommandType = CommandType.StoredProcedure



            Dim userParam As AseParameter = cmd.Parameters.Add("@companyKeyCode", AseDbType.Integer)
            userParam.Direction = ParameterDirection.Input
            userParam.Value = companyKeyCode

            Dim userParamOut As AseParameter = cmd.Parameters.Add("@Total", AseDbType.Integer)
            userParamOut.Direction = ParameterDirection.Output
          
            'cmd.Parameters("@Total").Direction = ParameterDirection.InputOutput

            'Dim userParamOut As AseParameter = cmd.Parameters.Add("@result", AseDbType.Integer)
            'userParamOut.Direction = ParameterDirection.Output
            ' cmd.Parameters.Add(userParamOut)


            cmd.ExecuteNonQuery()
            'cmd.Dispose()

            outputId = DataHelper.ToNullableInt32(userParamOut.Value)

        Catch ex As AseException
            Throw New Exception(ex.Message)
        Finally
            conn.Dispose()
        End Try
        Return outputId

Thanks is advance.

Regards,

Syukur

---

akois

Answer 2

Seems you are using ASE ADO.NET Data Provider which is provided by Sybase.

I remember of facing similar issue long back and below was my understanding specific to ASE. Based on some experiments, it looks like output parameters must be declared FIRST when the proc is created. If output parameters are listed after input parameters, it seems that they are assigned DBNull.Value after the proc is executed.

The problems you are experiencing seem to be by the provider’s API itself and I doubt if someone from here will help you.

You can post your question to the respective API’s forum or support site or can have a try at asking @ ADO.NET Managed Providers forum.

---

Lingaraj Mishra

Answer 3

Hi Lingaraja Mishra,

Thanks for your advise, unfortunately i have tried it but it doesn't work.  i will try to ask to suggested forum. Before that do you manage to resolve your problem last time?

---

akois

Answer 4

companyKeyCode int, @Total int OUTPUT 


AS 


set @Total = 0
select @Total = count(*) from Prospectus where companyKeyCode=@companyKeyCode 
select @Total

I see you are using ExecuteNonQuery but this stored procedure also returns a resultset.  Try adding SET NOCOUNT ON and remove the last SELECT statement from the proc like the example below. 

CREATE PROC proc_CountProspectus
	@companyKeyCode int
	, @Total int OUTPUT 
AS
SET NOCOUNT ON

set @Total = 0
select @Total = count(*) from Prospectus where companyKeyCode=@companyKeyCode 
GO

---

Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

Answer 5

Thanks Dan Guzman, but still doesn't work... :(

Someone please help me..

---

akois

Answer 6

I was hoping my suggestions would address the issue but it seems the problem is likely with the Sybase provider.  You might try posting this question to a Sybase forum (http://www.sybase.com/detail?id=1026832) where they should have more first-hand experience with the Sybase ADO.NET provider. 

---

Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

Answer 7

I would have to go along with Dan on this one. I don't see any examples in the Sybase documentation that demonstrate how to use ExecuteNonQuery to return an output parameter.

---

Paul ~~~~ Microsoft MVP (Visual Basic)