This is a migrated thread and some comments may be shown as answers.

Handle NULL data in SQL

3 Answers 84 Views
General Discussions
This is a migrated thread and some comments may be shown as answers.
David House
Top achievements
Rank 2
David House asked on 19 Mar 2010, 04:10 AM
I have a simple column to hold phone numbers. It is an nvarchar(20) to allow for standard phone numbers with extensions.

The first 14 digits will be a standard US phone number. The column allows NULL values and I want to format the data to the following:

(999) 999-9999. This is being done with an expression as follows:

="(" + Fields.PrimaryPhone.SubString(0,3) + ") " + Fields.PrimaryPhone.SubString(3,3) + "-" + Fields.PrimaryPhone.SubString(6)

The issue we are having is if a value is NULL, the expression errors out. I have tried an IIF function, but it does not fix the error condition. Here is the error we are receiving:

 

An error has occured while processing TextBox 'primaryPhoneDataTextBox':

Object reference not set to an instance of an object.

 

 

We have tried a conditional format prior to the expression, and that has not worked either...

Need some assistance as to how to do this...

Thanks...

 

3 Answers, 1 is accepted

Sort by
1
Peter
Telerik team
answered on 22 Mar 2010, 07:07 PM
Hello David,

Have you tried using the Format Builder Dialog - you can define custom formats such as (###) ###-#### for US phone numbers.

If you have to check for null values you can use IsNull to check an expression (Fields.PrimaryPhone) and either return the checked expression or a replacement value.

Sincerely yours,
Peter
the Telerik team

Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items.
0
David House
Top achievements
Rank 2
answered on 22 Mar 2010, 07:23 PM
IsNull does nothing. The IIF doesn't work either.

The Format Builder only works on dates and numbers, not strings.

What other options is there?
0
Peter
Telerik team
answered on 24 Mar 2010, 01:13 PM
Hello David House,

I have attached a sample project that demonstrates how to accomplish your task. I have used User Function and IsNull in the expression to achieve the desired effect. Other possible solutions are to change the datatype of your database field to be numeric so that you can use the Format Builder or if you want to keep it as string, you can keep the formatted string in the database, so there would not be a need for changes on the report end.

Kind regards,
Peter
the Telerik team

Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items.
Tags
General Discussions
Asked by
David House
Top achievements
Rank 2
Answers by
Peter
Telerik team
David House
Top achievements
Rank 2
Share this question
or