Form with Telephone numbers

[lsdev]

I have a form with a binded Telephone number which was of type number(11), I have now changed this to type char(11) with a constraint as follows:
CHECK(
(SUBSTR(Tel,1,1) BETWEEN '0' AND '9')
AND
(SUBSTR(Tel,2,1) BETWEEN '0' AND '9')
AND
(SUBSTR(Tel,3,1) BETWEEN '0' AND '9')
AND
(SUBSTR(Tel,4,1) BETWEEN '0' AND '9')
AND
(SUBSTR(Tel,5,1) BETWEEN '0' AND '9')
AND
(SUBSTR(Tel,6,1) BETWEEN '0' AND '9')
AND
(SUBSTR(Tel,7,1) BETWEEN '0' AND '9')
AND
(SUBSTR(Tel,8,1) BETWEEN '0' AND '9')
AND
(SUBSTR(Tel,9,1) BETWEEN '0' AND '9')
AND
(SUBSTR(Tel,10,1) BETWEEN '0' AND '9')
AND
(SUBSTR(Tel,11,1) BETWEEN '0' AND '9')
)

I have since changed the datatype of the data set for this column to system.string.

It all works fine in that the form displays the telephone number perfectly fine, but my issue now is that when I update i am getting a constraint error?? I have the textbox limited to 11 in length and basically if I change anything else in the form and try to save these changes it seems to think the telephone number entry is in valid? BUT if I remove the first zero and replace it with anything else like 3 or 4, or something it updates fine.

Any ideas?


Regards

 

[lsdev]

Just to clear this up I have found that simply rebuilding the queries again solved the probelm? Very strange, I do wish vb.net enforced some kind of permanent link when developing so a change in a backend database is picked up, or there was a user friendly way of refreshing the data set

 

[cjard]

aieee carumba.. that's HORRIBLE!

In Oracle i'd either use a regular expression to check that the phone number was an 11 digit number starting with 0, or I would:

LENGTH(TRANSLATE(phone_number, ' 0123456789', ' ')) IS NULL

which would remove all numbers from the phone number parameter reducing it to a null (empty strings are null in oracle) and then the LENGTH would return null

If there was ANY other chartacter in that field, then the LENGTH would not be null.

SQLServer has an equivalent to TRANSLATE, i'm sure.. and also, you can try casting to number..

 

[cjard]

Just to clear this up I have found that simply rebuilding the queries again solved the probelm? Very strange, I do wish vb.net enforced some kind of permanent link when developing so a change in a backend database is picked up, or there was a user friendly way of refreshing the data set

Heh, but you can develop an app without ever having seeen the backend db...But also you dont ALWAYS want to refresh changes to the schema! THere is a user friendly way.. right click the table adapter and choose configure

 

[lsdev]

aieee carumba.. that's HORRIBLE!

In Oracle i'd either use a regular expression to check that the phone number was an 11 digit number starting with 0, or I would:

LENGTH(TRANSLATE(phone_number, ' 0123456789', ' ')) IS NULL

which would remove all numbers from the phone number parameter reducing it to a null (empty strings are null in oracle) and then the LENGTH would return null

If there was ANY other chartacter in that field, then the LENGTH would not be null.

SQLServer has an equivalent to TRANSLATE, i'm sure.. and also, you can try casting to number..

Well that sounds like a better alternative, to all checks I have, but what is wrong with the way I have done it with a check constraint? I was shun upon for using a NUMBER datatype, so I thought seens it is never having any caculations made on it a CHAR was a better option. You can't ideally perform silly sum(tel) operations now

 

[lsdev]

Heh, but you can develop an app without ever having seeen the backend db...But also you dont ALWAYS want to refresh changes to the schema! THere is a user friendly way.. right click the table adapter and choose configure

^^ the above is what I did but when you have 16 related data tables it becomes more than irritating

 

[cjard]

If you change 16 tables, why are you complaining about having to configure 16 times? If you change 1 table, just configure that one.

Note, establishing 16 relations in one dataset is a bit dodgy.. you will run into problems if you arent using all related tables