title: Calling multiple stored procedures and Gridviews with 1 button click
body: <p>Background: I'm converting an access run time that searches plans by zip code, plan type, and age. </p>
  
  <p>So far, I have the main search down with the stored procedure <code>get_zip_plan_age</code> displaying correctly but am not sure if I'm calling the stored procedure <code>get_lowest_female_insurance_rate</code> correctly in the C# code-behind and how to write the code differently for <code>get_lowest_female_rate</code> which displays the lowest female rate for each age group vs <code>get_zip_plan_age</code> which displays all data.</p>
  
  <p>Question: </p>
  
  <ul>
  <li>How do you call <code>get_lowest_female_rate</code> correctly in the code-behind? Is what I have correct?</li>
  <li>How would you write the stored procedure code for <code>get_lowest_female_rate</code> which displays the lowest female rate for each age group (65, 70, 75, 80)?</li>
  </ul>
  
  <p>Here's a screenshot of the access runtime: </p>
  
  <p><img src="https://i.stack.imgur.com/YlfZT.jpg" alt="app screenshot"></p>
  
  <p>Here's my code for default.aspx.cs:</p>
  
  <pre><code>    protected void Search_Zip_Plan_Age_Button_Click(object sender, EventArgs e)
      {                          
          using (SqlConnection cn = new SqlConnection())
          {
              cn.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["PriceFinderConnectionString"].ToString();
              cn.Open();
  
              using (SqlCommand cmd = cn.CreateCommand())
              {
                  cmd.CommandText = "get_zip_plan_age";                    
                  cmd.CommandType = CommandType.StoredProcedure;
  
                  cmd.CommandText = "get_lowest_female_rate";
                  cmd.CommandType = CommandType.StoredProcedure;
  
                  cmd.CommandText = "get_lowest_male_rate";
                  cmd.CommandType = CommandType.StoredProcedure;
  
                  cmd.CommandText = "get_carrier_info";
                  cmd.CommandType = CommandType.StoredProcedure;
  
  
                  SqlParameter parm = cmd.CreateParameter(); 
                  parm.ParameterName = "@insur_age";
                  parm.DbType = DbType.Int64;
                  parm.Value = Convert.ToInt64(this.insur_age.Text);
                  cmd.Parameters.Add(parm);
  
                  parm = cmd.CreateParameter(); 
                  parm.ParameterName = "@zip_code";
                  parm.DbType = DbType.String;
                  parm.Value = this.ZipCode.Text;
                  cmd.Parameters.Add(parm);
  
                  parm = cmd.CreateParameter(); 
                  parm.ParameterName = "@plan_code";
                  parm.DbType = DbType.String;
                  parm.Value = this.PlanCode.Text;
                  cmd.Parameters.Add(parm);
  
                  SqlDataReader reader = cmd.ExecuteReader();
                  Zip_Plan_Age_GridView.DataSource = reader;
                  Zip_Plan_Age_GridView.DataBind();
              }
          }
      }
  </code></pre>
  
  <p>Here's the code for the stored procedure get_zip_plan_age that displays all data: </p>
  
  <pre><code>    ALTER PROCEDURE get_zip_plan_age 
  -- Add the parameters for the stored procedure here
  @zip_code nvarchar(16),
  @plan_code nvarchar(16),
  @insur_age int = 0 
  
      AS
      BEGIN
  -- SET NOCOUNT ON added to prevent extra result sets from
  -- interfering with SELECT statements.
  SET NOCOUNT ON;
  
  IF @insur_age &gt; 0 
  BEGIN 
  
      SELECT 
  
        [state_code], 
        [zip_code], 
        [female_value], 
        [male_value], 
        [carrier_name], 
        [update_date], 
        [insur_age], 
        [plan_code], 
        [spousal_discount] 
  
        FROM [state_zipcode_plans] 
  
        WHERE (([insur_age] = @insur_age) 
        AND ([zip_code] = @zip_code) 
        AND ([plan_code] = @plan_code)) 
  
        ORDER BY [male_value], [plan_code]
  
  END  
  
      ELSE BEGIN
  
        SELECT 
  
        [state_code], 
        [zip_code], 
        [female_value], 
        [male_value], 
        [carrier_name], 
        [update_date], 
        [insur_age], 
        [plan_code], 
        [spousal_discount] 
  
        FROM [state_zipcode_plans] 
  
        WHERE (([zip_code] = @zip_code) 
        AND ([plan_code] = @plan_code)) 
  
        ORDER BY [male_value], [plan_code]
  
  END
   END
   GO
  </code></pre>