Monday, April 21, 2008

Autocomplete, Jayrock, JQuery, ASP.net...

***WARNING: Not a beginner article, but not an advanced one either. Start at your own risk, I will do my best to answer questions via comments.***

What?
I have put together an autocomplete using an ASP.net ashx (handler) webservice with Jayrock and JQuery. So I'm sure you are wondering what the big deal is... Well, first off, Jayrock uses JSON-RPC and its own proxy to tunnel the Asynchronous request to the webservice and this caused some problems with most of the JQuery autocompletes I tried.

Why?
Well, for two reasons. One, it was a pain to finally get it to work (although now it seems trivial and I would like the search engines to pick this up to help others, if there are others) and two, because I set a goal of doing one good post per day (man this is a huge goal!).

How?

First create a new ASP Handler *.ashx, I called mine Lookup.ashx.
Next, you need to download: JQuery, Jayrock (**Make sure to get the Lastest builds**). Add the Jayrock DLL's as References to your Project.
Below is my Function for Wildcard searching (FYI: I put this into another class **NOTE** This returns a datatable, no db schema is proved, so you will have to figure this out) and my Lookup.ashx is below that.



Public Function GetUser(ByVal Search As String) As DataTable
Dim strQuery As String

Dim dt As New DataTable
Dim words() As String = Split(Search, " ")

strQuery = "SELECT LTRIM(RTRIM(First_Name)) + ' ' + LTRIM(RTRIM(Last_Name)) AS FullName, LTRIM(RTRIM(ISNULL(Login, ''))) As UserID, LTRIM(RTRIM(ISNULL(Job_Title, ''))) As Job " _
& " FROM Media.dbo.Employee e " _
& " WHERE 1 = 1 AND Login IS NOT NULL AND Login <> ''"

Dim tempStr As String
Dim i As Integer

For i = 0 To UBound(words)
tempStr = " AND ((e.First_Name LIKE '%" & words(i) & "%') OR (e.Last_Name LIKE '%" & words(i) & "%') OR (e.Login LIKE '%" & words(i) & "%'))"

If Len(words(i)) > 0 Then
strQuery += tempStr
End If
Next


Dim myConn As New System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ToString)
Dim myCmd As New System.Data.SqlClient.SqlCommand(strQuery, myConn)

Try
myConn.Open()
Dim myReader As System.Data.SqlClient.SqlDataReader
myReader = myCmd.ExecuteReader
dt.Load(myReader)
Finally
myConn.Close()
End Try

Return dt
End Function
Here's the Lookup.ashx
<%@ WebHandler Language="VB" Class="Lookup" %>

Imports System
Imports System.Web
Imports Jayrock.Json
Imports Jayrock.JsonRpc
Imports Jayrock.JsonRpc.Web

Public Class Lookup
Inherits JsonRpcHandler

_
Public Function Lookup(ByVal q As String) As Collection
Dim Users As New Collection()
Dim myRequest As New RequestClass()

Dim User As New UserEmployee

Dim dr As DataRow
Dim dt As DataTable

dt = myRequest.GetUser(q)

For Each dr In dt.Rows
User.FullName = dr("FullName")
User.UserID = dr("UserID")
User.Job = dr("Job")
Users.Add(New UserEmployee(User))
Next

dt.Dispose()

Return Users
End Function

End Class

Public Class UserEmployee
Public FullName As String
Public UserID As String
Public Job As String

Public Sub New()

End Sub

Public Sub New(ByVal User As UserEmployee)
Me.FullName = User.FullName
Me.UserID = User.UserID
Me.Job = User.Job
End Sub
End Class

Next create a standard ASP.net page *.aspx


In the <head> section of your aspx page, add:

<style type="text/css">
.suggestionsBox {
position: relative;
left: 30px;
margin: 10px 0px 0px 0px;
width: 200px;
background-color: #212427;
-moz-border-radius: 7px;
-webkit-border-radius: 7px;
border: 2px solid #000;
color: #fff;
}

.suggestionList {
margin: 0px;
padding: 0px;
}

.suggestionList li {
margin: 0px 0px 3px 0px;
padding: 3px;
cursor: pointer;
}

.suggestionList li:hover {
background-color: #659CD8;
}
</style>
<script type="text/javascript" src="Lookup.ashx?proxy"></script>
<script type="text/javascript" src="js/json.js"></script>
<script type="text/javascript" src="js/jquery-1.2.3.js"></script>

<script type="text/javascript">
function jQueryChannel() {
this.rpc = function(call) {
if (!call.callback)
throw new Error('Synchronous calls not supported.');
$.ajax({
type: "POST",
url: call.url,
data: JSON.stringify(call.request),
beforeSend: function(xhr) {
xhr.setRequestHeader("X-JSON-RPC", call.request.method);
},
success: function(s) {
call.callback(JSON.eval(s));
}
});
}
}

function suggest(inputString) {
if(inputString.length > 3) {
var lookup = new Lookup(); //Instantiates new Jayrock Proxy from *.ashx?proxy
lookup.channel = new jQueryChannel(); // Overrides default Jayrock Proxy Channel

lookup.Lookup(inputString, function(data) {
//$('#test').html("Length: " + data.result.length + " Test" + dump(data.result));
var results = data.result;

if (results.length > 0) {
var html = "";

//html += "<ul>";
for(var i = 0; i < results.length; i++) {
html += "<li onclick=\"fill('" + results[i].userID + "');\">" + results[i].fullName + "<br />(" + results[i].job + ") " + "<br />" + results[i].userID + "</li>";
}
//html += "</ul>";

$('#autoSuggestionsList').html(html);
$('#suggestions').show();
}
});
} else { // Hide the suggestion box.
$('#suggestions').hide();
}
} // lookup

function fill(value) {
$("#ctl00_ContentPlaceHolder1_txtUserID").val(value);
setTimeout("$('#suggestions').hide();", 200);
}
</script>
In the body of your aspx page, add a textbox and then modify it as shown below.
<div>
<label>
<b>UserID:</b>
<asp:TextBox ID="txtUserID" runat="server"></asp:TextBox><span style="font-size: 16pt">
<asp:Label ID="lblUserID" runat="server" Font-Size="12pt"></asp:Label>
</span>
</label>
<div class="suggestionsBox" id="suggestions" style="display: none;">
<img src="images/autocomplete_upArrow.png" style="position: relative; top: -12px; left: 30px" alt="upArrow" />
<div class="suggestionList" id="autoSuggestionsList"></div>
</div>
</div>
In your code behind (*.aspx.vb) add to the Page_Load function:
txtUserID.Attributes.Add("onkeyup", "suggest(this.value);")
txtUserID.Attributes.Add("onblur", "fill();")
This adds 'onkeyup="suggest(this.value);" onblur="fill();"' to your ASP.net Textbox

Now, unless I missed something, you should have a working fast, simple, autocomplete using JQuery (awesome Javascript framework, Jayrock (easy to build JSON webservices with), and JSON (lightweight).

To be fair, I must give credit, where credit is due...

Thanks go to the creators of JQuery, Atif Aziz (the creator of Jayrock) and his response on Google Groups for JayRock and JQuery as well as
Jamie at http://nodstrum.com/2007/09/19/autocompleter/ who's autocomplete I bastardized.

5 comments:

simpsons88 said...

Well, you make it seem simple. I used to work closely with VB.Net, i hope my VB skills have not gone missing. I'll try this and reply with some feedback.

Mike said...

Thanks Adam, let me know if you have any questions.

Jitendra Patil said...

hey i liked your post but i am C#.net guy so dude can you send me the C# code on jitendrapatil@in.com
please .

Giotz said...

Finally a developer that posts an example in VB.Net and not that nasty C# Yoda-like language. Awesome work!

Thanks!

sameer said...

hi how to do auto complete text box with web service winch is hosted in my PC.