SQL Server Date Time Format

Please visit my new Web Site https://coderstechzone.com

Most of the developers expect that sql server date time format will behave like VB or .net but the reality is different. The only way to translate a DateTime into a specific format is to convert it to a VARCHAR or other "string" data type. This means that it is no longer a DateTime. It is a VARCHAR. For front end presentation it will be required to format the DateTime whatever circumstances. Kalen Delaney's provide us a solution. Here i want to discuss the most common aspects of formatting Sql Server DateTime. Before using enlisted styles to format the DateTime why not we try to dvelop a custom method for common "dd/MM/yyyy" format.

For DATETIME built in function you can visit this link.

The below sql will give you the most common format:

SELECT RIGHT('0' + RTRIM(DAY(GETDATE())),2) + '/' + RIGHT('0' + RTRIM(MONTH(GETDATE())),2) + '/' + RTRIM(YEAR(GETDATE()))

FORMAT: 25/04/2009

In the above example DAY,MONTH,YEAR return you the day no, month no & year respectively. RIGHT method is used to keep the fixed 2 length string & RTRIM is used to merge the numeric value with string "/".

It looks hard. We have a easy way which i want to share with you.

To do that we will use CONVERT function in the following way:

CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

Where data_type will be varchar or char, length will be the total length of your expected format, expression will be any valid sql server expression & style will denote the output format.

The list of available styles are given below:

Style	Format	Example
1	MM/dd/yy	04/25/09
2	yy.MM.dd	09.04.25
3	dd/MM/yy	25/04/09
4	dd.MM.yy	25.04.09
5	dd-MM-yy	25-04-09
6	dd MMM yy	25 Apr 09
7	MMM dd, yy	Apr 25, 09
10	MM-dd-yy	04-25-09
11	yy/MM/dd	09/04/25
12	yyMMdd	090425
100 or 0	MMM dd yyyy hh:miAM (or PM)	Apr 25 2009 1:10PM
101	MM/dd/yyyy	04/25/2009
102	yyyy.MM.dd	2009.04.25
103	dd/MM/yyyy	25/04/2009
104	dd.MM.yyyy	25.04.2009
105	dd-MM-yyyy	25-04-2009
106	dd MMM yyyy	25 Apr 2009
107	MMM dd, yyyy	Apr 25, 2009
108	hh:mm:ss	13:12:22
109 or 9	MMM dd yyyy hh:mi:ss:mmmAM (or PM)	Apr 25 2009 1:12:40:263PM
110	MM-dd-yyyy	04-25-2009
111	yyyy/MM/dd	2009/04/25
112	yyyyMMdd	20090425
113 or 13	dd MMM yyyy hh:mm:ss:mmm(24h)	25 Apr 2009 13:13:30:983
114	hh:mi:ss:mmm(24h)	13:13:42:200

Ok now you know the style value and its corresponding format. Now i want to show you how we can use this style or format in sql server query statement. You can also use this format from asp.net C# application when you built a query string. You can also use those formats in your where clause as conditional purposes.

Query	Output
SELECT CONVERT(VARCHAR, getdate(), 1)	04/25/09
SELECT CONVERT(VARCHAR, getdate(), 10)	04-25-09
SELECT CONVERT(VARCHAR, getdate(), 100)	Apr 25 2009 1:25PM
SELECT CONVERT(VARCHAR, getdate(), 114)	13:25:48:153

The above all formats may does not satisfy your condition. So you have to create a custom statement like my first example.

To read more:
http://www.databasejournal.com/features/mssql/article.php/10894_2197931_1/Working-with-SQL-Server-DateTime-Variables-Part-Two---Displaying-Dates-and-Times-in-Different-Formats.htm

Sending Web Email in ASP.NET C#

Please visit my new Web Site https://coderstechzone.com

E-mail is one of the most common popular methods of communication for both personal and business purposes. It also plays an important role in each and every Web site specially for registration page or sales order page of an e-commerce site & also for online invoicing. The .NET framework makes the task of sending email from a Web page unbelievably easy and I'll show you how to do it in a matter of minutes.

Here i am using gmail smtp to send email using asp.net cause i think most of the reader has a gmail id so that he can just copy the below method & can send email. Just need to change the from & to email address. Better first try with gmail cause its easy to test. Then go for your own mail server.

Send an email using Visual Studio 2005:
At first import the namespace: using System.Net.Mail;
Now copy the below code under Send button:
C#:

MailMessage mailMsg = new MailMessage();
// if you want to send HTML BODY then true for plain text body then false
mailMsg.IsBodyHtml = true;
mailMsg.Subject = "Asp.net articles";
mailMsg.Body = "<a href='http://shawpnendu.blogspot.com'>GO TO SHAWPNENDU'S BLOG</a>";
mailMsg.From = new MailAddress(shawpnendu@gmail.com);
mailMsg.To.Add(mxxion@yahoo.com);
SmtpClient smtp = new SmtpClient("smtp.gmail.com");
smtp.Credentials = new System.Net.NetworkCredential("shawpnendu@gmail.com", "password here");
// If you use your smtp server then try first with blocking the below line.
smtp.EnableSsl = true;
smtp.Send(mailMsg);

VB.NET:
At first import the name space System.Net.Mail

Dim mailMsg As New MailMessage
mailMsg.IsBodyHtml = True
mailMsg.Subject = "Asp.net articles"
mailMsg.Body = "<a href='http://shawpnendu.blogspot.com'>GO TO SHAWPNENDU'S BLOG</a>"
mailMsg.From = New MailAddress(shawpnendu@gmail.com)
mailMsg.To.Add(mxxion@yahoo.com)
Dim smtp As New SmtpClient("smtp.gmail.com")
smtp.Credentials = New System.Net.NetworkCredential("shawpnendu@gmail.com", "password here")
smtp.EnableSsl = True
smtp.Send(mailMsg)

Send email with Attachment:
Just copy the below line before smtp.Send(mailMsg).
You can also add more than one attachment at a time.
C#:

mailMsg.Attachments.Add(new Attachment("c:\\abc.txt"));
//mailMsg.Attachments.Add(new Attachment("Add another file here"));

VB.NET:
Just copy the below line before smtp.Send(mailMsg).
You can also add more than one attachment at a time.

mailMsg.Attachments.Add(New Attachment("c:\\abc.txt"))
'mailMsg.Attachments.Add(New Attachment("second file path"))

NOTES:
If you are going to host your web site then ask administrator the below questions:
1. Does mail server configured? If yes then collect smtp server name & replace with smtp.gmail.com
2. Does the mail server require authentication before sending an email? If yes then modify credentials else remove credential line from above code segment.
3. Does the mail server relay is on? If no then make it yes.
4. Does the mail server require SSL validation. If no then remove EnableSSL line.

Output Like:


If mail has an attacment:


Common error message:
Mailbox unavailable. The server response was: 5.7.1 Unable to relay for..............
Need to configure the SMTP server to allow relaying for either your IP, "FROM" address, or credentials. Notify hosting server administrator to allow the relay aganist your from email address. If you are using localhost then right click on mycomputer then manage then services & applications then IIS then right click on default smtp virtual server then properties then click on access tab then click on relay button then select only the list below then click on ADD & set the IP 127.0.0.1

To read how to validate entered email address using javascript click on the below link:
http://shawpnendu.blogspot.com/2009/04/cross-browser-javascript-aspnetc-email.html

Cross-browser javascript to disable browser's back button

Please visit my new Web Site https://coderstechzone.com

This is the most common problem for a developer to prevent the user to press the browser back button. One easy solution is you can open a new window which has a disabled back button. Another solution is you can remove the toolbar using javascript.

But most of the cases i think you cant apply above techniques. Thats why i want to share my experience that how we can resolve this problem. One thing you have to understand that there's no perfect way to keep the user from backing up, but we can force the user to go forwards again.

For few cases like after successful login, after a confirm checkout of an e-commerce site even after signout you don't want to user go back form your page. So this is mandatory for us to prevent the user to go to the previous page. To explain the script fuctionality lets i have a login.aspx page. When user successfully logged in then the system redirect the user to his transaction page transaction.aspx. Now from transaction.aspx page i dont want to go back to the login.aspx page. According to the example you have to put the below javascript code just under the head tag of login.aspx page.

<head runat="server"><title>login page</title>
<script type="text/javascript">
setTimeout("window.history.forward(1)", 0);
window.onunload=function{return false;}
</script>
</head >

Now run the login page. After login click on the browser back button & check what haapend. I want to share what is onunload event. In a easy way i can explain that onunload event is: it is called right before the browser navigates to a new URL. So i hope that everything is clear.

Script tested for the following browsers:
*** Internet Explorer
*** Opera
*** Mozilla Firefox
*** Google Chrome

For further reading:
http://www.hunlock.com/blogs/Mastering_The_Back_Button_With_Javascript

How to use ASP.NET AdRotator Control

Please visit my new Web Site https://coderstechzone.com

In recent days rising of of eCommerce, affiliation program, internet marketing, site hosting , pay per click , freelance advertising banners have played a lucrative role of primary or additional income source for many web sites. So in one senetence we can say that the AdRotator control of ASP.Net is used to display a sequence of ad images which is one of the built in ASP.Net server control. If you need to generate more ads in a single place in your web page you may think about AdRotator. Because the AdRotator feeds sequence of ads with its corresponding URL. You could use the target property within the AdRotator control for the destination page opener. If you set target='blank' then ads will be displayed in a new browser otherwise within the container page.

Since the AdRotator server control use XML file to display the ads so you have give the first look on how you can generate XML file forAdRotator. Here i will describe the format of XML:

*** Must begin and end with an tag.
*** Under tag there may be one or more tags which represents a single ad.
*** Under tag you can define the below elements:

Element	Required?	Description
<imageurl>	No	Image location
<navigateurl>	No	URL to link to the ad
<alternatetext>	No	Image alternate text
<impressions>	No	Display ratio based on hits

So for an example the basic syntax of an XML file looks like:

<advertisements>
<ad>
<imageurl>/Path/abc_company_logo.gif </imageurl> <navigateurl>http://abccompany.com </navigateurl>
<alternatetext>Want to share your development experience!</alternatetext>
<keyword>Cosmetics</keyword>
<impressions>3</impressions>
</ad>
<ad>
<imageurl>/Path/def_company_logo.gif</imageurl> <navigateurl>http://defcompany.com</navigateurl>
<alternatetext>Want to share your development experience!</alternatetext<

<keyword>Sunglass</keyword>

<impressions>5</impressions>

</ad>
</advertisements>

So now open a notepad paste this above code & modify it in your way & save the file in root directory with a name testadrotator.xml for testing. Now add an AdRotator control in your page. HTML code looks like:

<asp:adrotator id="firstadrotator" runat="server" advertisementfile="testadrotator.xml" borderwidth="2"/>

Now press F5 hope your Adrotator will work.

One very cool feature of AdRotator control is 'Keyword'. You can set the KeywordFilter property to show only Ads that match the keyword you specify in the XML file. Lets you have two pages one is for cosmetics & other is for sunglasses. So definitely you want to display cosmetics related ads in your cosmetics page. So you have to add the KeywordFilter="Cosmetics" in the AdRotator control like:

<asp:adrotator id="firstadrotator" runat="server" advertisementfile="testadrotator.xml" borderwidth="2" keywordfilter="Cosmetics" />

The above AdRotator will display only cosmetics Ads. So i think basic understaning is clear. Now i am looking for how we can dynamicaly generate the asp.net(C#) server side control AdRotator & change configuration xml file. To do that you have to add a Panel in your page & runtime add the control in the panel container by just 3 lines like:

AdRotator firstadrotator = new AdRotator();
// You can change dynamically xml file from here
firstadrotator.AdvertisementFile = "testadrotator.xml";
Panel1.Controls.Add(firstadrotator);

Hope now you can use the AdRotator control in your page.

How to Encrypt Passwords using MD5 in ASP.NET(C#) application

Please visit my new Web Site https://coderstechzone.com

Encryption for sensitive data like password is essential in everyday development. MD5 hashing algorithm is one of the most commonly used algorithms in asp.net arena and is one of the best. There are two general classes of encryption: one-way encryption and two-way encryption. Using two-way encryption you can encrypt a text as well as you can decrypt it. But for one-way encryption the difference is you can't decrypt it. MD5 encryption is an example of a one-way encryption algorithm.

This is the common task for every registration page where user put their name & their password. You can encrypt the both or only password & save it into the database. So that no one can read the encrypted password which will increase the application security policy. Here i will show you how you can encrypt password. To make it generic it will be best to add a static class so that you can reuse it over this application. The static function is given below:

public static string md5(string sPassword)
{
System.Security.Cryptography.MD5CryptoServiceProvider x = new System.Security.Cryptography.MD5CryptoServiceProvider();
byte[] bs = System.Text.Encoding.UTF8.GetBytes(sPassword);
bs = x.ComputeHash(bs);
System.Text.StringBuilder s = new System.Text.StringBuilder();
foreach (byte b in bs)
{
s.Append(b.ToString("x2").ToLower());
}
return s.ToString();
}

Now you an encrypted data. Insert it into your database table. So user creation is completed. Now how we can authenticate the valid user? Its easy again send the user input to the md5 method which will return you an encrypted string -- now compare with your database. If matched then the user verified.

For further reading:
http://www.4guysfromrolla.com/articles/103002-1.aspx
http://aspalliance.com/535_Using_MD5_Encryption.all

For Cryptographic details:
http://www.developer.com/net/net/article.php/1548761

Passing data/parameters/values from one aspx page to another aspx page

Please visit my new Web Site https://coderstechzone.com

Passing parameters from one page to another page is a very common task in Web development. Specially when you develop a project & redirects or navigates from one ASP.NET Web page to another, you will frequently want to pass information from the source page to the target page . In this post, I will show you some ways of transferring data/parameters/values between one page to another page which is also termed as State Management. The pages I created to show you the example is really simple which consists of a text field and a button in the first page named as page1 and a blank second page named as page2.

According to the characteristics of aspx page lifecycle, web form do not retain their value after a page is displayed. To resolve this problem, ASP.NET provides the following ways to retain variables between pages:

*** Query Strings

*** Cookies
*** Session variables
*** Server.Transfer

*** Post Back URL

Let we have two aspx page named page1.aspx(contains txtName TextBox, txtAge TextBox & cmdTransfer Command Button) & a blank page2.aspx

Using Query String:
The most common & easiest way of passing data is query string. Usually we pass value(s) through query string of the page and then this value is pulled from Request object in another page. One thing keep in mind that user can view query string value any time(from brwser address, View source, even on mouse over of a link etc.). So never pass information which is secure like password through query string. Query string appear after a question mark of a hyperlink & you can add more by adding & Like:

<a href='http://shawpnendu.blogspot.com?var1=10&var2=10'>......</a>

Below is an example:
Suppose you want to pass the txtName TextBox value from page1 to page2 on cmdTransfer button click event:

protected void cmdTransfer_Click(object sender, EventArgs e)
{
Response.Redirect("page2.aspx?myname=" + txtName.Text);
}

Now the question is how we can read/retrieve my name from page2.aspx page? The way is:

string myname = Request.QueryString["myname"];

Now more advance is if you want to transfer special charaters or a line with spaces then you must have to use Server.UrlEncode method before transfer & Server.UrlDecode method to read data from page2.aspx:

protected void cmdTransfer_Click(object sender, EventArgs e)
{
Response.Redirect("page2.aspx?myname=" + Server.UrlEncode(txtName.Text));
}

and to read from page2:

string myname = Server.UrlDecode(Request.QueryString["myname"]);

Now i want to show you how you can transfer more data:

protected void cmdTransfer_Click(object sender, EventArgs e)
{
Response.Redirect("page2.aspx?myname="+Server.UrlEncode(txtName.Text)+"&myage="+txtAge.Text);
}

Now read both name & age from page2 in the same way:

string myname = Request.QueryString["myname"];
string myage = Request.QueryString["myage"];

Now i want to show you how you can detect does a parameter contains value or not.
So we have to modify read data section in page2 in the following way:

string myname ="";
if (Request["myname"] != null)
myname = Request.QueryString["myname"];

Using Cookies:
Developers use cookies to store small amounts of information on the client. But keep in mind that user may refuse cookies, so you have to handle it. Finally i can say cookies are created on the server side but saved on the client side.

Example:
Now we create a cookie from page1 & read from page2:
for page1 button click event:

protected void cmdTransfer_Click(object sender, EventArgs e)
{
if(Request.Browser.Cookies) // To check that the browser support cookies
{
HttpCookie cookie = new HttpCookie("myname");
cookie.Value = txtName.Text;
cookie.Expires = DateTime.Now.AddDays(1);
Response.Cookies.Add(cookie);
Response.Redirect("page2.aspx");
}
else
{
// You have to follow other option.
}
}

Now read from page2:

if(Request.Cookies["myname"] != null)
Response.Write(Request.Cookies["myname"].Value);

Using Session Variables:
By default, session variables are stored in the webserver's memory. Session variables are unique per each user. If you open same url in two browser then server will create two independent sessions for you. Keep in mind that each session has a expire time. You can configure session time from IIS. Also one another thing is, you can receive session value form any pages after assigning. Write the below code under page1 button:

protected void cmdTransfer_Click(object sender, EventArgs e)
{
Session["myname"] = txtName.Text;
Response.Redirect("page2.aspx");
}

Now read session variable value from page2:

string myname ="";
if(Session["myname"]!=null)
myname=Session["myname"].ToString();

Using Server.Transfer:
We will use Server. Transfer to send the control to a new page. Note that Server.Transfer only transfers the control to the new page and does not redirect the browser to it, which means you will see the address of the old page in your URL. If the user clicks Refresh on his browser, the browser prompt a message warning that the page can not be regreshed without resending information. Write the below code under page1 button click event:

protected void cmdTransfer_Click(object sender, EventArgs e)
{
Server.Transfer("page2.aspx", true);
}

And write the below code in page2 load event:

if (Request.Form["txtEmail"])
Response.Write(Request.Form["txtNamel"]);

Now run the page1, enter your name & click. You will see your name in page2.

Using Post Back URL:
ASP.NET 2.0 has been introduced Cross page posting. By PreviousPage object you can search controls value from which page you are redirected here. To implement Cross page posting you have to define the PostBackUrl property of page1 button to page2. Like:

<asp:Button id="cmdTransfer" runat="server" Text="Transfer" PostBackUrl="page2.aspx"/>

Now write the below code in page2 load event:

TextBox txtName = (TextBox)(PreviousPage.FindControl("txtName"));
string myname =txtName.Text;

So keep experimenting.

Cross-browser javascript & asp.net(C#) email address validation

Please visit my new Web Site https://coderstechzone.com

Email address validation is a most common scenario for a developer. It will be the best if we can use javascript validation along with server side validation since user may off javascript from browser settings. The following example shows how you can validate an email address for a form. The script is cross browser compatible (works for all browsers such as IE, Firefox, Opera).

Javascript email validation using regular expression:
Here i tried to find out a simple solution to prhibit users to enter wrong email address. There is a lot of scope here to modify the regular expression. To do that you have to learn regular expression syntax. Now add the below function within your head tag or within the content tag for child page.

<script language="javascript" type="text/javascript">
function validateEmail()
{
var regex = /^[a-zA-Z0-9._-]+@([a-zA-Z0-9.-]+\.)+[a-zA-Z0-9.-]{2,4}$/;
return regex.test(document.getElementById('<%= txtEmail.ClientID %>').value);
}
</script>

So simple. Ok now we want to modify a little bit for better look & feel.

<script type="text/javascript">
function validateEmail()
{
var obj=document.getElementById('<%= txtEmail.ClientID %>');
var regex = /^[a-zA-Z0-9._-]+@([a-zA-Z0-9.-]+\.)+[a-zA-Z0-9.-]{2,4}$/;
if(regex.test(obj.value))
{
//You can also assign stylesheet by
//obj.className='....';
obj.style.backgroundColor = '';
obj.style.backgroundColor = '';
return true;
}
else
{
//Changing Background Color so that user can understand that its invalid
//You can also assign stylesheet by
//obj.className='....';
obj.style.backgroundColor = '#FD5E53';
obj.style.borderColor = '#CD4A4A';
return false;
}
}
</script>

Now i am showing how we use the above javascript email validation function in our aspx page.

<asp:TextBox ID='txtEmail' runat="server"></asp:TextBox>
<asp:Button id="cmdSave" runat="server" Text="Save" OnClientClick="return validateEmail();" />

Simple output:


Asp.net C# email address validation:
I have already explained why we need to write server side validation. Now i want to show you an example how we can validate email address:

For code reusability it will be best to add a static class in our project for such type of validation. Here i am adding a static class named validation.cs:

public static class Validation
{
public const string EmailStandard = @"^[a-zA-Z0-9._-]+@([a-zA-Z0-9.-]+\.)+[a-zA-Z0-9.-]{2,4}$";
public static bool ValidateEmail(string emailID)
{
if (emailID != null)
return System.Text.RegularExpressions.Regex.IsMatch(emailID, EmailStandard);
else
return false;
}
}

Now from our aspx page write the below code when user click on a button to proceed.

if (Validation.ValidateEmail(txtEmail.Text))
{
//Valid Email
}
else
{
//Invalid Email
//Notify user
return;
}

Hope now readrers can easily implement the both javascript & server side validation & also can modify.

How to format GridView Rows/Columns in Design/Run time

Please visit my new Web Site https://coderstechzone.com

Here i will discuss how you can format data in GridView rows. Most of the time we require to render our GridView control by custom formatting since data source format does not contain our exact required formats. Like, you want to display numeric values in two decimal format or want to display short date instead of considering time portion from databse. In this post i will try to resolve below issues:

*** How to format date/numeric data in GridView in design time
*** How to format data in GridView in run time/code behind
*** How to format Template Column
*** How to use DataFormatString property to format data
*** What is HtmlEncode?

List of Date Formats:

Format character	Description	Pattern	Example
d	Short date	MM/dd/yyyy	12/1/2009
D	Long date	dddd, dd MMMM yyyy	Sunday, April 12, 2009
t	Short time	HH:mm	5:12 PM
T	Long time	HH:mm:ss	5:12:00 PM
f	Full date/time(short time)	dddd, dd MMMM yyyy HH:mm	Sunday, April 12, 2009 5:12 PM
F	Full date/time(long time)	dddd, dd MMMM yyyy HH:mm:ss	Sunday, April 12, 2009 5:12:00 PM
g	General date/time(short time)	MM/dd/yyyy HH:mm	4/12/2009 5:12 PM
G	General date/time(long time)	MM/dd/yyyy HH:mm:ss	4/12/2009 5:12:00 PM
m or M	Month day	MMMM dd	April 12
r or R	GMT	ddd, dd MMM yyyy HH':'mm':'ss 'GMT'	Sun, 12 Apr 2009 7:12:00 GMT
s	Sortable date/time	yyyy'-'MM'-'dd'T'HH':'mm':'ss	2009-04-12T14:12:00
u	UniversalSortableDateTimePattern using universal time	yyyy'-'MM'-'dd HH':'mm':'ss'Z'	2009-04-12 14:12:00z
U	Full date and time (long date and long time) using universal time	dddd, dd MMMM yyyy HH:mm:ss	Sunday, April 12, 2009 10:12:00 PM
y or Y	Year month	MMMM yyyy	April, 2009

Format date in bound column:

Consider we have a GridView that displays Supplier information with Code, Name, Address, Contact no and Supply date where the Supply date is stored with time in database. To do that for bound column apply the below technique:

<asp:BoundField DataField="LastDelivery" HeaderText="Recent Delivery" DataFormatString = "{0:y}" HtmlEncode="false"></asp:BoundField>

OR

<asp:BoundField DataField="LastDelivery" HeaderText="Recent Delivery" DataFormatString = "{0:MMMM yyyy}" HtmlEncode="false"></asp:BoundField>

If you want to format date using SQL query then Click Here.

i.e. You can use both format or pattern column to format date column like below:



Formatting columnar data in a GridView code-behind:

protected void gvEdit_RowDataBound(object sender, GridViewRowEventArgs e)
{if (e.Row.RowType == DataControlRowType.DataRow)
{e.Row.Cells[4].Text = Convert.ToDateTime(((DataRowView)e.Row.DataItem)["LastDelivery"]).ToString("y");
}
}

Format Template Column:
Add a template column with textbox/label etc.. then format it in the below way:

<asp:TemplateField HeaderText="Recent Delivery">
<ItemTemplate><asp:Label ID="lbl" runat="server" Text='<%# Eval("LastDelivery", "{0:y}") %>'></asp:Label>
</ItemTemplate></asp:TemplateField>

List of Numeric Formats:

Format	Description	Example
C	Currency format	$10.00
D	Decimal format	10
E	Scientific format	1.000000E+001
F	Fixed format	10.00
G	General format	10
N	Number format	10.00
X	Hexadecimal format	A

Note: Format characters are not case-sensitive, except for "X"

The value after the format character specifies the number of significant digits or decimal places to display. For example, the formatting string "{0:F2}" displays a fixed-point number with two decimal places.

Format numeric data in bound column:

<asp:BoundField DataField="Price" HeaderText="Price" DataFormatString="{0:C2}" HtmlEncode="false">

Format numeric data in Template Column:

<asp:TemplateField HeaderText="Recent Delivery">
<ItemTemplate>
<asp:Label ID="lbl" runat="server" Text='<%# Eval("Price", "{0:C2}") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>

Formatting columnar data in a GridView code-behind:

protected void gvEdit_RowDataBound(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.DataRow)
((Label)e.Row.FindControl("lbl")).Text = Convert.ToDecimal(((DataRowView)e.Row.DataItem)["ID"]).ToString("C5");
}

You may need to add few prefix like AU/US then use :
DataFormatString="AU {0:C5}" HtmlEncode="false"
OR
DataFormatString="US {0:C5}" HtmlEncode="false"



NOTE: Most of the beginner forget to add HtmlEncode="false"

What is HtmlEncode?
Microsoft introduced this new property to prevent cross site scripting (CSS) attacks. This way if there is any malicious text in the fields, it will get encoded and will not executeon client's browser. If this property is set to true, the formatting implementation in the control first encoded the text using HttpUtility.HtmlEncode. And after that it calls String.Format method on the encoded text value.Where as when HtmlCode is set to false, the formatting is done on the data value itself. So the way to fix your problem with formatting would be to set HtmlEncode property to false.

To Read More:
http://msdn.microsoft.com/en-us/library/aa479342.aspx
http://www.netomatix.com/development/gridviewcustomdataformatting.aspx

How to configure SQL Server 2005 to allow remote connections

Please visit my new Web Site https://coderstechzone.com

When you try to connect to an instance of Sql Server 2005 from your application or froma remote computer most of the times you may get an error message. Because by default, SQL Server 2005 doesn’t allow remote connection so you have to enable it manually. If you try to connect to remote SQL Server without enable remote connection first, you’ll see one of these error messages:

1. Sql server does not allow remote connections.
2. SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified.
3. An error has occured while establishing a connection to the server. When connecting to SQL Server 2005,this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.(provider:Named Pipes Provider,error:40-Could not open connection to SQL Server))Server does not exist or access denied.

Steps for resolution:

1. Enable remote connections on the instance of SQL Server that you want to connect to from a remote computer.
2. Turn on the SQL Server Browser service.
3. Configure the firewall to allow network traffic that is related to SQL Server and to the SQL Server Browser service.

Enable remote connection:
If you use Sql Server Management studio then:

open.
Connect to the remote server.Right click on the server instance.Select properties.
Click Connections.Check to allow remote connections to this server.



If you use Express or Developer edition then follow the below steps:

Click Start -->> Programs -->> Microsoft SQL Server 2005 -->>
Configuration Tools -->> SQL Server Surface Area Configuration.
Now click Surface Area Configuration for Services and Connections.
Now expand Database Engine, click Remote Connections, click Local and remote connections, click the appropriate protocol to enable for your environment, and then click Apply.



Restart MSSQLSERVER service from
Start -->> Control Panel -->> Administrative Tools -->>
Service -->> Select MSSQL Server -->> Restart Service

Enable Sql Server Browser service:
One thing noted that this may increase security risk. Microsoft recommend that you use this process only if you really require this process.To enable the SQL Server Browser service, follow these steps.

Click Start -->> Programs -->> Microsoft SQL Server 2005 -->>
Configuration Tools -->> SQL Server Surface Area Configuration
Now click Surface Area Configuration for Services and Connection
Select SQL Server Browser -->> Select Startup type Automatic -->> Apply




Create exception in windows firewall for SQL Server:
If you are running a firewall on the computer that is running SQL Server 2005, external connections to SQL Server 2005 will be blocked unless SQL Server 2005 and the SQL Server Browser service can communicate through the firewall. You must create an exception for each instance of SQL Server 2005 that you want to accept remote connections and an exception for the SQL Server Browser service.

Open Start -->> Control Panel -->> Windows Firewall
Select Exceptions tab -->> Add Program -->> Browse
Click the C:\Program Files\Microsoft SQL Server\90\Shared\sqlbrowser.exe executable program, click Open, and then click OK.

Note: path may be different depending upon your instalattion.

You can get more details from :
http://support.microsoft.com/kb/914277
http://www.linglom.com/2007/08/31/enable-remote-connection-to-sql-server-2005-express/