ASP.NET

object oPassword = cmd.ExecuteScalar();
string sPassword = (oPassword == null) ? "Not found" : oPassword.ToString();

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