Data type to be used to store 10 digit mobile number?

Question

In my current vb.net (visual studio 2010) project I am dealing with SQL Server CE data base.
For storing mobile numbers, I am using nvarchar data type for Mobile_no field.

while testing i have entered 1234567890 in field Mobile_no successfully..

But at the time of retrieval i am getting this error :
Expression evaluation caused an overflow. [Name of function (if known)=]

So what should i do to store mobile numbers in sql ce data base ?

EDIT :
Code for Insert :

Dim SQLquery As String
Dim enumber As String
enumber = 1234567890
insqertSql = "INSERT INTO tbl_cust(c_id,Mobile_no) VALUES(@CId, @Phno)"
            Dim cmd As New SqlCeCommand(insqertSql, con)
            cmd.Parameters.Add("@CId", c_id)
            cmd.Parameters.Add("@Phno", enumber)
            cmd.ExecuteNonQuery()

Query for retrieval :

SQLquery = "SELECT * FROM tbl_cust WHERE ePhone =" & txt_number.text

If you store it as a string then retrieve it as string. There is no overflow involved if you do that, but please show the code that retrieve that field — Steve, Aug 10 '13 at 16:07
Sounds like you're possibly storing as a string but try to retrieve it as an integer. — Joachim Isaksson, Aug 10 '13 at 16:09
@DonA , Steve & Joachim Isaksson . . .Please see the updated code. — Vikram, Aug 10 '13 at 17:10
there is no error if i enter real mobile number instead of 1234567890 dummy number.....why so ??? — Vikram, Aug 10 '13 at 17:22
Why NVARCHAR? Do you think someone is going to have umlauts or Chinese symbols in their phone number? VARCHAR should be sufficient. If it's a string, always treat it as a string, so put single quotes around the value. And please read up on SQL injection so you'll want to properly parameterize your queries. — Aaron Bertrand, Aug 10 '13 at 17:26

score 1 · Accepted Answer · edited May 23 '17 at 11:50

The problem is in the string concatenation in the retrieve query.

SQLquery = "SELECT * FROM tbl_cust WHERE ePhone =" & txt_number.text

Here, you miss the single quotes that should enclose a string value when used as a WHERE condition. However, in your INSERT query you use a parameterized approach, why you don't use the same approach for the select?

SQLquery = "SELECT * FROM tbl_cust WHERE ePhone = @number"
Dim cmd As New SqlCeCommand(SQLquery, con)
cmd.Parameters.Add("@number", txt_Number.Text)
SqlCeDataReader reader = cmd.ExecuteReader()  
........

If you use a parameterized query, the proper handling of string quotes, decimal numbers and date formatting is passed to the underlying framework code that knows better than you and me how to pass these values to the database engine. Also, the parameterized query remove any possibilities of Sql Injection.

Data type to be used to store 10 digit mobile number?

1 Answers1