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 UpdateJobItemStatus @JobItemID as bigint, @Status as int as

BEGIN
Begin Tran

-- Fail => locked, release locked, increase NoOfFailure
If @Status = 4
BEGIN
Update OutboundQueueItem set Status = @Status, NoOfFailure=NoOfFailure+1
where JobItemID = @JobItemID
IF (@@ERROR <> 0) GOTO ERR_HANDLER
END
ELSE
BEGIN
Update OutboundQueueItem 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.CommandText = "UpdateJobItemStatus";

oleCommand.CommandType = CommandType.StoredProcedure;

oleCommand.Connection = conn;

OleDbParameter paramJobItemID = new OleDbParameter("@JobItemID",
OleDbType.BigInt, 8, ParameterDirection.Input, false, 0, 0, "JobItemID",
DataRowVersion.Current, lngJobItemID);

OleDbParameter paramStatus = new OleDbParameter("@Status",
OleDbType.Integer, 4, ParameterDirection.Input, false, 0, 0, "Status",
DataRowVersion.Current, intStatus);

oleCommand.Parameters.Add(paramJobItemID);

oleCommand.Parameters.Add(paramStatus);
......
......

 

[William Ryan]

You'll want to use Either ReturnValue or Output as parameter type...that
should fix it for you.

 

[Mullin Yu]

I got error:


Procedure or function UpdateJobItemStatus has too many arguments specified.

try

{

long lngJobItemID = 20040113000014001;

int intStatus = 4;

ICWLogon.clsICLogon oLogon = new ICWLogon.clsICLogonClass();

string rtn = oLogon.Logon("edms3", "edms3", "",
ICWLogon.eLogonMode.eOnlineMode);

Console.WriteLine("rtn: " + rtn);

string dbstring = oLogon.DatabaseConn.ConnectionString + ";database=" +
oLogon.DatabaseConn.DefaultDatabase;

// set up connection information

OleDbConnection oleConn = new OleDbConnection(dbstring);

oleConn.Open();

OleDbCommand oleCommand = new OleDbCommand();

oleCommand.CommandText = "UpdateJobItemStatus";

oleCommand.CommandType = CommandType.StoredProcedure;

oleCommand.Connection = oleConn;

// create the input parameter

OleDbParameter paramJobItemID = new OleDbParameter("@JobItemID",
OleDbType.BigInt, 8, ParameterDirection.Input, false, 0, 0, "JobItemID",
DataRowVersion.Current, lngJobItemID);

OleDbParameter paramStatus = new OleDbParameter("@Status",
OleDbType.Integer, 4, ParameterDirection.Input, false, 0, 0, "Status",
DataRowVersion.Current, intStatus);

// create the "return value" parameter

//OleDbParameter paramReturn = new OleDbParameter("RETURN_VALUE",
OleDbType.Integer, 4, ParameterDirection.ReturnValue, false, 0, 0, "",
DataRowVersion.Current, "");

OleDbParameter paramReturn = new OleDbParameter("RETURN_VALUE",
OleDbType.Integer, 4);

paramReturn.Direction = ParameterDirection.ReturnValue;

oleCommand.Parameters.Add(paramJobItemID);

oleCommand.Parameters.Add(paramStatus);

oleCommand.Parameters.Add(paramReturn);


oleCommand.ExecuteNonQuery();

Console.WriteLine("RETURN_VALUE:" + paramReturn.Value);


}

catch(Exception ex)

{

Console.WriteLine(ex.Message);

}

 

[Mullin Yu]

i found the problem.

just add RETURN_VALUE before others.