OUTPUT parameters

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi everybod

I have a Stored procedure(SyBase 12.5) that returns a OUTPUT value, but it always returns a DBNull. I test the SP in other application (DBArtisan like a Query Analizer) and it returns the correct value, but when I test in my NET application (made in C#) it returns DBNull. If I try to make a cast I get the exception "Object cannot be cast from DBNull to other types

This is my code my SP

CREATE PROCEDURE dbo.sp_Prueba
@vsTransId char(4)
@vsContratoId varchar(4)
@vsLoginUser varchar(40)
@vsParamList varchar(200)
@iResp int OUTPU

A
Declare @iError in
Declare @OK in

BEGI

Select @iError =
Select @OK =

BEGIN TRAN Prueba

Insert into LogTransacc
Values (@vsTransId,@vsContratoId,@vsLoginUser,"Pruebas Juan",@vsParamList,"Ini",getdate()

Select @iError = @@erro

if(@iError = @OK)
Begi
Select @iResp = @OK
COMMIT TRAN Prueba
En
els
Begi
Select @iResp = @iErro
ROLLBACK TRAN Prueba
En

EN

And this my function

private void Insertar()
string strcadena = cadenaConn
OleDbConnection oleConn = null
try
oleConn = new OleDbConnection(strcadena)
oleConn.Open()

OleDbCommand oleCmmd = new OleDbCommand()

oleCmmd.CommandType = CommandType.StoredProcedure
oleCmmd.Connection = oleConn
oleCmmd.CommandText = "sp_Prueba"

oleCmmd.Parameters.Add("@vsTransId",OleDbType.Char,4)
oleCmmd.Parameters["@vsTransId"].Value = "9500"

oleCmmd.Parameters.Add("@vsContratoId",OleDbType.VarChar,4)
oleCmmd.Parameters["@vsContratoId"].Value = "0000"

oleCmmd.Parameters.Add("@vsLoginUser",OleDbType.VarChar,40)
oleCmmd.Parameters["@vsLoginUser"].Value = "Juan Carlos Gonzalez"

oleCmmd.Parameters.Add("@vsParamList",OleDbType.VarChar,200)
oleCmmd.Parameters["@vsParamList"].Value = "Esta es una prueba para insertar utilizando Threads"

oleCmmd.Parameters.Add("@iResp", OleDbType.Integer)
oleCmmd.Parameters["@iResp"].Direction = ParameterDirection.Output

oleCmmd.ExecuteNonQuery()

if(oleCmmd.Parameters["@iResp"].Value.Equals(System.DBNull.Value)
MessageBox.Show("OUTPUT Null")

els
MessageBox.Show("OUTPUT No null")

oleCmmd.Dispose()


catch(Exception Ex)
Console.WriteLine(Ex.Message)

finally
if(oleConn != null)
if(oleConn.State != System.Data.ConnectionState.Closed)
oleConn.Close()





Does someone help me, please

Regard
J.C.
 
Juan Carlos:

Close the connection before trying to reference it.

Bill Vaughn has probably the best discussion on it at
www.betav.com ->Articles -> MSDN ->Retrieving the Gozoutas

HTH,

Bill

www.devbuzz.com
www.knowdotnet.com

Juan Carlos said:
Hi everybody

I have a Stored procedure(SyBase 12.5) that returns a OUTPUT value, but it
Click to expand...
always returns a DBNull. I test the SP in other application (DBArtisan like
a Query Analizer) and it returns the correct value, but when I test in my
NET application (made in C#) it returns DBNull. If I try to make a cast I
get the exception "Object cannot be cast from DBNull to other types"
This is my code my SP:

CREATE PROCEDURE dbo.sp_Prueba(
@vsTransId char(4),
@vsContratoId varchar(4),
@vsLoginUser varchar(40),
@vsParamList varchar(200),
@iResp int OUTPUT
)
AS
Declare @iError int
Declare @OK int

BEGIN

Select @iError = 0
Select @OK = 0

BEGIN TRAN Pruebas

Insert into LogTransacc
Values (@vsTransId,@vsContratoId,@vsLoginUser,"Pruebas Juan",@vsParamList,"Ini",getdate())

Select @iError = @@error

if(@iError = @OK)
Begin
Select @iResp = @OK
COMMIT TRAN Pruebas
End
else
Begin
Select @iResp = @iError
ROLLBACK TRAN Pruebas
End

END

And this my function:

private void Insertar() {
string strcadena = cadenaConn;
OleDbConnection oleConn = null;
try {
oleConn = new OleDbConnection(strcadena);
oleConn.Open();

OleDbCommand oleCmmd = new OleDbCommand();

oleCmmd.CommandType = CommandType.StoredProcedure;
oleCmmd.Connection = oleConn;
oleCmmd.CommandText = "sp_Prueba";

oleCmmd.Parameters.Add("@vsTransId",OleDbType.Char,4);
oleCmmd.Parameters["@vsTransId"].Value = "9500";

oleCmmd.Parameters.Add("@vsContratoId",OleDbType.VarChar,4);
oleCmmd.Parameters["@vsContratoId"].Value = "0000";

oleCmmd.Parameters.Add("@vsLoginUser",OleDbType.VarChar,40);
oleCmmd.Parameters["@vsLoginUser"].Value = "Juan Carlos Gonzalez";

oleCmmd.Parameters.Add("@vsParamList",OleDbType.VarChar,200);
oleCmmd.Parameters["@vsParamList"].Value = "Esta es una prueba para insertar utilizando Threads";

oleCmmd.Parameters.Add("@iResp", OleDbType.Integer);
oleCmmd.Parameters["@iResp"].Direction = ParameterDirection.Output;

oleCmmd.ExecuteNonQuery();

if(oleCmmd.Parameters["@iResp"].Value.Equals(System.DBNull.Value))
MessageBox.Show("OUTPUT Null");

else
Click to expand...
MessageBox.Show("OUTPUT No null");
 
Hi William, thanks for answer m

I close the connection after the ExecuteNonQuery, but I get the same result. Look

private void Insertar()
string strcadena = cadenaConn
OleDbConnection oleConn = null
try
oleConn = new OleDbConnection(strcadena)
oleConn.Open();
OleDbCommand oleCmmd = new OleDbCommand()

oleCmmd.CommandType = CommandType.StoredProcedure
oleCmmd.Connection = oleConn
oleCmmd.CommandText = "sp_Prueba"

oleCmmd.Parameters.Add("@vsTransId",OleDbType.Char,4)
oleCmmd.Parameters["@vsTransId"].Value = "9500"

oleCmmd.Parameters.Add("@vsContratoId",OleDbType.VarChar,4)
oleCmmd.Parameters["@vsContratoId"].Value = "0000"

oleCmmd.Parameters.Add("@vsLoginUser",OleDbType.VarChar,40)
oleCmmd.Parameters["@vsLoginUser"].Value = "Juan Carlos Gonzalez"

oleCmmd.Parameters.Add("@vsParamList",OleDbType.VarChar,200)
oleCmmd.Parameters["@vsParamList"].Value = "Esta es una prueba para insertar utilizando Threads"

oleCmmd.Parameters.Add("@iResp", OleDbType.Integer)
oleCmmd.Parameters["@iResp"].Direction = ParameterDirection.Output

oleCmmd.ExecuteNonQuery()
oleConn.Close()

if(oleCmmd.Parameters["@iResp"].Value.Equals(System.DBNull.Value)
MessageBox.Show("OUTPUT Null")

/*int iValor = Convert.ToInt32(oleCmmd.Parameters["@iResp"].Value)
txtCaja.Text = txtCaja.Text + iValor.ToString()
//MessageBox.Show(iValor.ToString());*
Thread.Sleep(1000)
oleCmmd.Dispose()


catch(Exception Ex)
Console.WriteLine(Ex.Message)

finally
if(oleConn != null)
if(oleConn.State != System.Data.ConnectionState.Closed)
oleConn.Close()


Finalizar(Thread.CurrentThread.Name)



What is wrong
Regard
J.C.
 
Back
Top