Member Avatar for Robert Walker

I can loop while the datatable is full, but get an error when it is empty. How do I know it's empty.

  • 9 Contributors
  • 10 Replies
  • 7K Views
  • 4 Years Discussion Span
  • Latest Post Latest Post by black_sun191

Dani AI

Generated

Two practical safeguards will prevent the exception you see when the result set is empty and when a column contains NULLs.

  1. Let the adapter tell you if anything was returned. The Fill method returns the number of rows added, so you can bail out before touching Rows(0). Also, if you only expect one result set, fill a DataTable directly (a DataSet has no Rows property; you must go through Tables(0)).
Using myConn As New OleDb.OleDbConnection(connStr),
      myAdapt As New OleDb.OleDbDataAdapter(myQuery, myConn)

    Dim dt As New DataTable()
    Dim added As Integer = myAdapt.Fill(dt)
    If added = 0 Then
        ' No rows returned
        Return
    End If

    Dim r As DataRow = dt.Rows(0)
    tbGATE.Text = If(r.IsNull("dGate"), "", CStr(r("dGate")))
    tbCONC.Text = If(r.IsNull("dConc"), "", CStr(r("dConc")))
    tbPAY.Text = If(r.IsNull("dPayment"), "", CStr(r("dPayment")))

    ' Do not assign a value to DisplayMember; it expects a column name when data-bound.
    ' If you just want to show the value from the row:
    cbWEATHER.Text = If(r.IsNull("dWeather"), "", CStr(r("dWeather")))
End Using

Notes:

  • Fill returning 0 means nothing was added; see DataAdapter.Fill.
  • To check for NULL safely, use DataRow.IsNull as shown; docs: DataRow.IsNull.
  • If you do use a DataSet, access rows via Dim dt = myDataSet.Tables(0) first, or name the table via adapter.Fill(myDataSet, "Results") and use Tables("Results").

Recommended Answers

All 10 Replies

Member Avatar for Paladine

What is the error you are getting?

You could do a test for the number of records and only loop if it is greater than 0.

And how are you looping through the table?

Pseudo-Code:

Dim dr As OleDbDataReader = cmd.ExecuteReader()

Do While dr.Read
...
...
Loop

Hope this helps

Member Avatar for Robert Walker

Dim myConnStr As String = "Provider=Microsoft.jet.OLEDB.4.0;Data Source=" + myPath
Dim myConn As New OleDb.OleDbConnection(myConnStr)
Dim myAdapt As New OleDb.OleDbDataAdapter(myQuery, myConn)

Try
myConn.Open()
Dim myReader As New DataSet
myAdapt.Fill(myReader)

**
** I need to check if myReader is empty
**

tbGATE.Text = myReader.Rows(0)("dGate")
tbCONC.Text = myReader.Rows(0)("dConc")
tbPAY.Text = myReader.Rows(0)("dPayment")

**
** I need to check if myReader.Rows(0)("dWeather") is empty
**

cbWEATHER.DisplayMember = myReader.Rows(0)("dWeather")

myConn.Close()

Catch ex As Exception
MessageBox.Show(ex.Message)
End Try

Member Avatar for Paladine

(For sake of confusion, I would not name my Dataset as myReader - seems to refer to a datareader to someone not knowing what you are coding for.... just some programming edicate I thought I should pass on)

Saying that......

Add the line

If myReader.Tables("dWeather").Rows.Count > 0 Then

Hope this helps

Dim myConnStr As String = "Provider=Microsoft.jet.OLEDB.4.0;Data Source=" + myPath
Dim myConn As New OleDb.OleDbConnection(myConnStr)
Dim myAdapt As New OleDb.OleDbDataAdapter(myQuery, myConn)

Try
myConn.Open()
Dim myReader As New DataSet
myAdapt.Fill(myReader)

**
** I need to check if myReader is empty
**

tbGATE.Text = myReader.Rows(0)("dGate")
tbCONC.Text = myReader.Rows(0)("dConc")
tbPAY.Text = myReader.Rows(0)("dPayment")

**
** I need to check if myReader.Rows(0)("dWeather") is empty
**

cbWEATHER.DisplayMember = myReader.Rows(0)("dWeather")

myConn.Close()

Catch ex As Exception
MessageBox.Show(ex.Message)
End Try

Member Avatar for ESHbyESH

Also might do well to ensure the object itself is not Nothing.
If a SQL call fails, the dataset/table object can remain uninitialized, in which case myReader.Rows will throw an error.

Member Avatar for janet_ss

ds.tables(0).rows.count

janet.

Member Avatar for Comatose

Really Janet.... A Thread From 2005?

Member Avatar for Teme64

Better late than never :D

Member Avatar for keith_mas

Try this

If Not dataset1 Is Nothing Then
.....
... Process dataset
.....
End If

Member Avatar for G_Waddell

You probably can guess this from the previous threads but I use:

dim MyTable as Datatable
'fill datatable....
If (MyTable Is Nothing) Or (MyTable.Rows.Count =0 ) Then
'it's empty
Else
'it exists and there are rows 
End if
Edited by Nick Evan because: Added code-tags
Member Avatar for black_sun191

last one works....thanks man...

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.