Archive

Archive for the ‘ASP.Net’ Category

OleDBConnection – Unspecified error

August 5, 2013 1 comment

On top of SharePoint 2010, I was trying to create an excel parser where I hit with this strange “Unspecified error”. The exception is getting generated from “OleDBConnection.Open” method

There was no inner exceptions or stack traces, which made it difficult to find the root cause. After trying out different steps like uninstalling office, restarting iis etc. eventually I resolved it by the below 3 steps

  1. Download latest Access Databas Engine http://www.microsoft.com/en-in/download/details.aspx?id=13255. Ensure to select the correct platform 32 vs 64
  2. Connection string = “Provider=Microsoft.ACE.OLEDB.12.0;Data Source=fileLocation;Extended Properties=”Excel 12.0 Xml;HDR=Yes;IMEX=1”
  3. In the web configuration file turn off Impersonation <identity impersonate=”false” />

In the connection string I am using HDR=Yes, this mean the first row in the excel file is a header row. IMEX=1 will force the excel cells to be treated as Text rather than the Excel General type which converts the data (The feature I hate most)

Advertisements

ASP.Net JQuery Cascading Dropdown

In this article I am going to explain how easily we can implement cascading drop down in an asp.net web application. To illustrate assume we have parent child relationship Authors and Books. Once Author is selected the Books of that Author should get populated on the second drop down control.

CodeBehind

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class _Default : System.Web.UI.Page 
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (IsPostBack == false)
        {
            //Assume this list we get from database
            List<string> authors = new List<string>()
            {
                "Stephen King",
                "Nicholas Sparks",
                "Jodi Picoult",
                "Nora Roberts"
            };

            this.ddlAuthors.DataSource = authors;
            this.ddlAuthors.DataBind();
        }
    }

    [WebMethod()]
    public static string GetBooks(string author)
    {
        //Author = Key and Book = Value
        Dictionary<string, List<string>> books = 
new Dictionary<string, List<string>>()
        {
           {"Stephen King", new List<string>() 
{"The Stand", "It", "The Shining"} },
           {"Nicholas Sparks", new List<string>() 
{"The Notebook", "The Last Song", "A Walk to Remember", "Dear John"} },
           {"Jodi Picoult", new List<string>() 
{"My Sister's Keeper", "Ninteen Minutes", "The Pact", "Plain Truth"} },
           {"Nora Roberts", new List<string>() 
{"Montana Sky", "Angels Fall", "Carolina Moon", "Northern Ligths"} }
        };

        if (books.ContainsKey(author))
        {
            return string.Join(";", books[author].ToArray());
        }

        return string.Empty;
    }
}

Aspx Page

<%@ Page Language="C#" AutoEventWireup="true"  CodeFile="Default.aspx.cs" 
Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" 
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
    <script type="text/javascript" 
    src="http://cdnjs.cloudflare.com/ajax/libs/jquery/1.8.3/jquery.min.js"></script>
</head>
<body>
    <script type="text/javascript">
        $(document).ready(function () {
            $('#' + '<%=this.ddlAuthors.ClientID %>').change(FillBooks);
        });

        function FillBooks() {
            $.ajax(
                {
                    type: "POST", 
                    url: "Default.aspx/GetBooks", 
                    data: "{'author':'" + 
$('#' + '<%=this.ddlAuthors.ClientID %>').val() + "'}",
                    contentType: "application/json; charset=utf-8",
                    dataType: "json",
                    success: successFillBooks,
                    error: errorFillBooks
                }
            );
        }
        function successFillBooks(result) {
            if (result["d"] != null) {
                var books = result["d"].split(';');
                var length = books.length;
                document.getElementById('<%=ddlBooks.ClientID %>').
options.length = 0;
                var dropDown = document.
getElementById('<%=ddlBooks.ClientID %>');
                for (var i = 0; i < length - 1; ++i) {
                    var option = document.createElement("option");
                    option.text = books[i];
                    option.value = books[i];
                    dropDown.options.add(option);
                }
            }
        }
        function errorFillBooks() {
            alert("Error occured");
        }
    </script>
    <form id="form1" runat="server">
    <div>
        Authors: <asp:DropDownList ID="ddlAuthors" runat="server">
</asp:DropDownList>
        <br />
        Books: <asp:DropDownList ID="ddlBooks" runat="server">
</asp:DropDownList>
    </div>
    </form>
</body>
</html>

Categories: ASP.Net, C#, jQuery

jQuery KeyDown Event

There will be scenarios which require to capture KeyDown event and execute some business logic. Here is one generic function I uses to accomplish this task.

    function CheckKey(e) {
        var keynum;
        if (window.event) // IE
        {
            keynum = e.keyCode;
        }
        else if (e.which) // Netscape/Firefox/Opera
        {
            keynum = e.which;
        }
        if (keynum == 8) {
           //Example: If backspace clear id
           $('#employeeid').val('');
        }
    }

Usage

   <input id="txtEmployee" onkeydown="CheckKey(event);" />      
Categories: ASP.Net, jQuery

jQuery AutoComplete Control

In this post I am going to explain the steps required to use jQuery type ahead control.

Autocomplete, when added to an input field, enables users to quickly find and select from a pre-populated list of values as they type, leveraging searching and filtering. Dependencies include jQuery.UI.core and jQuery.UI.widget

STEP1: Including required js files

<script type="text/javascript" src="/js/jquery.min.js"></script>
<script type="text/javascript" src="/js/jquery.ui.core.js"></script>
<script type="text/javascript" src="/js/jquery.ui.widget.js"></script>

STEP2: HTML entries

<input type="text" id="txtEmployeeName" onkeypress="$('#employeeId').val('');"/>
<input type="hidden" id="employeeId" />

STEP3: jQuery code

$(function () {
$("#txtEmployeeName").autocomplete({
   source: function (request, response) {
      var getUrl = "/SomeWCFService/Employees?" +
          "$filter=substringof('" + request.term + "',EmployeeName) eq true)" +
          "&$orderby=EmployeeName";
      $.ajax({
         url: getUrl,
         type: "GET",
         contentType: "application/json; charset=utf-8",
         dataType: "json",
         data: {
            featureClass: "P",
            style: "full",
            maxRows: 10
         },
         dataFilter: function (data, type) {
            return data.replace(/\\'/g, "'");
         },
         success: function (data) {
            response($.map(data.d, function (result) {
               return {
                  label: result.EmployeeName,
                  value: result.ClientId
               }
            }));
         }
      },
      focus: function (event, ui) {
         var stringValue = ui.item.label;
         stringValue = stringValue.replace(/\/g, '');
         stringValue = stringValue.replace(/\/g, '');
         $("#txtEmployeeName").val(stringValue);
         return false;
      },
      minLength: 3,
      select: function (event, ui) {
         var stringValue = ui.item.label;
         stringValue = stringValue.replace(/\/g, '');
         stringValue = stringValue.replace(/\/g, '');
         $("#txtEmployeeName").val(stringValue);
         $("#employeeId").val(ui.item.value);
         return false;
      },
      open: function () {
         $("#txtEmployeeName").removeClass("ui-corner-all").addClass("ui-corner-top");
      },
      close: function () {
         $("#txtEmployeeName").removeClass("ui-corner-top").addClass("ui-corner-all");
      }
  }
);
Categories: ASP.Net, jQuery