VS 2012 [RESOLVED] How to deal with null in Template field-VBForums
Results 1 to 20 of 20

Thread: [RESOLVED] How to deal with null in Template field

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Dec 2010
    Posts
    25

    Resolved [RESOLVED] How to deal with null in Template field

    The following works exactly as I want except throws an error when the Phone number in SQL to be displayed is non-existent / null.

    Code:
                    <asp:TemplateField HeaderText="Phone Number" SortExpression="PHONE">
                        <EditItemTemplate>
                            <asp:TextBox ID="txtPhNumber" runat="server" Text='<%# Bind("PHONE")%>' ReadOnly="true" Visible="false"></asp:TextBox>
                            <asp:Label ID="lblLftPrn" runat="server" Text="(" />
                            <asp:TextBox ID="txtPhArea" runat="server" MaxLength="3" Width="26px" Text='<%# Eval("PHONE").ToString().Substring(0, 3)%>' ></asp:TextBox>
                            <asp:Label ID="lblRgtPrn" runat="server" Text=") " />
                            <asp:TextBox ID="txtPhPref" runat="server" MaxLength="3" Width="26px" Text='<%# Eval("PHONE").ToString().Substring(3, 3)%>' ></asp:TextBox>
                            <asp:Label ID="lblHyph" runat="server" Text=" - " />
                            <asp:TextBox ID="txtPhNumb" runat="server" MaxLength="4" Width="33px" Text='<%# Eval("PHONE").ToString().Substring(6)%>' ></asp:TextBox>
                        </EditItemTemplate>
                    </asp:TemplateField>
    How do I deal with that in the Display Code? I do not want to go to Code-Behind.

  2. #2
    Frenzied Member KGComputers's Avatar
    Join Date
    Dec 2005
    Location
    Cebu, PH
    Posts
    1,820

    Re: How to deal with null in Template field

    You can check if the Template Field is null using the ternary operator.

    Code:
    <EditItemTemplate>
                <asp:TextBox ID="txtPhNumb" runat="server" Text='<%# String.IsNullOrEmpty(Eval("PHONE").ToString()) ? string.Empty : Eval("PHONE").ToString().Substring(0, 3) %>' ></asp:TextBox>
    </EditItemTemplate>
    Note that this is C# syntax.

    - kgc
    CodeBank: VB.NET & C#.NET | ASP.NET
    Programming: C# | VB.NET
    Blogs: My Blog | GitHub Repo | ASP Snippets | CodeASP.net
    ___________________________________________________________________________________

    Rating someone's post is a way of saying Thanks...

  3. #3

    Thread Starter
    Junior Member
    Join Date
    Dec 2010
    Posts
    25

    Re: How to deal with null in Template field

    Was not familiar with Ternary operator : 'condition ? first_expression : second_expression'; very cool.

    When I try the question mark (C#), "The '?' character cannot be used here." When I try 'If' (VB), "End of statement expected."

    So, still stuck. How to deal with null?
    Last edited by tagtech; Mar 16th, 2017 at 01:06 PM.

  4. #4

    Thread Starter
    Junior Member
    Join Date
    Dec 2010
    Posts
    25

    Re: How to deal with null in Template field

    Code:
    <asp:TextBox ID="txtPhArea" runat="server" MaxLength="3" Width="26px" Text='<%# IIf(Eval("PHONE").ToString() = "", String.Empty, Eval("PHONE").ToString().Substring(0, 3))%>' ></asp:TextBox>
    Works if I have a value / phone number. Returns : "Index and length must refer to a location within the string" if do not have a value / phone number.
    Code:
    <asp:TextBox ID="txtPhArea" runat="server" MaxLength="3" Width="26px" Text='<%# IIf(Eval("PHONE").ToString() = "", Eval("PHONE").ToString().Substring(0, 3), String.Empty)%>' ></asp:TextBox>
    Returns : "Index and length must refer to a location within the string" if do not have a value / phone number. Returns blank spaces in the PHONE fields if I do have a value / phone number.

    If the second code example doesn't error out and returns blanks when I have a phone number, then the first code example should work correctly and not try to "Index and length" the null.

    Any thoughts?

  5. #5
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    38,886

    Re: How to deal with null in Template field

    Change the word IIf to If, but keep the brackets etc.

    IIf() is a function, so evaluates all parameters.

    If() is the ternary operator (like ? in C#), so only evaluates as needed.

  6. #6

    Thread Starter
    Junior Member
    Join Date
    Dec 2010
    Posts
    25

    Re: How to deal with null in Template field

    Quote Originally Posted by si_the_geek View Post
    Change the word IIf to If, but keep the brackets etc.

    IIf() is a function, so evaluates all parameters.

    If() is the ternary operator (like ? in C#), so only evaluates as needed.
    I agree in principle but in practice 'If' returns the two Errors: (1) Expression expected and (2) Expression is not a method.

  7. #7
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    30,585

    Re: How to deal with null in Template field

    you posted the IIF version, can you post the If version? the only difference between the two should be one "I"

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  8. #8
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    30,585

    Re: How to deal with null in Template field

    Quote Originally Posted by si_the_geek View Post
    Change the word IIf to If, but keep the brackets etc.

    IIf() is a function, so evaluates all parameters.

    If() is the ternary operator (like ? in C#), so only evaluates as needed.
    I think that last statement is what may have confused things.... it OPERATES the same as the ? operator in C# but the syntax is different.
    IF(boolean expression, expression when true, expression when false)
    So it's syntactically the same as IIF, it just operates differently under the covers.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  9. #9

    Thread Starter
    Junior Member
    Join Date
    Dec 2010
    Posts
    25

    Re: How to deal with null in Template field

    Quote Originally Posted by techgnome View Post
    you posted the IIF version, can you post the If version? the only difference between the two should be one "I"
    Code:
    <asp:TextBox ID="txtPhArea" runat="server" MaxLength="3" Width="26px" Text='<%# If(Eval("PHONE").ToString() = "", String.Empty, Eval("PHONE").ToString().Substring(0, 3))%>' ></asp:TextBox>
    I now understand (I didn't before) that 'IIf' evaluates both the 'true' and 'false' results and that that is why I get the index and length error when the value / phone number is null. However, 'If' (as in code above) returns the 'build error', "Expression expected."

  10. #10

    Thread Starter
    Junior Member
    Join Date
    Dec 2010
    Posts
    25

    Re: How to deal with null in Template field

    So, I have a valid action performed if the value / phone number is NOT null (false to the If) but need some valid action taken when the value / phone number IS null (true to the If).

    Can someone suggest how to make a default value for the 'True' portion of the following? As is, I still get a 'build error', "Expression expected."
    Code:
    <asp:TextBox ID="txtPhArea" runat="server" MaxLength="3" Width="26px" Text='<%# If(Eval("PHONE").ToString() = "", Eval("PHONE").ToString().Substring(0, 3) = "   ", Eval("PHONE").ToString().Substring(0, 3))%>' ></asp:TextBox>

  11. #11
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    30,585

    Re: How to deal with null in Template field

    Sigh... it's because Eval("Phone") results in a null value... you CAN'T use .ToString on it if it's null...

    Acutally all of your pieces in the If there are incorrect... well, maybe the thrid piece is OK... but the first two are not going to work.

    You need to check to see if it is null, and if not then return the left 3, otherwise return a blank string.

    Code:
    If(Eval("PHONE") is nothing, "", Eval("PHONE").ToString().Substring(0, 3))
    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  12. #12

    Thread Starter
    Junior Member
    Join Date
    Dec 2010
    Posts
    25

    Re: How to deal with null in Template field

    Quote Originally Posted by techgnome View Post
    Sigh... it's because Eval("Phone") results in a null value... you CAN'T use .ToString on it if it's null...

    ... well, maybe the thrid piece is OK...

    ... otherwise return a blank string.

    Code:
    If(Eval("PHONE") is nothing, "", Eval("PHONE").ToString().Substring(0, 3))
    -tg
    Heavy sigh back at you. Thanks for the lesson on .ToString and null; something else I didn't know. I know that the "thrid piece is OK" as if I abandon the 'If' statement, successfully 'build' and run against a value / phone number it works. How do I "otherwise return a blank string?" I still get a 'build error', "Expression expected" even when I change to your kind suggestion. What should the 'True' action be? Thank you for your interest and help.

  13. #13

    Thread Starter
    Junior Member
    Join Date
    Dec 2010
    Posts
    25

    Re: How to deal with null in Template field

    Iif is similar ?: operator in C#, AFAIK you can't use If
    Therefore:
    Code:
    <asp:TextBox ID="txtPhArea" runat="server" MaxLength="3" Width="26px" Text='<%# IIf(Eval("PHONE") Is DBNull.Value, "   ", Eval("PHONE").ToString().Substring(0, 3))%>' ></asp:TextBox>
    The above passes the 'build' and works if I HAVE a value / phone number. It still crashes with 'index, length' error if I do NOT have a value / phone number (null). It seems to me that I should be able to do both 'True' action and 'False' action within the Display code / DetailsView TemplateField and not have to go to Behind code.

  14. #14
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    30,585

    Re: How to deal with null in Template field

    try changing the Is to =


    Assuming you have a SQL select backing this data, I'd "fix" it there. Set the field to a blank string in the SQL .... then your checks would work.
    Code:
    If(Eval("PHONE") = "", "", Eval("PHONE").ToString().Substring(0, 3))
    OR, have it return a string that is always the right length. Then you skip the checks all together and simply break apart the field, which would be nothing but spaces.
    Code:
    Eval("PHONE").ToString().Substring(0, 3)

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  15. #15
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    17,379

    Re: How to deal with null in Template field

    I always do ALL my formatting in the SQL select (when possible).

    Having extra formatting apply in various places - yikes.

    Actually to be safe I write a SCALAR function in MS SQL and use it so that we have consistency - one of the most important things for your users.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  16. #16

    Thread Starter
    Junior Member
    Join Date
    Dec 2010
    Posts
    25

    Re: How to deal with null in Template field

    Quote Originally Posted by techgnome View Post
    Assuming you have a SQL select backing this data, I'd "fix" it there. Set the field to a blank string in the SQL .... then your checks would work.
    OK. So, I replaced all of the null phone numbers in the database with ten blanks and the simple parse works all the time with no 'If'. Thank you for the (too obvious for me) solution.
    Still; there has to be a way that 'If(Condition, True action, False action)' can be made to work.

  17. #17
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    17,379

    Re: [RESOLVED] How to deal with null in Template field

    Replacing existing nulls with blanks only deals with today.

    What happens if another NULL gets put into a new row?

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  18. #18

    Thread Starter
    Junior Member
    Join Date
    Dec 2010
    Posts
    25

    Re: [RESOLVED] How to deal with null in Template field

    ARRRGh! How do you mark as unresolved?

  19. #19
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    30,585

    Re: [RESOLVED] How to deal with null in Template field

    That's why I suggest updating the SQL ... not the table.

    Code:
    Select Id, Name, isnull(Phone, '          ') as Phone....
    from ....
    Then it doesn't matter if a null ends up in the table or not.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  20. #20

    Thread Starter
    Junior Member
    Join Date
    Dec 2010
    Posts
    25

    Re: [RESOLVED] How to deal with null in Template field

    Quote Originally Posted by techgnome View Post
    That's why I suggest updating the SQL ... not the table.
    I've returned to zapping the existing SQL database PHONE field entries to make them ten blanks and added a 'If' to the previously written ItemInserting logic to make a null PHONE entry instead be blanks.
    Code:
    Protected Sub Record_Inserting(ByVal sender As Object, ByVal e As DetailsViewInsertEventArgs) Handles DetailsView1.ItemInserting
    
            Dim txtSSNAll As TextBox = DetailsView1.FindControl("txtSSNumber")
            Dim txtSSNOne As TextBox = DetailsView1.FindControl("txtSSN1")
            Dim txtSSNTwo As TextBox = DetailsView1.FindControl("txtSSN2")
            Dim txtSSNThree As TextBox = DetailsView1.FindControl("txtSSN3")
            Dim txtPhoneAll As TextBox = DetailsView1.FindControl("txtPhNumber")
            Dim txtPhoneOne As TextBox = DetailsView1.FindControl("txtPhArea")
            Dim txtPhoneTwo As TextBox = DetailsView1.FindControl("txtPhPref")
            Dim txtPhoneThree As TextBox = DetailsView1.FindControl("txtPhNumb")
    
            txtSSNAll.Text = txtSSNOne.Text + txtSSNTwo.Text + txtSSNThree.Text
            txtPhoneAll.Text = txtPhoneOne.Text + txtPhoneTwo.Text + txtPhoneThree.Text
    
            e.Values("SSN") = txtSSNAll.Text
    
            If txtPhoneAll.Text = "" Then
                txtPhoneAll.Text = "          "
            End If
    
            e.Values("PHONE") = txtPhoneAll.Text
    
        End Sub
    I'd still rather do the 'IF(Condition, T action, F action)' in the Display Code and feel like there MUST be a straightforward way to accomplish the task.

    Perhaps formulating queries (Googling) is something else that I'm no good at but I have not found any decent examples of entering / updating SSN and PHONE. It seems that someone sometime must have done this well but here I am recreating the wheel.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.