title: GridView only shows 1 row even though multiple rows are returned
body: <p>I have a GridView for which the datasource is a DataTable, populated by an SqlDataAdapter.  I've tested the SQL query multiple times with multiple criteria in SQL Management Studio and returned multiple rows in a dataset, however when the data is bound to the GridView on my ASP.NET page, only the first row displays where there should be several rows.</p>
  
  <p>Other solutions to this problem had to do with the OnRowDataBound event, however I don't use that event.</p>
  
  <p>Any pointers on making my code (especially my SQL query) more effective/efficient would be appreciated as well.</p>
  
  <p>UPDATE: The query seems to return multiple results when the query is executed with the following parameters but at other time: assignment = 2 requesttype = OT otcode = 3</p>
  
  <p>Here is the ASP.NET page with the search criteria (textboxes and dropdownlists) and the GridView in question:</p>
  
  <pre><code>&lt;div&gt;
      &lt;label id="lblCardNumber" for="tbCardNumber"&gt;Card number&lt;/label&gt;
      &lt;asp:TextBox runat="server" ID="tbCardNumber"&gt;&lt;/asp:TextBox&gt;
      &lt;Label ID="lblUsername" for="tbUsername"&gt;Username&lt;/Label&gt;
      &lt;asp:TextBox runat="server" ID="tbUsername"&gt;&lt;/asp:TextBox&gt;
      &lt;Label ID="lblAssignment" for="ddlAssignment"&gt;Assignment&lt;/Label&gt;
      &lt;asp:DropDownList runat="server" CssClass="textbox" ID="ddlAssignment"&gt;
          &lt;asp:ListItem Value='NULL' Text='Select' /&gt;
          &lt;asp:ListItem Value="1" Text='1' /&gt;
          &lt;asp:ListItem Value="2" Text='2' /&gt;
          &lt;asp:ListItem Value="3" Text='3' /&gt;
          &lt;asp:ListItem Value="4" Text='4' /&gt;
          &lt;asp:ListItem Value="5" Text='5' /&gt;
      &lt;/asp:DropDownList&gt;
      &lt;Label ID="lblRequestType" for="ddlRequestType"&gt;Request Type&lt;/Label&gt;
      &lt;asp:DropDownList runat="server" ID="ddlRequestType"&gt;
          &lt;asp:ListItem Value="NULL" Text="select" /&gt;
          &lt;asp:ListItem Value="All" Text="All" /&gt;
          &lt;asp:ListItem Value="OT" Text="OT" /&gt;
          &lt;asp:ListItem Value="CO" Text="CO" /&gt;
          &lt;asp:ListItem Value="TR" Text="TR" /&gt;
          &lt;asp:ListItem Value="EP" Text="EP" /&gt;
          &lt;asp:ListItem Value="TO" Text="TO" /&gt;
      &lt;/asp:DropDownList&gt;
      &lt;div id="OTSubTypes" class="subtypes OT"&gt;
          &lt;label for="ddlOTCode"&gt;ot code no.&lt;/label&gt;
          &lt;asp:DropDownList runat="server" CssClass="textbox" ID="ddlOTCode"&gt;
              &lt;asp:ListItem Value='0' Text='Select' /&gt;
              &lt;asp:ListItem Value="1"&gt;1&lt;/asp:ListItem&gt;
              &lt;asp:ListItem Value="2"&gt;2&lt;/asp:ListItem&gt;
              &lt;asp:ListItem Value="3"&gt;3&lt;/asp:ListItem&gt;
              &lt;asp:ListItem Value="4"&gt;4&lt;/asp:ListItem&gt;
              &lt;asp:ListItem Value="5"&gt;5&lt;/asp:ListItem&gt;
              &lt;asp:ListItem Value="6"&gt;6&lt;/asp:ListItem&gt;
          &lt;/asp:DropDownList&gt;
      &lt;/div&gt;
      &lt;div id="COSubTypes" class="subtypes CO"&gt;
          &lt;label for="rblCourtSession"&gt;Court Session&lt;/label&gt;
          &lt;asp:RadioButtonList runat="server" ID="rblCourtSession"&gt;
              &lt;asp:ListItem&gt;morning&lt;/asp:ListItem&gt;
              &lt;asp:ListItem&gt;afternoon&lt;/asp:ListItem&gt;
              &lt;asp:ListItem&gt;standby&lt;/asp:ListItem&gt;
          &lt;/asp:RadioButtonList&gt;
          &lt;label for="rblCourtType"&gt;Court Type&lt;/label&gt;
          &lt;asp:RadioButtonList runat="server" ID="rblCourtType"&gt;
              &lt;asp:ListItem&gt;superior&lt;/asp:ListItem&gt;
              &lt;asp:ListItem&gt;ygc&lt;/asp:ListItem&gt;
              &lt;asp:ListItem&gt;traffic&lt;/asp:ListItem&gt;
          &lt;/asp:RadioButtonList&gt;
          &lt;label for="rblAppearanceType"&gt;Appearance Type&lt;/label&gt;
          &lt;asp:RadioButtonList runat="server" ID="rblAppearanceType"&gt;
              &lt;asp:ListItem&gt;trailing&lt;/asp:ListItem&gt;
              &lt;asp:ListItem&gt;da Conference&lt;/asp:ListItem&gt;
              &lt;asp:ListItem&gt;administrative hearing&lt;/asp:ListItem&gt;
          &lt;/asp:RadioButtonList&gt;
      &lt;/div&gt;
      &lt;div id="TRSubTypes" class="subtypes TR"&gt;
          &lt;label for="ddlTrainingCode"&gt;Training Code&lt;/label&gt;
          &lt;asp:DropDownList runat="server" CssClass="textbox" ID="ddlTrainingCode"&gt;
              &lt;asp:ListItem Value='0' Text='Select' /&gt;
              &lt;asp:ListItem&gt;1&lt;/asp:ListItem&gt;
              &lt;asp:ListItem&gt;2&lt;/asp:ListItem&gt;
              &lt;asp:ListItem&gt;3&lt;/asp:ListItem&gt;
              &lt;asp:ListItem&gt;4&lt;/asp:ListItem&gt;
              &lt;asp:ListItem&gt;5&lt;/asp:ListItem&gt;
              &lt;asp:ListItem&gt;6&lt;/asp:ListItem&gt;
          &lt;/asp:DropDownList&gt;
      &lt;/div&gt;
      &lt;Label ID="lblBeginningDate" for="tbBeginningDate"&gt;Beginning Date&lt;/Label&gt;
      &lt;asp:TextBox runat="server" ID="tbBeginningDate"&gt;&lt;/asp:TextBox&gt;
      &lt;Label ID="lblAccountNumber" for="tbAccountNumber"&gt;Account Number&lt;/Label&gt;
      &lt;asp:TextBox runat="server" ID="tbAccountNumber"&gt;&lt;/asp:TextBox&gt;
      &lt;div&gt;
          &lt;asp:Button runat="server" ID="btnSearch" Text="search" OnClick="btnSearchClick" /&gt;
          &lt;asp:Button runat="server" ID="btnClearFields" Text="clear fields"/&gt;
      &lt;/div&gt;
      &lt;div class="table-responsive"&gt;
          &lt;asp:GridView runat="server" class="table table-striped table-hover" ID="gvSearchResults" GridLines="None" AutoGenerateColumns="false"&gt;
              &lt;Columns&gt;
                  &lt;asp:BoundField DataField="card_number" HeaderText="Card Number" /&gt;
                  &lt;asp:BoundField DataField="RequestType" HeaderText="Card Type" /&gt;
                  &lt;asp:BoundField DataField="beginning_date" HeaderText="Date" /&gt;
                  &lt;asp:BoundField DataField="status_id" HeaderText="Status" /&gt;
                  &lt;asp:TemplateField&gt;
                      &lt;HeaderTemplate&gt;
                          edit
                      &lt;/HeaderTemplate&gt;
                      &lt;ItemTemplate&gt;
                      &lt;a href="&lt;%#Eval("RequestType") %&gt;Request.aspx?id=&lt;%#Eval("card_number") %&gt;"&gt;edit/view&lt;/a&gt;
                      &lt;/ItemTemplate&gt;
                  &lt;/asp:TemplateField&gt;
              &lt;/Columns&gt;
          &lt;RowStyle CssClass="cursor-pointer" /&gt;
      &lt;/asp:GridView&gt;
  &lt;/div&gt;
  </code></pre>
  
  <p>Here is the c#:</p>
  
  <pre><code>protected void btnSearchClick(object sender, EventArgs e)
  {
      string connstring = ConfigurationManager.ConnectionStrings["TimeHubDBCS"].ConnectionString;
  
      SqlCommand cmd = new SqlCommand();
      cmd.CommandType = System.Data.CommandType.StoredProcedure;
  
      using (SqlConnection conn = new SqlConnection(connstring))
      {
          cmd.Connection = conn;
  
          //declare parameters that are independent of request type
          cmd.Parameters.Add("@CardNumber", SqlDbType.VarChar).Value = tbCardNumber.Text;
          cmd.Parameters.Add("@Username", SqlDbType.VarChar).Value = tbUsername.Text;
          cmd.Parameters.Add("@Assignment", SqlDbType.VarChar).Value = ddlAssignment.SelectedValue;
          cmd.Parameters.Add("@BeginningDate", SqlDbType.VarChar).Value = tbBeginningDate.Text;
  
          //declare sqlcommand arguments and parameters that are dependent on request type
          switch (ddlRequestType.SelectedValue.ToString().ToLower())
          {
              case "co":
                  cmd = new SqlCommand("spSearchCORequests", conn);
  
                  cmd.Parameters.Add("@CourtSession", SqlDbType.VarChar).Value = rblCourtSession.SelectedValue;
                  cmd.Parameters.Add("@CourtType", SqlDbType.VarChar).Value = rblCourtType.SelectedValue;
                  cmd.Parameters.Add("@AppearanceType", SqlDbType.VarChar).Value = rblAppearanceType.SelectedValue;
                  break;
              case "ep":
                  cmd = new SqlCommand("spSearchEPRequests", conn);
                  break;
              case "ot":
  
                  cmd.CommandText = "spSearchOTRequests";
                  //cmd = new SqlCommand("spSearchOTRequests", conn);
  
                  cmd.Parameters.Add("@AccountNumber", SqlDbType.VarChar).Value = tbAccountNumber.Text;
                  cmd.Parameters.Add("@OTCode", SqlDbType.VarChar).Value = ddlOTCode.SelectedValue;
                  break;
              case "pt":
                  cmd = new SqlCommand("spSearchPTRequests", conn);
                  break;
              case "to":
                  cmd = new SqlCommand("spSearchTORequests", conn);
                  break;
              case "tr":
                  cmd = new SqlCommand("spSearchTRRequests", conn);
  
                  cmd.Parameters.Add("@TrainingCode", SqlDbType.VarChar).Value = ddlTrainingCode.SelectedValue;
                  break;
          }
  
          SqlDataAdapter daSearchResults = new SqlDataAdapter();
          daSearchResults.SelectCommand = cmd;
  
          DataTable dtSearchResults = new DataTable();
  
          try
          {
              conn.Open();
              daSearchResults.Fill(dtSearchResults);
  
              conn.Close();
          }
          catch(Exception ex)
          {
              message = "search error: ";
              ClientScript.RegisterStartupScript(this.GetType(), "Popup", "ShowPopup('" + message + ex.Message + "');", true);
          }
  
          gvSearchResults.DataSource = dtSearchResults;
          gvSearchResults.DataBind();
  
          gvSearchResults.UseAccessibleHeader = true;
          gvSearchResults.HeaderRow.TableSection = TableRowSection.TableHeader;
      }
  }
  </code></pre>
  
  <p>Here is the SQL query:</p>
  
  <pre><code>ALTER PROCEDURE spSearchOTRequests
  @CardNumber VARCHAR(MAX)=NULL,
  @Username VARCHAR(MAX)=NULL,
  @Assignment VARCHAR(MAX)=NULL,
  @BeginningDate VARCHAR(MAX)=NULL,
  @AccountNumber VARCHAR(MAX)=NULL,
  @OTCode VARCHAR(MAX)=NULL   
  AS
  BEGIN
      SET @CardNumber = ISNULL(@CardNumber, '')
      SET @Username = ISNULL(@Username, '')
      SET @Assignment = ISNULL(@Assignment, '')
      SET @BeginningDate = ISNULL(@BeginningDate, '')
      SET @AccountNumber = ISNULL(@AccountNumber, '')
      SET @OTCode = ISNULL(@OTCode, '')
  
      IF @CardNumber = '' BEGIN SET @CardNUmber = @CardNumber + '%' END
      IF @Username = '' BEGIN SET @Username = @Username + '%' END
      IF @Assignment = '' BEGIN SET @Assignment = @Assignment + '%' END
      IF @BeginningDate = '' BEGIN SET @BeginningDate = @BeginningDate + '%' END
      IF @AccountNumber = '' BEGIN SET @AccountNumber = @AccountNumber + '%' END
      IF @OTCode = '' BEGIN SET @OTCode = @OTCode + '%' END
  
      SELECT
          card_number,
          UserCredited,
          user_star,
          assignment,
          beginning_date,
          event_number,
          ot_code,
          status_id,
          RequestType
      FROM dbo.OTCard
      WHERE
          (@CardNumber IS NULL OR (@CardNumber = '') OR (card_number LIKE @CardNumber))
          AND (@Username IS NULL OR (@Username = '') OR (UserCredited LIKE @Username))
          AND (@Assignment IS NULL OR (@Assignment = '') OR (assignment LIKE @Assignment))
          AND (@BeginningDate IS NULL OR (@BeginningDate = '') OR (beginning_date LIKE @BeginningDate))
          AND (@AccountNumber IS NULL OR (@AccountNumber = '') OR (event_number LIKE @AccountNumber))
          AND (@OTCode IS NULL OR (@OTCode = '') OR (ot_code LIKE @OTCode))
  
      OPTION (RECOMPILE)
  END
  </code></pre>