How to get return value from sp when using OleDbConnection

[Mullin Yu]

As subject, if the sp returns a recordset, i can use OlbDbAdapter to get it,
but how about just a return value from sp, e.g. 0 - successful; 1 - error

like
lter proc UpdateJobItemSt atus @JobItemID as bigint, @Status as int as

BEGIN
Begin Tran

-- Fail => locked, release locked, increase NoOfFailure
If @Status = 4
BEGIN
Update OutboundQueueIt em set Status = @Status, NoOfFailure=NoO fFailure+1
where JobItemID = @JobItemID
IF (@@ERROR <> 0) GOTO ERR_HANDLER
END
ELSE
BEGIN
Update OutboundQueueIt em set Status = @Status where JobItemID = @JobItemID
IF (@@ERROR <> 0) GOTO ERR_HANDLER
END

Commit Tran
RETURN 0
ERR_HANDLER:
PRINT 'Unexpected error occurred!'
ROLLBACK TRAN
RETURN 1

END

=============== =============== =============

OleDbCommand oleCommand = new OleDbCommand();

oleCommand.Comm andText = "UpdateJobItemS tatus";

oleCommand.Comm andType = CommandType.Sto redProcedure;

oleCommand.Conn ection = conn;

OleDbParameter paramJobItemID = new OleDbParameter( "@JobItemID ",
OleDbType.BigIn t, 8, ParameterDirect ion.Input, false, 0, 0, "JobItemID" ,
DataRowVersion. Current, lngJobItemID);

OleDbParameter paramStatus = new OleDbParameter( "@Status",
OleDbType.Integ er, 4, ParameterDirect ion.Input, false, 0, 0, "Status",
DataRowVersion. Current, intStatus);

oleCommand.Para meters.Add(para mJobItemID);

oleCommand.Para meters.Add(para mStatus);
......
......

[William Ryan]

You'll want to use Either ReturnValue or Output as parameter type...that
should fix it for you.
"Mullin Yu" <mu*******@ctil .com> wrote in message
news:u0******** ******@TK2MSFTN GP09.phx.gbl...

As subject, if the sp returns a recordset, i can use OlbDbAdapter to get it, but how about just a return value from sp, e.g. 0 - successful; 1 - error

like
lter proc UpdateJobItemSt atus @JobItemID as bigint, @Status as int as

BEGIN
Begin Tran

-- Fail => locked, release locked, increase NoOfFailure
If @Status = 4
BEGIN
Update OutboundQueueIt em set Status = @Status, NoOfFailure=NoO fFailure+1
where JobItemID = @JobItemID
IF (@@ERROR <> 0) GOTO ERR_HANDLER
END
ELSE
BEGIN
Update OutboundQueueIt em set Status = @Status where JobItemID = @JobItemID IF (@@ERROR <> 0) GOTO ERR_HANDLER
END

Commit Tran
RETURN 0
ERR_HANDLER:
PRINT 'Unexpected error occurred!'
ROLLBACK TRAN
RETURN 1

END

=============== =============== =============

OleDbCommand oleCommand = new OleDbCommand();

oleCommand.Comm andText = "UpdateJobItemS tatus";

oleCommand.Comm andType = CommandType.Sto redProcedure;

oleCommand.Conn ection = conn;

OleDbParameter paramJobItemID = new OleDbParameter( "@JobItemID ",
OleDbType.BigIn t, 8, ParameterDirect ion.Input, false, 0, 0, "JobItemID" ,
DataRowVersion. Current, lngJobItemID);

OleDbParameter paramStatus = new OleDbParameter( "@Status",
OleDbType.Integ er, 4, ParameterDirect ion.Input, false, 0, 0, "Status",
DataRowVersion. Current, intStatus);

oleCommand.Para meters.Add(para mJobItemID);

oleCommand.Para meters.Add(para mStatus);
.....
.....

[Mullin Yu]

I got error:
Procedure or function UpdateJobItemSt atus has too many arguments specified.

try

{

long lngJobItemID = 200401130000140 01;

int intStatus = 4;

ICWLogon.clsICL ogon oLogon = new ICWLogon.clsICL ogonClass();

string rtn = oLogon.Logon("e dms3", "edms3", "",
ICWLogon.eLogon Mode.eOnlineMod e);

Console.WriteLi ne("rtn: " + rtn);

string dbstring = oLogon.Database Conn.Connection String + ";database= " +
oLogon.Database Conn.DefaultDat abase;

// set up connection information

OleDbConnection oleConn = new OleDbConnection (dbstring);

oleConn.Open();

OleDbCommand oleCommand = new OleDbCommand();

oleCommand.Comm andText = "UpdateJobItemS tatus";

oleCommand.Comm andType = CommandType.Sto redProcedure;

oleCommand.Conn ection = oleConn;

// create the input parameter

OleDbParameter paramJobItemID = new OleDbParameter( "@JobItemID ",
OleDbType.BigIn t, 8, ParameterDirect ion.Input, false, 0, 0, "JobItemID" ,
DataRowVersion. Current, lngJobItemID);

OleDbParameter paramStatus = new OleDbParameter( "@Status",
OleDbType.Integ er, 4, ParameterDirect ion.Input, false, 0, 0, "Status",
DataRowVersion. Current, intStatus);

// create the "return value" parameter

//OleDbParameter paramReturn = new OleDbParameter( "RETURN_VAL UE",
OleDbType.Integ er, 4, ParameterDirect ion.ReturnValue , false, 0, 0, "",
DataRowVersion. Current, "");

OleDbParameter paramReturn = new OleDbParameter( "RETURN_VAL UE",
OleDbType.Integ er, 4);

paramReturn.Dir ection = ParameterDirect ion.ReturnValue ;

oleCommand.Para meters.Add(para mJobItemID);

oleCommand.Para meters.Add(para mStatus);

oleCommand.Para meters.Add(para mReturn);
oleCommand.Exec uteNonQuery();

Console.WriteLi ne("RETURN_VALU E:" + paramReturn.Val ue);
}

catch(Exception ex)

{

Console.WriteLi ne(ex.Message);

}

[Mullin Yu]

i found the problem.

just add RETURN_VALUE before others.
"Mullin Yu" <mu*******@ctil .com> wrote in message
news:uY******** ******@tk2msftn gp13.phx.gbl...

I got error:
Procedure or function UpdateJobItemSt atus has too many arguments specified.
try

{

long lngJobItemID = 200401130000140 01;

int intStatus = 4;

ICWLogon.clsICL ogon oLogon = new ICWLogon.clsICL ogonClass();

string rtn = oLogon.Logon("e dms3", "edms3", "",
ICWLogon.eLogon Mode.eOnlineMod e);

Console.WriteLi ne("rtn: " + rtn);

string dbstring = oLogon.Database Conn.Connection String + ";database= " +
oLogon.Database Conn.DefaultDat abase;

// set up connection information

OleDbConnection oleConn = new OleDbConnection (dbstring);

oleConn.Open();

OleDbCommand oleCommand = new OleDbCommand();

oleCommand.Comm andText = "UpdateJobItemS tatus";

oleCommand.Comm andType = CommandType.Sto redProcedure;

oleCommand.Conn ection = oleConn;

// create the input parameter

OleDbParameter paramJobItemID = new OleDbParameter( "@JobItemID ",
OleDbType.BigIn t, 8, ParameterDirect ion.Input, false, 0, 0, "JobItemID" ,
DataRowVersion. Current, lngJobItemID);

OleDbParameter paramStatus = new OleDbParameter( "@Status",
OleDbType.Integ er, 4, ParameterDirect ion.Input, false, 0, 0, "Status",
DataRowVersion. Current, intStatus);

// create the "return value" parameter

//OleDbParameter paramReturn = new OleDbParameter( "RETURN_VAL UE",
OleDbType.Integ er, 4, ParameterDirect ion.ReturnValue , false, 0, 0, "",
DataRowVersion. Current, "");

OleDbParameter paramReturn = new OleDbParameter( "RETURN_VAL UE",
OleDbType.Integ er, 4);

paramReturn.Dir ection = ParameterDirect ion.ReturnValue ;

oleCommand.Para meters.Add(para mJobItemID);

oleCommand.Para meters.Add(para mStatus);

oleCommand.Para meters.Add(para mReturn);
oleCommand.Exec uteNonQuery();

Console.WriteLi ne("RETURN_VALU E:" + paramReturn.Val ue);
}

catch(Exception ex)

{

Console.WriteLi ne(ex.Message);

}