Autocomplete extender with Id and Description

It was a very long time ago that i worked with the Ajax Toolkit, but sometimes you are requested to implement old projects..

And the language is VB.NET, that i was not using since 2003.

The request was to implement an Autocomplete extender from a customers list (from a Sql Server table), but in the database is requested to write the autoincrement ID, not the description.

In order to implement the ajax toolkit, in the .aspx markup must be added the header:

<%@ Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" TagPrefix="cc1" %>

With the obvious adding of a reference , for the ASP.NET project, to AjaxControlToolkit.dll

A simple autoextender can be written as

<asp:TextBox ID="txtAgency" runat="server" ></asp:TextBox>
<cc1:AutoCompleteExtender ID="aceAgency" FirstRowSelected="false" MinimumPrefixLength="1" TargetControlID="txtAgency" ServiceMethod="GetListAgencies" runat="server">

in the aspx page; in the code behind for this page we can implement a page method (the “ServiceMethod”) as

<System.Web.Script.Services.ScriptMethod(), _
System.Web.Services.WebMethod()> _
Public Shared Function GetListAgencies(ByVal prefixText As String, ByVal count As Integer) As List(Of String)
    Dim conn As SqlConnection = New SqlConnection
    conn.ConnectionString = <string from web.config>
    Dim cmd As SqlCommand = New SqlCommand
    cmd.CommandText = "SELECT AnagId, AnagName FROM [dbo].[SomeTableOrView] WHERE AnagName LIKE @SearchText + '%'"
    cmd.Parameters.AddWithValue("@SearchText", prefixText)
    cmd.Connection = conn
    Dim lstAgencies As List(Of String) = New List(Of String)
    Dim sdr As SqlDataReader = cmd.ExecuteReader
    While sdr.Read
    End While
    Return lstAgencies
End Function

This works, we type one letter (MinimumPrefixLength=”1″) on the keyboard and we are proposed with the list of names beginning with the typed letter.

But from the database in our sample we have only the AnagName value , the AnagId is not connected in some manner with the selected item: if we need to save that Id instead of the name this approach is not valid.

In order to save that id we need some javascript code and another approach in the web method.

First, this is the new aspx markup:

<asp:TextBox ID="txtAgency" runat="server"></asp:TextBox>
<cc1:AutoCompleteExtender ID="aceAgency" FirstRowSelected="false" TargetControlID="txtAgency" ServiceMethod="GetListAgencies" OnClientHidden="onClientHiddenAgency" MinimumPrefixLength="1"  EnableCaching="true" OnClientItemSelected="onClientItemSelectedAgency" OnClientPopulating="onClientPopulatingAgency" runat="server">
<input type="hidden" id="hdnAgencyId" name="hdnAgencyId" runat="server" value="0" />

The OnClient… properties are the names of javascript functions; the EnableCaching is true because the underlying data are in this case very static.

Note that there is an hidden field where to save the requested Id and this is runat=server because we need to refer this field when we will save the record.

The javascript functions (ok, not the best javascript code…):

var boolChosenAgency = false;

function onClientItemSelectedAgency(source, e) {
    window.document.getElementById("<%=hdnAgencyId.ClientID%>").value = e.get_value();
    boolChosenAgency = true;

function onClientHiddenAgency(source, e) {
    if (!boolChosenAgency) {
        window.document.getElementById("<%=hdnAgencyId.ClientID%>").value = "0";
        window.document.getElementById("<%=txtAgency.ClientID%>").value = "";

function onClientPopulatingAgency(source, e) {
    window.document.getElementById("<%=hdnAgencyId.ClientID%>").value = "0";
    boolChosenAgency = false;

What happens with this code is:

  • When the user begins to write, the Id value is changed to 0 and boolChosenAgency is set to false (“the user has not stille chosen an valid value”)
  • Only when an item is chosen the Id (coming from e.get_value()) is written in the hidden field
  • OnClientHiddenAgency is always executed, if the user has opened the list but has not clicked on a value then the id value is changed to 0

On the code behind so we must verify : if the hidden id is 0 and/or the textbox is empty the the id must be null on the db, otherwise we can write the int value.

The scope of the code is also: if the user write a not valid text, the Id is invalidated.

But how we can have the id in e.get_value() ?

This is done with this code for the web method:

<System.Web.Script.Services.ScriptMethod(), _
System.Web.Services.WebMethod()> _
Public Shared Function GetListAgencies(ByVal prefixText As String, ByVal count As Integer) As List(Of String)
    Dim conn As SqlConnection = New SqlConnection
    conn.ConnectionString = <string from web.config>
    Dim cmd As SqlCommand = New SqlCommand
    cmd.CommandText = "SELECT AnagId, AnagName FROM [dbo].[SomeTableOrView] WHERE AnagName LIKE @SearchText + '%'"
    cmd.Parameters.AddWithValue("@SearchText", prefixText)
    cmd.Connection = conn
    Dim lstAgencies As List(Of String) = New List(Of String)
    Dim sdr As SqlDataReader = cmd.ExecuteReader
    While sdr.Read
        lstAgencies.Add(AjaxControlToolkit.AutoCompleteExtender.CreateAutoCompleteItem(sdr("AnagName").ToString, sdr("AnagID").ToString))
    End While
    Return lstAgencies
End Function

The trick is done using the CreateAutoCompleteItem method as in the above code.

In the javascript code we can use e.get_value for the ID and e.get_text() for the name (“AnagName” in the sample).

Even the best sites…

Categories: Uncategorized

Cars copy & paste

Nissan Pulsar Nismo Concept      Saab 9.3

No ideas for an original Nissan family feeling ?….

Categories: Uncategorized

Breeze SaveChanges issue with Ie10

Every new technology is an hard path , an high mountain to climb.

I’m working to an MVC project with ASP.NET 4.5 , Breeze and Angular, authentication done with the new ASP.NET Identity, Entity Framework 6.

The big problem , for the average developer (me) , is:

  • For Microsoft, on MSDN you can find a lot of verbose pages about the syntax: an simple CRUD example using these technologies , add update delete of records, is hard to find.
  • For thirdy parties technologies you can find abstruse , overcomplexed samples ; but still there is a lack of a simple table management with CRUD operations.

In every case the samples simply ignore (at the least the ones that i discover with Google) the everyday questions with concurrency : another user could have deleted the record that you are trying to save, or you are trying to save a record of a table where there is an unique index for a field.

Ok , this was the rant, now the problem.

The code is about the update of a record in a Vehicle Classes table (ClassiMezzi).

Using Breeze, in a BreezeController (a class decorated with “[BreezeController]”) the typical sample for an update is

public SaveResult SaveChanges(JObject saveBundle)
    return _repo.SaveChanges(saveBundle);

This works with Ie11, the latest Firefox, Chrome, Safari; _repo is an repository object that at the end calls the Entity Framework context provider (in our sample EFContextProvider<ClassiMezziContext>)

But if you try to use this method for adding a new record on a table where there is an index UNIQUE, there are troubles with the error messages using Internet Explorer 10.

For these new apps i simply don’t consider Ie < 10; if you are still using Ie6 , Ie7.. time to upgrade …but to ignore the previous version is a bit excessive, for me: i test everything with the latest Firefox, Chrome also under Linux Android and Mac, for Ie to test the latest version and the previous seems reasonable.

And so, testing the code with multiuser issues (record deleted from another user, check constraints, UNIQUE indexes) i discovered the problem.

From the javascript side , the controller is called with an Ajax call with callbacks for success or failure:

function saveFailed(error) {
    try {
    catch (ex) { }
    service.ClasseMezzoEntity = null;
    if (error.httpResponse.status === 401 || error.httpResponse.status === 403) {
        logError(localize.localizeText("UnauthorizedOperation"), null, true);
    //var strMsg = breeze.saveErrorMessageService.getErrorMessage(error).toLowerCase(); old version ok with Ie11 , not Ie10
    var strMsg =[0].toLowerCase();
    if (strMsg.indexOf("entities may have been modified or deleted since entities were loaded") > 0) {
        logWarning(localize.localizeText("AnotherUsrHasChangeData"), null, true);
    if (strMsg.indexOf("ix_classimezzi") > 0) {
        logWarning(localize.localizeText("ClassNameDuplicated"), null, true);
    if (strMsg.indexOf("errdelete:anotheruser") > 0) {
        logWarning(localize.localizeText("AnotherUsrHasDeleteData"), null, true);
    //  other code for unknown error management
    var msg = breeze.saveErrorMessageService.getErrorMessage(error);
    // etc.
function saveClasseMezzo() {
    service.isSaving = true;// see at the end of this module
    service.lastSaveSuccess = true;
    return manager.saveChanges()

For example we try to add another ClasseMezzo (Vehicle class) with the same name of an existing item on the the db table.

With Ie11 and other latest browsers the first controller code is ok , and if our ajax call fails because the UNIQUE index the first

var strMsg = breeze…. commented code works perfectly.

But while with Ie11 etc. the error.message in javascript contains the string “Cannot insert duplicate key row …” with the index name that helps to manage the error (in this sample the index name is “ix_classimezzi”) in Ie10 we get

That is an anonymous “An error has occurred” that can’t help to manage the error, and no trace of the original SQL error in httpResponse: a big trouble if i would to say to the customer “you are trying to insert an duplicate item” because could be another thing , for example an SQL check violation.

The problem is the simple approach in the WebApi2 controller.

In order to manage correctly the errors coming from multiuser issues the WebAPI2 controller code must be:

[Authorize(Roles = "PowerUser")]
public SaveResult SaveChanges(JObject saveBundle)
    SaveResult objRes = null;
    List<object> objErr = null;
    var classimezziList = JsonConvert.DeserializeObject<List<ClassiMezzi>>(saveBundle.SelectToken("entities").ToString());
        objRes = _repo.SaveChanges(saveBundle);
        return objRes;
    catch (Exception ex)
        var keyMappings = new List<KeyMapping>();
        objErr = new List<object> { JsonConvert.SerializeObject(ex.Message) };
        return new SaveResult()
            Entities = classimezziList.Cast<object>().ToList(),
            Errors = objErr,
            KeyMappings = keyMappings

Let’s examine the code.

We pass to the controller an JSON object , that we want deserialized in order to correctly manage the error; note in the catch management code: is explicitly created an SaveResult object , instead of the automatic creation.

Setting the Errors field to something != null , in our case the error message serialization, we cause the call of the javascript failure callback , and in the javascript failure code the message must be read as

var strMsg =[0].toLowerCase();

because when the code is falling back we have this , now:

So we can manage the things as in the next rows.

This is valid also for Ie11 , and Firefox, Chrome under Windows, Linux, Mac, Android.

At least, this is the situation with the current latest Breeze (1.3.8)

The requested resource does not support http method ‘POST’

After a life of web sites developed with Web Forms, now i’m working with MVC.

Not a basic MVC, but SPA apps using Angular, Breeze, Twitter Bootstrap.. an huge jump.

In the Controller folder of my solution i was thinking to create an utilities controller, in this case for the logging of javascript errors, paging utilities, and so on.

So i initially created a class as:

public class ServicesController : ApiController
	public void SubmitError(string jsRespoText, string jsstatus, string jssource)
		ExceptionUtility.LogJsExceptionFile(jsRespoText, jsstatus, jssource);

	public JsonGenResponse CreatePagerStdTable(int startRowIndex, int totalCount, int pageSize, string jsLoadDataFunName, string Filter, string QueryType)
		// this will be another post…

For the HttpGet , no problem.

When i tried from Fiddler the HttpPost method i got the error written in the subject of this post.

A Controller decorated with [BreezeController] works as POST, and it was tested.. but it uses a JObject parameter, i noticed.

The POST problem pops out when you try to use primitive (string, int..) parameters in a Web API call instead of an object.

A solution could be to use [FromBody], with this attribute WebAPI search the attribute:value in the request body.

But there is a problem : is not possible to specify more than one “[FromBody]” , so is not possible to write:

public void SubmitError([FromBody]string jsRespoText, [FromBody]string jsstatus, [FromBody]string jssource)

The solution is to create a class, in my case

public class JsErrorData
    public string jsRespoText { get; set; }
    public string jsstatus { get; set; }
    public string jssource { get; set; }


And then write the method as

public void SubmitError(JsErrorData jsObjErr)
    ExceptionUtility.LogJsExceptionFile(jsObjErr.jsRespoText, jsObjErr.jsstatus, jsObjErr.jssource);

The javascript caller:

function logJsError(responseText, status, source, callback) {
    var ajaxImpl = breeze.config.getAdapterInstance("ajax");
        type: "POST",
        contentType: "application/json;charset=UTF-8",
        url: strBaseHttp + "api/Services/SubmitError",
        data: "{'jsRespoText': '" + responseText.replace(/'/g, '') + "','jsstatus':'" + status + "','jssource':'" + source + "'}",
        success: function (html) {
        async: true

Now HttpPost is working.

Categories: .NET, JQuery, MVC, SPA

Good idea

Categories: Uncategorized

Oracle RAC Linked server in Sql 2014 64 bit

I was needing to create a linked server from SQL Server 2014 64 bit, on a pc with Windows 8.1 64 bit , to an Oracle configured in RAC (Real Application Clusters).

The RAC configuration is composed from 3 servers .

The first step is to install the Oracle software from

Here must be downloaded 64-bit ODAC 12c Release 2 ( for Windows x64 , the file because there is an guided installer.

After a while it starts then use the default choice, that is the Oracle Data Access Components which installs the Data Provider for .NET, Oracle Provider for OLEDB, Oracle Services for Microsoft Transaction Server, Oracle instant client.

Then use the Next button accepting all defaults until the end.

At the end there is a wizard for the compilation of tnsnames.ora file, but in this case we are working with a RAC and the wizard cannot manage this configuration.

After the installation is recommended to reboot, then we should see in Sql Management Studio the OraOLEDB provider listed:

By default in C:\app\studio\product\12.1.0\client_1\Network\Admin there is our TNSNAMES.ORA file

We must know the NETBIOS or the IP of the servers in RAC and the name of the Oracle instance, in our case TRAFSRV for example.

So the TNSNAMES.ORA , indicating the IP for each or our 3 RAC server, should be :

    (ADDRESS = (PROTOCOL = TCP)(HOST = = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = = 1521))
     (RETRIES = 5)
     (DELAY = 3)

It should be verified that the 1521 port is opened in the firewall, in both Sql and Oracle servers.

Now we can create the linked server , for example

USE [master]
EXEC master.dbo.sp_addlinkedserver @server = N'ORAARTA33', @srvproduct=N'Oracle', @provider=N'OraOLEDB.Oracle', @datasrc=N'TRAFSRV'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'ORAARTA33',@useself=N'False',@locallogin=NULL,@rmtuser=N'ORACLEUSER',@rmtpassword='ORACLEUSERPWD'

In this sample ORAARTA33 is the name of the linked server, it there are no problems we should see

Categories: .NET, Oracle, SQL Server

Asp:ButtonColumn not working in Ie11

I was working to an old VB.NET web application with Visual Studio 2010 , .NET 4.0; the solution for tables uses asp:DataGrid

Until Internet Explorer 10 all ok, with Ie11 the asp:ButtonColumn inside an asp:DataGrid was not working, clicking the link nothing.

It seems that the .NET 4 framework does not work well with Ie11 , and the update to .NET 4.5 was not an option (Windows Server 2003).

With the latest updates Ie11 was still recognized as Mozilla 0.0

The solution is to add an App_Browser folder

And then inside the folder create an .browser file with this content:

  <browser id="IE11" parentID="Mozilla">
      <userAgent match="Trident\/7.0; rv:(?'version'(?'major'\d+)(\.(?'minor'\d+)?)(?'letters'\w*))(?'extra'[^)]*)" />
      <userAgent nonMatch="IEMobile" />
      <userAgent match="Trident/(?'layoutVersion'\d+)" />
      <capability name="browser" value="IE" />
      <capability name="layoutEngine" value="Trident" />
      <capability name="layoutEngineVersion" value="${layoutVersion}" />
      <capability name="extra" value="${extra}" />
      <capability name="isColor" value="true" />
      <capability name="letters" value="${letters}" />
      <capability name="majorversion" value="${major}" />
      <capability name="minorversion" value="${minor}" />
      <capability name="screenBitDepth" value="8" />
      <capability name="type" value="IE${major}" />
      <capability name="version" value="${version}" />

  <!-- Mozilla/5.0 (Windows NT 6.3; Trident/7.0; rv:11,0) like Gecko -->
  <browser id="IE110" parentID="IE11">
      <capability name="majorversion" match="11" />

      <capability name="ecmascriptversion" value="3.0" />
      <capability name="jscriptversion" value="5.6" />
      <capability name="javascript" value="true" />
      <capability name="javascriptversion" value="1.5" />
      <capability name="msdomversion" value="${majorversion}.${minorversion}" />
      <capability name="w3cdomversion" value="1.0" />
      <capability name="ExchangeOmaSupported" value="true" />
      <capability name="activexcontrols" value="true" />
      <capability name="backgroundsounds" value="true" />
      <capability name="cookies" value="true" />
      <capability name="frames" value="true" />
      <capability name="javaapplets" value="true" />
      <capability name="supportsCallback" value="true" />
      <capability name="supportsFileUpload" value="true" />
      <capability name="supportsMultilineTextBoxDisplay" value="true" />
      <capability name="supportsMaintainScrollPositionOnPostback" value="true" />
      <capability name="supportsVCard" value="true" />
      <capability name="supportsXmlHttp" value="true" />
      <capability name="tables" value="true" />
      <capability name="supportsAccessKeyAttribute" value="true" />
      <capability name="tagwriter" value="System.Web.UI.HtmlTextWriter" />
      <capability name="vbscript" value="true" />
Categories: .NET, Ajax, Visual Studio 2010

BI for Visual Studio 2013

Still unavailable…someday at this address.

Update 05/23 : available !

Categories: BI, SQL Server, SSIS

Get every new post delivered to your Inbox.