title: Gridview is not displayed when using stored procedure in SQLDatasource
body: <p>I stumbled across this before and I know it is a common problem. One related question is <a href="https://stackoverflow.com/q/5596451/777982">GridView is empty</a>. But it does not solve my problem.</p>
  
  <p>Ideally what I want is update my gridview depending on the value in a textbox when I click the 'Find' button. It does work for me in my other page. But I have some problem here. I have not changed any default parameters. Here is the gridview</p>
  
  <pre><code>&lt;asp:GridView ID="GridView1" runat="server" AllowPaging="True" 
      DataSourceID="SqlDataSource1" AutoGenerateColumns="False" 
      DataKeyNames="ID"&gt;
      &lt;Columns&gt;
          &lt;asp:BoundField DataField="ID" HeaderText="ID" InsertVisible="False" 
              ReadOnly="True" SortExpression="ID" /&gt;
          &lt;asp:BoundField DataField="companyname" HeaderText="companyname" 
              SortExpression="companyname" /&gt;
          &lt;asp:BoundField DataField="contactfirstname" HeaderText="contactfirstname" 
              SortExpression="firstname" /&gt;
          &lt;asp:BoundField DataField="contactlastname" HeaderText="contactlastname" 
              SortExpression="lastname" /&gt;
          &lt;asp:BoundField DataField="phonenumber" HeaderText="phonenumber" 
              SortExpression="phonenumber" /&gt;
          &lt;asp:BoundField DataField="contactid" HeaderText="contactid" 
              SortExpression="contactno" /&gt;
      &lt;/Columns&gt;
  &lt;/asp:GridView&gt;
  </code></pre>
  
  <p>In my "Find" Button click event. I am using this code</p>
  
  <pre><code>protected void btnFind_Click(object sender, EventArgs e)
  {
      SqlDataSource1.DataBind(); /* Edit: I don't need this, I realized */
  }
  </code></pre>
  
  <p>But it does not load anything. The whole gridview dos not appear. If I use select statement, the gridview does appear with results. I have tested my storedprocedure inside the gridview and it works the way it should. Don't know what exactly could be the problem here? Anyone came across it?</p>
  
  <p>I think I read somewhere if the stored procedure returns 0 results, gridview will not be displayed.</p>
  
  <p>My SQLDataSource</p>
  
  <pre><code>&lt;asp:SqlDataSource ID="SqlDataSource1" runat="server" 
      ConnectionString="&lt;%$ ConnectionStrings:MyDBconn %&gt;" 
      SelectCommand="usp_GetContactNo" SelectCommandType="StoredProcedure"&gt;
      &lt;SelectParameters&gt;
          &lt;asp:FormParameter DefaultValue="" FormField="txtCompanyName" 
              Name="companyname" Type="String" /&gt;
          &lt;asp:FormParameter FormField="txtFirstName" Name="firstname" Type="String" /&gt;
          &lt;asp:FormParameter FormField="txtLastName" Name="lastname" Type="String" /&gt;
          &lt;asp:FormParameter FormField="txtPhone" Name="phone" Type="String" /&gt;
          &lt;asp:FormParameter FormField="txtContactID" Name="contactNo" Type="String" /&gt;
      &lt;/SelectParameters&gt;
  &lt;/asp:SqlDataSource&gt;
  </code></pre>
  
  <p>StoredProcedure is below. Note I might have changed some names above for security reasons but below is the original fields.</p>
  
  <pre><code>ALTER PROCEDURE [dbo].[usp_GetContactIDs] 
      -- Add the parameters for the stored procedure here
      @companyname varchar(255) = NULL, 
      @firstname varchar(255) = NULL,
      @lastname varchar(255) = NULL,
      @phone varchar(10) = NULL,
      @contactid varchar(15) = NULL
  AS
  BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;
      Declare @sql varchar(1000);
  
  
  
      set @sql = 'select ID, companyname,contactfirstname,contactlastname,phonenumber, contactid from oet WHERE ID is not null'
  
      if(@companyname is not null)
          set @sql = @sql + ' AND companyname = '''+ @companyname + '''';
  
      if(@firstname is not null)
          set @sql = @sql + ' AND contactfirstname = '''+ @firstname + '''';
  
      if(@lastname is not null)
          set @sql = @sql + ' AND contactlastname = '''+ @lastname + '''';
  
      if(@phone is not null)
          set @sql = @sql + ' AND phonenumber = '''+ @phone + '''';
  
      if(@contactid is not null)
          set @sql = @sql + ' AND contactid = '''+ @contactid + '''';
  
  
      exec (@sql)
  
  
  END
  </code></pre>