Joseph Michael Pesch
VP Programming

SQL Server Collation Conflict

by 30. April 2010 16:17

You may receive an error like the one shown below when joining two tables that have different collation settings.  This can happen commonly when using temporary tables if the TEMPDB database has different collation settings than the operational database.  For example: I had an environment where the TEMPDB had "SQL_Latin1_General_CP1_CI_AS" while the operational database had "Latin1_General_CI_AI".  In my case I created a SQL statement using a temporary table and then joined the temporary table to a table in the operational database and received the following error: Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AI" in the equal to operation. The resolution was to apply the COLLATE attribute to the joined column(s) as shown in sample below.

create table #tmp(DNIS char(10))

 

 

insert into #tmp values('0000000001')

insert into #tmp values('0000000002')

insert into #tmp values('0000000003')

insert into #tmp values('0000000004')

insert into #tmp values('0000000005')

 

 

select cd.DNIS

from CallDetails cd

join #tmp on cd.DNIS = #tmp.DNIS collate Latin1_General_CI_AI

 

 

Tags:

SQL Server

C# Amortization Calculator Web User Control

by 22. April 2010 16:24

Create Default.aspx page with this html:

<%@ Page Language="C#" AutoEventWireup="true" %>

 

<%@ Register Src="CalcAmortizationSchedule.ascx" TagName="CalcAmortizationSchedule" TagPrefix="calc" %>

<!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>Amortization Calculator</title>

</head>

<body>

  <form id="MainForm" runat="server">

  <calc:CalcAmortizationSchedule ID="CalcAmortizationScheduleControl" runat="server" />

  </form>

</body>

</html>

 

 

Create CalcAmortizationSchedule.ascx with this html and C# code:

<%@ Control Language="C#" AutoEventWireup="true" CodeFile="CalcAmortizationSchedule.ascx.cs" Inherits="CalcAmortizationSchedule" %>

<asp:Panel ID="DataEntryPanel" HorizontalAlign="Center" Font-Names="Calibri, Tahoma, Verdana, Courier, Times"

  runat="server">

  Loan Amount:

  <asp:TextBox ID="LoanAmountTextBox" Text="250,000" Width="65" runat="server"></asp:TextBox>

  Interest Rate:

  <asp:TextBox ID="InterestRateTextBox" Text="5.875" Width="65" runat="server"></asp:TextBox>

  Term (months):

  <asp:TextBox ID="AmortizationTermTextBox" Text="360" Width="65" runat="server"></asp:TextBox>

  <asp:Button ID="SubmitButton" Text="Calculate" OnClick="SubmitButton_Click" runat="server" />

</asp:Panel>

<hr />

<asp:Panel ID="ResultPanel" runat="server">

</asp:Panel>

using System;

using System.Diagnostics;

using System.Linq;

using System.Web.UI.WebControls;

using System.Text.RegularExpressions;

 

 

public partial class CalcAmortizationSchedule : System.Web.UI.UserControl

{

  protected void SubmitButton_Click(object sender, EventArgs e)

  {

    // Base Variables

    double InterestRate = 0;

    double.TryParse(this.InterestRateTextBox.Text, out InterestRate);

    if (InterestRate > 1) InterestRate = InterestRate / (12 * 100);

    double LoanAmount = 0;

    double.TryParse(this.LoanAmountTextBox.Text, out LoanAmount);

    int AmortizationTerm = 0;

    int.TryParse(this.AmortizationTermTextBox.Text, out AmortizationTerm);

    IQueryable<AmortPayment> ap = CalcIt(InterestRate, LoanAmount, AmortizationTerm);

    GridView gv = new GridView();

    gv.DataSource = ap;

    gv.AutoGenerateColumns = true;

    gv.Font.Names = new string[] { "Calibri", "Tahoma", "Verdana", "Courier", "Times" };

    gv.Font.Size = FontUnit.Small;

    gv.HorizontalAlign = HorizontalAlign.Center;

    gv.RowStyle.HorizontalAlign = HorizontalAlign.Center;

    gv.RowCreated += new GridViewRowEventHandler(gv_RowCreated);

    gv.RowDataBound += new GridViewRowEventHandler(gv_RowDataBound);

    gv.DataBind();

    this.ResultPanel.Controls.Clear();

    this.ResultPanel.Controls.Add(gv);

    if (ap.Where(t => t.Month == ap.Last().Month -1).Single().Payment != ap.Last().Payment)

    {

      // Last payment is different than the prior months payment

      this.ResultPanel.Controls.Add(new Literal { Text = "<div style='margin:auto;width:595px;font-family:Calibri,Tahoma,Verdana,Courier,Times'>* NOTE: final payment has been adjusted to result in zero balance.</div>" });

    }

  }

 

 void gv_RowDataBound(object sender, GridViewRowEventArgs e)

  {

    if (e.Row.RowType == DataControlRowType.DataRow)

    {

      double val = 0;

      int cel = -1;

      foreach (DataControlFieldCell col in e.Row.Cells)

      {

        cel++;

        if (cel == 0) continue; // Skip Month cell

        double.TryParse(col.Text, out val);

        col.Text = val.ToString("c");

      }

    }

  }

 

 

  void gv_RowCreated(object sender, GridViewRowEventArgs e)

  {

    if (e.Row.RowType == DataControlRowType.Header)

    {

      foreach (DataControlFieldHeaderCell col in e.Row.Cells)

      {

        col.Text = Regex.Replace(col.Text, @"[A-Z]", new MatchEvaluator(PrefixSpace));

        col.Width = 85;

 

      }

    }

  }

 

 

  public static string PrefixSpace(Match m)

  {

    string x = m.ToString();

    return " " + x;

  }

 

 

  public class AmortPayment

  {

    public int Month { get; set; }

    public double Payment { get; set; }

    public double Balance { get; set; }

    public double MonthlyInterest { get; set; }

    public double CummulativeInterest { get; set; }

    public double MonthlyPrincipal { get; set; }

    public double CummulativePrincipal { get; set; }

  }

 

 

  public static IQueryable<AmortPayment> CalcIt(double InterestRate, double LoanAmount, int AmortizationTerm)

  {

 

    // Iteration Variables

    double CurrentBalance = LoanAmount;

    double MonthlyInterest = 0;

    double CummulativeInterest = 0;

    double MonthlyPrincipal = 0;

    double CummulativePrincipal = 0;

 

 

    // Calculate the monthly payment and round it to 2 decimal places

    double MonthlyPayment = (LoanAmount * InterestRate) / (1 - 1 / Math.Pow((1 + InterestRate), (AmortizationTerm)));

    MonthlyPayment = Math.Round(MonthlyPayment, 2);

 

 

    // Storage Variable

    AmortPayment[] ap = new AmortPayment[AmortizationTerm];

 

 

    // Loop for amortization term (number of monthly payments)

    for (int j = 0; j < AmortizationTerm; j++)

    {

 

      // Calculate monthly cycle

      MonthlyInterest = CurrentBalance * InterestRate;

      MonthlyPrincipal = MonthlyPayment - MonthlyInterest;

      CurrentBalance = CurrentBalance - MonthlyPrincipal;

 

 

      if (j == AmortizationTerm - 1 && CurrentBalance != MonthlyPayment)

      {

        // Adjust the last payment to make sure the final balance is 0

        MonthlyPayment += CurrentBalance;

        CurrentBalance = 0;

      }

 

 

      // Add to cummulative totals

      CummulativeInterest += MonthlyInterest;

      CummulativePrincipal += MonthlyPrincipal;

 

 

      ap[j] = new AmortPayment

      {

        Month = j+1,

        Payment = Math.Round(MonthlyPayment, 2),

        Balance = Math.Round(CurrentBalance, 2),

        MonthlyInterest = Math.Round(MonthlyInterest, 2),

        CummulativeInterest = Math.Round(CummulativeInterest, 2),

        MonthlyPrincipal = Math.Round(MonthlyPrincipal, 2),

        CummulativePrincipal = Math.Round(CummulativePrincipal, 2)

      };

 

 

      //Display

      Debug.WriteLine("Term: " + AmortizationTerm + " month " + j + " payment " + MonthlyPayment

                               + " Interest " + MonthlyInterest + " Principal " + MonthlyPrincipal

                               + " Balance " + CurrentBalance);

    }

 

 

    // Return storage variable as querable object

    return ap.AsQueryable();

 

 

  }

 

 

}

 

Sample screen shot:

Tags:

[None]

JavaScript Issue with Decimal Values

by 20. April 2010 16:43

Doing simple math, such as subtracting two decimal values, can result in strange decimal values (make sure to use the Math.round function to set the desired decimal places on the final outcome) as outlined in the below sample:

<!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>

</head>

<body>

 

 

  <script language="javascript" type="text/javascript">

    function Subtract() {

      alert(parseFloat(document.getElementById("txt1").value));

      alert(parseFloat(document.getElementById("txt2").value));

      var thirdVal = 0;

      thirdVal = parseFloat(document.getElementById("txt1").value) - parseFloat(document.getElementById("txt2").value);

      alert(thirdVal);

      document.getElementById("txt3").value = Math.round(thirdVal * 100) / 100;

    }

  </script>

 

 

  <form id="form1" runat="server">

  <div>

    first Param:

    <input type="text" id="txt1" value="47389.10" />

    Second Param:

    <input type="text" id="txt2" value="14216.73" />

    Result:

    <input type="text" id="txt3" value="" />

    <input type="button" onclick="Subtract()" id="subtract" value="calculate" />

  </div>

  </form>

</body>

 

</html>

 

 

 

Tags:

[None]

C# Sample Iterating Enum

by 19. April 2010 16:11

using System;

 

namespace TempConsole

{

  class Program

  {

    enum DayOfWeek { Sunday = 1, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday };

 

    static void Main(string[] args)

    {

      Console.Title = "Sample Iterating Enum";

      foreach (DayOfWeek t in Enum.GetValues(typeof(DayOfWeek)))

      {

        Console.WriteLine(t.ToString() + " = " + (int)t);

      }

      Console.ReadKey();

    }

  }

}

Tags:

Excel

Browsing to .SVC File (WCF Web Service) Results in Server Error 500

by 16. April 2010 14:17

Run the following command to re-register the Service Model components of the WCF framework:

"%windir%\Microsoft.NET\Framework\v3.0\Windows Communication Foundation\ServiceModelReg.exe" -r

Tags:

[None]