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.