By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,238 Members | 1,746 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,238 IT Pros & Developers. It's quick & easy.

How to get return value from sp when using OleDbConnection

P: n/a
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);
......
......


Nov 15 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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**************@TK2MSFTNGP09.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 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);
.....
.....

Nov 15 '05 #2

P: n/a
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);

}
Nov 15 '05 #3

P: n/a
i found the problem.

just add RETURN_VALUE before others.
"Mullin Yu" <mu*******@ctil.com> wrote in message
news:uY**************@tk2msftngp13.phx.gbl...
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);

}

Nov 15 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.