Friday, December 17, 2010

isnull is a Saviour

I was trying to query my userName from the database...it was stored as firstname, middlename and lastname and being the dunce I am, I did the simple

Select firstName+' '+MiddleName+' '+lastName as userName from members

Right ?

WRONG !!!

If any of the cols is a null, which usually is for the middle name, the concatanation returns null...

BOOOO

So, now am doing this instead

Select ltrim(isnull(FirstName,'')+' '+isnull(MiddleName, '')+' '+isnull(LastName,'')) as userName from members

and all is well in my world again !!!

Tuesday, December 14, 2010

So I had a gridview which had no data... Now what...

AHhh.. there is empty data template for gridview ... Woo Hoo... here it goes from microsoft site :

The empty data row is displayed in a GridView control when the data source that is bound to the control does not contain any records. You can define your own custom user interface (UI) for the empty data row by using the EmptyDataTemplate property.

<%@ Page language="C#" %>

<!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>GridView EmptyDataTemplate Example</title>
</head>
<body>
    <form id="form1" runat="server">

      <h3>GridView EmptyDataTemplate Example</h3>

      <asp:gridview id="CustomersGridView" 
        datasourceid="CustomersSqlDataSource" 
        autogeneratecolumns="true"
        runat="server">

        <emptydatarowstyle backcolor="LightBlue"
          forecolor="Red"/>

        <emptydatatemplate>

          <asp:image id="NoDataImage"
            imageurl="~/images/Image.jpg"
            alternatetext="No Image" 
            runat="server"/>

            No Data Found.  

        </emptydatatemplate> 

      </asp:gridview>

      <!-- This example uses Microsoft SQL Server and connects  -->
      <!-- to the Northwind sample database. Use an ASP.NET     -->
      <!-- expression to retrieve the connection string value   -->
      <!-- from the Web.config file. The following query        -->
      <!-- returns an empty data source to demonstrate the      -->
      <!-- empty row.                                           -->
      <asp:sqldatasource id="CustomersSqlDataSource"  
        selectcommand="Select [CustomerID], [CompanyName], [Address], [City], [PostalCode], [Country] From [Customers] Where CustomerID='NoID'"
        connectionstring="<%$ ConnectionStrings:NorthWindConnectionString%>"
        runat="server">
      </asp:sqldatasource>

    </form>
  </body>
</html>

Monday, November 29, 2010

Formating dates in SQL Server 2008

Problem
There are many instances when dates and times don't show up at your doorstep in the format you'd like it to be, nor does the output of a query fit the needs of the people viewing it. One option is to format the data in the application itself. Another option is to use the built-in functions SQL Server provides to format the date string for you.

Solution
SQL Server provides a number of options you can use to format a date/time string. One of the first considerations is the actual date/time needed. The most common is the current date/time using getdate(). This provides the current date and time according to the server providing the date and time. If a universal date/time is needed, then getutcdate() should be used. To change the format of the date, you convert the requested date to a string and specify the format number corresponding to the format needed. Below is a list of formats and an example of the output:

DATE FORMATS
Format #	Query (current date: 12/30/2006)	Sample
1	select convert(varchar, getdate(), 1)	12/30/06
2	select convert(varchar, getdate(), 2)	06.12.30
3	select convert(varchar, getdate(), 3)	30/12/06
4	select convert(varchar, getdate(), 4)	30.12.06
5	select convert(varchar, getdate(), 5)	30-12-06
6	select convert(varchar, getdate(), 6)	30 Dec 06
7	select convert(varchar, getdate(), 7)	Dec 30, 06
10	select convert(varchar, getdate(), 10)	12-30-06
11	select convert(varchar, getdate(), 11)	06/12/30
101	select convert(varchar, getdate(), 101)	12/30/2006
102	select convert(varchar, getdate(), 102)	2006.12.30
103	select convert(varchar, getdate(), 103)	30/12/2006
104	select convert(varchar, getdate(), 104)	30.12.2006
105	select convert(varchar, getdate(), 105)	30-12-2006
106	select convert(varchar, getdate(), 106)	30 Dec 2006
107	select convert(varchar, getdate(), 107)	Dec 30, 2006
110	select convert(varchar, getdate(), 110)	12-30-2006
111	select convert(varchar, getdate(), 111)	2006/12/30

TIME FORMATS
8 or 108	select convert(varchar, getdate(), 8)	00:38:54
9 or 109	select convert(varchar, getdate(), 9)	Dec 30 2006 12:38:54:840AM
14 or 114	select convert(varchar, getdate(), 14)	00:38:54:840

You can also format the date or time without dividing characters, as well as concatenate the date and time string:

Sample statement	Output
select replace(convert(varchar, getdate(),101),'/','')	12302006
select replace(convert(varchar, getdate(),101),'/','') + replace(convert(varchar, getdate(),108),':','')	12302006004426

Next Steps

The formats listed above are not inclusive of all formats provided. Experiment with the different format numbers to see what others are available
These formats can be used for all date/time functions, as well as data being served to clients, so experiment with these data format conversions to see if they can provide data more efficiently

Wednesday, November 24, 2010

Generate Random password SQL Server 2008

Well I was transferring users from one table to another and had to add random passwords to the user table.

Could not figure out a way to save my life and then in desperation came up with this methord

SELECT [PERSON_ID] ,'IAI'+ SUBSTRING(convert(varchar(50),NEWID()),0,6) as password from Persons

Hope the next time I dont spend hours trying to randomize characters and strings :)

Monday, November 22, 2010

javascript confirm C# button

In all good web applications, the user is asked to confirm whether he/she wants to delete something in case the delete button was pressed accidentally.

Although it seems like a pain to do, it is actually really easy if you find it acceptable to use javascript's “confirm” statement, which will popup a dialog box with a particular question with “ok” and “cancel” buttons. You have no control of the title of the popup, but in IE it says “Microsoft Internet Explorer“ and I believe it says “[Javascript Application]“ or similar in Firebird.

The javascript code for it is simple:

function confirm_delete()
{
if (confirm("Are you sure you want to delete the custom search?")==true)
    return true;
else
    return false;
}

and

   < asp:Button ID="btnRemoveSchool" runat="server" CssClass="btn" Text="Remove the School/Organization" OnClientClick="javascript:return confirm_delete();" / >

Add Item to DropDownList After DataBind

We often want the first option in a drop-down list to not default to the first item in the list. Instead we would prefer the option of adding a –Select Item– at the top of theasp:DropDownList. After the DataBind, execute an Item.Insert.

ddlAuthor.DataTextField = "FullName";
ddlAuthor.DataValueField = "AuthorID";
ddlAuthor.DataSource = authorDB.GetAuthors();
ddlAuthor.DataBind();
ddlAuthor.Items.Insert(0, "-- Select Author --");

Page flicker on postback

I was working on a form where one dropdownlist is populated based on values of selected item in another dropdown and it caused the page to flicker each time.

So, Robicool from work gave me this cool piece of code to stop the annoyng page flickers

protected void Page_PreInit(object sender, EventArgs e)
   {

   if (Request.Browser.IsBrowser("IE"))
   {
   string v = Request.Browser.Version;
   if (v.Length > 0)
   v = v.Substring(0, 1);
   if ((v == "7") || (v == "6"))
   {
   SmartNavigation = true;
   MaintainScrollPositionOnPostBack = false;
   }
   else
   {
   SmartNavigation = false;
   MaintainScrollPositionOnPostBack = true;
   }
   }
   else
   {
   SmartNavigation = false;
   MaintainScrollPositionOnPostBack = true;
   }
   }

Wednesday, November 10, 2010

Potato Pattis Reciepe

5 medium potatoes
A bunch of fresh coriander leaves, chopped
1 tsp jeera / cumin
2 green chillies, chopped
1/2 tsp red chilli powder
1 to 2 tbsp all-purpose flour
1/4 cup - Onion, chopped finely (optional....I dont use it)
1/2 tsp - Ginger, grated (optional....I dont use it)
Salt to taste

Boil the potatoes and peel off the skin.
After cooling mash it.. Divide into two equal parts
First part of potatoes get slow stir fried with cumin, chillies, coriander, chilli powder and salt. Cool down the mix and divide it into 10-12 small portions and roll into balls
add flour to second half of potatoes and prepare it like a dough. Pls dnt add any water.
To make final pattis , take flour dough in hand, make a small circle out of it, put the spiced potato ball inside it and close the ball. make sure the spiced potatos are completely covered by floured potatos else it explodes while frying....

Heat oil in pan and deep fry the pattis till light brown colour.

Serve it hot with ketch up.

Thursday, November 4, 2010

Flush Socket in .NET or C# to ensure send of acknowledgement

Today, I faced a problem regarding synchronizing a C# TCP/IP Client with a JAVA TCP/IP server.

The problem was, client sends data and waits for acknowledgment. Server listens for data and when it gets data, it sends acknowledgment to the client and start listening for data again. Now when server starts listening for data again it gets old data i.e. it was not getting blocked on the read() function which it should, as client hasn't send any more data.

the .Net Socket Send page (http://msdn.microsoft.com/en-us/library/ms145160.aspx) has the following:

"There is also no guarantee that the data you send will appear on the network immediately. To increase network efficiency, the underlying system may delay transmission until a significant amount of outgoing data is collected. A successful completion of the Send method means that the underlying system has had room to buffer your data for a network send."

So, the problem was that the data was not being flushed. I searched for Flush method in System.Net.Sockets.Socket class but it was not there. There was a suggestion like:

1. Use the SetSocketOption function and set the value of ReceiveBuffer option to 0. It didn't work. Though the recivebuffer length was 0, the data was still being buffered.
2. Use the IOControl function to set the value for Flush option. But you won't find the integer value for Flush Option (atleast I was not able to)

I tried to use NetworkStream class. You create a new stream using socket and then you create StreamReader and StreamWrite object using NetworkStream object

NetworkStream stream = new NetworkStream(socket);
StreamReader sr = new StreamReader(stream);
StreamWriter sw = new StreamWriter(stream);

Then, use Flush() method of StreamWriter to flush the data. You can also set the property called AutoFlush of StreamWriter to automatically flush the stream after write operation.
But Flush() method is more reliable according to their site...

This is my code in case you are really really curious

Instead of doing

//first send an acknowledgement to client saying we are good...Now you can stop

const string sendData = "< response status="\" > success < / response >";

                      byte[] bytes = Encoding.UTF8.GetBytes(sendData);
                      if (listenerSocket != null)
                      {
                          listenerSocket.Send(bytes, bytes.Length, 0);
                      }

I am doing ....

   NetworkStream ns = new NetworkStream(listenerSocket);
   StreamWriter w = new StreamWriter(ns);

const string sendData = "< response status="\" > success < / response >";

   string message;
   try
   {
   w.Write(sendData);
   w.Flush();
   message = String.Format("\n\nPOST successful. bytes sent " + xml.Length);
   }
   catch (Exception ex)
   {
   message = String.Format("\n\nPOST failed. Received exception {0}", ex);
   }
   finally
   {
   w.Dispose();
   ns.Dispose();
   }

And yet the server does not receive any acknowledgment. I am very clueless at this point and any suggestion would be more than welcomed.

Update:

I think I have figured out a solution

This send code seems to be working in my case

 //--trying the asynchronous thing
IAsyncResult ar = listenerSocket.BeginSend(asen.GetBytes(xml), 0, xml.Length, SocketFlags.None, null, null);
listenerSocket.EndSend(ar);
string message = String.Format("\n\nPOST successful. bytes sent " +ar.ToString()+"\n"+ xml.Length);

Hope it works for you as well

Source Control Explorer Accidental deletion and file recovery

Sometimes, you do stupid things. But VSS is definitely more forgiving than real life and totally and completely saved me hours of rework.

1) Tried to copy files in Visual Studio 2010.
2) It moved the aspx file but not cs file
3) Deleted the original files

So, now I had a aspx file sitting around without a cs file... And I cried...

But then I tried this...

1) Shut down all VSS clients / VS IDEs.
2) Open Visual SourceSafe Explorer and login into VSS database.
3) Undo all the checkouts of any projects or the solution.
4) If the projects were modified by a check-in, roll it back.
5) Go to the parent folders, show properties, and undelete any accidentally deleted files.
6) Do a GET with VSS and make sure the project file as well as the "project's files" are correct in your working folder.
7) Shut down VSS explorer.
8) Try an Open of the project with Visual Studio.

And it worked... YES it totally did....

Wednesday, November 3, 2010

Where is my windows authentication in IIS 7 ?

Ahh... I installed II7 on windows server 2008 and realized that in the paranoia of making the server secure, I didnt install windows authentication...

But then I couldnt find the feature anywhere.. and trust me, I looked..

Finally, I figured it out...

So, putting a note here for future reference

Windows Server 2008 or Windows Server 2008 R2

On the taskbar, click Start, point to Administrative Tools, and then click Server Manager.
In the Server Manager hierarchy pane, expand Roles, and then click Web Server (IIS).
In the Web Server (IIS) pane, scroll to the Role Services section, and then click Add Role Services.
On the Select Role Services page of the Add Role Services Wizard, select Windows Authentication, and then click Next.
On the Confirm Installation Selections page, click Install.
On the Results page, click Close.

Monday, November 1, 2010

Chrome v/s IE and firefox

I was looking at my website usage logs for last month and compared it to the last year's and realized something that I knew already.

Chrome is definitely taking a chip of market share from IE and firefox.

Here are my numbers for the various browsers used to access my site

Even though chrome is still a small part of total traffic, given the current trend, the numbers are bound to pick up....

On a side note, am excited about IE9.

Hari Om Pawaar

I love this poetry...

How can I format a number to x decimal places?

If you have the number 28 and you want to display it as 28.00 here's how you can format it:

int val = 28;
string formatted = val.ToString("N2");

Of course, if you want to format it to 5 decimal places just use ToString("N5") instead.

Tuesday, October 26, 2010

Hari Om Pawaar

रात के अँधेरे में न ऐसा कोई काम करो के मुह को छुपाना पड़े दिन के उजाले में

और दिन के उजाले में ऐसा न कोई काम करो के नीद नहीं आये तुम्हे रात के अँधेरे में

Wednesday, October 20, 2010

How to send an email via GMail using .NET

It’s really easy to use GMail as an SMTP client:

public class GoogleSmtpTest
{
    public void SendEmailViaGmail()
    {
        var message = new MailMessage(
            "xxx@gmail.com",
            "yyy@joebloggs.com", 
            "Hi via Google SMTP",
            "some body");

        var client = new SmtpClient("smtp.gmail.com")
        {
            EnableSsl = true,
            Port = 587,
            Credentials = new NetworkCredential("xxx@gmail.com", "xxx's gmail password")
        };

        client.Send(message);
    }
}

The only thing to note is that the SSL port given in the documentation (465) doesn’t seem to work, but the TLS one (587) works fine.

Monday, October 18, 2010

Hyperlink in DataList Asp.Net 2.0

Simple hyperlink in asp.net 2.0 Datalist
< asp:HyperLink runat="server" ID="Hyperlink2" Text='< %# Bind("Contact_URL") % >' NavigateUrl='< %# Bind("Contact_URL") % >' Target="_blank">

IE 9 beta is out for testing

New News ! Microsoft's Internet Explorer 9 is now out for developers to test. IE9 beta is available in 33 languages. By testing IE9 beta it seems like a lot of effort has gone into making IE 9 a better browser as it is much more faster than IE8.

One of the coolest feature of Internet Explorer 9 beta is that it automatically warns you via a pop-up message window when your add-on features increase the load time and browsing time, and it give you the feature to disable them.

IE9 beta supports HTML5 AND CSS3.

IE9 beta is definitely worth a try. Let's try it !

The Internet Explorer 9 beta version can be downloaded from here: http://ie.microsoft.com/testdrive/

A small note: As of most windows products, the install requires a restart... So, do it in your spare time when a restart wont mess everything else in your world !!!

Reset password for sa using windows authentication

So, happily installing the new database on my dev machine and realized had forgotten the "sa" password. It kept on giving me the infamous "Login failed for user sa, error 18456" error message.

So, I googled and found the following two methords

sp_password NULL,'new_password','sa'
go

Used this and it worked like a charm... Another method I found was

1. Open the "SQL Server Enterprise Manager". This is usually under "Start"-->"Programs"-->"Microsoft SQL Server".

2. Navigate to the "Logins" object under the "Security" folder on the SQL Server you wish to administer. Then, right click on the 'sa' account and select "Properties".

3. Now, enter a new password in the "Password" field under the "Authentication" options.

Wednesday, October 13, 2010

SQL SERVER – Retrieve Current Date Time in SQL Server CURRENT_TIMESTAMP, GETDATE(), {fn NOW()}

There are three ways to retrieve the current datetime in SQL SERVER.

CURRENT_TIMESTAMP, GETDATE(), {fn NOW()}

CURRENT_TIMESTAMP
CURRENT_TIMESTAMP is a nondeterministic function. Views and expressions that reference this column cannot be indexed. CURRENT_TIMESTAMP can be used to print the current date and time every time that the report is produced.

GETDATE()
GETDATE is a nondeterministic function. Views and expressions that reference this column cannot be indexed. GETDATE can be used to print the current date and time every time that the report is produced.

{fn Now()}
The {fn Now()} is an ODBC canonical function which can be used in T-SQL since the OLE DB provider for SQL Server supports them. {fn Now()} can be used to print the current date and time every time that the report is produced.

If you run following script in Query Analyzer. I will give you same results. If you see execution plan there is no performance difference. It is same for all the three select statement.

SELECT CURRENT_TIMESTAMPGOSELECT {fn NOW()}
GOSELECT GETDATE()GO

Performance:
There is absolutely no difference in using any of them. As they are absolutely same.

My Preference:
I like GETDATE(). Why? Why bother when they are same!!!

Update Table values based on another table values

You may wish to update records in one table based on values in another table. Since you can't list more than one table in the UPDATE statement, you can use the EXISTS clause.

For example:

UPDATE suppliers

SET supplier_name = ( SELECT customers.name
FROM customers
WHERE customers.customer_id = suppliers.supplier_id)

WHERE EXISTS
( SELECT customers.name
FROM customers
WHERE customers.customer_id = suppliers.supplier_id);

Whenever a supplier_id matched a customer_id value, the supplier_name would be overwritten to the customer name from the customers table.

Passing of death wand in Harry Potter book 7

So I listened to harry potter audio book and finally figured out the order of passing of elder's wand.. here it is

Tuesday, October 12, 2010

.NET Runtime Optimization Service (clr_optimization_v2.0.50727_32) - Failed to compile: D:\Apps\SQLServer\100\Tools\Binn\VSShell\Common7\Tools\VDT\DataProjects.dll . Error code = 0x8007000b

So, I installed SQL Server 2008 and changed my TCP/IP port

Why ? you'd ask

Ah am just too paranoid..thats why.

Anyways, I do that and try to connect and SQL Server Gods get angry on me

"Msg 18456, Level 14, State 1, Server , Line 1
Login failed for user ''"

".NET Runtime Optimization Service (clr_optimization_v2.0.50727_32) - Failed to compile: D:\Apps\SQLServer\100\Tools\Binn\VSShell\Common7\Tools\VDT\DataProjects.dll . Error code = 0x8007000b"

I restarted my server...and the issue seems to have fixed.

Now I know what to do next time !!!

Get list names of tables in database sql server 2008

I was trying to clean up my database and first step stumped me. I couldnt figure out how to get the table names from Sql Server 2008 database

SELECT TABLE_NAME, *
FROM INFORMATION_SCHEMA.TABLES

This works perfectly

Gave me the following information

TABLE_NAME	TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	TABLE_TYPE
category	DBName	dbo	category	BASE TABLE

Then, I tried the following

SELECT s.name + '.' + o.name as tableName
FROM sys.schemas s
JOIN sys.objects o ON s.schema_id = o.schema_id
WHERE o.type = 'U'
ORDER BY s.name, o.name

and it gave me the following

TABLE_NAME

dbo.category

Other possible types that can be used:

C: Check constraint
D: Default constraint
F: Foreign Key constraint
L: Log
P: Stored procedure
PK: Primary Key constraint
RF: Replication Filter stored procedure
S: System table
TR: Trigger
U: User table
UQ: Unique constraint
V: View
X: Extended stored procedure

UPDATE suppliers
SET supplier_name =	( SELECT customers.name FROM customers WHERE customers.customer_id = suppliers.supplier_id)
WHERE EXISTS ( SELECT customers.name FROM customers WHERE customers.customer_id = suppliers.supplier_id);