none
Check if a data colum is NULL or not. RRS feed

  • Question

  • The code below is what I'm using.  How do I check if there is data before creating a label.

    Public Class Form1
        Private recipesLst As List(Of Recipe) = New List(Of Recipe)()
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            Dim connString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\\Test.accdb;"
    
            Using cn As OleDbConnection = New OleDbConnection(connString)
                cn.Open()
                Dim cmdText As String = "SELECT RecipeID, RecipeName, OtherData FROM Eiergeregte"
    
                Using cmd As OleDbCommand = New OleDbCommand(cmdText, cn)
                    Dim reader = cmd.ExecuteReader()
    
                    While reader.Read()
                        ListBox1.Items.Add(reader("RecipeName"))
                        recipesLst.Add(New Recipe With {
                            .RecipeID = Convert.ToInt32(reader("RecipeID")),
                            .RecipeName = reader("RecipeName").ToString(),
                            .OtherData = reader("OtherData").ToString()
                        })
                    End While
                End Using
            End Using
        End Sub
        Private Sub ListBox1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ListBox1.SelectedIndexChanged
            Dim recipeSelected As String = ListBox1.SelectedItem.ToString()
            Dim result = recipesLst.Where(Function(x) x.RecipeName = recipeSelected).FirstOrDefault()
            TextBox1.Text = result.RecipeID.ToString()
            TextBox2.Text = result.RecipeName
            TextBox3.Text = result.OtherData
        End Sub
    End Class
    Class Recipe
        Public Property RecipeID As Integer
        Public Property RecipeName As String
        Public Property OtherData As String
    End Class

    Friday, February 14, 2020 9:25 AM

All replies

  • Hi,
    try this:

        Private Sub ListBox1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ListBox1.SelectedIndexChanged
            If ListBox1.SelectedItem Is Nothing Then Exit Sub
            Dim recipeSelected As String = ListBox1.SelectedItem.ToString()
    ...


    --
    Best Regards / Viele Grüße
    Peter Fleischer (former MVP for Developer Technologies)
    Homepage, Tipps, Tricks

    Friday, February 14, 2020 9:54 AM
  • Hello,

    If a value coming back from a SELECT statement does not have data you can set a default value or use Nz in the SELECT as shown here.

    SELECT RecipeID, RecipeName, Nz(OtherData,'(empty)') AS Other FROM Eiergeregte; 

    Then in code use Other for OtherData and note '(empty') can be whatever you want.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Friday, February 14, 2020 12:15 PM
    Moderator
  • Pleas explane how to use Nz
    Friday, February 14, 2020 12:48 PM
  • Pleas explane how to use Nz

    Right here

    https://www.techonthenet.com/access/functions/advanced/nz.php


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Friday, February 14, 2020 1:00 PM
    Moderator
  • Hendri,

    The word null in English does not always mean the same as zero in Afrikaans. Afrikaans is a Lower Saxon language just like Dutch. We often have the same problem. Null in SQL server means an unused column. In VB DBValue.Null

    Die woord null in Engels is nie altyd dieselfde as dieselfde nul Afrikaans. Afrikaans is 'n Neder-Saksiese taal net as Nederlands. Ons het dieselfde probleem. Null in SQL-server is 'n nie-gebruikte kolom nie. In VB DBValue.Null


    Success
    Cor





    Friday, February 14, 2020 5:57 PM
  • Thanks for that Cor.  

    The only Afrikaans I am using is for the parts that will be seen by the user, all the rest is VB programing English. 

    Friday, February 14, 2020 8:46 PM
  • For the novice I am, I came up with this and it seems to work fine.  

    If result.Con14 = "" Then
       MsgBox("Empty")
    Else
       MsgBox("Contains Data")
    End If

    • Proposed as answer by simonb549 Tuesday, February 18, 2020 12:15 PM
    Saturday, February 15, 2020 5:43 PM
  • For the novice I am, I came up with this and it seems to work fine.  

    If result.Con14 = "" Then
       MsgBox("Empty")
    Else
       MsgBox("Contains Data")
    End If

    Well, this simply carries on the idea that everything needs to be done after a query is executed and is wrong as a database is made to handle things like this as I have pointed out. 

    Now you might say this is over my head or I can't understand this, that is what we call learning new things :-)


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Sunday, February 16, 2020 6:00 PM
    Moderator
  • Hi,

    Use DBNull.Value.Equals on the object without converting it to a string.

    Try this code

            If Not DBNull.Value.Equals(row(fieldName)) Then
                ...
            Else
                ...
            End If

    Hope it be helpful.

    Best Regards,

    Julie


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Proposed as answer by simonb549 Tuesday, February 18, 2020 12:15 PM
    Monday, February 17, 2020 2:14 AM
    Moderator