jQuery Autocomplete with Razor Web Pages and a SQL CE Database

There are a number of autocomplete solutions for jQuery, including the widget that comes as part of the jQuery UI library. This article looks at incorporating the jQuery UI Autocomplete widget into a Razor-based ASP.NET Web Pages site.

The first file you need is the latest jQuery library, and then you should navigate to the UI download part of the jQuery site. Choose all of the UI Core parts, and then choose Autocomplete. I left the theme as the default UI Lightness, although you can choose any you prefer. Once you click download, you get a little package which includes a customised js file and css for your chosen theme and widgets. I placed the js files into a folder I created called Scripts. I took the customised style sheet from the jQuery download and added that to a folder I named Styles. I also added another style sheet to that folder which I named sites.css, and added just one class declaration:

body {
    font-size: 70%;
}
label{
    font: 1.1em Arial;
}

Finally, I took the images folder that came with the UI download, and added that to the Styles folder.

Autocomplete is a widget that attaches to a text box (input type=text). To keep the demo simple, I created a form called Autocomplete.cshtml that only contains one text box:

<!DOCTYPE html>

<html lang="en">
    <head>
        <meta charset="utf-8" />
        <title></title>
        <script src="@Href("~/Scripts/jquery-1.6.2.min.js")" type="text/javascript"></script>
        <script src="@Href("~/Scripts/jquery-ui-1.8.15.custom.min.js")" type="text/javascript"></script>
        <link href="@Href("~/Styles/site.css")" type="text/css" rel="stylesheet" />
        <link href="@Href("~/Styles/jquery-ui-1.8.15.custom.css")" type="text/css" rel="stylesheet" />

    </head>
    <body>
        <form method="post">
            <label for="product">Enter product: </label>
            <input type="text" name="product" id="product" />
        </form>
    </body>
</html>

You can see from the code that I reference both jQuery files and both style sheets. Make sure you reference the UI file after the core jQuery library. I also added another <script> block to the <head> section:

<script type="text/javascript">
    $(function(){
        $('#product').autocomplete({source:'/GetProducts/'});
    });
</script>

This snippet targets the element with the ID of "product" (the text box) and attaches the autocomplete widget to it. It also requires a source for data, which is a URL in this case for a page that has not been created yet. GetProducts.cshtml is simple:

@{
    var db = Database.Open("Northwind");
    var sql = "SELECT ProductName FROM Products WHERE ProductName LIKE @0";
    var term = Request["term"] + "%";
    var result = db.Query(sql, term);
    var data = result.Select(p => new{label = p.ProductName});
    Json.Write(data, Response.Output);
}

When you start typing into the textbox in Autocomplete.cshtml, GET requests are fired to the GetProducts page, with whatever has been typed so far sent as a querystring value, which has been named "term". You can see this clearly in Firebug:

This is used as a parameter value with a wildcard appended so that any item in the Products table with a ProductName value that starts with "term" is selected and returned from the SQL query. At the moment that data is not much use for the Autocomplete widget, which expects JSON objects that have a label or value property, or both. In the sample code, I have used LINQ to Objects to project the result of the SQL query into a collection of anonymous objects with a property of label. I could have used an alias in the SQL instead:

@{
    var db = Database.Open("Northwind");
    var sql = "SELECT ProductName AS label FROM Products WHERE ProductName LIKE @0";
    var term = Request["term"] + "%";
    var result = db.Query(sql, term);

    Json.Write(result, Response.Output);
}

Either way, the data is JSON encoded using the JSON helper's Write method and sent to the browser:

And the widget displays the possible matches:

There are a range of options you can apply to the Autocomplete widget. For instance, the current sample fires a request on every keyup event. You can change that using the minLength option. Other options are detailed along with demos at the official documentation page.

The sample detailed above is available as a download

Date Posted:
Last Updated:
Posted by:
Total Views to date: 27372

8 Comments

- Don Blaire

Thank you for your post. Just learning ASP.NET and the Razor syntax, I was looking for an autocomplete solution using SQL CE and you provided the solution. I appreciate how you explained it so well and provided a download of the sample.

- Ronald

Hello Mike,

Great post. But I have a question. How can you also give the productID to the list of labels?

Greetings

- Alex R

Hi, Mike!

Excelent post. I've downloaded and tryed to use the example, but it was not functioning.

So, I tried to debug it in WebDeveloper Express, but in that time I've noticed that the code is not reaching "GETProducts" as an error like the one below ins shown:

RunTime Error of MIcrosoft JScript: '$' is not definied.

Sure is a stupid thing here is making me dazzle, but... Would you help me?

Thank you very much!

Alexandre

- Mike

Alex - I have just downloaded the sample and it worked first time. Are you certain you haven't accidentally altered any of the code?

- Jan

Thank you so much for the post! I have a problem I'm hoping you can help me with:

When I typed something in the textbox, the autocomplete didn't show up. Then I notice that on the GetProducts page, the "Database" in the line below has a red error line reading: "The name 'Database' does not exist in the current context.'

var db = Database.Open("Northwind");

So, is the reason why the autocomplete didn't show up because it's not connected to the database? If so, what's an alternative way to connect to the DB? Or what should be fixed so that I don't get the red error line?

I'm really new to this and I will appreciate it very much if you can help me. Thank you!

- Mike

@Jan

If you are using VS or VWD, you may need to add
@using WebMatrix.Data;
to the top of your file. If that doesn't help, try posting a question to a community forum.

- WebDevi

Hello Mike,
Thank you for this post and all other post related to Webmatrix, they have been very helpful.
I have a question and need help :)
I implemented the Autocomplete feature on my website and its working fine to pull up users name from database. I have a page "ViewUserProfile.cshtml" which takes a parameter from querystring and displays the users profile information. What I am trying to do is open this page from the autocomplete matches, similar to Facebook search, when you search for a person it displays list of users and when we click on that user it opens up that users facebook page.

- Rahul Bhatnagar

Hey mike,

I have implemented the above code. bt when I run the app it shows all the results above the webpage instead loading the text box as autosuggest. Pl help.
Thanks
Add your comment

If you have any comments to make about this article, please use this form to do so. Make sure that your comment relates specifically to the article above. More general comments can be posted through the form on the Contact page.

Please note, all comments are moderated, and I end up deleting quite a lot. The kind of things that will ensure your comment is deleted without ever seeing the light of day are as follows:

  • Requests to fix your code (post a question to forums.asp.net instead, please)
  • Gratuitous links to your own site or product
  • Anything abusive or libellous
  • Spam

I do not pass email addresses on to spammers, so a valid one will assist me in responding to you personally if required.

Recent Comments

faysal 3/3/2015 11:46 AM
In response to Inline Editing With The WebGrid
Nice one can you please tell us how we can do ad and delete functionality in this. for e.g if i on...

Fairoze Mohamed Musthafa 3/2/2015 8:33 AM
In response to Date formatting in VBScript
Appreciated !!!!...

mahdi 3/1/2015 10:16 AM
In response to Getting the identity of the most recently added record
Great Article....

Sohrab 2/28/2015 12:53 PM
In response to Displaying One-To-Many Relationships with Nested Repeaters
hi.this was very usefull for me.after spending 6 hours I could find best answer for my alot....

Abolfazl RoshanZamir 2/28/2015 10:36 AM
In response to Date Formatting in C#
very informative... thanks for sharing sir......

Oscar Duran 2/27/2015 2:00 PM
In response to How To Check If A Query Returns Data In ASP.NET Web Pages
Thank you very much Mike, this post has been very useful to me....

lama 2/27/2015 1:46 PM
In response to iTextSharp - Working with Fonts
thanks alot!! is it possible to add line height value to a paragraph ?...

Oded Dror 2/27/2015 5:23 AM
In response to MVC 5 with EF 6 in Visual Basic - Creating an Entity Framework Data Model
Hi there, Where I can find Contoso University Database? Thanks, Oded Dror...

hosein ey 2/25/2015 1:56 PM
In response to ASP.NET MVC 5 with EF 6 - Working With Files
tnx for this article do you think next version of entityframework support's the sql server and ?...

Saywer Ford 2/25/2015 5:15 AM
In response to Optimising ASP.NET Web Pages Sites - Bundling And Minification
Great Article. I did everything right and working fine. How about page specific js files. Lets I...