c# - getting output variable from stored procedure

gwarcher
gwarcher used Ask the Experts™
on
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

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Bhavesh ShahLead Analyst
Top Expert 2010

Commented:
Here is my code, calling a store proceudure with 2 inputs and 1 output:


SqlCommand command = new SqlCommand("[usp_storeProc1]", Conn);
                command.CommandTimeout = 0; //infinite wait
                command.CommandType = System.Data.CommandType.StoredProcedure;
                SqlParameter ParamOri = command.Parameters.Add("@ori_KBName", SqlDbType.NVarChar);
                ParamOri.Direction = ParameterDirection.Input;
                ParamOri.Size = 50;
                ParamOri.Value = "param_1";

                SqlParameter ParamDes = command.Parameters.Add("@des_KBName", SqlDbType.NVarChar);
                ParamDes.Direction = ParameterDirection.Input;
                ParamDes.Size = 50;
                ParamDes.Value = "param_2";

                SqlParameter ParamRes = command.Parameters.Add("@resultStr", SqlDbType.NVarChar);
                ParamRes.Size = 100;
                ParamRes.Direction = ParameterDirection.Output;
                    
                Conn.Open();
                int res = command.ExecuteNonQuery();
                strin result = (string)command.Parameters[2].Value;

                Conn.Close();

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial