-
Sep 20th, 2021, 07:15 AM
#1
Thread Starter
Lively Member
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.
-
Sep 20th, 2021, 08:21 AM
#2
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
-
Sep 20th, 2021, 12:29 PM
#3
Thread Starter
Lively Member
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
-
Sep 20th, 2021, 01:11 PM
#4
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)
-
Sep 21st, 2021, 04:16 AM
#5
Thread Starter
Lively Member
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
-
Sep 21st, 2021, 05:56 AM
#6
Re: Extra Value that is EMPTY
Like this:
Code:
If DBNull.Value.Equals(Me.DgUnitsUp.Rows(i).Cells(8).Value) Then
-
Sep 21st, 2021, 06:38 AM
#7
Thread Starter
Lively Member
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
-
Sep 21st, 2021, 08:16 AM
#8
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
-
Sep 21st, 2021, 09:18 AM
#9
Thread Starter
Lively Member
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
-
Sep 21st, 2021, 10:00 AM
#10
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.
-
Sep 21st, 2021, 10:53 AM
#11
Thread Starter
Lively Member
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
-
Sep 21st, 2021, 11:11 AM
#12
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)
-
Sep 22nd, 2021, 06:59 AM
#13
Thread Starter
Lively Member
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
-
Sep 22nd, 2021, 12:12 PM
#14
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.
-
Sep 22nd, 2021, 02:56 PM
#15
Thread Starter
Lively Member
Re: Extra Value that is EMPTY
Highlighted in RED - I want instead to add to the database a NULL value...
Thanks dday9
-
Sep 22nd, 2021, 03:02 PM
#16
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|