Gridview is not displayed when using stored procedure in SQLDatasource

I stumbled across t开发者_JAVA技巧his before and I know it is a common problem. One related question is GridView is empty. But it does not solve my problem.

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

<asp:GridView ID="GridView1" runat="server" AllowPaging="True" 
    DataSourceID="SqlDataSource1" AutoGenerateColumns="False" 
    DataKeyNames="ID">
    <Columns>
        <asp:BoundField DataField="ID" HeaderText="ID" InsertVisible="False" 
            ReadOnly="True" SortExpression="ID" />
        <asp:BoundField DataField="companyname" HeaderText="companyname" 
            SortExpression="companyname" />
        <asp:BoundField DataField="contactfirstname" HeaderText="contactfirstname" 
            SortExpression="firstname" />
        <asp:BoundField DataField="contactlastname" HeaderText="contactlastname" 
            SortExpression="lastname" />
        <asp:BoundField DataField="phonenumber" HeaderText="phonenumber" 
            SortExpression="phonenumber" />
        <asp:BoundField DataField="contactid" HeaderText="contactid" 
            SortExpression="contactno" />
    </Columns>
</asp:GridView>

In my "Find" Button click event. I am using this code

protected void btnFind_Click(object sender, EventArgs e)
{
    SqlDataSource1.DataBind(); /* Edit: I don't need this, I realized */
}

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?

I think I read somewhere if the stored procedure returns 0 results, gridview will not be displayed.

My SQLDataSource

<asp:SqlDataSource ID="SqlDataSource1" runat="server" 
    ConnectionString="<%$ ConnectionStrings:MyDBconn %>" 
    SelectCommand="usp_GetContactNo" SelectCommandType="StoredProcedure">
    <SelectParameters>
        <asp:FormParameter DefaultValue="" FormField="txtCompanyName" 
            Name="companyname" Type="String" />
        <asp:FormParameter FormField="txtFirstName" Name="firstname" Type="String" />
        <asp:FormParameter FormField="txtLastName" Name="lastname" Type="String" />
        <asp:FormParameter FormField="txtPhone" Name="phone" Type="String" />
        <asp:FormParameter FormField="txtContactID" Name="contactNo" Type="String" />
    </SelectParameters>
</asp:SqlDataSource>

StoredProcedure is below. Note I might have changed some names above for security reasons but below is the original fields.

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

It took me more than a day to solve it. The problem was Master pages. when I removed the master page from the page everything worked fine. What was happening is txtFirstName control was on the page, but it was never found since I was using a master page. As a result it was passing a null value every time. It did not produce any error (which is should), that made debugging very tricky.

There were a bunch of other properties that you need to look at. Important ones are

In SQL DataSource, configur DataSource, Parameter section : select the parameter and click on 'show advance options', ConvertEmptyStringToNull. You may need to change it to false.

SQLDatasourc -> Properites -> CancelSelectOnNullParamter = change it to false

I just experienced similar trouble, at last I solved it by set the CancelSelectOnNullParameter false in [SqlDataSource]->[Properties] view or code as: <asp:SqlDataSource ID="..." runat="server" ... CancelSelectOnNullParameter="False">

this is easily missed if your store procedure allow null parameters as it's set true as default