Link to home
Start Free TrialLog in
Avatar of gwarcher
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!


/*******************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;
        }

Open in new window

Avatar of Bhavesh Shah
Bhavesh Shah
Flag of India image

ASKER CERTIFIED SOLUTION
Avatar of fabianbaptista
fabianbaptista

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial