Filter GridView using StoredProcedure with Null value using C# and VB.Net in ASP.Net

Hi pratikshir,

As per your query i have create the sample. If you have any doubt please revert me back. Here i am making use of TOP cluase to show you sample. So you need to remove the TOP from your select query.

Database

Here I am making use of Microsoft’s Northwind Database. The download and install instructions are provided in the following article.

Download and install Northwind Database

SQL

CREATE PROCEDURE CustomerSearch
	@ContactName NVARCHAR(500) = NULL
	,@Phone NVARCHAR(500) = NULL
AS
BEGIN
	SELECT TOP 10 CustomerID,ContactName,City,Phone
	FROM Customers
	WHERE (ContactName = @ContactName OR @ContactName IS NULL)
	AND (Phone = @Phone OR @Phone IS NULL)
END

Execute Query

EXEC CustomerSearch 'Mudassar Khan','(26) 642-7012'

Output

Execute Query

EXEC CustomerSearch null,'(26) 642-7012'

Output 

Execute Query

EXEC CustomerSearch 'Mudassar Khan',null

Output 

Execute Query

EXEC CustomerSearch null,null

Output 

HTML

<table>
    <tr>
        <td>
            Enter Mobile No:
        </td>
        <td>
            <asp:TextBox ID="txtMobileNo" runat="server">
            </asp:TextBox>
        </td>
    </tr>
    <tr>
        <td>
            Enter Name :
        </td>
        <td>
            <asp:TextBox ID="txtName" runat="server"></asp:TextBox>
        </td>
    </tr>
    <tr>
        <td colspan="2" align="center">
            <asp:Button ID="btnSubmit" runat="server" Text="Submit" OnClick="btnSubmit_Click" />
        </td>
    </tr>
</table>
<br />
<asp:GridView ID="gvDetails" runat="server">
</asp:GridView>

C#

protected void btnSubmit_Click(object sender, EventArgs e)
{
    string cns = ConfigurationManager.ConnectionStrings["ConString"].ConnectionString;
    using (SqlConnection con = new SqlConnection(cns))
    {
        SqlCommand cmd = new SqlCommand("CustomersSearch");
        if (!string.IsNullOrEmpty(txtMobileNo.Text))
        {
            cmd.Parameters.AddWithValue("@Phone", txtMobileNo.Text);
        }
        if (!string.IsNullOrEmpty(txtName.Text))
        {
            cmd.Parameters.AddWithValue("@ContactName", txtName.Text);
        }
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Connection = con;
        con.Open();
        gvDetails.DataSource = cmd.ExecuteReader();
        gvDetails.DataBind();
        con.Close();
    }
}

VB.Net

Protected Sub btnSubmit_Click(sender As Object, e As EventArgs)
    Dim cns As String = ConfigurationManager.ConnectionStrings("ConString").ConnectionString
    Using con As New SqlConnection(cns)
        Dim cmd As New SqlCommand("CustomersSearch")
        If Not String.IsNullOrEmpty(txtMobileNo.Text) Then
            cmd.Parameters.AddWithValue("@Phone", txtMobileNo.Text)
        End If
        If Not String.IsNullOrEmpty(txtName.Text) Then
            cmd.Parameters.AddWithValue("@ContactName", txtName.Text)
        End If
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Connection = con
        con.Open()
        gvDetails.DataSource = cmd.ExecuteReader()
        gvDetails.DataBind()
        con.Close()
    End Using
End Sub

Screenshot

dear sir ,

thanks @zameerji

but have one querie in this if passed two parametrs

and like

@name ,

@mobileNo

then will get info 

if search Name should show info

or 

if serch Mobile should info

like google search engine...

Hi pratikshir,

I have modified my previous reply with complete code as per your query by implementing the procedure. Refrer the below link.

http://www.aspforums.net/Threads/113634/search-Query-for/Replies/1#Replies