***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
<%@ 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>In the body of your aspx page, add a textbox and then modify it as shown below.
<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>
<div>In your code behind (*.aspx.vb) add to the Page_Load function:
<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>
txtUserID.Attributes.Add("onkeyup", "suggest(this.value);")
txtUserID.Attributes.Add("onblur", "fill();")
This adds 'onkeyup="suggest(this.value);" onblur="fill();"' to your ASP.net TextboxNow, 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.
