Copy-Paste/Renaming the Sheet Name in Microsoft Excel

I thought everything was super easy with Microsoft Products. At least with MS Office. But just realized that MS has long way to go with respect to good UX. All I was trying to do is - to rename a sheet in Excel by pasting a string from clip board. Did you ever try it?

After spending about half-an-hour experimenting and searching through the web, I finally figured out how to do it. Some keys to remember:

[CTRL] + [INS] -> Copy

[SHFT] + [INS] -> Paste

[SHFT] + [DEL] -> Delete

This did the trick for me, but if you try to copy a cell within excel and paste it as a sheet name, that wouldn't work. Because, you will actually be copying the cell and not the content inside it. You can go to the edit box of the cell and copy content and then try pasting it with + .

In my case, I still couldn't do it. The string never gets copied... after a bit of research, figured out that the sheet name has a lenght limitation (31 chars) and I was trying to copy a larger string. Otherwise, the shortcut keys listed above do the job!

I feel like a scientist today.. :)

Accessing Active Directory without C#.Net

Earlier I posted an article which talks about basic C# code to access AD. Recently, I came across a method with which we could access some basic data from AD in windows without any programming.

Create a shortcut on your desktop with the following target:

%windir%\system32\rundll32.exe dsquery.dll,OpenQueryWindow

When you open the shortcut, you would see a window similar to the one:

As you can guess, it is an easy search tool to traverse through the AD for different data. You might say 'What is the big deal in this?'. We already get this info with our windows network or with outlook addressbook. But this can give us access to larger set of data if you observe carefully.

For instance, the description field in the Users would give the employee designation(role) in our organization. What is interesting is that information is confidential and no one is supposed to share it with others. But some one didn't take care while mapping that info to AD. Now I know who is exactly at my level and who is one level ahead.. :)

Have you find any other interesting data with this? Let me know by posting comments..

Accessing Active Directory through C#.Net

If you are reading from an office computer, you might have already been logged in to a domain using your domain user id and password. Most of the organizations use Active Directory (AD) to organize their internal computing assets and also control access to it. Have you ever used an intranet website that greets with your full name (without logging in)? System.DirectoryServices name space in .Net gives access to AD services and allows us to do lot of things.

To see the current logged in account details,

string DomainUser = System.Security.Principal.WindowsIdentity.GetCurrent().Name.Replace("\\", "/");

To get the full name of the currently logged in user,

DirectoryEntry ADEntry = new DirectoryEntry("WinNT://" + DomainUser);

string FullName = ADEntry.Properties["FullName"].Value.ToString();

And if you want to loop through all the available resources in AD,

foreach (string propName in ADEntry.Properties.PropertyNames)

{

foreach (object value in ADEntry.Properties[propName])

{

Response.Write( propName + " : "+ value.ToString() + " : " + value.GetType() + "\n");

}

}

This introduction will only help have a glance at AD. You could do a lot more if you explore the AD and System.DirectoryService name space.

Ever foxed by Nulls in DB (SQL Server and Oracle)?

Recently, I landed into an issue that gave an opportunity to explore more about null values.

Consider two tables X(Int Column A) and Y(Int Column B) with following values:

A (Table X):

-----------

1

NULL

2

3

4

B (Table Y):

-----------

1

2

4

NULL

1. Run the query 'SELECT B FROM Y WHERE B IS NULL'. You will see one NULL record.
2. Run the query 'SELECT * FROM X WHERE A NOT IN(SELECT B FROM Y WHERE B IS NULL ) '. You will see no results. I was expecting it to return 1,2,3, and 4 values.
3. Run the query 'SELECT * FROM X WHERE A IN(SELECT B FROM Y WHERE B IS NULL ) '. You will see no results.
4. Run the query 'SELECT * FROM X WHERE A NOT IN(SELECT B FROM Y WHERE B IS NOT NULL ) '. Again, you will see no results.

Queries listed at 3 and 4 look quite opposite but both return no result. If you are wondering which database I am talking about, I am using SQL Server 2005 with ANSI_NULLS ON. I see the same behavior with Oracle 11i as well.

Reason for this behavior is simple, DBs treat NULLS as 'Unknown' value. If you are trying to compare something with an 'Unknown' value, that is the reason we get an 'Unknown' result.. :) In SQL Server, if you turn off ANSI_NULLS (using SET ANSI_NULLS OFF statement), you would get different results for the above queries.

This leads us to a question. How should we write safe queries to not get surprises while in production?. This article explains how we can use NOT EXISTS instead of NOT IN. You can read more about NULLs in Oracle in this article. And if you are looking for the SQL SERVER help, check out this article.

How to covert comma separated values into a table in Oracle?

One of my friends was working on production support for a system and was dealing with a tidious process of manually verifying thousands of rows to filter out certain records. The process was error prone and demanded lot of concentration all the time. She was looking for a script to make it better.

The requirement: She would be given a set of data (comma separated values) and she need to verify if that data is present in database or not. If she is given 1000 numbers but only 600 exist in database, she has to figure out the missing 400 and send them back for some action.

She wanted to use a join (left outer) on the table in DB and the given list to get the missing records, but the list she got is a comma delimted list and she was looking for a way to convert it into table. I gave the following script (after googling and some research) which did the trick for her..

Step1: Create type

create or replace type split_tbl as table of varchar2(32767);
/
show errors;

Step2: Create function

create or replace function splitfunc

(
p_list varchar2,
p_del varchar2 := ','
) return split_tbl pipelined
is
l_idx pls_integer;
l_list varchar2(32767) := p_list;

l_value varchar2(32767);
begin
loop
l_idx := instr(l_list,p_del);
if l_idx > 0 then
pipe row(substr(l_list,1,l_idx-1));
l_list := substr(l_list,l_idx+length(p_del));
else
pipe row(l_list);
exit;
end if;
end loop;
return;

end splitfunc;
/
show errors;

Step2: The Query

select a.column_value from table(SPLITFUNC('143,3455,356')) a
left outer join TBL_MyTable b on a.column_value = b.TBL_pk
where b.TBL_pk is null;

As you can see, this script creates a type and then a user defined function which uses the type. In the query, we use the function to make the list as table and then use the returned table in join to filter the data.

If you are wondering what that type does, check this out. Also you can know more about the Pipelined table functions here.

Let me know if this helped you and also if you have a better method to do this..

How to get table space name in Oracle and file group name in SQL Server?

I was working on an upgrade script which is supposed to create a new table in the database. One catch was it has to be created on an existing table space in the oracle DB and similarly in a file group for SQL Server.

In Oracle, I have an existing table (say TBL_ExistingTable) in the target table space and need to create my new table in the same table space. I used the following script to get the desired table space name and create new table (say TBL_NewTable) in it:

declare

i_count number:=0;

v_sqlstmt VARCHAR2(2000);

v_tablespace VARCHAR2(81);

begin

select tablespace_name into v_tablespace from user_segments where upper(segment_name) = 'TBL_ExistingTable';

select count(*) into i_count

from user_tables where table_name = upper('TBL_NewTable');

if i_count <>

v_sqlstmt := 'create table TBL_NewTable ('||

'Tbl_Pk number( 10, 0 ) not null ,' ||

'.... full table definition.....................' ||

')ROWDEPENDENCIES tablespace '|| v_tablespace ;

execute immediate v_sqlstmt;

end if;

end;

/

EXIT

Similarly for SQL Server, I used the following script:

declare @vc_filegroup varchar(250)

declare v_sqlstmt VARCHAR2(2000)

SELECT @vc_filegroup = coalesce(GroupName,'')

FROM SysFileGroups

WHERE GroupID IN

(

SELECT GroupID FROM SysIndexes

WHERE UPPER(SysIndexes.ID) IN (OBJECT_ID('TBL_ExistingTable'))

)

SET @v_sqlstmt = ' create table TBL_NewTable

(

Tbl_Pk int identity(1, 1)not null ,

................ full definition.....................................................

)

ON ' + @vc_filegroup;

IF (object_id('TBL_NewTable') IS NULL AND @vc_filegroup != '')

EXEC (@v_sqlstmt);

go

Do you have a better way of doing this? Or found this helpful? Please share your feedback by posting comments..

Ever got spammed? Ever used CAPTCHA?

We all know what spam is and might have already discussed lot about it. But, did you ever face spam which directly impacted your revenue and user experience? I have experienced that. I used to work for an online portal in which users can post their comments, new topics, etc. One fine day series of spam attacks occurred and the site became literally unusable and had to be taken offline. We had to cleanup all that mess and to prevent such attacks, introduced a captcha in all the places where user submissions are taken.

The topic that I want to discuss here is the effect of captcha on the site. Is it all positive or does it have some negative effect as well? This article explains in detail that users don't always like captcha and it drives away considerable number of genuine users.

Fortunately, it also points to a simple solution - Honeypot CAPTCHA technique. Phil Haack suggests use of a blank hidden field (using CSS) which will be ignored by real users but would be filled by the bots. I liked this idea and would surely use it in future.

Did you have any experience with Spam Attacks and Captchas? Share your suggestions/experiences by posting comments..

Ever Copied Video/Movie Fie from a Bad/Scratched CD/DVD?

I had to copy an important and large video from an old CD and ran in to errors wasting lot of time yesterday. I tried doing the copy through windows which took lot of time and ended abruptly with a 'Cyclic Redundancy Check Error'. Being a smart guy, I tried it the old DOS way. But still no luck! It also ended in same CRC error after taking lot of time.

Obviously, there is something wrong with my old cd data and part of it might be bad. But why should I loose all my data just because of few bad sectors? I kept wondering why the copy functionality in windows is not smart enough to ignore those damaged bytes and copy the rest of the file. Specially with video files, it won't hurt even if we miss few bytes here and there. I knew there will be others who think on the similar lines and googled for a solution. I got lots of links and info. I carefully selected an application and it did the trick for me magically.

The tool is 'Copy It Anyway'. And I got it from here. After installing it, all I had to do is give it the source and destination details and it did the rest. Copying was faster except for the sound alerts when it hit a bad byte. After copying the entire file, I played the video and to my surprise, I don't notice any issues with it except for few damaged frames. This tool was a life saver for me.

Do you have any experience like this? Do share your views by posting comments.

ASP.Net - Caching static files (images, css, js, etc. ) to improve performance

Recently we had a need for performance improvement in our large web application. With one simple solution, we could achieve great performance improvement. We just implemented static file caching. We cached all static files like images in our web application which resulted in less calls to the server with each request and improving performance in many folds.

To understand the impact of static files with your application, you might want to use a utility like fiddler or microsoft network monitor. Hit your page and see what files are actually request and transmitted from server each time. In our case, we had these logos, menu images, css files, common js files etc. received each time a page request is made. After logging these calls for a simple use case, we could see how this is hampering the performance.

There is a nice article explaining different ways to achieve static file caching in ASP.Net. We used http module to listen to the requests and append/edit some headers to do the caching part. Without touching thousands of pages in the app and without even touching the IIS (which would be difficult to do for 1000+ existing customers hosting it in their network) we could improve the performance considerably. If you are looking for the actual code that made it work, you might want to dig into the HttpContext.Response.Cache class. You might find the following methods useful:

SetValidUntilExpires(true);

SetCacheability(HttpCacheability.ServerAndPrivate);

SetMaxAge(TimeSpan.FromMinutes(expirationTime));

SetExpires(DateTime.UtcNow.AddMinutes(expirationTime));

Did you find this tip useful? Or do you know of any other tips to improve performance of ASP.Net applications? Do share your views by posting comments.

C# coalesce, double question-mark (??) operator

I have been coding in C# for years now but never knew this - the coalesce like operator in C#.

If I had to check a value with null before using it, I use the well known ternary operator (? :). But C# made our lives easier by introducing ?? operator for this task. For example, if you have a variable isParent and need to check for null, you can do:

isParent != null ? isParent : default_value;

Now, with the new ?? operator, we can write the same as:

isParent ?? default_value;

With the above expression, isParent will be used only if it is not null otherwise, default_value will be used. Its clean, isn't it? This might be a simple operator but it sure saves us from introducing few errors specially when we have some checks with nested ternary operators.

PS: This was introduced with nullable types in C#, and is available from .Net 2.0.

Java Script and Array.Sort()

I was working on porting some enhancement to new version and found some interesting javascript code similar to this:

array.sort(sortorder);

First I didn't get what that parameter ( sortorder) is, then a careful look revealed that it is a function that is written to change the default sort behavior and add some custom sort logic.

function sortorder(a, b){
//Compare a, b with some logic and return a value (-1, 0, 1)
}

In order to understand this correctly, I researched a bit and found some interesting points with the sort() function of javascript. By default this function sorts the array alphabetically (if we don't provide any parameter). It would work for you if you want to sort a string/character array in ascending order. If you want to change the order of sorting, you might need to use reverse function like:

array.reverse();

In case of sorting array with numbers, the default sort would give some unexpected surprises. It would again sort alphabetically so number like 5 would come after a number like 23. To get away with this and do the correct sort, you can define your own sort function and pass the function name as parameter to array.sort(). For example, following function would sort the numbers in ascending order:

function sortorder(a, b){
return a-b;
}

If you want to change the order to descending:

function sortorder(a, b){
return b-a;
}

Its as simple as that. You can even return -ve or 0 or +ve value randomly to randomize (or shuffle) the array. But beware of the performance impact of this.

Chat with your yahoo buddy from MSN messenger!

I use MSN IM at work. The other day a colleague approached me and asked me to add him in my buddy list. And he gave me his Yahoo mail id. I told him I use only MSN. But he gently smiled and educated me that MSN/Live messenger can add Yahoo contacts and vice versa. I couldn't believe that but when tried, it worked magically!




Then I visited yahoo messenger page and confirmed that it officially works:



Then I was wondering will someday google join this and extend its gtalk to work with MSN and Yahoo? I am not sure about this but heard that it has plans to support integration with AIM in near future. A little bit of googling tells me that we can tweak gtalk to work with MSN and Yahoo IMs easily. If you are interested in this, check out this article!

How to delete a windows service?

I had to work with a windows service few days ago. An application creates a windows service which does some work in the background. During the development, we ended up creating multiple services with different names. For those of you who don't know where to look for these, just open up the command prompt from your windows box and type services.msc

In my case, I was managing my task by stopping/disabling the old services and starting/enabling the one that is in use. But the list grew and the management task is getting ugly. Then I came to know about a simple command to get rid of these useless entries/services.


From command prompt, invoke the service control manager with SC command and with corresponding parameters. In my case, I had to give 'SC delete [service name]'


To see various parameters available hit SC without any parameter. Hope this helps you when dealing with services.

Regular Expression and URL Manipulation Attacks!

The other day I was involved in fixing a bug on a page. Actually, that bug was a regression after fixing a url manipulation attack. The jscript.net line of code that caused the issue is:

str = str1.replace(/[^\w]/ig, "") ;

All this does is to check the string (part of the url) for any non-alphabetical characters and replace any such characters with empty string. To explain the regular expression:

^ denotes the negative of what follows it is equal to saying 'Not ...'

\w denotes any of the alphabets and digits

i this switch denotes the ignore case option

g this switch denotes the global replace option

Now, the expression would be self explanatory. It is searching for all characters except alphanumeric ones and replacing any such with "" irrespective of case and in the whole string.

The issue we faced is, we had certain field in query string which takes a negative value. Even the minus (-) sign infront of the value is getting replaced with empty string and the page is going for a toss. I have fixed the issue by correcting the regular expression as:

str = str1.replace(/[^\w\-]/ig, "") ;

As you can see, I have included the minus sign also in the 'Not ..' list. Not sure, if there are any other hidden issues with this.. Do you have abetter solution to tackle this issue? Do share your comments..

Oracle - Enabling Debugging for Store Procedures

The other day I was trying to debug a stored procedure in Oracle (using PL SQL Developer tool) and was greeted with the following error when I tried debugging:




[Error: ORA-0131: Insufficient privileges]


As the error says, the user id that I was using doesn't have debug permissions. To resolve this:

• Goto command prompt (Start -> run -> cmd)
• Hit command 'sqlplus'
• Then connect to db as system admin using conn system/@;
• You can then use grant debug connect session to ; to grant user the debug permission.
• From PL SQL Developer tool, right click on the package and select 'Add debug information'.
• Start debugging (hit F9).

Be Happy!

I read about ZenHabits in another blog and checked it out. The first post itself caught my attention and really liked it.

Zen Habits is written by Leo Babauta, a father of six and an excellent writer. It’s not mere chance that Zen Habits is among the 100 most popular blogs on the internet. It’s well written, it’s inspiring and contains advice, tips and wisdom anyone can benefit from…unless you’re already perfect.