SQL Server Error : Arithmetic overflow error converting expression to data type int

After running a simple aggregate sql "select sum(imagesize) from images" is producing following error:-

Arithmetic overflow error converting expression to data type int

The table "images" is a very simple, having datatype of imagesize as bigint. The table has at least 2 million rows and should not be a problem at all. Tried number of options but no luck and finally found out the issue is actually because of the aggregate function it self. Aggregate function "sum" returns int but the totals of imagesize field is exceeding the size of int and hence was showing the error message. So the fix for this is changing the sql to return bigint as follows :-

select sum(cast(imagesize as bigint)) as 'TotalImageSize' from images

Now this worked.
Your most unhappy customers are your greatest source of learning. – Bill Gates

Continue Reading

Apply JQueryUI for html element browse or input type=file

Jquery UI provides very best features that can be applied to html controls, one such feature is applying JQuery UI styling for buttons. Applying button style as such is very easy, but how can this be applied for a html element input(type=file). Now that looks little tricky. Ok first consider following html :

Now bind events

$(document).ready(function () {
        $('#btnBrowseAttachment').button();
        $('#btnBrowseAttachment2').button();

        $('#btnUpdateAttachment').button();

    });

So far we have added controls and have assigned JQuery UI buttons feature but we have too many controls. Apply following CSS styles

#btnBrowseAttachment2{
 position:absolute;
 /* start of transparency styles */
 opacity:0;
 -moz-opacity:0;
 filter:alpha(opacity:0);
 /* end of transparency styles */
 z-index:2; /* bring the real upload interactivity up front */
 width:270px;
}

Run the app and you should be able to see only one browse button (btnBrowseAttachment), ie.. btnBrowseAttachment2 button has been applied transparent style so that the click event binded to btnBrowseAttachment2 will fire when clicked btnBrowseAttachment.

For a list of all the ways technology has failed to improve the quality of life, please press three. ~Alice Kahn

Continue Reading

Generate summary using multiple aggregations with NHibernate

NHibernate allows adding multiple aggregations at the same time for a single ICriteria and generates multiple objects. Below sample shows how this can be achieved

using (var session = _sessionFactory.OpenSession())
            {
                ICriteria criteria = session.CreateCriteria(typeof(InvoiceLine));
                criteria.Add(Restrictions.Where(i => i.Invoice.ID == 1234));
                criteria.SetProjection(Projections.ProjectionList()
                    .Add(Projections.Count("ID"))
                    .Add(Projections.Sum("Price"))
                    .Add(Projections.Sum("Quantity"));
                object[] retObjects = (object[])criteria.UniqueResult();    
            }

Now you have got some list of objects which you can convert to your desired type. So you have to manually covert them, how about if NHibernate does this for you. First create a summary class which does not need to be part of you database

public class InvoiceSummary
    {
        public int LineCount;
        public long LineTotalPrice;
        public long TotalQuantity;        
    }

Now populate the data

      using (var session = _sessionFactory.OpenSession())
            {
                ICriteria criteria = session.CreateCriteria(typeof(InvoiceLine));
                criteria.Add(Restrictions.Where(i => i.Invoice.ID == 1234));
                criteria.SetProjection(Projections.ProjectionList()
                    .Add(Projections.Count("ID").As("LineCount"))
                    .Add(Projections.Sum("Price").As("LineTotalPrice"))
                    .Add(Projections.Sum("Quantity").As("TotalQuantity")));
     .SetResultTransformer(NHibernate.Transform.Transformers.AliasToBean(typeof(InvoiceSummary)));
                InvoiceSummary summary = (InvoiceSummary)criteria.UniqueResult();
            }

NHibernate.Transform.Transformers.AliasToBean does the job of using the alias names to assign the properties of InvoiceSummary object Technological progress has merely provided us with more efficient means for going backwards. ~Aldous Huxley

Continue Reading

Using NHibernate Criteria with Join to get row count

Google for lot of places for using Criteria object having joins and get rowcount either by using Projections.RowCount or CriteriaTransformer.TransformToRowCount. Now I have started looking into NHibernate source code which does also provides with examples of how to use it. So the example is as follows :-

  public void TransformToRowCountTest()
  {
   ISession s = OpenSession();
   ITransaction t = s.BeginTransaction();

   ICriteria crit = s.CreateCriteria(typeof(Student));
   ICriteria subCriterium = crit.CreateCriteria("PreferredCourse");
   subCriterium.Add(Property.ForName("CourseCode").Eq("MCSD"));


   ICriteria countCriteria = CriteriaTransformer.TransformToRowCount(crit);

   int rowCount = (int)countCriteria.UniqueResult();

   t.Rollback();
   s.Close();
  }

This does the job of counting number of students who are enrolled for a course with code "MCSD" The real danger is not that computers will begin to think like men, but that men will begin to think like computers. ~Sydney J. Harris

Continue Reading

Add or Modify model data before submit in ASP.NET MVC when using jQuery Form Plugin

Having recently started using ASP.NET MVC for a website I am quite impressed the way MVC framework works as opposed to standard ASP.NET web forms.

Also coupled the website with jQuery Form Plugin which works seamlessly with ASP.NET MVC. jQuery Form Plugin basically provides various options such as
beforeSubmit - to validate before submitting data
success - to refresh/update content after success form submition

All works great, now what I actually need is to change data before submit so I have made some changes to jQuery Form Plugin as follows :-

.....
.....
// give pre-submit callback an opportunity to abort the submit
if (options.beforeSubmit && options.beforeSubmit(a, this, options) === false) {
    log('ajaxSubmit: submit aborted via beforeSubmit callback');
    return this;
}

// give addDataBeforeSubmit an opportunity to add custom data to be send along with form submit
if(options.addDataBeforeSubmit)
{
 var moreData = options.addDataBeforeSubmit();
 $.each( moreData, function( i, item ) {
  var bExists = false;
  $.each( a, function( j, aitem ) {
   if(aitem.name == item.key){
    aitem.value = item.value;
    bExists = true;
   }
  });
  if(!bExists)
  {
   a.push({ name: item.key, value: item.value });
  }
 });
}

// fire vetoable 'validate' event
this.trigger('form-submit-validate', [a, this, options, veto]);
if (veto.veto) {
 log('ajaxSubmit: submit vetoed via form-submit-validate trigger');
 return this;
}

.....
.....

So this should cope with changing/adding new values to data that is to be sent for controller action (post)

Man is still the most extraordinary computer of all.
~John F. Kennedy

Continue Reading

javascript or .js files not loading properly for static html file in chrome, firefox works fine in IE

Kind of feeling itchy since couple of weeks for not being able to find out why javascript or .js files not loading properly for static html file in chrome and firefox, but works absolutely fine in IE.

Ok now used browser debugging tools, IE show the .js files loaded successfully and works fine. But chrome/forefix shows script were loaded like gibberish (chineese,japanese something like that). Something fishy going on, but how would you find it. Ok tried some other static html files and works absolutely fine, but only the html generated using xsl is having the issue.

No luck, then suddenly felt something wrong with file. Ok opened the working file in notepad and have found this file is saved ASCII encoding. Now the buggy file its in unicode that's it.

IE loads all references in the format available by the file, but chrome/firefox tries to load based on the base file (.html file) which has

m eta http-equiv="Content-Type" content="text/html; charset=utf-16"

So modified xsl to output standard utf-8 format and job done.

Never trust anything that can think for itself if you can't see where it keeps its brain.
~J.K. Rowling

Continue Reading

JQuery dialog inside update panel generating duplicate elements

JQuery dialog is a nice feature provided by JQuery UI, having said care should be taken when using dialog boxes inside an update panel.

Consider a dialog box is inside an update panel as shown below :-

      
Blogger Name
      
  
      
Blogger email
      


  

Now javascript for displaying the dialog

var btnAddBloggerData= $("#btnAddBloggerData");
btnAddBloggerData
  .unbind("click")
  .bind("click", function () {
  this.disabled = true;
  $("#bloggerdata_dialogform").dialog("destroy");
  $("#bloggerdata_dialogform").dialog({
            autoOpen: true,
            modal: true,
            width: 500,
            buttons: {
                Cancel: function () {
                    $(this).dialog("close");
                },
                "Create": function (evt) {
                /*ajax call to update */
               }
            },
            close: function () {

            }
        });

});

All seems right and works fine. If this has been an asp.net server control (.ascx) would have completely weird behaviour. Each time a post back occurs a new dialog is added and finally there would be duplication of ids ie.. number of elements with id as "bloggerdata_dialogform" will increase for every postback.

Hence to resolve this issue do not use $("#bloggerdata_dialogform").dialog("destroy"); which is a killer. Now modify your script to

$.ready(function(){
   $("#bloggerdata_dialogform").dialog({
            autoOpen: false,
            modal: true,
            width: 500,
            buttons: {
                Cancel: function () {
                    $(this).dialog("close");
                },
                "Create": function (evt) {
                /*ajax call to update */
               }
            }
        });
});
var btnAddBloggerData= $("#btnAddBloggerData");
btnAddBloggerData
  .unbind("click")
  .bind("click", function () {
  this.disabled = true;
  $("#bloggerdata_dialogform").dialog("open");

});

So instead of destroying the element and use the same element for every postback.

Errors using inadequate data are much less than those using no data at all.
Charles Babbage

Continue Reading