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. .
These appear to be errors from the UI rather than the back end SQL Server database.
\###-###-### 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
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
+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! :)
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.
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.
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.
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
