jQuery autocomplete searching within columns and order

Question

I have jQuery Autocomplete (not jQueryUI) to search for fname and lname in my database. Right now it is great that it can search for first name and then filter it out and search for last name. But is there a way to get it the other way around also?

ie. Searching Crystal brings up Crystal Santos, Crystal Wickers, Crystal Hayes, etc
but searching Santos brings up Santos Rameros, Santos Riveria, etc (It assumes Santos is fname)

Is there anyway to change this? For it to filter both fname and lname.

Also can I get it to search within a column? I have one that filters for departments but some departments have multiple values. It will only search the first before moving to the second.

ie. Searching for Medical office controller you would have to search MEDICAL first. You cannot search Controller or Office and have it bring up that result.

Please tell me if clarification is needed.

Handler ASHX

public void ProcessRequest (HttpContext context) {
    string prefixText = context.Request.QueryString["q"];
    using (SqlConnection conn = new SqlConnection())
    {
        conn.ConnectionString = ConfigurationManager.ConnectionStrings["Rollup2ConnectionString"].ConnectionString; 
        using (SqlCommand cmd = new SqlCommand())
        {
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "SP_AUTOCOMPLETE";                           
            cmd.Parameters.Add(new SqlParameter("@SearchText", SqlDbType.VarChar));      
            cmd.Parameters["@SearchText"].Value = prefixText;             
            cmd.Connection = conn;           
            StringBuilder sb = new StringBuilder();      
            conn.Open();           
            using (SqlDataReader sdr = cmd.ExecuteReader())           
            {               
                while (sdr.Read())   
                {                    
                    sb.Append(sdr["NUID"].ToString() + " ").Append(sdr["FNAME"].ToString() + " ").Append(sdr["LNAME"].ToString() + " ")                       
                        .Append(Environment.NewLine);          
                }        
            }            
        conn.Close();            
        context.Response.Write(sb.ToString());  
        }

    }
}

public bool IsReusable {
    get {
        return false;
    }
}

This is currently the one for fname lname, The one for department is exactly the same except for field changes.

Stored Proc NAME

@SearchText VARCHAR(255)
AS
BEGIN

SET NOCOUNT ON;
SELECT RTRIM(NUID) NUID, RTRIM(FNAME) FNAME, RTRIM(LNAME) LNAME 
FROM T_USER 
WHERE NUID like @SearchText + '%' OR FNAME like @SearchText + '%' OR LNAME like @SearchText + '%'

END

Stored Proc Dept

@SearchText VARCHAR(255)
AS
BEGIN

SET NOCOUNT ON;
SELECT RTRIM(DEPT_DESC) DEPT_DESC, RTRIM(DEPT_ID) DEPT_ID
FROM T_ROLLUP_DEPT
WHERE DEPT_ID like @SearchText + '%' OR DEPT_DESC like @SearchText + '%'

END

Answer 1

That is more of a database question. You would need to go back to your stored procedure to and add a OR clause is WHERE to say

WHERE (FNAME LIKE '%SearchText%') OR (LNAME LIKE '%SearchText%')

After adjusting the SQL statement, when you type in "Santos," you will now get Crystal Santos, Santos Rameros, Santos Riveria, etc.

The second part of your question also requires a adjustment to your SQL stored procedure. Just add LIKE '%%' to your WHERE clause. So if your user searches "Controller", Medical controller officer will come up.