Results 1 to 16 of 16

Thread: Extra Value that is EMPTY

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Jul 2021
    Posts
    71

    Extra Value that is EMPTY

    Hi,

    I have the following code below that pulls the list of 'tenantfullname' from the table to the datagridview. However, I would like the top value on the dropdownlist to be empty. Because there is a scenario that the cell value will not be attached to a tenant. How will I introduce an extra empty cell on the TOP of the combobox.

    Thanks in advance


    Code:
      Try
                Using con As New SqlConnection("Data Source=DESKTOP-O6TSDMJ;Initial Catalog=RCMS;Integrated Security=True")
                    DsUnitdetails.EnforceConstraints = False
                    Me.VwunitdetailsTableAdapter.Fill(Me.DsUnitdetails.vwunitdetails, TxtPropertyIDClick.Text)
    
                    con.Open()
    
                    Dim dt As New DataTable()
                    Using cmd As New SqlCommand("Select tenantregID, tenantfullname from tbltenants order by firstname", con)
                        Using adapter As New SqlDataAdapter(cmd)
                            adapter.Fill(dt)
                            With TenantName
                                .DisplayMember = "tenantfullname"
                                .ValueMember = "tenantregID"
                                .DataSource = dt
                            End With
                        End Using
                    End Using
    
                    con.Close()
                End Using
    
            Catch ex As Exception
                MessageBox.Show(ex.ToString())
            End Try
    Last edited by dr225; Sep 20th, 2021 at 07:30 AM.

  2. #2
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    11,711

    Re: Extra Value that is EMPTY

    Without messing around with any of the VB.NET code, you could simply do a UNION in your SQL on null/blank values:
    Code:
    SELECT NULL AS tenantregID, '' AS tenantfullname UNION SELECT tenantregID, tenantfullname FROM tbltenants ORDER BY firstname
    "Code is like humor. When you have to explain it, it is bad." - Cory House
    VbLessons | Code Tags | Sword of Fury - Jameram

  3. #3

    Thread Starter
    Lively Member
    Join Date
    Jul 2021
    Posts
    71

    Re: Extra Value that is EMPTY

    Thanks dday9..

    So I changed and called the UNION in the SQL - it brings the results as required. Now when I select an empty cell and click update I get an error stating

    System.NullReferenceException: 'Object reference not set to an instance of an object.'

    The code that causes the issue is :

    Code:
     If Me.DgUnitsUp.Rows(i).Cells(8).Value.ToString() = "" Then
        Me.DgUnitsUp.Rows(i).Cells(3).Value = True
    Else
        Me.DgUnitsUp.Rows(i).Cells(3).Value = False
    End If
    I tried to change it to 'Is Nothing' but that didn't help...

    N/B:

    tenantregID is an int and allows NULL values.

    Thanks again
    Last edited by dday9; Sep 20th, 2021 at 02:16 PM. Reason: Added code tags

  4. #4
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    11,711

    Re: Extra Value that is EMPTY

    DbNull is not the same as Nothing or an empty string. It is used to specifically check if the database value is null. You can use something like:
    Code:
    DBNull.Value.Equals(value)
    "Code is like humor. When you have to explain it, it is bad." - Cory House
    VbLessons | Code Tags | Sword of Fury - Jameram

  5. #5

    Thread Starter
    Lively Member
    Join Date
    Jul 2021
    Posts
    71

    Re: Extra Value that is EMPTY

    Thanks dday9


    Code:
                   If Me.DgUnitsUp.Rows(i).Cells(8).Value.ToString() = DBNull.Value.Equals(value) Then
                        Me.DgUnitsUp.Rows(i).Cells(3).Value = True
                    Else
                        Me.DgUnitsUp.Rows(i).Cells(3).Value = False
                    End If
    What will replace the 'value'?


    Cheers

  6. #6
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: Extra Value that is EMPTY

    Like this:
    Code:
                   If DBNull.Value.Equals(Me.DgUnitsUp.Rows(i).Cells(8).Value) Then

  7. #7

    Thread Starter
    Lively Member
    Join Date
    Jul 2021
    Posts
    71

    Re: Extra Value that is EMPTY

    Thanks si_the_geek..

    As a follow up:

    I have this code which works fine but when the selected value is NULL then I receive the error message:

    System.NullReferenceException: 'Object reference not set to an instance of an object.'



    Code:
    Dim isInteger As Boolean = True
                    Try
                        Dim castValue As Integer = CInt(DgUnitsUp.Rows(i).Cells(8).Value.ToString())
                    Catch ex As InvalidCastException
                        isInteger = False
                        cmd.Parameters.AddWithValue("@tenantregID", DgUnitsUp.Rows(i).Cells(4).Value.ToString())
                    End Try
                    If isInteger = True Then
                        cmd.Parameters.AddWithValue("@tenantregID", DgUnitsUp.Rows(i).Cells(8).Value.ToString())
                    End If

    How can also incorporate that if the value selected on the combobox is NULL then it should save a NULL value at tenatregID field for that record i.e., no tenant assigned to that unit.

    Thanks

  8. #8
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    11,711

    Re: Extra Value that is EMPTY

    The underlying issue is the same. You are attempting to access a member, in this case ToString, on a value that is null.
    Code:
    Dim tenantregID As Integer
    If (Not DBNull.Value.Equals(Me.DgUnitsUp.Rows(i).Cells(8).Value) AndAlso Integer.TryParse(Me.DgUnitsUp.Rows(i).Cells(8).Value.ToString(), tenantregID)) Then
        ' value 8 is not null and can be converted to an Integer
        cmd.Parameters.AddWithValue("@tenantregID", DgUnitsUp.Rows(i).Cells(8).Value.ToString())
    Else If (Not DBNull.Value.Equals(Me.DgUnitsUp.Rows(i).Cells(8).Value)) Then
        ' value 8 is not null but cannot be converted to an Integer
        If (Not DBNull.Value.Equals(Me.DgUnitsUp.Rows(i).Cells(4).Value)) Then
            cmd.Parameters.AddWithValue("@tenantregID", DgUnitsUp.Rows(i).Cells(4).Value.ToString())
        Else
            ' value 8 is not null but value 4 is null
        End If
    Else
        ' value 8 is null
    End If
    "Code is like humor. When you have to explain it, it is bad." - Cory House
    VbLessons | Code Tags | Sword of Fury - Jameram

  9. #9

    Thread Starter
    Lively Member
    Join Date
    Jul 2021
    Posts
    71

    Re: Extra Value that is EMPTY

    Thanks dday9...

    When I select NULL and click update the following line executes an error at run time
    Code:
    If (Not DBNull.Value.Equals(Me.DgUnitsUp.Rows(i).Cells(8).Value) AndAlso Integer.TryParse(Me.DgUnitsUp.Rows(i).Cells(8).Value.ToString(), tenantregID)) Then

    Error Message:

    Code:
    System.NullReferenceException: 'Object reference not set to an instance of an object.'

    N.B: - If you select a tenant and click update it saves it OK.

    Thanks

  10. #10
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    11,711

    Re: Extra Value that is EMPTY

    I don't know why you'd be getting the error. My only other suggestion would be to also check that it is not Nothing before you check if it is not DBNull.
    "Code is like humor. When you have to explain it, it is bad." - Cory House
    VbLessons | Code Tags | Sword of Fury - Jameram

  11. #11

    Thread Starter
    Lively Member
    Join Date
    Jul 2021
    Posts
    71

    Re: Extra Value that is EMPTY

    I have tried changing to this:

    Code:
    If (Not Nothing.Value.Equals(Me.DgUnitsUp.Rows(i).Cells(8).Value) AndAlso Integer.TryParse(Me.DgUnitsUp.Rows(i).Cells(8).Value.ToString(), tenantregID)) Then
                        ' value 8 is not null and can be converted to an Integer
                        cmd.Parameters.AddWithValue("@tenantregID", DgUnitsUp.Rows(i).Cells(8).Value.ToString())
    I receive the error message:

    System.NullReferenceException: 'Object variable or With block variable not set.'

    Thanks

  12. #12
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    11,711

    Re: Extra Value that is EMPTY

    No, not that. Change that back to what it was. What I meant by checking that the value is not Nothing before you check if it is not DBNull is:
    Code:
    Me.DgUnitsUp.Rows(i).Cells(8).Value IsNot Nothing AndAlso Not DBNull.Value.Equals(Me.DgUnitsUp.Rows(i).Cells(8).Value) AndAlso Integer.TryParse(Me.DgUnitsUp.Rows(i).Cells(8).Value.ToString(), tenantregID)
    "Code is like humor. When you have to explain it, it is bad." - Cory House
    VbLessons | Code Tags | Sword of Fury - Jameram

  13. #13

    Thread Starter
    Lively Member
    Join Date
    Jul 2021
    Posts
    71

    Re: Extra Value that is EMPTY

    Code:
    If (Me.DgUnitsUp.Rows(i).Cells(8).Value IsNot Nothing AndAlso Not DBNull.Value.Equals(Me.DgUnitsUp.Rows(i).Cells(8).Value) AndAlso Integer.TryParse(Me.DgUnitsUp.Rows(i).Cells(8).Value.ToString(), tenantregID)) Then
                        ' value 8 is not null and can be converted to an Integer
                        cmd.Parameters.AddWithValue("@tenantregID", DgUnitsUp.Rows(i).Cells(8).Value.ToString())
                    ElseIf (Not DBNull.Value.Equals(Me.DgUnitsUp.Rows(i).Cells(8).Value)) Then
                        ' value 8 is not null but cannot be converted to an Integer
                        If (Me.DgUnitsUp.Rows(i).Cells(4).Value IsNot Nothing AndAlso Not DBNull.Value.Equals(Me.DgUnitsUp.Rows(i).Cells(4).Value)) Then
                            cmd.Parameters.AddWithValue("@tenantregID", DgUnitsUp.Rows(i).Cells(4).Value.ToString())
                        Else
                            ' value 8 is not null but value 4 is null
                        End If
                    Else
                        ' value 8 is null
    
                    End If
    In these two locations - I would like to record a NULL value for the tenantregID field in the database.

    Sorry, I wasn't clear before...

    Thanks

  14. #14
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    11,711

    Re: Extra Value that is EMPTY

    I don't understand your problem? If you want to use a null value for the tenantregID field when those conditions are true, then use a null value.
    "Code is like humor. When you have to explain it, it is bad." - Cory House
    VbLessons | Code Tags | Sword of Fury - Jameram

  15. #15

    Thread Starter
    Lively Member
    Join Date
    Jul 2021
    Posts
    71

    Re: Extra Value that is EMPTY

    Highlighted in RED - I want instead to add to the database a NULL value...

    Thanks dday9

  16. #16
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    11,711

    Re: Extra Value that is EMPTY

    I don't understand. If you want to give it a null value, then set the parameter value to DBNull.Value.
    "Code is like humor. When you have to explain it, it is bad." - Cory House
    VbLessons | Code Tags | Sword of Fury - Jameram

Posting Permissions

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



Click Here to Expand Forum to Full Width