Access ... thanks Microsoft for another problem!

Today I have this funny problem which Access and OleDB gave me two different results for the same view.  After hours of puzzling, finally found out it is because of the wild card used in the LIKE clause.

The story as follow, I have a view in Access similar to the below

select * where code like '*fun*'

This works fine within Access, i.e. only result contains "fun" returned.  However, when I accessing this view within VisualStudio via OleDB, the conditional clause was ignored.  Since I am accessing the same view within the same Access database, it really puzzled me why the different results returned, especialy there was no errors returned.

Initially I thought it was because of caching, so I restarted my machine.  Run the query within Visual Studio after restart, same problem.

Then I removed the connection within Visual Studio and recreate it as I thought it may caused by wrong settings.  Again, same problem.

Finally, I  replaced the * with % as I would write my LIKE in standard SQL. Bingo, everything works as intended.

Thanks Microsoft.  Can you not try to be special and screw things up?!

Session and .NET application

It seems session ID does not created until it is first used, i.e. you may get an error when you are trying to retrieve it the first time. To avoid this, you may consider to put something similar to below in your Global.asax file if you are using .NET

protected void Session_Start(object src, EventArgs e)

{

// http://stackoverflow.com/questions/904952/whats-causing-session-state-has-created-a-session-id-but-cannot-save-it-becaus

// This is needed in order to maintain a stable session id

// it seems the session only got created at its first used. The line below just does that!

string sessionId = Session.SessionID;

}

source: as stated in the comments of the codes.

MVC2 Ajax and Date

With .NET MVC2/3, you can serialize an object into JSON easily by using the JSON(object) function. However, there is a minor issue you may want to watch out. In .NET, DateTime object is serialized as an signed long integer of the milliseconds sing the January 1, 1970. If you use the normal eval() method to convert the JSON string into objects, you will get the date evaluated wrong.

Instead you should use the Sys.Serialization.JavaScriptSerializer.deserialize() function (as below) instead.

var a = Sys.Serialization.JavaScriptSerializer.deserialize(context.get_data());

Also see: http://weblogs.asp.net/bleroy/archive/2008/01/18/dates-and-json.aspx

JQuery UI Autocomplete and MVC

Today I have my fun with the Autocomplete feature of JQuery with .NET MVC back-end.

For once the Microsoft solution is not the trouble, but the JQuery is. To cut the story short, here is what I wanted to do:

1. a textbox allowing a user to enter a post code
2. use this post code to locate matching address from the database
3. display the address NOT the post code on the drop down list.
4. when an address selected, complete the post code and the address on the form.

As I am using MVC2 framework for my server side programming, it is trivial to do the query and to pass back the data in JSON format.

[HttpPost]
public ActionResult GetAddresses(string term)
{
AddressEntity _en = new AddressEntity ();
var addresses = _en.Addresses
.Where(p => p.PostCode.Contains(term))
.OrderBy(o => o.StreetName)
.ToList();

// .net is quite happy to serialize an object to
// json representation.
return Json(addresses);
}

The Json() function will nicely serialize the object in JSON format for you. To prove this work, I use IE9's developer tool to check the response body



Now, the tricky part. There are so many different tutorials on this very subject, and some are a bit out-dated. For example, to use the Autocomplete, you do not need to use the plug-in anymore, but to include the JQuery UI library as it is part of the standard UI library.

Anyway, here are things that works for me.

Firstly, include the following library and styles in the head section of your HTML page

<script type="text/javascript"
src="http://ajax.googleapis.com/ajax/libs/jquery/1.6.2/jquery.min.js"></script>
   <script type="text/javascript"
src="https://ajax.googleapis.com/ajax/libs/jqueryui/1.8.14/jquery-ui.min.js"></script>
   <link rel="stylesheet"
href="http://ajax.googleapis.com/ajax/libs/jqueryui/1.8/themes/ui-lightness/jquery-ui.css" type="text/css" media="all">

Next, create a Textbox and give it a unique ID

// this will generate HTML equivalent
// <input id="postTags" type="text">
<%: Html.TextBox("postTags")  %>

Lastly, include the script similar to below:

$("#postTags").autocomplete(
{
source: function (request, response) {
$.ajax({
url: '<%=Url.Action("GetAddresses", "Home") %>',
type: "POST",
dataType: "json",
data: {
term: request.term
},
success: function (data) {
response($.map(data, function (item) {
// remap the object to the structed used by Autocomplete
return { label: item.FullOfficialName, value: item, id: item.ID }
}))
}
}); // end .ajax
},
minLength: 2,
select: function (event, ui) {
$("#Street").val(ui.item.value.StreetName);
$("#postTags").val(ui.item.value.PostCode);
// according to the documentation, cancelling the event, i.e. return false
// will prevent the default replacement action.
return false;
}
});

Here are some explanations.

In this example, the source is from an external source, and is obtained through AJAX. The source: function (request, response) specifies where the source is.

For the source option, you may notice the URL is generated dynamically using

<%=Url.Action("GetAddresses", "Home") %>

Effectively, it generate a URL similar to this "/Home/GetAddresses"

Another important option is the dataType. It is important to set it to json, so the response will get handled correctly.

Now, the most important part. It seems the array used by the Autocomplete is an array of object which has 3 fields, label, value, and id. Since our object may carry more (or less) information, we need to do some transformations, hence the function:

success: function (data) {
response($.map(data, function (item) {
// remap the object to the structed used by Autocomplete
return { label: item.StreetName, value: item, id: item.ID }
}))
}

The purpose of this function is to use the map function to transform the objects in the array retrieved from the server to the objects with the format used by Autocomplete array.

The label field is used by Autocomplete for the text displaying to the user, the value is the value used when an option is selected and finally, the id is the ID of the option.

As stated before, in my situation, I need all the information from the server, therefore I decided to set the value as the object itself. However, this causes a problem. When an option is selected, the value inserted to the textfield is [object] rather than the post code I wanted because the system do no know how to render my object.

In order to overcome this, I customize the select event handler as following

select: function (event, ui) {
$("#Street").val(ui.item.value.StreetName);
$("#postTags").val(ui.item.value.PostCode);
// according to the documentation, cancelling the event, i.e. return false
// will prevent the default replacement action.
return false;
}

This handler is invoked when a selection is made, and it will retrieve and use the correct fields in my object for various part of my form. One important note, at the end of this function, you need to return false to stop the default behavior, otherwise, the #postTags will still be shown as [object] as before.

Composite keys

If you have a database table which holds keys from another two tables and you do not want duplications, you may want to do the following

ALTER TABLE
ADD UNIQUE (, )

Now the database will ensure not entries have the same key1+key2 combination!

Describe the structure of a table in SQLServer

You can get the structure of a table on SQL Server using the query

exec sp_help 'tablename'

Google Code University

Just came across the Google code University, and find the materials there are very useful to refresh the distance memory of "Programming Lectures" had in University years years ago ... and something new too

http://code.google.com/edu/courses.html