OUTPUT parameters

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.

William
Thu May 13 19:38:49 CDT 2004

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" <anonymous@discussions.microsoft.com> wrote in message
news:B40DA1C0-C679-4621-82C0-4C938D6E8E5E@microsoft.com...
> Hi everybody
>
> 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 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
>
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?
>
> Regards
> J.C.

anonymous
Fri May 14 09:06:08 CDT 2004

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.