UpdateCommand not returning correct value to DataSet

[joanneshovelton]

Hi,

I am having trouble with my UpdateCommand stored procedure. It updates
the row in the Data Source OK but keeps returning the original value of
"Cost" to the DataSet rather than the newly updated value. Any values
changed in the stored procedure before the Update statement seem to be
returned correctly. I set parameters such as @Cost to InputOutput
because sometimes they need to be changed in the stored procedure e.g.
when @Cost=-1.

Here is a simplified version of stored procedure and C# calling code
follows:-

*** BEGIN **************

ALTER PROCEDURE spUpdateRowInSubscription
@UserID int,
@Cost money OUTPUT,
@ID int
AS

--Cost values of -1, set them to null
if @Cost=-1
begin
-- set it to null
select @Cost=null
end

UPDATE subscription
SET
UserID = @UserID,
Cost = @Cost
WHERE ID=@ID

select @UserID UserID,
@Cost Cost

RETURN

*** END ****************

And here is the calling code in c#:-

*** BEGIN ****************
public void UpdateSub(DataSet dsSubs)
{
//Connection string
string strConnection = "Provider=MSDataShape; Data
Provider=SQLOLEDB; server=SERVER; uid=myID; pwd=myPwd; database=sctc;
Connect Timeout=90";

//Connection object
OleDbConnection conn = new OleDbConnection(strConnection);

//create command objects using stored procedures etc.
//UpdateRowSource property of command objects defaults to 'Both'

//Select
OleDbCommand selectCommand = new OleDbCommand(cstrTableName,conn);
selectCommand.CommandType = CommandType.TableDirect;

//Update
OleDbCommand updateCommand = new
OleDbCommand("spUpdateRowInSubscription",conn);
updateCommand.CommandType = CommandType.StoredProcedure;

// ...

//Set up the parameters
OleDbParameterCollection cparams;

//Update command parameters
//-------------------------
cparams=updateCommand.Parameters;

//input/output params
cparams.Add("@UserID",System.Data.OleDb.OleDbType.Integer,4,"UserID");
cparams["@UserID"].Direction = System.Data.ParameterDirection.Input;
cparams["@UserID"].SourceVersion = DataRowVersion.Current;

cparams.Add("@Cost",System.Data.OleDb.OleDbType.Currency,8,"Cost");
cparams["@Cost"].Direction =
System.Data.ParameterDirection.InputOutput;
cparams["@Cost"].SourceVersion = DataRowVersion.Current;

//primary key
AddParameterToCollection(cparams,"@ID",System.Data.OleDb.OleDbType.Integer,4,"ID",ParameterDirection.Input,DataRowVersion.Original);

//Create the DataAdapter
//----------------------
OleDbDataAdapter da = new
OleDbDataAdapter(selectCommand.CommandText,conn);
// ...
da.UpdateCommand = updateCommand;

//Update the datasource using the custom update logic
da.Update(dsSubs,"subscription");
}
*** END ********************************

Can anyone tell me how to return the current value of "Cost" to the
DataSet?

Thanks in advance for your help,
Jo

P.S. I did try putting a SELECT statement at the end of the sproc but
it didn't make any difference.

 

[Rogas69]

Well, actually you are returning the same Cost value unless you pass -1 to
the stored proc and then you return -1. On the other hand, typical update
command does not return any result - it calls Command.ExecuteNonQuery
method. If you want to get control what is returned from this procedure, you
have to call stored procedure on your own.
If you want to return the value as scalar there has to be only one value as
ExecuteScalar takes first column of first row - in this case @UserID. If you
executed ExecuteRow method you could get both values but still - you are
returning only values you passed as parameters.
What is the point of returning user id you pass to the procedure anyway? If
you do not change the @Cost in procedure - which actually you do not
(except -1 case) - you know the value of @Cost even before the procedure is
called. And in -1 case you get NULL as result anyway.
And you do not have to make @Cost parameter OUTPUT to change locally its
value in sp anyway.

Peter

Hi,

I am having trouble with my UpdateCommand stored procedure. It updates
the row in the Data Source OK but keeps returning the original value of
"Cost" to the DataSet rather than the newly updated value. Any values
changed in the stored procedure before the Update statement seem to be
returned correctly. I set parameters such as @Cost to InputOutput
because sometimes they need to be changed in the stored procedure e.g.
when @Cost=-1.

Here is a simplified version of stored procedure and C# calling code
follows:-

*** BEGIN **************

ALTER PROCEDURE spUpdateRowInSubscription
@UserID int,
@Cost money OUTPUT,
@ID int
AS

--Cost values of -1, set them to null
if @Cost=-1
begin
-- set it to null
select @Cost=null
end

UPDATE subscription
SET
UserID = @UserID,
Cost = @Cost
WHERE ID=@ID

select @UserID UserID,
@Cost Cost

RETURN

*** END ****************

And here is the calling code in c#:-

*** BEGIN ****************
public void UpdateSub(DataSet dsSubs)
{
//Connection string
string strConnection = "Provider=MSDataShape; Data
Provider=SQLOLEDB; server=SERVER; uid=myID; pwd=myPwd; database=sctc;
Connect Timeout=90";

//Connection object
OleDbConnection conn = new OleDbConnection(strConnection);

//create command objects using stored procedures etc.
//UpdateRowSource property of command objects defaults to 'Both'

//Select
OleDbCommand selectCommand = new OleDbCommand(cstrTableName,conn);
selectCommand.CommandType = CommandType.TableDirect;

//Update
OleDbCommand updateCommand = new
OleDbCommand("spUpdateRowInSubscription",conn);
updateCommand.CommandType = CommandType.StoredProcedure;

// ...

//Set up the parameters
OleDbParameterCollection cparams;

//Update command parameters
//-------------------------
cparams=updateCommand.Parameters;

//input/output params
cparams.Add("@UserID",System.Data.OleDb.OleDbType.Integer,4,"UserID");
cparams["@UserID"].Direction = System.Data.ParameterDirection.Input;
cparams["@UserID"].SourceVersion = DataRowVersion.Current;

cparams.Add("@Cost",System.Data.OleDb.OleDbType.Currency,8,"Cost");
cparams["@Cost"].Direction =
System.Data.ParameterDirection.InputOutput;
cparams["@Cost"].SourceVersion = DataRowVersion.Current;

//primary key
AddParameterToCollection(cparams,"@ID",System.Data.OleDb.OleDbType.Integer,4,"ID",ParameterDirection.Input,DataRowVersion.Original);

//Create the DataAdapter
//----------------------
OleDbDataAdapter da = new
OleDbDataAdapter(selectCommand.CommandText,conn);
// ...
da.UpdateCommand = updateCommand;

//Update the datasource using the custom update logic
da.Update(dsSubs,"subscription");
}
*** END ********************************

Can anyone tell me how to return the current value of "Cost" to the
DataSet?

Thanks in advance for your help,
Jo

P.S. I did try putting a SELECT statement at the end of the sproc but
it didn't make any difference.