vb.net IsDBNull if statement not displaying msgbox

Question

I am trying to check for empty records in an access db and thought that the code I am using would work. What is supposed to happen, is if there are no records for that table in the db, then the msgbox is displayed. However, nothing is displaying when I run the code. Am I using IsDBNull the correct way or is there a better way to do it. I am getting to grips with using params instead of & references and this shall be changed after testing. Many thanks.

Dim con1 As New OleDbConnection("Provider = Microsoft.ACE.OLEDB.12.0;Data Source=C:\domain\test.accdb")    
Dim sql As String
            sql = "SELECT * FROM Departments where Customer = '" & customer & "'"
            If IsDBNull(sql) Then
                MessageBox.Show("No record") <---THIS NOT FIRING
                ' This is our DataAdapter. This executes our SQL Statement above against the Database
                ' we defined in the Connection String
            Else
                Dim adapter As New OleDbDataAdapter(sql, con1)
                ' Gets the records from the table and fills our adapter with those.
                Dim dt As New DataTable("Departments")
                adapter.Fill(dt)
                ' Assigns our DataSource on the DataGridView
                dgv1.DataSource = dt
                '
                Dim sql1 As String
                sql1 = "SELECT * FROM Departments"
                Dim adapter1 As New OleDbDataAdapter(sql1, con1)
                Dim cmd1 As New OleDbCommand(sql1, con1)
                'Dim dt1 As New DataTable("Departments")
                con1.Open()
                Dim myreader As OleDbDataReader = cmd1.ExecuteReader
                myreader.Read()

                con1.Close()
            End If

score 2 · Accepted Answer · answered Oct 25 '13 at 07:48

Yes, you are using IsDBNull completely wrong. The documentation for that method states:

Returns a Boolean value that indicates whether an expression evaluates to the System.DBNull class.

The sql variable you're sending into the IsDBNull method clearly does not evaluate to System.DBNull, as you just set it to some other value yourself.

You use IsDBNull after you've executed your SQL, to check to see if a specific field in your result was NULL. To do what you want, you'll just want to check if the data table has any rows it in after you call Fill.

Dim dt As New DataTable("Departments")
adapter.Fill(dt)
If dt.Rows.Count = 0 Then MessageBox.Show("No record")

Is it possble to have the message after the grid has loaded. Thanks — user1532468, Oct 25 '13 at 08:02

schudel · Answer 2 · 2013-10-25T12:25:31.923

IsDbNull will not call your database. It just tests if the parameter is equal to DbNull.Value which will never be the case if you call it with a string.

you could try this

Dim con1 As New OleDbConnection("Provider = Microsoft.ACE.OLEDB.12.0;Data Source=C:\domain\test.accdb")
Dim sql As String
sql = "SELECT COUNT(*) FROM Departments where Customer = '" & customer & "'"
con1.Open()
Dim cmd1 As New OleDbCommand(sql, con1)
If cmd1.ExecuteScalar() = 0 Then
  MessageBox.Show("No record")
Else
  Dim adapter As New OleDbDataAdapter(sql, con1)
  ' Gets the records from the table and fills our adapter with those.
  Dim dt As New DataTable("Departments")
  adapter.Fill(dt)
  ' Assigns our DataSource on the DataGridView
  dgv1.DataSource = dt
  '
  Dim sql1 As String
  sql1 = "SELECT * FROM Departments"
  Dim adapter1 As New OleDbDataAdapter(sql1, con1)
  cmd1 = New OleDbCommand(sql1, con1)
  'Dim dt1 As New DataTable("Departments")
  Dim myreader As OleDbDataReader = cmd1.ExecuteReader
  myreader.Read()

  con1.Close()
End If

Not sure I understand your answer. Do I move con1 as to where you have it? Thanks — user1532468, Oct 25 '13 at 08:14

vb.net IsDBNull if statement not displaying msgbox

2 Answers2

Linked