adding update parameter as output parameter

[mrp9090]

How do you add an update parameter as an output parameter? Here are my update parameters :

SqlDataSource1.UpdateParameters.Clear();

SqlDataSource1.UpdateParameters.Add("ForecastKey", TypeCode.Int32, intID.ToString());
SqlDataSource1.UpdateParameters.Add("UserKey", TypeCode.Int32, UserKey.ToString());
SqlDataSource1.UpdateParameters.Add("CompanyKey", TypeCode.Int64, lngCompanyKey.ToString());
SqlDataSource1.UpdateParameters.Add("ForecastType", TypeCode.Char, chrForecastType.ToString());
SqlDataSource1.UpdateParameters.Add("MoneyValue", TypeCode.Double, dblMoneyValue.ToString());
SqlDataSource1.UpdateParameters.Add("ForecastPercentage", TypeCode.Double, dblForecastPercentage.ToString());
SqlDataSource1.UpdateParameters.Add("DueDate", TypeCode.DateTime, dtmDueDate.ToString());
SqlDataSource1.UpdateParameters.Add("Result", TypeCode.Boolean.ToString());

The final parameter (the output parameter) causes an error. The code works fine when I remove the output parameter completely from my code and stored procs etc

 

[jbenson001]

Which one is the output parameter?

 

[mrp9090]

The output parameter is the last one (the boolean). I don't know whether I've used the correct syntax for adding update parameters that are output parameters though.

 

[jbenson001]

SqlDataSource1.UpdateParameters("Result").Direction = ParameterDirection.Output;

Make sure your SP actually has an Output Param in it.

And don't confuse and output param with a return value.

Jim

 

[mrp9090]

You're setting the parameter there, but how do you add it to the updateparameters collection?

 

[jbenson001]

You have already added it. I am just showing you that you need to specify the direction.

 

[mrp9090]

I still get the same error 'error converting datatype bit to nvarchar'.

 

[jbenson001]

You didn't specify you were getting that error. Show all of your code where you are trying to use the output param, and post the SP.

Jim

 

[mrp9090]

Here's my SP :

CREATE procedure dbo.UpdateForecast

(
@ForecastKey int,
@CompanyKey bigint,
@ForecastType char(1),
@MoneyValue float,
@ForecastPercentage float,
@DueDate datetime,
@UserKey int,
@Result bit output
)

as

declare @err int
declare @rowcount int

update Forecast
set CompanySiteKey = @CompanyKey,
MoneyValue = @MoneyValue,
Probability = @ForecastPercentage,
ForecastType = @ForecastType,
InvoiceDate = @DueDate,
UserKey = @UserKey
where ForecastKey = @ForecastKey

select @err = @@error, @rowcount = @@rowcount

if (@err <> 0) or (@rowcount = 0)
begin
goto on_error
end

set @result = 1
return @result

on_error:
rollback tran
set @result = 0
return @result
GO

Here's my code :

SqlDataSource1.UpdateParameters.Clear();

SqlDataSource1.UpdateParameters.Add("ForecastKey", TypeCode.Int32, intID.ToString());
SqlDataSource1.UpdateParameters.Add("UserKey", TypeCode.Int32, UserKey.ToString());
SqlDataSource1.UpdateParameters.Add("CompanyKey", TypeCode.Int64, lngCompanyKey.ToString());
SqlDataSource1.UpdateParameters.Add("ForecastType", TypeCode.Char, chrForecastType.ToString());
SqlDataSource1.UpdateParameters.Add("MoneyValue", TypeCode.Double, dblMoneyValue.ToString());
SqlDataSource1.UpdateParameters.Add("ForecastPercentage", TypeCode.Double, dblForecastPercentage.ToString());
SqlDataSource1.UpdateParameters.Add("DueDate", TypeCode.DateTime, dtmDueDate.ToString());
SqlDataSource1.UpdateParameters.Add("Result", TypeCode.Boolean.ToString());
SqlDataSource1.UpdateParameters["Result"].Direction = ParameterDirection.Output;


//update forecast table
DataAccess da = new DataAccess();

//DBResult dbrUpdateForecast = (X.DBResult)
da.UpdateForecast(intID, UserKey, lngCompanyKey, chrForecastType,
dblMoneyValue, dblForecastPercentage,
dtmDueDate);

switch (dbrUpdateForecast)
{
case DBResult.OK:
break;

case DBResult.Error:
genAlert("UpdateForecast", "Error updating forecast");
break;
}



public DBResult UpdateForecast(int intID, int UserKey,
long lngCompanyKey, char chrForecastType,
double dblMoneyValue, double dblForecastPercentage,
DateTime dtmDueDate)
{
DBResult dbrUpdateForecast;

SqlConnection objConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["XConnectionString"].ConnectionString);

SqlCommand objCommand = new SqlCommand("UpdateForecast", objConnection);
objCommand.CommandType = CommandType.StoredProcedure;

SqlParameter prmForecastKey = new SqlParameter("@ForecastKey", SqlDbType.Int, 4);
prmForecastKey.Value = intID;
objCommand.Parameters.Add(prmForecastKey);

SqlParameter prmUserKey = new SqlParameter("@UserKey", SqlDbType.Int, 4);
prmUserKey.Value = UserKey;
objCommand.Parameters.Add(prmUserKey);

SqlParameter prmCompanyKey = new SqlParameter("@CompanyKey", SqlDbType.BigInt, 8);
prmCompanyKey.Value = lngCompanyKey;
objCommand.Parameters.Add(prmCompanyKey);

SqlParameter prmForecastType = new SqlParameter("@ForecastType", SqlDbType.Char, 1);
prmForecastType.Value = chrForecastType;
objCommand.Parameters.Add(prmForecastType);

SqlParameter prmMoneyValue = new SqlParameter("@MoneyValue", SqlDbType.Float, 8);
prmMoneyValue.Value = dblMoneyValue;
objCommand.Parameters.Add(prmMoneyValue);

SqlParameter prmForecastPercentage = new SqlParameter("@ForecastPercentage", SqlDbType.Float, 8);
prmForecastPercentage.Value = dblForecastPercentage;
objCommand.Parameters.Add(prmForecastPercentage);

SqlParameter prmDueDate = new SqlParameter("@DueDate", SqlDbType.DateTime, 8);
prmDueDate.Value = dtmDueDate;
objCommand.Parameters.Add(prmDueDate);

SqlParameter prmResult = new SqlParameter("@Result", SqlDbType.Bit, 1);
prmResult.Direction = ParameterDirection.Output;
objCommand.Parameters.Add(prmResult);

try
{
objConnection.Open();
objCommand.ExecuteNonQuery();
objConnection.Close();

if ((bool)prmResult.Value == true)
{
dbrUpdateForecast = DBResult.OK;
}
else
{
dbrUpdateForecast = DBResult.Error;
}
}
catch
{
dbrUpdateForecast = DBResult.Error;
}

return dbrUpdateForecast;
}

 

[jbenson001]

Does the error occur on this line?:
if ((bool)prmResult.Value == true)

The output valule is bit, either 0 or 1, not True or False.

0=false, 1=true

 

[mrp9090]

The error doesn't occur on this line, it occurs after all the update code has been executed, which is strange. I've decided to scrap the output parameter as it wasn't totally necessary, thanks for you help anyway.

Cheers,

Mike

 

[jbenson001]

The problem is in the way you are using the output parameter. You have to assign it a value, it is not populated automatically

YourOutPutParam = objCommand.ExecuteNonQuery();