Format Telephone Number in GridView

I see another thread somewhat like my question at:

ASP.NET GridView Column - formatting telephone number

but i do not know if it answers my question as he is using code-behi开发者_开发百科nd to make the colum. All I did was insert the GridView control in visual studio. BTW, the data is being populated in the Grid, I am just trying to get the formatting set now.

I am using Microsoft Visual Studio Professional 2010. (Also SQL Management Studio for my database but this information may not be needed, just trying to give enough to make sure what i am doing is understood)

I am making a website in ASP.NET with Visual Basic.net code behind.

The site is basically a contact list site.

3 Text Box Fields. First Name, Last Name, Main Phone #.

Add Record Button (Takes the information from the text boxes and inserts into a database)

GridView that shows the database that is being populated with the information

I have a "Main Phone Number" Column and this pulls a telephone number to show in GridView. The number is only 10 digits, no formatting...(i.e. 999-999-9999)

I am trying to make GridView take the 9999999999 and make it (999) 999-9999

If I look at the DataFormatString I have tried many combinations of "{0:(###) ###-####}" with and without the quotations and also with all zeroes instead of pound signs.

Through my research it seemed that if I want to use DataFormatString I need to make my phone number in my database an int. So I deleted my table and re-created it from a varchar to an int. I get to the DataFormatString by clicking on Gridview Tasks (arrow at top right of GridView)... then "Edit columns"... then under "Selected Fields" I click the name of the column... "Main Phone Number" then on the under "CommandField properties" I scroll down to "DataFormatString".

I hope I am not being too detailed. I have really appreciated all the help.

I found this:

http://www.tek-tips.com/viewthread.cfm?qid=328173

but i don't know how i would go about using it.. seeing as how because my code was done by Visual Studio... some of it looks like this

UPDATE: I posted the wrong code originally, either way, based off what I stated Kelsey was able to suggest an answer for me that worked.

Below is my new code WITH the corrections that Kelly gave.

    <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" 
            DataKeyNames="EmpId" DataSourceID="SqlDataSource1" 
            EmptyDataText="There are no data records to display." CellPadding="4" 
        ForeColor="#333333" GridLines="None" Height="136px" Width="299px" 
              AllowSorting="True">
            <AlternatingRowStyle BackColor="White" />
            <Columns>
                <asp:CommandField ShowDeleteButton="True" ShowEditButton="True" />
                <asp:BoundField DataField="EmpId" HeaderText="EmpId" ReadOnly="True" 
                    SortExpression="EmpId" Visible="False" />
                <asp:BoundField DataField="FirstName" HeaderText="First Name" 
                    SortExpression="FirstName" />
                <asp:BoundField DataField="LastName" HeaderText="Last Name" 
                    SortExpression="LastName" />
<%--                <asp:BoundField DataField="MainPhoneNumber" HeaderText="Main Phone Number" 
                    SortExpression="MainPhoneNumber" />--%>
                    <asp:TemplateField HeaderText="Main Phone Number"> 
                <ItemTemplate> 
                 <asp:Literal ID="litPhone"  runat="server" Text='<%# string.Format("{0:(###) ###-####}", Int64.Parse(Eval("MainPhoneNumber").ToString())) %>' /> 
                </ItemTemplate> 
                </asp:TemplateField> 

            </Columns>
            <FooterStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />
            <HeaderStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />
            <PagerStyle BackColor="#FFCC66" ForeColor="#333333" HorizontalAlign="Center" />
            <RowStyle BackColor="#FFFBD6" ForeColor="#333333" />
            <SelectedRowStyle BackColor="#FFCC66" Font-Bold="True" ForeColor="Navy" />
            <SortedAscendingCellStyle BackColor="#FDF5AC" />
            <SortedAscendingHeaderStyle BackColor="#4D0000" />
            <SortedDescendingCellStyle BackColor="#FCF6C0" />
            <SortedDescendingHeaderStyle BackColor="#820000" />
        </asp:GridView>

Since you didn't post your GridView code I have to assume that your columns are using a BoundField like this or something similar:

<Columns>
    <asp:BoundField DataField="MainPhoneNumber" DataFormatString="{0:(###) ###-####}" />

Since it is a string, you can't use the DataFormatString property so you will need to change your BoundField to a TemplateField. Just replace your BoundField with the following TemplateField and it should work:

<asp:TemplateField>
    <ItemTemplate>
        <asp:Literal ID="litPhone" runat="server" Text='<%# string.Format("{0:(###) ###-####}", Int64.Parse(Eval("MainPhoneNumber").ToString())) %>' />
    </ItemTemplate>
</asp:TemplateField>

The key here is the code that evaluates the databound field and converts it to an Int64 so that the string formatter will work. Note that I am using an Int64 and not just an int which is 32 bit because a 10 digit number will not fit.

I have tested it and it works :)

On dynamic columns - not as easy... I went after the data-source (MS SQL) with a udf.

/*
    print dbo.formatPhone('1234567');
    print dbo.formatPhone('1234567890');
    print dbo.formatphone('(314)522-4949');
    print dbo.formatPhone('(314) 522-4949 x 104');
*/

create Function [udf_FormatPhone]
(
    @rawPhone as varChar(50) = null
) Returns VarChar(50) 
As
begin 
Declare @formatPhone varChar(50);
Declare @len int;

set @rawPhone = replace(@rawPhone,'(','');
set @rawPhone = replace(@rawPhone,')','');
set @rawPhone = replace(@rawPhone,'-','');
set @rawPhone = replace(@rawPhone,' ','');
set @len=len(@rawPhone)

set @formatPhone =Case
when @len < 7   then @rawPhone
when @len =7    then Substring(@RawPhone,1,3) + '-' + substring(@RawPhone,4,4)
when @len =10   then substring(@RawPhone,1,3) + '-' + substring(@RawPhone,4,3) + '-' + substring(@RawPhone,7,4)
when @len > 10  then substring(@rawPhone,1,3) + '-' + substring(@rawPhone,4,3) + '-' + substring(@RawPhone,7,4) + ' ' + substring(@rawPhone,11,50)
else    @RawPhone
end

return(@formatPhone);
end

and the result of "3015551212x234"

Another alternative is to modify the SELECT statement to cast to a number (that can hold enough digits) as long as you are sure it does not contain anything characters that are not numbers. In SQL Server this is done via the built in CAST or CONVERT functions:

SELECT CAST(Phone as BIGINT) as Phone

Then a boundfield with DataFormatString will work as expected:

DataFormatString="{0:(###) ###-####}"

I generally avoid template fields because then I cannot set

EnableSortingAndPagingCallbacks="True"

on a gridview. I also like the leaner code from the boundfield.