How to convert asp.net NULL value to "" (blank string)

Question

I have a situation where I am using a detailsview with the SQLDataSource to update a record in an SQL Server Database.

My issue is that textfields that are not completed are converted to NULL in the database. This causes issue as the website using third party web services that can not handle to NULL DB value.

Is there a way that if the textfield is left empty that when the update or insert occurs, that the data is converted to "" (blank string) instead of the NULL value?

peak · Accepted Answer · 2016-02-17 20:36:49Z

5

Since you are using SQLDataSource, when you insert or update a record, there is a ConvertEmptyStringToNull property of the Insert/update parameters. Set this to false, and set the string to empty, if no value is provided to that control. Look at the example below:

<asp:SqlDataSource ID="SqlDataSource1" runat="server">
        <InsertParameters>
            <asp:ControlParameter ConvertEmptyStringToNull="false" />
        </InsertParameters>
        <UpdateParameters>
            <asp:ControlParameter ConvertEmptyStringToNull="false" />
        </UpdateParameters>
    </asp:SqlDataSource>

edited Feb 17, 2016 at 20:36

peak

119k21 gold badges185 silver badges218 bronze badges

answered May 6, 2011 at 1:59

Muhammad Akhtar

52.2k37 gold badges142 silver badges191 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Jason Over a year ago

Thanks for your answer. I have already added "ConvertEmptyStringToNull" to the SQLDataSource, however it still isnserts the value NULL in the db if the field is empty.

Muhammad Akhtar Over a year ago

You set it false ? if still entering Null then its strange.

Jason Over a year ago

Thanks Muhammad, your solution did work. I was adding the convertemptystringtonull parameter to the details view not the sqldatasource.

Element · Accepted Answer · 2011-05-05 23:50:02Z

0

You could set a default value of "" on the database table column. Or in your SQL select statements you could do something like

SELECT IsNull(ColName,"")
FROM SomeTable

The

answered May 5, 2011 at 23:50

Element

4,0597 gold badges45 silver badges51 bronze badges

2 Comments

Jason Over a year ago

I don't think that would work. I would have to write the IsNull SQL for every column returned. This is my SELECT statement: SELECT [subscriberID], [title], [firstName], [surname], [company], [state], [town], [address], [passwordHint], [email], [password], [postcode], [country], [phoneBH], [PhoneAH], [mobile], [dateOfBirth], [notes], [checked], [guid], [nickName], [avatar] FROM [subscribers] WHERE ([subscriberID] = @subscriberID).

Jason Over a year ago

I would have to use the IsNull SQL for every column returned. Any of those columns could be NULL and I need to convert them to an empty string when updating. I have to do this because I do not have full access to all the code. I am just adding on to an already working application.

Collectives™ on Stack Overflow

How to convert asp.net NULL value to "" (blank string)

2 Answers 2

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related