Monthly Archives: September 2012

Row_Number SQL Server

Row_Number or Sequence Generator in SQL Server

Is is used to generate a sequence number or a row number in a result table. That is if you want to run a query and you rank certain records in certain order, this where you can use Row Number function. It is a function and is follow be ()


select ID, row_number() over(order by id) as RowNumber from mytable

will create the sequence number in ORDER based on id. This will be the simplest squence. When ever ID changes, row number is incremented. Since ID is our primary key, it changes for every record, the RowNumber will be same as ID (if not record was deleted).

Lets take it one more level. Lets say your table has data arranged by states, that is Connecticut (CT), Massachusetts (MA). You want to rank your records differently in each state. You can do that using PARTITION BY clause

select ID, row_number() over(PARTITION BY STATE order by id) as RowNumber from mytable

This will start giving sequence numbers to each record based on each state. So if you have 10 records in MA and 5 in CT. The row number will be 1-10 for MA and then 1-5 for CT.

Solution – Preserve Leading Zeros in Excel

If you happen to copy data from somewhere to excel (such as a data in SQL format), you would have noticed that excel does not store leading zero. There are some solutions that lets you have exact number of leading zeros but what if you have leading zero with some numbers and no zeros with the other. The exact number of zeros solution will force you to use zeros with numbers that did not have these zeros before. Here is the solution.

Preserve Leading Zeros in Excel

You are copying data from an SQL table, some of the numbers has leading zeros, other do not. Copy the text in clipboard, open the Excel file, format the field which is supposed to contain this number first, then copy the data. The leading zeros will be maintained!

How to run javascript in asp net page_load

I tried

but it did not work. Found out, you have to use RegisterStartupScript in order to run JavaSacript in ASP.NET page. More help is on MSDN

Fix ASP.NET Menu Layout Issue in Chrome and Safari

I had this menu issue which would not render properly in Chrome and Safari but run ok in Internet Explore and FireFox. The following is the fix. Put the following code at the top of your Page_load() function of the page where the issue exists.

/* Fix the Chrome and Safari Menu layout issue (ASP) */
if (Request.UserAgent.IndexOf("AppleWebKit") > 0)

I copied this originally from somewhere. Can’t seem to find it.

How much does a doctor visit cost in the US?

For specialist without insurance it cost me $300 to visit dermatologist (specialist). This is extremely high just for 5 minutes of doctor. Waiting excluding, the doctor is not likely to spend more than 10 min with you. Even if you consider the visit 30min from doctors perspective, the doctor rate comes out to be $600/hr. that is $4,800 a day. To me this is really ridiculous. Of course the doctor does not earn that much. He only gets a fraction of the money. Most of the money goes to hospital, staff and maintenance the doctor.

When I was coming from the same visit to home, my car exhaust broke and fell off. I went straight to my mechanic and asked him for quote. He said $300 (parts included) which would probably take 1 hours (if I remember it correctly), I did not hesitate at all, thinking that I just paid $300 for a 5 min appointment.

Do you think this is out outrageous? Please comment below.

Reading null values from datatabase in C# code

If you tried reading a null value from the database such as a null date, you might have across this exception

Specified cast is not valid

Casting Nullable Values

The following code works unless a null datetime is returned. Note that DateTime is followed by ? which means this date can accept a null value therefore nullabe.

/* assume nullable datetime in the database */
DateTime? exportDate = null;
exportDate = (DateTime)cmd.ExecuteScalar();

This is a little bit strange that you cannot use DateTime? operator to cast the return value as a nullable datetime. Although you can use it and it will not give you error, it does not give you the required result. That is it does not cast null values. To cast null values you must use As Operator.

Use As Operator with Nullable values

To case a nullable value, you must use As operator in C#. Also you must return the datetime as nullable field. The following is the correct version of the above code, which failed to return null values.

/* assum nullable datetime in the database */
DateTime? exportDate = null;
exportDate = cmd.ExecuteScalar() as DateTime?;

Bracketing on Nikon D5100

Nikon D5100 come with bracketing but it took me a while to use it. I will explain here how it be use. I am not impressed by it btw for the couple of pictures that I took with it just now.

First let me clear some misconception that I had. Bracketing is not something that can be used as HDR esp on Nikon D5100. I was expecting something like that. When I turn on Bracketing, I take a shot. I automatically get 3 exposures and then I can superimpose them on each other to make an HDR image etc or to enhance it. It does not work on Nikon D5100 that way.

How to use Bracketing on Nikon D5100

This is kind of stupid. It is not like you click once and you get three images. The way it works, you have to take 3 separate pictures for the three exposure. An indicator is available on screen when you click info button. The indictor for three types of bracketing available is a little different. The most effective that I have seen is AE bracketing. I would recommend to experiment with that first.

How to enable Bracketing

Menu (button) -> Custum Setting Menu -> e (bracketing) menu -> e2 , select AE bracketing and click OK.

Now go to button for information on screen

(button) -> (button again) -> Scroll down to BKT -> Ok button -> select bracketing level

Your camera is now in bracketing mode. The first shot it will take will be normal exposure. The second under exposed (how much? depends on your bracketing level setting) and the third shot will be over exposed. The camera will be in this mode unless you change it.

As I have said above, this is not very exciting. It is essentially three different images which I probably can do any how by changing the setting manually (if I had time). This does not do justice to true bracketing. So as someone said, Nikon D5100 does not support true bracketing.

By the way this information in in Nikon D5100 manual also. This information is on page 89.

Here is another youtube video that explains it as very well as well.

Condition on null does not work with =

What do you expect from the following code? Will the block inside if be executed?

IF ( @ID != NULL )
      UPDATE inventory
      SET    count = 5
      WHERE  id = @ID

The statments inside If will never be executed because it will always evaluate false. The reason it equal (=) operator is not compatible with NULL. To compare NULL value, use IS operator. To fix the above we can write

      UPDATE inventory
      SET    count = 5
      WHERE  id = @ID

Or we can use ISNULL operator to convert NULL into a value

IF ( Isnull(@ID, 0) = 0 )
      UPDATE inventory
      SET    count = 5
      WHERE  id = @ID

Object reference not set to an instance of an object.

How to troubleshoot Object reference not set to an instance of an object in C#.

Put a break point in the code, execute the code
Inspect the value where you are getting this error

Inspect the value you are assigning to a variable/object or inspect the object itself that is being assigned the value

open the watch1 widow (Dubug>Windows>Watch>Watch1), type in the variable exactly as it appears in code with the dot.  Are you getting a value for the field?
Are you getting nullreference exception instead? If so there is where the exception is coming from.

It is possible that are you fetching the value from the wrong place or you are assigning a value to something that does not exists.