gwarcher
asked on
c# - getting output variable from stored procedure
I have a simple stored procedure that I am using to retrieve the last recorded row in a table. I am then trying to pick up that result in C# and store it in a variable to make some calculations on it. The variable is zeroing out though and I'm not completely sure why as I have this block of code multiple times working successfully (not with this sort of stored procedure though. I am providing both.
Thanks!
Thanks!
/*******************STORED PROCEDURE*********************/
Create PROCEDURE [dbo].[SelectLastPct] (
@candidate VARCHAR(50),
@pctOfVote FLOAT OUTPUT)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
SELECT @pctOfVote = pctOfVote
FROM tblCandidate
WHERE ID = (SELECT MAX(ID) FROM tblCandidate)
END
GO
/********************END STORED PROCEDURE**************/
public double PreviousPctRep()
{
SqlConnection conn = new SqlConnection(
"ESTABLISHED CONNECTION");
//Create command object
SqlCommand nonqueryCommand = conn.CreateCommand();
double result;
double pctOfVote = 0;
try
{
conn.Open();
Console.WriteLine("SQL is Open!");
using (conn)
{
using (SqlCommand cmd = new SqlCommand("dbo.SelectLastPct"))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@candidate", "bob"));
SqlParameter countParameter = new SqlParameter("@pctOfVote", 0);
countParameter.Direction = ParameterDirection.Output;
cmd.Parameters.Add(countParameter);
cmd.Connection = conn;
cmd.ExecuteNonQuery();
if (cmd.Parameters["@pctOfVote"].Value != DBNull.Value)
{
pctOfVote = double.Parse(cmd.Parameters["@pctOfVote"].Value.ToString());
Console.WriteLine("Previous Pct of Candidate: " + pctOfVote);
}
else
{
pctOfVote = PctTotal(); //Method does not call to DBase, works fine
Console.WriteLine("No data so there is no percent of vote available");
}
}
}
}
catch (SqlException ex)
{
//display error
Console.WriteLine("Error: " + ex.ToString());
}
finally
{
//close conenction **********************************
conn.Close();
Console.WriteLine("sql is closed");
}
result = pctOfVote;
return result;
}
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
check out this link.
it may helps
http://www.devnewsgroups.net/adonet/t13565-how-run-stored-procedure-that-has-output-parameter.aspx