What's wrong with the SqlParameter?

  • Thread starter Thread starter Curious
  • Start date Start date
C

Curious

I have the following C#.NET code that executes a stored procedure and
returns an integer value from the stored procedure:

//insert to algo_executions
IDbCommand command = mConn.CreateCommand();

command.CommandType = CommandType.StoredProcedure;
command.CommandText =
"db_dynamic_trading.dbo.algo_insert_run_info";

// Convert day to DateTime format
DateTime date = new DateTime();
date = Convert.ToDateTime(mFirstTradingDay);

command.Parameters.Add(new SqlParameter("@FirstDay",
date));
command.Parameters.Add(new SqlParameter("@LimitType",
mLimitType.ToString()));
command.Parameters.Add(new SqlParameter
("@ParticipationRate", mMarketPercent));
command.Parameters.Add(new SqlParameter
("@CashPercentage", mCashPercent));

SqlParameter returnValueParam = new SqlParameter
("@RunId", 0);
returnValueParam.Direction =
ParameterDirection.ReturnValue;
command.Parameters.Add(returnValueParam);

command.ExecuteNonQuery();

int returnValue = (int)returnValueParam.Value;

However, it throws an exception upon executing the line of code with
"ExecuteNonQuery". The error is

"Procedure or function 'algo_insert_run_info' expects parameter
'@RunId', which was not supplied." I think I have supplied this
parameter before "ExecuteNonQuery". Any suggestion on how to get this
fixed?
 
You should probably post the .sql code as well.

At least the beginning part

CREATE db_dynamic_trading.dbo.algo_insert_run_info
(
stuff here
)
as
 
You should probably post the .sql code as well.

At least the beginning part

CREATE db_dynamic_trading.dbo.algo_insert_run_info
(
stuff here
)
as
Click to expand...

My stored procedure is below -- Note: run_id is auto-numbered column.
Each time I insert a record to the table, run_id is increased by 1. I
want to return the run_id for the record that is just inserted:

CREATE PROCEDURE dbo.algo_insert_run_info
(
@FirstDay DATETIME,
@LimitType VARCHAR(50),
@ParticipationRate REAL,
@CashPercentage REAL,
@RunId INT OUTPUT
)
AS

-- Insert a record
INSERT INTO dbo.algo_run_info
(
first_day,
limit_type,
participation_rate,
cash_percentage
)
VALUES
(
@FirstDay,
@LimitType,
@ParticipationRate,
@CashPercentage
)

SET @RunId = (SELECT IDENT_CURRENT('algo_run_info'))

GO
 
- Show quoted text -
Click to expand...

Thanks for the comment! It just turned out that the error was from the
SQL side. When I ran the stored procedure in the Management Studio, it
gave me that error. It was caused by the extra "RunId" output
parameter in the stored procedure. Once I removed that paramter,
everything was OK and I was also able to run it from C#.NET.

However, I'm confused about this parameter. I was advised to add it to
the parameter list as an "output" parameter in order to return its
value from the stored procedure. Now it seems that I should remove it
from parameter list.

Also, it seems that I must provide a value (0 as I assigned it to) for
"@RunId" in the following line in my C#.NET code:

SqlParameter returnValueParam = new SqlParameter
("@RunId", 0); // assign value to @RunId parameter
returnValueParam.Direction =
ParameterDirection.ReturnValue;
command.Parameters.Add(returnValueParam);

command.ExecuteNonQuery();

int returnValue = (int)returnValueParam.Value;

Is there anyway *not* to assign any value to "@RunId" in my C#.NET
code? I want to *get* its value from my stored procedure.
 
output parameters (by tsql syntax) effectively act as input/output
parameters.

So if you don't want the "sent in" value, you should default it to something
at the top of the procedure.
 
My stored procedure is below -- Note: run_id is auto-numbered column.
Each time I insert a record to the table, run_id is increased by 1. I
want to return the run_id for the record that is just inserted:

CREATE PROCEDURE dbo.algo_insert_run_info
(
@FirstDay DATETIME,
@LimitType VARCHAR(50),
@ParticipationRate REAL,
@CashPercentage REAL,
@RunId INT OUTPUT
)
AS

-- Insert a record
INSERT INTO dbo.algo_run_info
(
first_day,
limit_type,
participation_rate,
cash_percentage
)
VALUES
(
@FirstDay,
@LimitType,
@ParticipationRate,
@CashPercentage
)

SET @RunId = (SELECT IDENT_CURRENT('algo_run_info'))

GO
Click to expand...

The @RunId is no returnvalue. It's a parameter with the direction :
output (ParameterDirection.Output).

When you would rewrite your SQL query like :
------------- B; SQL -------------
CREATE ...
(
@FirstDay DATETIME,
@LimitType VARCHAR(50),
@ParticipationRate REAL,
@CashPercentage REAL
)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @RunId INT
[snip]

SET @RunId = (SELECT IDENT_CURRENT('algo_run_info'))
RETURN @RunId
END
------------- E; SQL -------------

Then you would have a real ReturnValue. The SQL Function returns your
RunID as expected (and does not require RunId to be specified in the
parameterlist).

..L.
 
Back
Top