How to solve Case Insensitive Issue in DB2 Query

we all know that SQL Server are case insensitive. when we implement the stored procedure, we did not worry about the filter criteria in the where clause.

I encounter an very strange error that use must use the correct format criteria when they run the application to search the database. the root cause of this issue is DB2 is case insensitive by default.

here is a work around to solve this issue. we can convert the filter column value to either upper case or lower case.


Select column1, column2 from DB2 Table
Where Upper(column1)=Upper(criteria one) and Lower(column2)=Lower(Criteria)

after I use the upper and lower function to convert the column values and filtered value in the stored procedure. now My Search application will ignore the case sensitive in the search input.

How to maintain Footer Link using Sharepoint List in Sharepoint 2013 Publishing Site

Since Web Content Management is one of the great feature in all version of SharePoint.  the basic version of sharepoint which foundation foundation already contain this great feature.

here i will show you it is very convenient if you have public facing web site running under SharePoint foundation. the user can easily maintain the footer navigation link of web site.


First we will create two custom site columns that associated with footer Link. one is the status site column which will allow user to temporarily turn on and off the link.  another one is the sort site column. this feature will let the user to change the link position in the footer link section.

Elements.xml of Status Column which inherits from Boolean Field Type. it will show as a checkbox to let the user check or uncheck to enable or disable the display of link.

<?xml version="1.0" encoding="utf-8"?>
<Elements xmlns="http://schemas.microsoft.com/sharepoint/">  
  <Field
       ID="{dd0b38e8-6cee-4135-ad6f-88a62aa84e4c}"
       Name="StatusColumn"
       DisplayName="Status"
       Type="Boolean"
       Required="TRUE"
       Group="Custom Site Columns">
  </Field>
</Elements>


add new Site column named Sort to the project and make it inherits from the Text Field Type. the user can input integer to indicate its position in the footer navigation links section.

<?xml version="1.0" encoding="utf-8"?>
<Elements xmlns="http://schemas.microsoft.com/sharepoint/">  
  <Field
       ID="{30701fa1-a7f5-4ece-bba0-2e44836b1c3f}"
       Name="SortOrder"
       DisplayName="Sort Order"
       Type="Text"
       Required="TRUE"
       Group="Custom Site Columns">
  </Field>
</Elements>

add new content type to the project and choose this content type to inherit from Item




Add those columns to the custom content type. as shown in the picture below

add new visual web parts to the project and modify the layout by adding the datalist asp.net control to

<div style="text-align:center;">
    <div style="width:600px; margin-left:auto; margin-right:auto;">
    <asp:DataList ID="dlFooterLink" runat="server" RepeatDirection="Horizontal">
        <ItemTemplate>
            <asp:HyperLink runat="server" Font-Underline="true"  Text='<%# "["+DataBinder.Eval(Container.DataItem,"Title")+"]" %>' 

            NavigateUrl='<%# DataBinder.Eval(Container.DataItem,"Url") %>'></asp:HyperLink>         </ItemTemplate>     </asp:DataList>     </div> </div>

in the code behind, we will implement the business logic that handle the sorting the order of each link and filter out those links with inactive status. In the page loading event,

protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
    using (SPSite spSite = new SPSite(SPContext.Current.Web.Url))
    {
        using (SPWeb spWeb = spSite.OpenWeb())
        {
            SPList footerLinkList = spWeb.Lists["test"];
            SPQuery spQuery = new SPQuery();
            spQuery.ViewFields = " <FieldRef Name='Title' /><FieldRef Name='URL' />";
            spQuery.Query = @"<Where><Eq><FieldRef Name='StatusColumn' /><Value Type='Boolean'>1</Value></Eq></Where>
                <OrderBy><FieldRef Name='SortOrder' /></OrderBy>";
            spQuery.ViewFieldsOnly = true;
            SPListItemCollection spListItems = footerLinkList.GetItems(spQuery);
            if (spListItems != null)
            {
                SPFieldUrlValue urlValue = new SPFieldUrlValue();
                //DataTable dt = new DataTable();
                //dt = spListItems.GetDataTable();
                var footerLinkItems = new List<FooterLink>();
                foreach (SPListItem spListItem in spListItems)
                {
                    urlValue = new SPFieldUrlValue(spListItem["URL"].ToString());
                    footerLinkItems.Add(new FooterLink()
                    {
                        Title = spListItem["Title"].ToString(),
                        Url = urlValue.Url
                    });
                }
                dlFooterLink.DataSource = footerLinkItems;
                dlFooterLink.DataBind();
            }
        }
    }
}
}

the code to handle the filter action

<Where><Eq><FieldRef Name='StatusColumn' /><Value Type='Boolean'>1</Value></Eq></Where>

the snippet of code that implement the sorting the position of links

<OrderBy><FieldRef Name='SortOrder' /></OrderBy>


since there is a URL column in the SharePoint which will store the navigation url of each link and  the format of URL is "www.yahoo.com, www.yahoo.com". if we just binding this column to the navigation url property of hyperlink control. the click action will failed due the incorrect format of url. the following code will solve this issue

 urlValue = new SPFieldUrlValue(spListItem["URL"].ToString());
 Url = urlValue.Url
then build and deploy. Now we can add this FootLinkNavigation Web Part to the bottom of the web page, when I click on the new item button on the ribbon toolbar. the page will loaded the following page.


after you removed the default content that associated with custom list. and added the custom content type to be the default content type of the custom sharepoint list.

the sample list that store all navigation links in the footer section

the sample web part to show the footer navigation section