ASP.NET

ALTER PROCEDURE dbo.advancedSearching
	@indexKindword varchar(250) = NULL,
	@indexKindword2 varchar(250) = NULL,
	@indexKindword3 varchar(250) = NULL
	
AS
BEGIN
	SELECT [companiesIndex].indexCatID, [companiesIndex].compantName, [companiesIndex].companyAddress, [companiesIndex].companyState, [companiesIndex].companyCity, [companiesIndex].companyPhone, [companiesIndex].companyFax, [companiesIndex].companyWebsite, [companiesIndex].keyWords
FROM companiesIndex
WHERE ([companiesIndex].cat LIKE '%' + @indexKindword + '%' OR [companiesIndex].cat IS NULL
AND [companiesIndex].companyState LIKE '%' + @indexKindword2 + '%' OR [companiesIndex].companyState IS NULL
AND [companiesIndex].companyCity LIKE '%' + @indexKindword3 + '%' OR [companiesIndex].companyCity IS NULL)
ORDER BY [compantName] 
END
	RETURN

protected void Page_Load(object sender, EventArgs e)
    {
        if (Request.QueryString["searchString"] != null)
        {
            DisplaySearchResults(Request.QueryString["searchString"]);
            Label50.Text = Repeater1.Items.Count.ToString();
        }
    }
    public void DisplaySearchResults(string strSearch)
    {
        SqlConnection newconn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["ASPNETDBConnectionString"].ConnectionString);
        SqlCommand cmd = new SqlCommand("spSearchByString", newconn);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@SearchString", strSearch);
        newconn.Open();

        Repeater1.DataSource = cmd.ExecuteReader();
        Repeater1.DataBind();

        newconn.Close();
        newconn.Dispose();
    }

SELECT * FROM TABLE
WHERE (CASE WHEN ISNULL(@P_NAME,'') = '' THEN '' ELSE NAME END)= ISNULL(@P_NAME,'')
AND (CASE WHEN ISNULL(@P_CITY,'') = '' THEN '' ELSE CITY END)= ISNULL(@P_CITY,'')
AND (CASE WHEN ISNULL(@P_STATE,'') = '' THEN '' ELSE STATE END)= ISNULL(@P_STATE,'')

Thus if you ask 
@P_Name = 'xxx'
@P_CITY = NULL
@P_STATE = 'yyyy'

hammadahsan wrote:

problem always resolve after restarting IIS

< httpRuntime maxRequestLength="102400" />

<%@ Page Language="C#" MasterPageFile="~/MasterPage.master" AutoEventWireup="true" CodeFile="ImportTimetable.aspx.cs" Inherits="ImportTimetable" Title="o!Booking Version 3" %>
<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
    <span>
        <br />
        <table border="0" cellpadding="2" cellspacing="0" style="font-family: Verdana">
            <tr>
                <td style="width: 72px">
                    <asp:Image ID="Image1" runat="server" Height="72px" ImageUrl="~/Images/CDICons/timetable72.png"
                        Style="behavior: url(PngBehaviour.htc)" Width="72px" /></td>
                <td>
                </td>
                <td style="width: 600px">
                    <span style="font-size: 13pt"><strong>Import Timetables<br />
                    </strong><span style="font-size: 8pt">Perform bulk bookings for class and/or exam timetables.
                        For a description of the file formats, see the online help.</span></span></td>
            </tr>
        </table>
        <span style="font-family: Verdana">
            <hr align="left" width="800" />
            <br />
        </span></span>
    <table border="0" cellpadding="0" cellspacing="0" style="font-size: 10pt; width: 82%;
        font-family: Verdana">
        <tr>
            <td style="width: 195px; height: 22px;" valign="top">
                Import Timetable</td>
            <td style="height: 22px">
                <asp:FileUpload ID="FileUploadTimetable" runat="server" Width="421px" />
                <asp:Button ID="btnImport" runat="server" BackColor="Transparent" BorderStyle="None"
                    Font-Names="Verdana" Font-Size="10pt" OnClick="btnImport_Click" Text="[Import]"
                    Width="64px" />
                <br />
                </td>
        </tr>
    </table>
    <br />
    <span style="font-size: 10pt; font-family: Verdana">
            <asp:Label ID="lblStatus" runat="server" Font-Names="Verdana" Font-Size="10pt" ForeColor="#8080FF"
        Width="100%"></asp:Label><br />
    </span>
 
</asp:Content>