Multiple columns and results on jQuery autocomplete plugin?

Question

I am using the jQuery autocomplete plugin. Not jQueryUI autocomplete. I can't seem to find any good instructions on jQuery autocomplete.

Does anyone know how to get multiple search parameters and results.
ie. User can search either ID, FNAME, LNAME and the autocomplete shows the results accordingly?

So search JON auto complete returns JON DOE N41, JONATHAN MILLER Q66

or

Search Q6 auto complete returns JONATHAN MILLER Q66, MIKE CAMPBELL Q67, etc

Here is my current code.

ashx handler:

public class Search_CS : IHttpHandler {

    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.CommandText = "select NUID from T_USER where " +
                "NUID like @SearchText + '%'";
                cmd.Parameters.AddWithValue("@SearchText", prefixText);
                cmd.Connection = conn;
                StringBuilder sb = new StringBuilder(); 
                conn.Open();
                using (SqlDataReader sdr = cmd.ExecuteReader())
                {
                    while (sdr.Read())
                    {
                        sb.Append(sdr["NUID"])
                            .Append(Environment.NewLine);
                    }
                }
                conn.Close();
                context.Response.Write(sb.ToString()); 
            }
        }
    }

    public bool IsReusable {
        get {
            return false;
        }
    }
}

And here is my HTML:

<script src="Scripts/jquery.autocomplete.js" type="text/javascript"></script>
<link href="Autocomplete/jquery.autocomplete.css" rel="stylesheet" type="text/css" />  

<script type="text/javascript">
    $(document).ready(function() {
        $("#<%=txtSearch.ClientID%>").autocomplete('Autocomplete/Search_CS.ashx', {width: 400, multiple: true, matchContains: true });
    });

</script>

Just for clarity, why won't the jQueryUI Autocomplete plugin do what you need? — jcolebrand, Aug 02 '12 at 22:43
It does do what I need. But the problem is that I can absolutely not get the jQueryUI Autocomplete working. Tried dozens of tutorials. None seem to want to work for me. — user1512593, Aug 02 '12 at 22:53

score 1 · Accepted Answer · answered Aug 02 '12 at 22:49

1

It'll have to do with your SQL query. You'll want to target those columns in your database and search all of them just like NUID.

cmd.CommandText = "select NUID, FNAME, LNAME from T_USER where NUID like @SearchText + '%' OR FNAME like @SearchText + '%' OR LNAME like @SearchText + '%'";

answered Aug 02 '12 at 22:49

Tony M

8,088
6
30
32

Alright. And for the results how would I go about that? Would it be similar to `using (SqlDataReader sdr = cmd.ExecuteReader()) { while (sdr.Read()) { sb.Append(sdr["NUID, FNAME, LNAME"]) .Append(Environment.NewLine); } } conn.Close();` – user1512593 Aug 02 '12 at 22:55
Yes, you'll want to append all of the returned column values to one string for output. – Tony M Aug 02 '12 at 23:02
Thanks I got it. Two more things if you have the time. Is it possible for when they select it and push enter that it only takes the NUID value? I cant seem to make the be the case. Also there is a long space after each parameter. ie g66 (20spaces) Jonathan (20spaces) Miller Is there a way to get rid of this? – user1512593 Aug 02 '12 at 23:41
You can do that through HTML and Javascript. If your autocomplete is building a
- 's, you could write a function that, on click, fills the target search box. Your li would look something like this: `
- `. The javascript function (since you're using jquery) could look something like this: `function fill(thisValue) { $('#searchbox').val(thisValue); }` Look at http://stackoverflow.com/questions/6442421/c-sharp-fastest-way-to-remove-extra-white-spaces for your spacing issue.
– Tony M Aug 03 '12 at 13:01

Multiple columns and results on jQuery autocomplete plugin?

1 Answers1