Wednesday, February 23, 2011

Client-side timezone detection and using it server-side in ASP.NET

First of all, this is pretty complicated and took me alot of time to figure out, even after reading alot of stuff on it. So here's the steps to get this to work

1. JavaScript for getting the timezone info
I recommend sticking these javascript functions in a .js file, i put them in ../js/TimeZone.js
function TimezoneDetect() {//see references for credits for this function
    var dtDate = new Date('1/1/' + (new Date()).getUTCFullYear());
    var intOffset = 10000; //set initial offset high so it is adjusted on the first attempt
    var intMonth;
    var intHoursUtc;
    var intHours;
    var intDaysMultiplyBy;

    //go through each month to find the lowest offset to account for DST
    for (intMonth = 0; intMonth < 12; intMonth++) {
        //go to the next month
        dtDate.setUTCMonth(dtDate.getUTCMonth() + 1);

        //To ignore daylight saving time look for the lowest offset.
        //Since, during DST, the clock moves forward, it'll be a bigger number.
        if (intOffset > (dtDate.getTimezoneOffset() * (-1))) {
            intOffset = (dtDate.getTimezoneOffset() * (-1));

    return parseFloat(intOffset / 60)
function setHiddenVal(hiddenId) {
    var hiddenObj = document.getElementById(hiddenId);
    hiddenObj.value = TimezoneDetect().toString();
 2. Call JavaScript from ASP.NET page
I figured the best way to do this is to stick the javascript call on my login page. So on the login page you need to add a HiddenField, Button, and a script tag.

Hidden Field:   <input runat='server' id="hiddenField" type="hidden" />

Button:   <asp:Button ID="btnLogin" runat="server"
        Text="Login" CssClass="button" OnClientClick="setHiddenVal('[hiddenField's ID]')" onclick="btnLogin_Click" />

Notice, the button has a server-side click event called btnLogin_Click, and a client-side on click event. In the on click event, you call setHiddenVal with the HiddenField's id as the parameter. You will need to find the assigned ID by loading the page and looking at the page source. If you are using master pages w/ content place holder's it will not be what you expect.

Script tag:  <script type="text/javascript" src="../js/TimeZone.js"></script>
As mentioned before ../js/TimeZone.js is where I put the javascript functions

3. Store the HiddenField value in the Session["UtcOffset"]
In the login button's server-side onclick event, btnLogin_Click, you can retrieve the HiddenField's value, and store it in the Session variable like this:
protected void btnLogin_Click(object sender, EventArgs e)
//auth code...

            Session["UtcOffset"] = hiddenField.Value;
//redirect to user's profile...
 4. Use Session["UtcOffset"] to calculate future UTC times and convert back to the user's timezone

First, how to convert a UTC DateTime to a DateTime in the user's timezone. Let's assume we have a function called getStartTimeUtcFromDatabase() which returns a UTC DateTime from the database. *Note: There's no error handling for clarity purposes

 //pull the UtcOffset which we saved in the Session in section 3 above
double utcOffset =  Convert.ToDouble(Session["UtcOffset"]); 

DateTime StartTime = getStartTimeUtcFromDatabase();
StartTime = StartTime.AddHours(utcOffset);

//For example, StartTime = 2/24/11 12:30 AM, and UtcOffset= -8 (PST)
//The end result would give us StartTime = 2/23/11 4:30 PM

Second, how to convert user input datetime to the equivalent UTC time. Let's assume we have already collected the date and time and concated them together into a DateTime object called userStartTime.
double utcOffset  = Convert.ToDouble(Session["UtcOffset"]) * -1;
userStartTime = userStartTime.AddHours(utcOffset);

//For example, user start time = 2/23/11 4:30 PM, and UtcOffset= -8 (PST)
//So because we are multiplying by -1, we are actually adding 8 to the datetime,
//this results in userStartTime = 2/24/11 12:30 AM

I hope this helps anyone who is trying to figure this out. It took me quite a bit of time to pull this all together.... . Here's the sources:

Tuesday, February 22, 2011

Dropdown of time zones and client-side timezone detection

UPDATE: Here's the code i mentioned I would post:

Using the bottom 2 references + a few others, I plan on pulling a user's time zone when they login, stick it in the Session[] and then use it to convert db stored UTC times to client times. Converting to UTC requires multiplying the offset by -1 and adding it. Converting to local time from the UTC requires just adding the offset. I will post code once I have time to work on that solution.

ASP.NET dropdownlist populated: Download the file from this blog post:

Alot of others can be found on this blog post and in the comments, including HTML, PHP, Java, and C#:

The above website also provided javascript for detecting the timezone and setting the value of the HTML drop down. Woot!

Dividing count(*) in SQL Server results in 0

Example: SELECT (COUNT(*) / (SELECT COUNT(*) FROM TABLE)) * 100 as [Percentage] FROM TABLE WHERE Val=10
This always returns 0. Why? It's because when SQL Server divides 2 integers, it returns an integer.

Convert one of the dividing numbers to float. For example, using the above query:
SELECT (CONVERT(float, COUNT(*)) / (SELECT COUNT(*) FROM TABLE)) * 100 as [Percentage] FROM TABLE WHERE Val=10


Composite keys in dimensions

I have a table with a composite key, but when I create a dimension from this table only 1 column is used in the key. How do I make a composite key in the dimension table?

Add multiple columns to the KeyColumns and then add a calculated "Name Column"

1. Create a named calculation on the table in the Data Source View, concating all composite keys. For example: OrderId + CONVERT(nchar, OrderType)

2. In the dimension, click on one of the key attributes. In Properties scroll down to KeyColumns and click "...", then add all columns to this

3. While still looking at Properties add the calculated field (from 1. above) to the Named Column. You may need to do this for each key field

4. If you run into any problems with this, please post a comment detailing it, so I have it for future reference to help others.


Friday, February 18, 2011

Add javascript to ASP.NET server control

In code behind Page_Load
this.txtAddress.Attributes.Add("onchange", "txtAddress_onchange()");

In ASP.NET page markup
    <script language="javascript" type="text/javascript">
        function txtAddress_onchange() {
            //do stuff

Note: This assumes I have a textbox with id txtAddress, and i want to add an "onchange" event.


Errors in the OLAP storage engine: The attribute key cannot be found when processing table

Error Message in SSAS:
Errors in the OLAP storage engine: The attribute key cannot be found when processing: Table: 'factTable', Column: 'columnName', Value: 'value'; 

Solution 1: Remove bad records
I am using test data, so I simply ran this query:
WHERE columnName NOT IN (SELECT columnName FROM dimensionTable)
To get rid of error-causing records, and then re-processed the cube.
Solution 2: Find and correct bad records
If you want 100% accuracy in your analysis, then you'll need to fix these records. To do that, find records that would cause these errors:
SELECT columnName FROM factTable
WHERE columnName NOT IN (SELECT columnName FROM dimensionTable)
Next, you'll want to add records in the dimensionTable for these records you found.


Thursday, February 17, 2011

Regex reference

1.Range between 0 and 1: [0]\.[0-9]+|[1]
Matches: 0, 1, 0.50, 0.999999
Non-matches: 1.1

2. Money without dollar sign:  ^\d+.\d{1,2}
Matches: 0.30, 9.45, 100.50
Non-matches: .45, 0.451, 9.512, 9.5

Will add more as the need arises.

Great site for testing regular expressions:

Saturday, February 12, 2011

The gridview fired event RowEditing which wasn't handled

I ran into this problem with the following code:
<asp:TemplateField HeaderText="">
<asp:LinkButton ID="btnCC2" runat="server"
CommandArgument='<%# DataBinder.Eval(Container.DataItem, "Id", "{0}") %>'
CommandName="Edit" OnCommand="btnCC_Click">

ASP.NET apparently thinks any control with "Edit" as the CommandName is supposed to be calling the RowEditing function.

To fix this, change CommandName to something else. I changed mine to CommandName="EditCC"

Wednesday, February 9, 2011

Switching between HTTP and HTTPS automatically in ASP.NET

I'm currently working an ASP.NET website and ran into a problem, which I think is pretty common. The problem is how to deal with secure pages. My first instinct is to add HttpContext.Current.Request.IsSecureConnection in the page load of every secured page, and then redirect back to the same page with "https" instead of "http" in the URL. The problem with this is if you don't have a SSL cert on your dev machine, the page will hang. The solution to this is simple: check if it's the dev environment and don't redirect to https.

This method does work, I am currently using it on a production website and there are no problems with it. I just put the logic in a user control, and put it on all the secured pages. However, as mentioned before, it requires an extra redirect.

And then I came across this elegant (open source!) solution to the project on Code Project:
Switching Between HTTP and HTTPS Automatically: Version 2 - CodeProject

In short, you add a reference to the WebPageSecurity.dll, and then in the web.config you specify which files and directories should be secured. It has an option just like "error page" where it only invokes on "RemoteOnly", so it won't try to redirect in your development environment.

I highly recommend using this solution, because it separates page security logic from the UI logic layer.