asp.net stored procedure input/output parameter

Question

I am trying to pass a single input parameter to a stored procedure that, I thought, had OUTPUT parameters declared, but seems to be input/output variables, thus giving me an error message that states that one of the parameters was not provided a value.

The C# calling code is set up as follows:

protected void CheckBoxClassRegion_btnSubmit(object sender, EventArgs e)
{
    date    @date;             /* Variable for the date of the game     */
    varchar @HomeTeam;         /* The name of the high school team      */
    varchar @AwayTeam;         /* The name of the other H.s. team       */
    int     @TeamID;           /* The ID number of the high school team */
    AddressText.Text = "";
    /**********************************************************************/
    /* The code below will initialize the connection to the database.     */
    /* As the connection string to the SQL database is defined as conn,   */
    /* the open method from conn will connect to the database, and the    */
    /* cmd variable will call on the stored procedure GetSchedule.        */
    /**********************************************************************/
    string strcon   = WebConfigurationManager.ConnectionStrings["FollowingHSFootballConnectionString"].ConnectionString;
    SqlConnection conn = new SqlConnection(strcon);
    conn.Open();
    SqlCommand cmd  = new SqlCommand("GetSchedule", conn);
    cmd.CommandType = CommandType.StoredProcedure;

    /**********************************************************************/
    /* The for loop below will determine which items from the checkbox    */
    /* were selected from the input and use the High School team name to  */
    /* pass to the stored procedure 'GetSchedule' to return the dates,    */
    /* home team and away team each game.                                 */
    /**********************************************************************/
                                                                            /**********************************************************************/
    foreach (ListItem item in CheckBoxClassRegion.Items)                    /* This loop will go through all of the checkboxed items              */
    {                                                                       /**********************************************************************/
        if (item.Selected == true)                                          /*    If this team has been selected                                  */
        {                                                                   /**********************************************************************/
            cmd.Parameters.AddWithValue("@TeamName", item.Text);            /*       Pass input parameter "Team Name"                             */
            SqlDataReader reader = cmd.ExecuteReader();                     /*       Utilize the reader function to ensure all games are included */ 
            while (reader.Read())                                           /*       While there are still items to be read                       */
            {                                                               /**********************************************************************/
                cmd.Parameters.Add("@date", SqlDbType.Date);
                cmd.Parameters["@date"].Direction = ParameterDirection.Output;

                cmd.Parameters.Add("@HomeTeam", SqlDbType.VarChar, 25);
                cmd.Parameters["@HomeTeam"].Direction = ParameterDirection.Output;

                cmd.Parameters.Add("@AwayTeam", SqlDbType.VarChar, 25);
                cmd.Parameters["@AwayTeam"].Direction = ParameterDirection.Output;

                cmd.ExecuteNonQuery();                                          /*       Execute the stored procedure                                 */

                Console.WriteLine(cmd.Parameters["@GameDate"].Value);
                Console.WriteLine(cmd.Parameters["@HomeTeam"].Value);
                Console.WriteLine(cmd.Parameters["@AwayTeam"].Value);
                Console.ReadLine(); 
            }

Stored Procedure:

ALTER PROCEDURE [dbo].[GetSchedule]

    @teamname varchar(25),
    @date Date OUTPUT,
    @HomeTeam varchar(25) OUTPUT,
    @AwayTeam varchar(25) OUTPUT

AS
BEGIN

    SET NOCOUNT ON;


    SELECT HomeSchedule.Date, HomeTeam.HighSchoolName, AwayTeam.HighSchoolName
    from (
      (Schedule$ as HomeSchedule inner join HighSchoolFootballTeam$ as HomeTeam 
        on HomeSchedule.HomeTeamID = HomeTeam.HighSchoolTeamID)
     inner join 
      (Schedule$ as AwaySchedule inner join HighSchoolFootballTeam$ as AwayTeam
        on AwaySchedule.AwayTeamID = AwayTeam.HighSchoolTeamID) 
     on HomeSchedule.GameID = AwaySchedule.GameID)
     where HomeTeam.HighSchoolName = @teamname or AwayTeam.HighSchoolName = @teamname
    Order by HomeSchedule.Date

END

How do I get the output variables of @Date, @HomeTeam and @AwayTeam to act as just output and not input/output variables so that the stored procedure does not expect an input value from them?

lots of examples on how to do this on the internet as well as on Stackoverflow.. have you tried a google search by change..? — MethodMan
– MethodMan, Commented Jun 29, 2015 at 21:51
I did, but I had been searching on asp.net stored procedures output parameters. I didn't find anything that differed from my code in how I defined an output parameter vs an input/output parameter. Do you have any recommendations? — MDDeVane
– MDDeVane, Commented Jun 30, 2015 at 4:00

ekad · Accepted Answer · 2015-06-30 05:06:03Z

4

Your stored procedure has @teamname parameter that's set as an input parameter, but you're not setting any value to @teamname parameter in your C# code, so that's why you got the error. You need to set a value to @teamname parameter as below.

while (reader.Read())                                           /*       While there are still items to be read                       */
{                                                               /**********************************************************************/
    cmd.Parameters.Add("@teamname", SqlDbType.VarChar, 25);
    cmd.Parameters["@teamname"].Value = ...; // set the value here

    cmd.Parameters.Add("@date", SqlDbType.Date);
    cmd.Parameters["@date"].Direction = ParameterDirection.Output;

    cmd.Parameters.Add("@HomeTeam", SqlDbType.VarChar, 25);
    cmd.Parameters["@HomeTeam"].Direction = ParameterDirection.Output;

    cmd.Parameters.Add("@AwayTeam", SqlDbType.VarChar, 25);
    cmd.Parameters["@AwayTeam"].Direction = ParameterDirection.Output;

    cmd.ExecuteNonQuery();                                          /*       Execute the stored procedure                                 */

    Console.WriteLine(cmd.Parameters["@GameDate"].Value);
    Console.WriteLine(cmd.Parameters["@HomeTeam"].Value);
    Console.WriteLine(cmd.Parameters["@AwayTeam"].Value);
    Console.ReadLine(); 
}

EDIT

After looking at the more complete code, here's your mistake

cmd.Parameters.AddWithValue("@TeamName", item.Text);
SqlDataReader reader = cmd.ExecuteReader();

You haven't added @date, @HomeTeam, and @AwayTeam parameters when you do SqlDataReader reader = cmd.ExecuteReader();, that's why you got the error. You should add those three parameters before executing cmd.ExecuteReader(). You also need to clear the parameters of cmd using command.Parameters.Clear(); since you're using cmd inside a loop and remove cmd.ExecuteNonQuery() since you're already executing the stored procedure when calling cmd.ExecuteReader(). Change your code as below

if (item.Selected == true)
{
    cmd.Parameters.Clear();
    cmd.Parameters.AddWithValue("@TeamName", item.Text);
    cmd.Parameters.Add("@date", SqlDbType.Date);
    cmd.Parameters["@date"].Direction = ParameterDirection.Output;
    cmd.Parameters.Add("@HomeTeam", SqlDbType.VarChar, 25);
    cmd.Parameters["@HomeTeam"].Direction = ParameterDirection.Output;
    cmd.Parameters.Add("@AwayTeam", SqlDbType.VarChar, 25);
    cmd.Parameters["@AwayTeam"].Direction = ParameterDirection.Output;
    SqlDataReader reader = cmd.ExecuteReader();
    while (reader.Read())
    {
        Console.WriteLine(cmd.Parameters["@GameDate"].Value);
        Console.WriteLine(cmd.Parameters["@HomeTeam"].Value);
        Console.WriteLine(cmd.Parameters["@AwayTeam"].Value);
        Console.ReadLine(); 
    }

edited Jun 30, 2015 at 5:06

answered Jun 29, 2015 at 21:53

ekad

14.7k26 gold badges46 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

MDDeVane Over a year ago

I probably should have included the code where I did include the assignment to @teamname foreach (ListItem item in CheckBoxClassRegion.Items) { if (item.Selected == true) { cmd.Parameters.AddWithValue("@TeamName", item.Text); SqlDataReader reader = cmd.ExecuteReader();

ekad Over a year ago

Don't add the code in the comments, it's difficult to read. Please edit your question and add the code to your question.

MDDeVane Over a year ago

You did answer the question to my issue, and I was able to move forward. Thank You.

Collectives™ on Stack Overflow

asp.net stored procedure input/output parameter

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related