output parameters, dbnull returned

[stessie]

Hi Am trying to execute a very basic stored proc (sybase)
It appears to run fine but no values get returned
It works perfectly when i execute it through sql advantage
any ideas?
here is code
CREATE PROC test
(
@person numeric(8, 0),
@name numeric(8, 0) OUTPUT

)
AS

IF @person = 1
SELECT @name =23

.NET Code
myCommand = New OleDbCommand("test", objConnect)
myCommand.CommandType = CommandType.StoredProcedure

myParam = myCommand.Parameters.Add("@person", OleDbType.numeric, 8)
myParam.SourceColumn = "person"

myParam = myCommand.Parameters.Add("@name", OleDbType.numeric,8)
myParam.Direction = ParameterDirection.Output
myParam.SourceColumn = "name"

objConnect.Open()

myCommand.ExecuteNonQuery()
Response.Write(myCommand.Parameters("@name&quot.Value.ToString())

objConnect.Close()


thanks in advance



;-)

 

[stessie]

Hi
Have been looking into this and a return value is returned fine
Is there a bug when using sybase that output parameters do not get returned?


Rgds
S

;-)

 

[RiverGuy]

Are you sending this sp a value other than 1? It appears your procedure only sets the value if your input parameter is 1.

 

[stessie]

Hi
I am only using this really for testing
the real stored proc I want to call has 6 input and 2 output parameters so I've cut it down
I always pass it 1

Rgds

;-)

 

[stessie]

Hi
Have found the solution
Call the proc from another proc and return a "recordset"
Reference this in .NET code
CREATE PROC test
(
@person numeric(8, 0),
@name numeric(8, 0) OUTPUT

)
AS

IF @person = 1
SELECT @name =23



CREATE PROC testCall
(
@person numeric(8, 0)

)
AS
SET NOCOUNT ON
DECLARE @name numeric(8,0)

EXEC test @person, @name OUTPUT
SELECT @name 'NAME'

GO


Dim strSql As String
Dim objConnect As New OleDbConnection
Dim objReader As OleDbDataReader
Dim myParam As OleDbParameter
Dim strMessage As String
Dim Result As DialogResult


sSQL = "testcall 1"
Dim myCommand As New OleDbCommand(sSQL, objConnect)

objConnect.Open()
objReader = myCommand.ExecuteReader()
objReader.Read()
strMessage = "This name is " & objReader.Item("NAME&quot

Result = MessageBox.Show(strMessage, "name", MessageBoxButtons.OKCancel, MessageBoxIcon.Question, MessageBoxDefaultButton.Button1, MessageBoxOptions.ServiceNotification)

Works fine!!

;-)