question

postonoh avatar image
postonoh asked

format for insert number with dashes

I have a database with phone number nvarchar, and a cell as numeric. I formatted both as ###-###-####. I have two question. Question 1. When a employee enter the number how to make except the format? Question 2. when a employee go to save it gives an error message can't save with the dashes. Tried both neither works. Is this a asp.net /c# problem or database issues. So I will know what to research. .
sql-server-2008asp.net
4 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@ThomasRushton - "number unobtainable" maybe ?!
2 Likes 2 ·
Just one question - what, exactly, is the error message?
0 Likes 0 ·
invalid value was enter for 'Business phone' was enter! invalid value was enter for 'Cell Phone' was enter!
0 Likes 0 ·
These appear to be errors from the UI rather than the back end SQL Server database.
0 Likes 0 ·
Grant Fritchey avatar image
Grant Fritchey answered
\###-###-### is not a number and therefore can't be stored in the numeric field. You can only put numbers in the numeric field. As far as storing the phone number, why not [try this approach][1]. [1]: http://www.codeproject.com/KB/database/SQLPhoneNumbersPart_1.aspx
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks I click on your link. I know that ###-###-#### are not number. I was using this to show a simple format. But you link was most helpful.
0 Likes 0 ·
Oleg avatar image
Oleg answered
If both phone and mobile are formatted as ###-###-#### and you do have a front end validator enforcing the format then as Grant already pointed out in his answer, you cannot store it in the numeric field. Assuming that the column storing the mobile number is defined as decimal(10, 0), and you have an entity named Employee which exposes string properties like FirstName, Phone, MobilePhone etc then you can strip the dashes from the string input and feed it to the procedure parameter, like this: private static void SaveEmployee(Employee emp) { string connectionString = ConfigurationManager.ConnectionStrings[0].ConnectionString; using (SqlConnection cn = new SqlConnection(connectionString)) { cn.Open(); using (SqlCommand cm = new SqlCommand()) { cm.CommandType = CommandType.StoredProcedure; cm.CommandText = "dbo.your_procedure_name"; cm.Connection = cn; cm.Parameters.Add("@firstName", SqlDbType.VarChar, 50); cm.Parameters[0].Value = emp.FirstName; cm.Parameters.Add("@phone", SqlDbType.VarChar, 20); cm.Parameters[1].Value = emp.Phone; cm.Parameters.Add("@mobile", SqlDbType.Decimal); cm.Parameters[2].Precision = 10; cm.Parameters[2].Scale = 0; // assuming that cell phone is not required if (string.IsNullOrEmpty(emp.MobilePhone)) { cm.Parameters[2].Value = DBNull.Value; } else { cm.Parameters[2].Value = Convert.ToDecimal(emp.MobilePhone.Replace("-", "")); } // more parameters go here... cm.ExecuteNonQuery(); } // command object is disposed cn.Close(); } // connection is disposed } Oleg
6 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

+1, but it's really not numbers. My phone number starts with +44. And yes, + does get replaced with 00 in *most* countries - but not all. + means 'prepend the local country's international dialing prefix here' and 00 is the international dialing prefix for some countries. However, there are lots of weird ones. I like 1666 from Hong Kong best! :)
3 Likes 3 ·
Very well said and I agree, but a better approach might just be to change the database so it stores the phone numbers as varchar(possibly with a check constraint to ensure it is always properly formatted, though that may be better handled in the GUI). Afterall, phone numbers are not numbers.
2 Likes 2 ·
This is true that the phone numbers are not numbers, but formatting aside, they contain numbers only. Since all formatting activities should be outsourced to GUI anyway, storing the phone number either as decimal with scale 0 or using scale 4 to accomodate extensions will save a little bit of space.
0 Likes 0 ·
I agree with @Timothy. While a phone number is just numbers, it isn't numeric data. It's a string. Still gave your solution a +1 though.
0 Likes 0 ·
Thanks, making change so both are varchar and going use a validator.
0 Likes 0 ·
Show more comments
ThomasRushton avatar image
ThomasRushton answered
You should probably look at storing them according to some form of standard. Unfortunately, due to each country making up its own rules about this sort of thing, standards are a bit vague. The nearest thing I can find (at the moment), is a [proposed notation from the ITU][1] (Internation Telecomms Union). Formatting and validation should be performed by the front end. And telephone numbers are not just restricted to digits. In the UK, phone numbers can be understood with a combination of +, space, parentheses and, of course, digits. eg: +44 (0) 123 4567890. Oh, and UK phone numbers are not all the same length... [1]: http://www.itu.int/rec/T-REC-E.123-200102-I/e
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.